Re: Index Full Scan Vs. Index Range Scan

"Viral Desai" <viral303_at_hotmail.com> wrote in message news:aa3ca283.0208121302.2e0a187a_at_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)

A full index scan reads an index in the same fashion as the full table scan, it starts at block 1 and proceeds. It also uses db_file_multiblock_read_count
A range scan traverses the keys in order. It doesn't use multiblock read ahead.
As you are hashing afterwards, there is no use in performing an index range scan.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address