<Hit Return to Start Example 1a>

### Example 1a ###

### Count of SPRADDR Records ###

### No Hint ###

### Rule Based Optimizer ###

1 select count(*)

2* from spraddr

<Hit Return to Display Explain Plan>

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'SPRADDR'

<Hit Return to Execute Query>

COUNT(*)

---------

663272

real: 4296

<Hit Return to Start Example 1b>

### Example 1b ###

### Count of SPRADDR Records ###

### INDEX Hint ###

### Cost Based Optimizer ###

1 select /*+ INDEX (SPRADDR SPRADDR_KEY_INDEX) */ count(*)

2* from spraddr

<Hit Return to Display Explain Plan>

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE (Cost=101 Card=1)

1 0 SORT (AGGREGATE)

2 1 INDEX (FULL SCAN) OF 'SPRADDR_KEY_INDEX' (UNIQUE) (Cost=101 Card=2000)

<Hit Return to Execute Query>

COUNT(*)

---------

663272

real: 832

<Hit Return to Start Example 2a>

### Example 2a ###

### Count of Mailing Addresses for Active Constituents ###

### No Hint ###

### Rule Based Optimizer ###

1 select count(*)

2 from spriden, spraddr

3 where spriden.spriden_pidm = spraddr.spraddr_pidm

4 and spriden.spriden_change_ind is null

5* and spraddr.spraddr_atyp_code = 'MA'

<Hit Return to Display Explain Plan>

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 SORT (AGGREGATE)

2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'SPRADDR'

4 2 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)

<Hit Return to Execute Query>

COUNT(*)

---------

476417

real: 30585

<Hit Return to Start Example 2b>

### Example 2b ###

### Count of Mailing Addresses for Active Constituents ###

### USE_HASH Hint ###

### Cost Based Optimizer ###

1 select /*+ USE_HASH(SPRIDEN SPRADDR) */ count(*)

2 from spriden, spraddr

3 where spriden.spriden_pidm = spraddr.spraddr_pidm

4 and spriden.spriden_change_ind is null

5* and spraddr.spraddr_atyp_code = 'MA'

<Hit Return to Display Explain Plan>

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE (Cost=7 Card=1 Bytes=31)

1 0 SORT (AGGREGATE)

2 1 HASH JOIN (Cost=7 Card=20 Bytes=620)

3 2 INDEX (FAST FULL SCAN) OF 'SPRADDR_KEY_INDEX' (UNIQUE) (Cost=3 Card=20 Bytes=320)

4 2 INDEX (FAST FULL SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE) (Cost=3 Card=100 Bytes=1500)

<Hit Return to Execute Query>

COUNT(*)

---------

476417

real: 16093

<Hit Return to Start Example 3a>

### Example 3a ###

### Count of Economics Majors in the 1990's ###

### No Hint ###

### Rule Based Optimizer ###

1 select count(distinct spriden.spriden_pidm)

2 from sgbstdn, spriden

3 where (sgbstdn.sgbstdn_majr_code_1 = 'ECON' or sgbstdn.sgbstdn_majr_code_2 = 'ECON')

4 and sgbstdn.sgbstdn_term_code_eff like '199%'

5* and sgbstdn.sgbstdn_pidm = spriden.spriden_pidm

<Hit Return to Execute Query and Display Explain Plan and Summary Execution Statistics>

COUNT(DISTINCTSPRIDEN.SPRIDEN_PIDM)

-----------------------------------

440

real: 41750

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 SORT (GROUP BY)

2 1 FILTER

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SGBSTDN'

4 3 NESTED LOOPS

5 4 TABLE ACCESS (FULL) OF 'SPRIDEN'

6 4 INDEX (RANGE SCAN) OF 'PK_SGBSTDN' (UNIQUE)

7 2 FILTER

8 7 TABLE ACCESS (BY INDEX ROWID) OF 'GORVRAC'

9 8 INDEX (RANGE SCAN) OF 'PK_GORVRAC' (UNIQUE)

10 7 TABLE ACCESS (FULL) OF 'GOBVBSC'

11 7 TABLE ACCESS (BY INDEX ROWID) OF 'GORVMAC'

12 11 INDEX (UNIQUE SCAN) OF 'PK_GORVMAC' (UNIQUE)

13 2 INDEX (UNIQUE SCAN) OF 'PK_GOBVUSR' (UNIQUE)

14 2 TABLE ACCESS (FULL) OF 'GOBVBSC'

15 2 TABLE ACCESS (BY INDEX ROWID) OF 'GOBVUSR'

16 15 INDEX (UNIQUE SCAN) OF 'PK_GOBVUSR' (UNIQUE)

17 2 TABLE ACCESS (BY INDEX ROWID) OF 'GORVMAC'

18 17 INDEX (UNIQUE SCAN) OF 'PK_GORVMAC' (UNIQUE)

19 2 TABLE ACCESS (BY INDEX ROWID) OF 'GORVRAC'

20 19 INDEX (RANGE SCAN) OF 'PK_GORVRAC' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

791864 consistent gets

5061 physical reads

0 redo size

193 bytes sent via SQL*Net to client

250 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

<Hit Return to Start Example 3b>

### Example 3b ###

### Count of Economics Majors in the 1990's ###

### INDEX Hint ###

### Cost Based Optimizer ###

1 select /*+ INDEX (SPRIDEN SPRIDEN_KEY_INDEX) */ count(distinct spriden.spriden_pidm)

2 from sgbstdn, spriden

3 where (sgbstdn.sgbstdn_majr_code_1 = 'ECON' or sgbstdn.sgbstdn_majr_code_2 = 'ECON')

4 and sgbstdn.sgbstdn_term_code_eff like '199%'

5* and sgbstdn.sgbstdn_pidm = spriden.spriden_pidm

<Hit Return to Execute Query and Display Explain Plan and Summary Execution Statistics>

COUNT(DISTINCTSPRIDEN.SPRIDEN_PIDM)

-----------------------------------

440

real: 3746

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE (Cost=61 Card=1 Bytes=64)

1 0 SORT (GROUP BY)

2 1 FILTER

3 2 NESTED LOOPS (Cost=61 Card=1 Bytes=64)

4 3 TABLE ACCESS (FULL) OF 'SGBSTDN' (Cost=60 Card=1 Bytes=51)

5 3 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE) (Cost=1 Card=1 Bytes=13)

6 2 FILTER

7 6 TABLE ACCESS (BY INDEX ROWID) OF 'GORVRAC' (Cost=1 Card=1 Bytes=100)

8 7 INDEX (RANGE SCAN) OF 'PK_GORVRAC' (UNIQUE) (Cost=2 Card=1)

9 6 TABLE ACCESS (FULL) OF 'GOBVBSC' (Cost=2 Card=1 Bytes=4)

10 6 TABLE ACCESS (BY INDEX ROWID) OF 'GORVMAC' (Cost=1 Card=1 Bytes=39)

11 10 INDEX (RANGE SCAN) OF 'PK_GORVMAC' (UNIQUE) (Cost=1 Card=1)

12 2 INDEX (UNIQUE SCAN) OF 'PK_GOBVUSR' (UNIQUE) (Cost=1 Card=1 Bytes=17)

13 2 TABLE ACCESS (FULL) OF 'GOBVBSC' (Cost=2 Card=20 Bytes=40)

14 2 TABLE ACCESS (BY INDEX ROWID) OF 'GOBVUSR' (Cost=1 Card=1 Bytes=37)

15 14 INDEX (RANGE SCAN) OF 'PK_GOBVUSR' (UNIQUE) (Cost=1 Card=8)

16 2 TABLE ACCESS (BY INDEX ROWID) OF 'GORVMAC' (Cost=1 Card=1 Bytes=39)

17 16 INDEX (RANGE SCAN) OF 'PK_GORVMAC' (UNIQUE) (Cost=1 Card=1)

18 2 TABLE ACCESS (BY INDEX ROWID) OF 'GORVRAC' (Cost=1 Card=1 Bytes=37)

19 18 INDEX (RANGE SCAN) OF 'PK_GORVRAC' (UNIQUE) (Cost=2 Card=1)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4812 consistent gets

471 physical reads

0 redo size

193 bytes sent via SQL*Net to client

250 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed