* Discussions on execution plan involved merge join and full index scan.


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.



Index scan turned into Full Table scan if I include a Join with a
reference table.

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)