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

From: abc <abc_at_abc.com.au>
Date: Wed, 4 Jun 2003 16:57:34 +1000
Message-ID: <bbk595$c7j$1@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 - 01:57:34 CDT