Re: What index access path does INDEX

From: Spendius <spendius_at_muchomail.com>
Date: 31 Dec 2006 02:58:19 -0800
Message-ID: <1167562699.145774.307620@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. Received on Sun Dec 31 2006 - 04:58:19 CST

Re: What index access path does INDEX_SS yield ?