Re: Index scan turns into Full Table scan when use JOIN with another Table on 3 Indexed Fields

What are the definitions of all the indexes and primary key invloved? Looks like index CQ_VPS_USER_CALLS_PI1 is built on stat_date, yes? In this case, the plan looks OK.

In the first query, you have a clear range for stat_date in the where clause, so a range scan was performed on a partitioned local index.

In the 2nd query, the fact table is joined to another dimension table ( I figure this is a warehouse environment?). You can do nested loop, merge join or hash join. The optimizer opted for merge join. Range scan was performed on the unique index INCO_MODEL_DATES_1, this is because of the md.name = 'abc' clause looks like. To avoid sort on the row source from the fact table, full index scan was performed on the primary key. The two row sets were then merged.

You probably can force a range scan on CQ_VPS_USER_CALLS_PI1 by using nested loop hint. You will have to see for yourself if it gives you better performance.

• Jusung Yang

"abc" <abc_at_abc.com.au> wrote in message news:<bbk595$c7j$1_at_mws-stat-syd.cdn.telstra.com.au>...
> Hi All,
>
> Would anyone show me how my SQL with multiple GROUP BY and Sub Selects
> turns from Index scan into Full Table scan if I include a Join with a
> reference table.
>
> I have 2 Queries which I simplify as follows (running on Oracle 8i)
>
> Query 1 : Index scan on t3
> ======
> Select a, b, c, count(*) FROM
> (Select /*+ index(t3) */
> t3.a, t3.b, t3.c
> FROM CQ_VPS_USER_CALLS t3,
> CQ_KEY_VALUE_PAIRS kvp,
> CQ_VPS_PROCESS_LOGS pl
> WHERE t3.stat_date >= '01-MAY-2003'
> AND t3.stat_date < '08-MAY-2003'
> AND t3.final_sec_id = kvp.kvp_id
> AND t3.suppl_batch_id = pl.suppl_batch_id )
> GROUP BY a,b,c
>
> Explain
> SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19745 Card=5771 Bytes=709833)
> WINDOW (SORT)
> SORT (GROUP BY) (Cost=19745 Card=5771 Bytes=709833)
> VIEW (Cost=19638 Card=5771 Bytes=709833)
> SORT (GROUP BY) (Cost=19638 Card=5771 Bytes=657894)
> VIEW (Cost=19438 Card=5771 Bytes=657894)
> SORT (GROUP BY) (Cost=19438 Card=5771 Bytes=340489)
> VIEW (Cost=19383 Card=5771 Bytes=340489)
> WINDOW (BUFFER)
> SORT (GROUP BY) (Cost=19383 Card=5771 Bytes=779085)
> HASH JOIN (Cost=19267 Card=5771 Bytes=779085)
> TABLE ACCESS (FULL) OF CQ_VPS_PROCESS_LOGS (Cost=9
> Card=1215 Bytes=10935)
> HASH JOIN (Cost=19257 Card=475 Bytes=59850)
> HASH JOIN (Cost=9629 Card=2778 Bytes=241686)
> TABLE ACCESS (FULL) OF CQ_KEY_VALUE_PAIRS (Cost=6
> Card=271 Bytes=7859)
> PARTITION RANGE (ITERATOR)
> TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> CQ_VPS_USER_CALLS (Cost=9622 Card=6971 Bytes=404318)
> INDEX (RANGE SCAN) OF CQ_VPS_USER_CALLS_PI1
> (NON-UNIQUE) (Cost=549 Card=6971)
> PARTITION RANGE (ITERATOR)
> TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> CQ_VPS_USER_CALLS (Cost=9622 Card=6971 Bytes=271869)
> INDEX (RANGE SCAN) OF CQ_VPS_USER_CALLS_PI1
> (NON-UNIQUE) (Cost=549 Card=6971)
>
>
> Query 2 : Index scan on t3 is no longer valid once Table md is
> incorporated - see Explain Plan
> with md.start_date, md.end_date and md.name as Indexed
> Fields
>
> Select a, b, c, count(*) FROM
> (Select /*+ index(t3) */
> t3.a, t3.b, t3.c
> FROM CQ_VPS_USER_CALLS t3,
> CQ_KEY_VALUE_PAIRS kvp,
> CQ_VPS_PROCESS_LOGS pl,
> TBCO_MODEL_DATES md
> WHERE t3.stat_date >= md.start_date AND t3.stat_date < md.end_date
> AND md.name = 'abc'
> AND t3.final_sec_id = kvp.kvp_id
> AND t3.suppl_batch_id = pl.suppl_batch_id )
> GROUP BY a,b,c
>
>
> SELECT STATEMENT Optimizer=ALL_ROWS (Cost=492015 Card=6002 Bytes=738246)
> WINDOW (SORT)
> SORT (GROUP BY) (Cost=492015 Card=6002 Bytes=738246)
> VIEW (Cost=491904 Card=6002 Bytes=738246)
> SORT (GROUP BY) (Cost=491904 Card=6002 Bytes=684228)
> VIEW (Cost=491698 Card=6002 Bytes=684228)
> SORT (GROUP BY) (Cost=491698 Card=6002 Bytes=354118)
> VIEW (Cost=491640 Card=6002 Bytes=354118)
> WINDOW (BUFFER)
> SORT (GROUP BY) (Cost=491640 Card=6002 Bytes=1134378)
> HASH JOIN (Cost=491475 Card=6002 Bytes=1134378)
> TABLE ACCESS (FULL) OF CQ_VPS_PROCESS_LOGS (Cost=9
> Card=1215 Bytes=10935)
> NESTED LOOPS (Cost=491465 Card=494 Bytes=88920)
> HASH JOIN (Cost=97539 Card=2834 Bytes=399594)
> TABLE ACCESS (FULL) OF CQ_KEY_VALUE_PAIRS (Cost=6
> Card=271 Bytes=7859)
> NESTED LOOPS (Cost=97532 Card=7110 Bytes=796320)
> MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=54)
> INDEX (RANGE SCAN) OF INCO_MODEL_DATES_1
> (UNIQUE) (Cost=1 Card=1 Bytes=27)
> SORT (JOIN) (Cost=1 Card=1 Bytes=27)
> INDEX (RANGE SCAN) OF INCO_MODEL_DATES_1
> (UNIQUE) (Cost=1 Card=1 Bytes=27)
> PARTITION RANGE (ITERATOR)
> TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> CQ_VPS_USER_CALLS (Cost=97530 Card=2844166 Bytes=164961628)
> INDEX (FULL SCAN) OF CQ_VPS_USER_CALLS_PK
> (UNIQUE) (Cost=95786 Card=2844166)
> PARTITION RANGE (ITERATOR)
> TABLE ACCESS (BY LOCAL INDEX ROWID) OF
> CQ_VPS_USER_CALLS (Cost=139 Card=2844166 Bytes=110922474)
> INDEX (RANGE SCAN) OF CQ_VPS_USER_CALLS_PI1
> (NON-UNIQUE) (Cost=116 Card=2844166)
>
>
>
>
> Many thanks.
Received on Wed Jun 04 2003 - 12:26:52 CDT