Index Full Scan Vs. Index Range Scan

From: Viral Desai <viral303_at_hotmail.com>
Date: 12 Aug 2002 14:02:30 -0700
Message-ID: <aa3ca283.0208121302.2e0a187a@posting.google.com>

RDBMS Version: 8.1.7.2
Operating System and Version: HP UX 11.0

I need some help understanding the access method that Oracle uses with Partitioned tables.

I have sql code (Please see below) that has a non-partitioned very small table "copa_mdo_srce_sys_map" and a very large partitioned table "FACT_DMNSN_Q3". Both tables, the index, and all partitions are analyzed using dbms_stats.

When I used nested loop join, there is range-scan on the large tables index and partitions are pruned, where as when I use hash join then full index scan occurs. Can someone explain why this is so different? It makes a lot of impact in run time and hash join performance is very bad.

(FYI - hash_area_size is 4194304 and hash_multiblock_io_count is 64. I have tried to change hash_area_size at session level but it has not impacted the method of index scan (full vs. range) on the large table.)

Thanks
Viral

NESTED LOOP

---

select --+use_nl(cm fd) index(fd)
fd.*
  from copa_mdo_srce_sys_map cm

,fact_dmnsn_q3 fd

 where fd.srce_sys_id = cm.srce_sys_id 
   and cm.mdo_id = 'NA' 
   and fd.due_perd = LAST_DAY(TO_DATE('&1', 'YYYYMM')) 

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1333 Card=54869 Bytes=5925852)
1 0 NESTED LOOPS (Cost=1333 Card=54869 Bytes=5925852) 2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9 Bytes=54)
3 1 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=148 Card=73158 Bytes=7462116)
5 4 INDEX (RANGE SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQUE) (Cost=30 Card=73158)

HASH JOIN

---

select --+use_hash(cm fd) index(fd)
fd.*
  from copa_mdo_srce_sys_map cm

,fact_dmnsn_q3 fd

 where fd.srce_sys_id = cm.srce_sys_id 
   and cm.mdo_id = 'NA' 
   and fd.due_perd = LAST_DAY(TO_DATE('&1', 'YYYYMM')); 

Execution Plan

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13103 Card=54869 Bytes=5925852)
1 0 HASH JOIN (Cost=13103 Card=54869 Bytes=5925852) 2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9 Bytes=54)
3 1 PARTITION RANGE (ALL)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=13101 Card=73158 Bytes=7462116)
5 4 INDEX (FULL SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQUE) (Cost=11693 Card=73158) Received on Mon Aug 12 2002 - 16:02:30 CDT