Re: Index Full Scan Vs. Index Range Scan

Thanks for your update.

But I need to understand the cause of the hash join not using range scan on index. The performance deteriorates with hash join in this case, which obviously is due to full scan on the entire index as per the plan.

I guess, I was not clear about my question, here is how I would restate it.. Why Oracle would not do range scan on the index with hash join? (as it does with nested loop join).

Thanks
Viral

Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ulgbm2dlk0933e_at_corp.supernews.com>...
> "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
Received on Mon Aug 12 2002 - 23:25:58 CDT