Re: Index Full Scan Vs. Index Range Scan

If a small number of records are needed to create your result set then use nested loops. I wouldn't use hash join here.

There is a reason nested loops and hash joins use different access paths. In your case, with nested loops, as Oracle loops through each record in copa_mdo_srce_sys_map it knows the index key value of the records it wants to retrieve from fact_dmnsn_q3. So it uses range scan.

With a hash join, Oracle first builds a hash table from copa_mdo_srce_sys_map with lots of hash buckets. It then goes through the records from fact_dmnsn_q3 one at a time, calculates its hash value and throws it into one of the hash buckets. As you can see, Oracle doesn't use key values to access fact_dmnsn_q3 so it has to read the entire index. In fact I'm surprised it used full index scan instead of fast full index scan.

viral303_at_hotmail.com (Viral Desai) wrote in message news:<aa3ca283.0208122025.1b8dcf15_at_posting.google.com>...
> 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 Tue Aug 13 2002 - 14:17:47 CDT