Re: Index Full Scan Vs. Index Range Scan

Richard,

I understand the concept that you just mentioned in your last paragraph that in the nested loop the inner table is read multiple times and for each row the large table's index is probed and hence it results in range scan, sounds very logical.

In the hash join, the process knows all the partitions/rows it needs to probe in the large table's index before even the first access is done, yet it does not do range scan, I'm really not convinced.

Does this also mean that Index Range Scan (Selective reading of index partitions/table partitions) can NEVER occur when the table is hash joined with smaller table?

Thanks for your detailed explanation regarding the difference between index full scan and FFS.

Regards,
Viral.

Richard Foote <Richard.Foote_at_oracle.com> wrote in message news:<3D589DF0.23C6A0A3_at_oracle.com>...
> Hi Jusung,
>
> I believe the doco is correct in this case (pleasant change :)
>
> A 'Full Scan' reads the entire index via the index traverse method and
> hence returns the data in sorted order. If all the required columns are
> found in the index there is no need to visit the table, or if a column
> is referenced, the optimizer decides it only needs to visit the table
> infrequently. The optimizer has decided that it's more efficient to read
> all the leaf pages in the index (plus some) one block at a time rather
> than read the entire table, multi blocks at a time.
>
> A 'Fast Full Index Scan' (which I believe Sybrand was referring) will
> read the entire index in the same manner as a Full Table Scan with multi
> block reads. However, as the index blocks are not in the same 'logical'
> sequence as the index's logical structure, the data return will
> effectively be in random order and an additional sort would be required
> (if necessary). The optimizer decides it's more efficient the read the
> entire index via multi block reads because either there is no
> requirement for the data to be ordered or the cost of the savings in the
> multiblock read outweighs the cost of an additional sort.
>
> The reason why the execution plan is using different index accesses is
> because in the nested loop, the inner table is read many times whereas
> in the hash join, the inner table is read the once, then accessed many
> times via the hash join operation.
>
> Cheers
>
> Richard
>
> Jusung Yang wrote:
> >
> > You probably were thinking about FAST Full Index Scan, Sybrand, not
> > Full Index Scan itself. I think FFIS will multiblock read, FIS will
> > not. It has some important implications on sorting performance, that's
> > why I remember it when I read the ORACLE DOC. Never trust the DOC, so
> > I heard. But tell me if this stuff from ORACLE 9i DOC is wrong. I put
> > **** in the place where I think is important.
> >
> > ---------------------
> > Full Scans
> > A full scan is available if a predicate references one of the columns
> > in the index. The predicate does not need to be an index driver. A
> > full scan is also available when there is no predicate, if both the
> > following conditions are met:
> >
> > All of the columns in the table referenced in the query are included
> > in the index.
> > At least one of the index columns is not null.
> > ****A full scan can be used to eliminate a sort operation, because the
> > data is ordered by the index key. It reads the blocks singly.****
> >
> > Fast Full Index Scans
> > Fast full index scans are an alternative to a full table scan when the
> > index contains all the columns that are needed for the query, and at
> > least one column in the index key has the NOT NULL constraint. A fast
> > full scan accesses the data in the index itself, without accessing the
> > table. ****It cannot be used to eliminate a sort operation, because
> > the data is not ordered by the index key. It reads the entire index
> > using multiblock reads (unlike a full index scan) and can be
> > parallelized.****
> >
> > Fast full scan is available only with the CBO. You can specify it with
> > the initialization parameter OPTIMIZER_FEATURES_ENABLE or the
> > INDEX_FFS hint. Fast full index scans cannot be performed against
> > bitmap indexes.
> >
> > **** A fast full scan is faster than a normal full index scan in that
> > it can use multiblock I/O and can be parallelized just like a table
> > scan.****
> >
> > "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 - 08:44:37 CDT