Re: Index Full Scan Vs. Index Range Scan

Sybrand Bakker wrote:

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

Not true (regarding the index scan).

A full index scan reads the leaf blocks of the index, in the index-key order, reading physcally one block at a time (if block not in the buffer cache). Version dependent, there might be some intelligent read-ahead behaviour.
A index FAST full scan reads up to db_file_multiblock_read_count blocks, like a full table scan, ignoring branch blocks. The index_ffs hint proposes a fast full scan to the optimiser. If choosen, it is clearly shown in the plan.

Kind Regards,

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