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

"Spendius" <spendius_at_muchomail.com> wrote in message news:1161245883.313859.216130_at_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)
>

8i is using a selectivity of 1% for

    function(column) = constant.
rather than using the underlying column selectivity or the index distinct_keys - which is what later versions of Oracle can do.

So the predicted number of rows from the first step is 12972 rather than 22 - and that's sufficiently different to make the join a hash join.

> 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)

> 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)

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html