Re: What index access path does INDEX_SS yield ?

"Spendius" <spendius_at_muchomail.com> wrote in message news:1167562699.145774.307620_at_i12g2000cwa.googlegroups.com...
> Sorry, here are a few more details:
>
> Version 10.2.0.2 64-bit on Sun/Solaris.
>
> We are in a DWH star schema (TABLE_1 is a fact, TABLE_2 a
> dimension).
>
> Table TABLE_1 is partitioned and contains 13 millions records.
> Its ID field (used in the WHERE clause) is the 2nd column of
> its primary key, but it's not indexed by itself (this is why
> I'd like to see the response time with a SKIP SCAN access).
>
> TABLE_2 is very small (about 50 rows), TABLE_3 too (51 rows).
>
> The result set is 6-row big.
>
> SQL> l
> 1 select *
> 2 from (
> 3 SELECT [--+ index_ss(TF TABLE_1_PK)]
> 4 ...
> 5 TF.ID id,
> 27 OT....,
> 34 ...
> 35 from TABLE_1 TF,
> 36 TABLE_2 TT,
> 37 TABLE_3 OT
> 38 WHERE TF.OTHE_ID = TT.OTHE_ID
> 39 AND OT.DSCR = TT.DSCR)
> 40* WHERE ID = 5485186
>
> Spontaneous optimizer solution (response time = about 1 mn when rset
> not yet in the buffer):
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29093 Card=57 Bytes=8037)
> 1 0 HASH JOIN (Cost=29093 Card=57 Bytes=8037)
> 2 1 MERGE JOIN (Cost=7 Card=51 Bytes=3672)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3' (TABLE) (Cost=2
> Card=51 Bytes=2346)
> 4 3 INDEX (FULL SCAN) OF 'TABLE_3_I01' (INDEX (UNIQUE)) (Cost=1
> Card=51)
> 5 2 SORT (JOIN) (Cost=5 Card=51 Bytes=1326)
> 6 5 TABLE ACCESS (FULL) OF 'TABLE_2' (TABLE) (Cost=4 Card=51
> Bytes=1326)
> 7 1 PARTITION RANGE (ALL) (Cost=29085 Card=57 Bytes=3933)
> 8 7 TABLE ACCESS (FULL) OF 'TABLE_1' (TABLE) (Cost=29085 Card=57
> Bytes=3933)
>
> When hinted, Oracle switches from a FTS on TABLE_1 to a full scan
> of its PK (only index on this table, global) (resp. time = 10 mn):
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2476880 Card=57
> Bytes=8037)
> 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TABLE_1' (TABLE)
> (Cost=48572 Card=1 Bytes=69)
> 2 1 NESTED LOOPS (Cost=2476880 Card=57 Bytes=8037)
> 3 2 MERGE JOIN (Cost=7 Card=51 Bytes=3672)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3' (TABLE) (Cost=2
> Card=51 Bytes=2346)
> 5 4 INDEX (FULL SCAN) OF 'TABLE_3_I01' (INDEX (UNIQUE))
> (Cost=1 Card=51)
> 6 3 SORT (JOIN) (Cost=5 Card=51 Bytes=1326)
> 7 6 TABLE ACCESS (FULL) OF 'TABLE_2' (TABLE) (Cost=4 Card=51
> Bytes=1326)
> 8 2 INDEX (FULL SCAN) OF 'TABLE_1_PK' (INDEX (UNIQUE))
> (Cost=48565 Card=6)
>
> Thanks.
>

Judging from the costing of the skip-scan in the other posting
>> SS sel: 4.3720e-06 ANDV (#skips): 4378945
the number of distinct value for the first column is huge - so the cost of a skip scan would be prohibitive - which is why Oracle has fallen back on the full scan.

 I don't know why the optimizer has decided to do this - but perhaps part of the algorithm for skip scans converts them to full scans if the cost exceeds some limit. I'd take this up with Oracle.

To simplify your test case, it looks as if a sime

    select from table1 where id = constant should show the same behaviour.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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

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