Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Creation of a FBI leads to drastic path change and catastrophical perf

Creation of a FBI leads to drastic path change and catastrophical perf

From: Spendius <spendius_at_muchomail.com>
Date: 19 Oct 2006 01:18:03 -0700
Message-ID: <1161245883.313859.216130@e3g2000cwe.googlegroups.com>


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

   9* AND UPPER(dcp.f6) LIKE 'XYZ'
D:\> /

Execution Plan



 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8017 Card=11009 Bytes=2124737)
 1 0 HASH JOIN (OUTER) (Cost=8017 Card=11009 Bytes=2124737)
 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)

(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
Bytes=26759790)

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

   9* AND dcp.f6 LIKE 'XYZ'
D:\> /

Execution Plan



 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=372 Card=19 Bytes=3667)  1 0 NESTED LOOPS (OUTER) (Cost=372 Card=19 Bytes=3667)
 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)

(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
Card=1162071) Received on Thu Oct 19 2006 - 03:18:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US