Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Creation of a FBI leads to drastic path change and catastrophical perf
Hello,
(v. 8.1.7.4)
We have a simple select between a table and a view. There's
only one search criterium on a name field belonging to the
table (this returns a single row) then we join it to the view. So
the optimiser should
1/ first retrieve this row from the table,
2/ and using the ID through a nested loop to retrieve the record
from the view (which happens to be a single row too).
We're not sure the field name (on which there's already a TAB2_NI index) against which we perform the search only contains capitals, thus we've created a 2nd index TAB2_FBI on this field, function-based. AS SOON AS we start using it in our query the optimiser switches from the NL path described above (with a response time of a hundredth of second) to a cascade of hash joins and FTS throughout all tables used in the view definition.
Why ? What can influence the optimiser so drastically so that it ends up choosing an access path *that* bad ?
Thanks.
Details of the different plans (and queries as well):
D:\> SELECT
2 bc.f1, bc.f2, bc.f3, bc.f4, 3 bc.f5, dcp.f6, bc.f7, 4 bc.f8, bc.f9, bc.f10, bc.f11 5 FROM view1 bc, 6 tab2 dcp 7 WHERE 8 bc.VIEW_ID = dcp.TAB_ID
Execution Plan
2 1 HASH JOIN (OUTER) (Cost=6782 Card=11009 Bytes=1706395) 3 2 NESTED LOOPS (Cost=5896 Card=11009 Bytes=1497224) 4 3 HASH JOIN (Cost=5896 Card=11009 Bytes=1442179) 5 4 HASH JOIN (OUTER) (Cost=3800 Card=11170 Bytes=1094660) 6 5 HASH JOIN (OUTER) (Cost=1818 Card=11170 Bytes=781900) 7 6 HASH JOIN (Cost=426 Card=11170 Bytes=446800) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'tab2' (Cost=2 Card=12972 Bytes=311328) 9 8 INDEX (RANGE SCAN) OF 'TAB2_FBI' (NON-UNIQUE)Bytes=26759790)
(Cost=1 Card=12972)
10 7 TABLE ACCESS (FULL) OF 'TAB_VIEW_1' (Cost=383 Card=582001 Bytes=9312016) 11 6 TABLE ACCESS (FULL) OF 'TAB_VIEW_2' (Cost=1279 Card=1165574 Bytes=34967220) 12 5 TABLE ACCESS (FULL) OF 'TAB_VIEW_3' (Cost=1816 Card=1162071 Bytes=32537988) 13 4 TABLE ACCESS (FULL) OF 'TAB_VIEW_4' (Cost=1851 Card=1145837 Bytes=37812621) 14 3 INDEX (UNIQUE SCAN) OF 'CUST_PK' (UNIQUE) 15 2 INDEX (FAST FULL SCAN) OF 'STAT_CUST_FK_I' (NON-UNIQUE)
(Cost=656 Card=1157856 Bytes=21999264)
16 1 TABLE ACCESS (FULL) OF 'TAB_VIEW_5' (Cost=979 Card=704205
D:\> SELECT
2 bc.f1, bc.f2, bc.f3, bc.f4, 3 bc.f5, dcp.f6, bc.f7, 4 bc.f8, bc.f9, bc.f10, bc.f11 5 FROM view1 bc, 6 tab2 dcp 7 WHERE 8 bc.VIEW_ID = dcp.TAB_ID
Execution Plan
2 1 NESTED LOOPS (OUTER) (Cost=296 Card=19 Bytes=3135) 3 2 NESTED LOOPS (OUTER) (Cost=220 Card=19 Bytes=2413) 4 3 NESTED LOOPS (OUTER) (Cost=144 Card=19 Bytes=1843) 5 4 NESTED LOOPS (Cost=87 Card=19 Bytes=1482) 6 5 NESTED LOOPS (Cost=87 Card=19 Bytes=1387) 7 6 NESTED LOOPS (Cost=68 Card=19 Bytes=760) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'tab2' (Cost=24 Card=22 Bytes=528) 9 8 INDEX (RANGE SCAN) OF 'TAB2_NI' (NON-UNIQUE)Card=1162071) Received on Thu Oct 19 2006 - 03:18:03 CDT
(Cost=3 Card=22)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_VIEW_1'
(Cost=2 Card=582001 Bytes=9312016)
11 10 INDEX (RANGE SCAN) OF 'PROR_PRSN_FK_I'
(NON-UNIQUE) (Cost=1 Card=582001)
12 6 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_VIEW_4'
(Cost=1 Card=1145837 Bytes=37812621)
13 12 INDEX (UNIQUE SCAN) OF 'PRSN_PK' (UNIQUE) 14 5 INDEX (UNIQUE SCAN) OF 'CUST_PK' (UNIQUE) 15 4 INDEX (RANGE SCAN) OF 'STAT_CUST_FK_I' (NON-UNIQUE)
(Cost=3 Card=1157856 Bytes=21999264)
16 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_VIEW_2' (Cost=4 Card=1165574 Bytes=34967220) 17 16 INDEX (RANGE SCAN) OF 'CNTC_PROR_FK_I' (NON-UNIQUE)
(Cost=3 Card=1165574)
18 2 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_VIEW_5' (Cost=4 Card=704205 Bytes=26759790) 19 18 INDEX (RANGE SCAN) OF 'ADDR_CNTC_FK_I' (NON-UNIQUE)
(Cost=3 Card=704205)
20 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_VIEW_3' (Cost=4 Card=1162071 Bytes=32537988) 21 20 INDEX (RANGE SCAN) OF 'CNIN_CNTC_FK_I' (NON-UNIQUE) (Cost=3