spool netech_hints_queries.out
set pagesize 9999
set linesize 120
column plan_plus_exp format a100
set autotrace traceonly explain
set timing off
prompt
pause <Hit Return to Start Example 1a>
prompt ### Example
1a ###
prompt ### Count
of SPRADDR Records ###
prompt ### No Hint
###
prompt ### Rule
Based Optimizer ###
select count(*)
from spraddr
list
prompt
pause <Hit Return to Display Explain Plan>
/
pause <Hit Return to Execute Query>
set autotrace off
set timing on
/
set autotrace traceonly explain
set timing off
prompt
pause <Hit Return to Start Example 1b>
prompt ### Example
1b ###
prompt ### Count
of SPRADDR Records ###
prompt ### INDEX
Hint ###
prompt ### Cost
Based Optimizer ###
select /*+ INDEX (SPRADDR SPRADDR_KEY_INDEX) */ count(*)
from spraddr
list
prompt
pause <Hit Return to Display Explain Plan>
/
pause <Hit Return to Execute Query>
set autotrace off
set timing on
/
set autotrace traceonly explain
set timing off
prompt
pause <Hit Return to Start Example 2a>
prompt ### Example
2a ###
prompt ### Count
of Mailing Addresses for Active Constituents ###
prompt ### No Hint
###
prompt ### Rule
Based Optimizer ###
select count(*)
from spriden, spraddr
where spriden.spriden_pidm = spraddr.spraddr_pidm
and spriden.spriden_change_ind is null
and spraddr.spraddr_atyp_code = 'MA'
list
prompt
pause <Hit Return to Display Explain Plan>
/
pause <Hit Return to Execute Query>
set autotrace off
set timing on
/
set autotrace traceonly explain
set timing off
prompt
pause <Hit Return to Start Example 2b>
prompt ### Example
2b ###
prompt ### Count
of Mailing Addresses for Active Constituents ###
prompt ###
USE_HASH Hint ###
prompt ### Cost Based
Optimizer ###
select /*+ USE_HASH(SPRIDEN SPRADDR) */ count(*)
from spriden, spraddr
where spriden.spriden_pidm = spraddr.spraddr_pidm
and spriden.spriden_change_ind is null
and spraddr.spraddr_atyp_code = 'MA'
list
prompt
pause <Hit Return to Display Explain Plan>
/
pause <Hit Return to Execute Query>
set autotrace off
set timing on
/
set autotrace on
set timing on
prompt
pause <Hit Return to Start Example 3a>
prompt ### Example
3a ###
prompt ### Count
of Economics Majors in the 1990's ###
prompt ### No Hint
###
prompt ### Rule
Based Optimizer ###
select count(distinct spriden.spriden_pidm)
from sgbstdn, spriden
where (sgbstdn.sgbstdn_majr_code_1 = 'ECON' or
sgbstdn.sgbstdn_majr_code_2 = 'ECON')
and sgbstdn.sgbstdn_term_code_eff like '199%'
and sgbstdn.sgbstdn_pidm = spriden.spriden_pidm
list
prompt
pause <Hit Return to Execute Query and Display Explain
Plan and Summary Execution Statistics>
/
set autotrace on
set timing on
prompt
pause <Hit Return to Start Example 3b>
prompt ### Example
3b ###
prompt ### Count
of Economics Majors in the 1990's ###
prompt ### INDEX
Hint ###
prompt ### Cost
Based Optimizer ###
select /*+ INDEX (SPRIDEN SPRIDEN_KEY_INDEX) */
count(distinct spriden.spriden_pidm)
from sgbstdn, spriden
where (sgbstdn.sgbstdn_majr_code_1 = 'ECON' or
sgbstdn.sgbstdn_majr_code_2 = 'ECON')
and sgbstdn.sgbstdn_term_code_eff like '199%'
and sgbstdn.sgbstdn_pidm = spriden.spriden_pidm
list
prompt
pause <Hit Return to Execute Query and Display Explain
Plan and Summary Execution Statistics>
/
spool off