Re: Index Full Scan Vs. Index Range Scan

Viral Desai wrote:

>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)

Viral,

I think you should remove the hint 'index(fd)'. It specifies the access method for the fact_dmndn_q3 table. I guess that the due_perd column is NOT the leading column of the index? Please post index details.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv. Received on Tue Aug 13 2002 - 07:52:22 CDT