| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: query performance
This is the whole explain plan for TEST and DEVL.
TEST SELECT STATEMENT Optimizer=CHOOSE (Cost=280908 Card=359135 Bytes=181363175) SORT (UNIQUE) (Cost=280908 Card=359135 Bytes=181363175) NESTED LOOPS (Cost=135732 Card=359135 Bytes=181363175)
NESTED LOOPS (OUTER) (Cost=48138 Card=14599 Bytes=7328698)
NESTED LOOPS (OUTER) (Cost=33539 Card=14599 Bytes=7241104)
NESTED LOOPS (OUTER) (Cost=18940 Card=14599 Bytes=6992921)
NESTED LOOPS (OUTER) (Cost=18940 Card=14599 Bytes=6759337)
NESTED LOOPS (OUTER) (Cost=11323 Card=2539 Bytes=868338)
NESTED LOOPS (OUTER) (Cost=8784 Card=2539 Bytes=825175)
NESTED LOOPS (OUTER) (Cost=6514 Card=2270 Bytes=719590)
NESTED LOOPS (OUTER) (Cost=6514 Card=2270 Bytes=660570)
NESTED LOOPS (OUTER) (Cost=6514 Card=2270
Bytes=601550)
NESTED LOOPS (OUTER) (Cost=6514 Card=2270
Bytes=542530)
NESTED LOOPS (OUTER) (Cost=4244 Card=2270
Bytes=483510)
NESTED LOOPS (OUTER) (Cost=1974 Card=2270
Bytes=424490)
NESTED LOOPS (OUTER) (Cost=663 Card=437
Bytes=77349)
NESTED LOOPS (OUTER) (Cost=321 Card=114
Bytes=19038)
NESTED LOOPS (OUTER) (Cost=249 Card=24
Bytes=3768)
NESTED LOOPS (OUTER) (Cost=225 Card=24
Bytes=3576)
NESTED LOOPS (OUTER) (Cost=201 Card=24
Bytes=3408)
NESTED LOOPS (OUTER) (Cost=177
Card=24 Bytes=3216)
NESTED LOOPS (OUTER) (Cost=177
Card=24 Bytes=3048)
NESTED LOOPS (OUTER) (Cost=177
Card=24 Bytes=2832)
NESTED LOOPS (OUTER) (Cost=177
Card=24 Bytes=2664)
NESTED LOOPS (OUTER) (Cost=1
77 Card=24 Bytes=2520)
MERGE JOIN (Cost=9 Card=24
Bytes=2136)
SORT (JOIN) (Cost=7
Card=19 Bytes=684)
HASH JOIN (OUTER)
(Cost=5 Card=19 Bytes=684)
TABLE ACCESS (FULL)
OF SUBM (Cost=1 Card=19 Bytes=494)
VIEW (Cost=3 Card=11
Bytes=110)
HASH JOIN (OUTER)
(Cost=3 Card=11 Bytes=737)
NESTED LOOPS
(Cost=1 Card=11 Bytes=572)
TABLE ACCESS
(FULL) OF OFFICE (Cost=1 Card=83 Bytes=3984)
INDEX (UNIQUE
SCAN) OF PDCR_PK (UNIQUE)
TABLE ACCESS
(FULL) OF OFFICE_AKA (Cost=1 Card=25 Bytes=375)
SORT (JOIN) (Cost=3
Card=24 Bytes=1272)
TABLE ACCESS (FULL) OF
QUOTE (Cost=1 Card=24 Bytes=1272)
VIEW (Cost=7 Card=5
Bytes=80)
HASH JOIN (Cost=7 Card=5
Bytes=235)
HASH JOIN (Cost=5
Card=17 Bytes=663)
HASH JOIN (Cost=3
Card=17 Bytes=527)
TABLE ACCESS
(FULL) OF CTAC_AKA (Cost=1 Card=17 Bytes=153)
TABLE ACCESS
(FULL) OF CTAC (Cost=1 Card=133 Bytes=2926)
TABLE ACCESS (FULL)
OF CTAC_ROLE (Cost=1 Card=135 Bytes=1080)
TABLE ACCESS (FULL) OF
QUOTE_ROLE (Cost=1 Card=40 Bytes=320)
INDEX (UNIQUE SCAN) OF
PROC_STATUS_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF
POL_STATUS_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF PROG_PK
(UNIQUE)
INDEX (UNIQUE SCAN) OF LINE_PK
(UNIQUE)
TABLE ACCESS (FULL) OF QUOTE_CONTR
(Cost=1 Card=19 Bytes=152)
TABLE ACCESS (FULL) OF QUOTE_CARR
(Cost=1 Card=17 Bytes=119)
TABLE ACCESS (BY INDEX ROWID) OF
MKT_CARR (Cost=1 Card=13 Bytes=104)
INDEX (UNIQUE SCAN) OF MKT_CARR_PK
(UNIQUE)
VIEW (Cost=3 Card=19 Bytes=190)
HASH JOIN (OUTER) (Cost=3 Card=19
Bytes=1273)
NESTED LOOPS (Cost=1 Card=10
Bytes=520)
TABLE ACCESS (FULL) OF OFFICE
(Cost=1 Card=83 Bytes=3984)
INDEX (UNIQUE SCAN) OF BRANCH_PK
(UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA
(Cost=1 Card=25 Bytes=375)
VIEW (Cost=3 Card=23 Bytes=230)
HASH JOIN (OUTER) (Cost=3 Card=23
Bytes=1541)
NESTED LOOPS (Cost=1 Card=14 Bytes=728)
TABLE ACCESS (FULL) OF OFFICE (Cost=1
Card=83 Bytes=3984)
INDEX (UNIQUE SCAN) OF CARR_PK
(UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA
(Cost=1 Card=25 Bytes=375)
VIEW (Cost=3 Card=44 Bytes=440)
HASH JOIN (OUTER) (Cost=3 Card=44
Bytes=2948)
NESTED LOOPS (Cost=1 Card=23 Bytes=1196)
TABLE ACCESS (FULL) OF OFFICE (Cost=1
Card=83 Bytes=3984)
INDEX (UNIQUE SCAN) OF MKT_PK (UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA (Cost=1
Card=25 Bytes=375)
TABLE ACCESS (BY INDEX ROWID) OF OFFICE (Cost=1
Card=83 Bytes=2158)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF OFFICE (Cost=1
Card=83 Bytes=2158)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF OFFICE_ADDR (Cost=1
Card=85 Bytes=680)
INDEX (RANGE SCAN) OF OFFICE_ADDR_OFFICE_FK (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF ADDR (Cost=1 Card=106
Bytes=1802)
INDEX (UNIQUE SCAN) OF ADDR_PK (UNIQUE)
VIEW (Cost=3 Card=69 Bytes=8349)
HASH JOIN (OUTER) (Cost=3 Card=69 Bytes=4623)
NESTED LOOPS (Cost=1 Card=36 Bytes=1872)
TABLE ACCESS (FULL) OF OFFICE (Cost=1 Card=83
Bytes=3984)
INDEX (UNIQUE SCAN) OF INSD_PK (UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA (Cost=1 Card=25
Bytes=375)
INDEX (UNIQUE SCAN) OF ST_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF ADDR (Cost=1 Card=106 Bytes=1802)
INDEX (UNIQUE SCAN) OF ADDR_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF ST (Cost=1 Card=104 Bytes=624)
INDEX (UNIQUE SCAN) OF ST_PK (UNIQUE)
VIEW
UNION-ALL
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
DEVL SELECT STATEMENT Optimizer=CHOOSE (Cost=7001 Card=4969 Bytes=5371489) SORT (UNIQUE) (Cost=4198 Card=4969 Bytes=5371489) NESTED LOOPS (Cost=1395 Card=4969 Bytes=5371489)
HASH JOIN (OUTER) (Cost=183 Card=202 Bytes=217756)
HASH JOIN (OUTER) (Cost=164 Card=202 Bytes=212302)
NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=204828)
NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=194324)
NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=183820)
NESTED LOOPS (OUTER) (Cost=145 Card=202 Bytes=173316)
HASH JOIN (OUTER) (Cost=145 Card=202 Bytes=169276)
HASH JOIN (OUTER) (Cost=127 Card=202 Bytes=165438)
HASH JOIN (OUTER) (Cost=112 Card=202 Bytes=160186)
HASH JOIN (OUTER) (Cost=97 Card=202 Bytes=154934)
NESTED LOOPS (OUTER) (Cost=82 Card=202
Bytes=149682)
NESTED LOOPS (OUTER) (Cost=82 Card=202
Bytes=145642)
NESTED LOOPS (OUTER) (Cost=82 Card=202
Bytes=140188)
HASH JOIN (OUTER) (Cost=82 Card=202
Bytes=134734)
HASH JOIN (OUTER) (Cost=67 Card=202
Bytes=130896)
HASH JOIN (OUTER) (Cost=52 Card=202
Bytes=127058)
HASH JOIN (Cost=33 Card=202
Bytes=122008)
NESTED LOOPS (OUTER) (Cost=25
Card=82 Bytes=35014)
HASH JOIN (OUTER) (Cost=25 Card=82
Bytes=30586)
HASH JOIN (OUTER) (Cost=21
Card=82 Bytes=27552)
HASH JOIN (OUTER) (Cost=17
Card=82 Bytes=25420)
HASH JOIN (OUTER) (Cost=13
Card=82 Bytes=21156)
HASH JOIN (OUTER) (Cost=9
Card=82 Bytes=16892)
HASH JOIN (OUTER)
(Cost=5 Card=82 Bytes=6232)
TABLE ACCESS (FULL) OF
SUBM (Cost=1 Card=82 Bytes=4674)
VIEW (Cost=3 Card=67
Bytes=1273)
HASH JOIN (OUTER)
(Cost=3 Card=67 Bytes=10452)
NESTED LOOPS
(Cost=1 Card=67 Bytes=6968)
TABLE ACCESS
(FULL) OF OFFICE (Cost=1 Card=82 Bytes=7462)
INDEX (UNIQUE
SCAN) OF PDCR_PK (UNIQUE)
TABLE ACCESS
(FULL) OF OFFICE_AKA (Cost=1 Card=82 Bytes=4264)
VIEW (Cost=3 Card=67
Bytes=8710)
HASH JOIN (OUTER)
(Cost=3 Card=67 Bytes=10452)
NESTED LOOPS (Cost=1
Card=67 Bytes=6968)
TABLE ACCESS
(FULL) OF OFFICE (Cost=1 Card=82 Bytes=7462)
INDEX (UNIQUE
SCAN) OF INSD_PK (UNIQUE)
TABLE ACCESS (FULL)
OF OFFICE_AKA (Cost=1 Card=82 Bytes=4264)
TABLE ACCESS (FULL) OF
OFFICE (Cost=1 Card=82 Bytes=4264)
TABLE ACCESS (FULL) OF
OFFICE (Cost=1 Card=82 Bytes=4264)
TABLE ACCESS (FULL) OF
OFFICE_ADDR (Cost=1 Card=82 Bytes=2132)
TABLE ACCESS (FULL) OF ADDR
(Cost=1 Card=82 Bytes=3034)
INDEX (UNIQUE SCAN) OF ST_PK
(UNIQUE)
TABLE ACCESS (FULL) OF QUOTE (Cost=1
Card=246 Bytes=43542)
VIEW (Cost=7 Card=45 Bytes=1125)
HASH JOIN (Cost=7 Card=45
Bytes=7515)
HASH JOIN (Cost=5 Card=55
Bytes=6325)
HASH JOIN (Cost=3 Card=67
Bytes=3484)
TABLE ACCESS (FULL) OF
QUOTE_ROLE (Cost=1 Card=82 Bytes=2132)
TABLE ACCESS (FULL) OF
CTAC_ROLE (Cost=1 Card=82 Bytes=2132)
TABLE ACCESS (FULL) OF CTAC
(Cost=1 Card=82 Bytes=5166)
TABLE ACCESS (FULL) OF CTAC_AKA
(Cost=1 Card=82 Bytes=4264)
VIEW (Cost=3 Card=67 Bytes=1273)
HASH JOIN (OUTER) (Cost=3 Card=67
Bytes=10452)
NESTED LOOPS (Cost=1 Card=67
Bytes=6968)
TABLE ACCESS (FULL) OF OFFICE
(Cost=1 Card=82 Bytes=7462)
INDEX (UNIQUE SCAN) OF BRANCH_PK
(UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA
(Cost=1 Card=82 Bytes=4264)
VIEW (Cost=3 Card=67 Bytes=1273)
HASH JOIN (OUTER) (Cost=3 Card=67
Bytes=10452)
NESTED LOOPS (Cost=1 Card=67
Bytes=6968)
TABLE ACCESS (FULL) OF OFFICE
(Cost=1 Card=82 Bytes=7462)
INDEX (UNIQUE SCAN) OF MKT_PK
(UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA
(Cost=1 Card=82 Bytes=4264)
INDEX (UNIQUE SCAN) OF POL_STATUS_PK
(UNIQUE)
INDEX (UNIQUE SCAN) OF PROC_STATUS_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF PROG_PK (UNIQUE)
TABLE ACCESS (FULL) OF QUOTE_CONTR (Cost=1 Card=82
Bytes=2132)
TABLE ACCESS (FULL) OF QUOTE_CARR (Cost=1 Card=82
Bytes=2132)
TABLE ACCESS (FULL) OF MKT_CARR (Cost=1 Card=82
Bytes=2132)
VIEW (Cost=3 Card=67 Bytes=1273)
HASH JOIN (OUTER) (Cost=3 Card=67 Bytes=10452)
NESTED LOOPS (Cost=1 Card=67 Bytes=6968)
TABLE ACCESS (FULL) OF OFFICE (Cost=1 Card=82
Bytes=7462)
INDEX (UNIQUE SCAN) OF CARR_PK (UNIQUE)
TABLE ACCESS (FULL) OF OFFICE_AKA (Cost=1 Card=82
Bytes=4264)
INDEX (UNIQUE SCAN) OF LINE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
INDEX (UNIQUE SCAN) OF OFFICE_PK (UNIQUE)
TABLE ACCESS (FULL) OF ADDR (Cost=1 Card=82 Bytes=3034)
TABLE ACCESS (FULL) OF ST (Cost=1 Card=82 Bytes=2214)
VIEW
UNION-ALL
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)
Thanks
-- Jim Poe (jpoe_at_fulcrumit.com)Received on Thu Oct 11 2001 - 10:07:12 CDT
 |
 |