Re: Index Full Scan Vs. Index Range Scan

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 12 Aug 2002 22:22:24 -0700
Message-ID: <42ffa8fa.0208122122.73d43fc7@posting.google.com>

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 - 00:22:24 CDT