Feeds:
Posts
Comments

Posts Tagged ‘index scan’

In case of index scan a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.

The index contains not only the indexed value, but also the rowids of rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a cluster scan.

An index scan can be various types like

1)Index Unique Scans
2)Index Range Scans
3)Index Range Scans Descending
4)Index Skip Scans
5)Full Scans
6)Fast Full Index Scans
7)Index Joins
8)Bitmap Indexes


1)Index Unique Scans
———————————————-

This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

This access path is used when all columns of a unique (B-tree) index or an index created as a result of a primary key constraint are specified with equality conditions. There is an example in later of this section.

2)Index Range Scans
—————————————————-

•An index range scan is a common operation for accessing selective data.

•Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

•If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions like col1=1 or col1<1 or col1>1 or (col1=1 AND col1=99 AND ..)

•Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.

•The hint INDEX(table_alias index_name) instructs the optimizer to use a specific index.

•Note that leading wildcards like %text does not result range scan but text% might result range scan.
Look at following examples, 88% used range scans but %88 did not used range scans.

SQL> create table table_a(n number ,k varchar2(15));

Table created.

SQL> begin
for i in 1 .. 10000
loop
insert into table_a values(i,’pc-‘||round(dbms_random.value(1,20000),0));
end loop;
end;
/

2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> create index table_a_I_K on table_a(k);

Index created.

SQL> select * from table_a where k like ‘88%’;

no rows selected

Execution Plan
———————————————————-
Plan hash value: 1124802227

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 22 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TABLE_A_I_K | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“K” LIKE ‘88%’)
filter(“K” LIKE ‘88%’)

Note
—–
– dynamic sampling used for this statement

SQL> select * from table_a where k like ‘%88’;
102 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1923776651

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 102 | 2244 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TABLE_A | 102 | 2244 | 8 (0)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“K” LIKE ‘%88’)

Note
—–
– dynamic sampling used for this statement

3)Index Range Scans Descending
——————————————————

•An index range scan descending is identical to an index range scan, except that the data is returned in descending order.

•Indexes, by default, are stored in ascending order.

•The optimizer uses index range scan descending when an order by descending clause can be satisfied by an index.

•The hint INDEX_DESC(table_alias index_name) is used for index range scan descending.

Example:
————————————-
SQL> select * from table_a where k like ‘8888%’;

8 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1124802227

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TABLE_A_I_K | 1 | | 2 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“K” LIKE ‘8888%’)
filter(“K” LIKE ‘8888%’)
SQL> select /*+index_desc(table_a)*/ * from table_a where k like ‘8888%’;

8 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3364135956

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TABLE_A | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| TABLE_A_I_K | 1 | | 2 (0)| 00:00:01 |
——————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“K” LIKE ‘8888%’)
filter(“K” LIKE ‘8888%’ AND “K” LIKE ‘8888%’)

4)Index Skip Scans:
—————————–

Discussed in
5)Full Scans
——————————

Discussed in
6)Fast Full Index Scans
—————————————–

Discussed in
7)Index Joins
——————————————

Discussed in
8)Bitmap Indexes
———————————————

Discussed in
To illustrate an example create a table and make it’s column primary key. Now put the indexed column in the where clause with an equality operator. Note that index unique scan will be used.


SQL> create table test_tab2 as select level col1, level col2 from dual connect by level<=100;

Table created.
Case 1: No index, so full table scan will performed.
——————————————————————————–
SQL> select * from test_tab2 where col1=99;

Execution Plan
———————————————————-
Plan hash value: 700767796

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TAB2 | 1 | 26 | 3 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“COL1″=99)

Note
—–
– dynamic sampling used for this statement

Create non-unique index on the table.

SQL> create index test_tab2_I on test_tab2(col1);
Index created.

Case 2: As on col1 there is non-unique index so range scan will be performed.
————————————————————————————————-
SQL> select * from test_tab2 where col1=99;

Execution Plan
———————————————————-
Plan hash value: 465564947

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB2_I | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“COL1″=99)

Note
—–
– dynamic sampling used for this statement

Now drop the index and add primary key on the table.
SQL> drop index test_tab2_I;
Index dropped.

SQL> alter table
2 test_tab2 add primary key(col1);

Table altered.

Case 3: Adding primary key with equality operation on column causes to use index unique scan.
——————————————————————————

SQL> select * from test_tab2 where col1=99;

Execution Plan
———————————————————-
Plan hash value: 1384425796

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB2 | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C006487 | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“COL1″=99)

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

A)Index Skip Scan
—————————————-

As the name suggest index skip scan does not scan complete index. But it scan of the subindexes.

Index skip scan lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Suppose if I make a make a composite index with two columns sex and id. The leading column sex contains only two distinct columns. Now if I query with non-leading column that is with id column then index skip scan will be used.

Example:
——————

SQL> create table test_skip_scan (sex varchar2(1), id number, address varchar2(20));
Table created.

SQL> create index test_skip_scan_I on test_skip_scan(sex,id);

Index created.

SQL> begin
for i in 1 .. 10000
loop
insert into test_skip_scan values(decode(remainder(abs(round(dbms_random.value(2,20),0)),2),0,’M’,’F’),i,null);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> analyze table test_skip_scan estimate statistics;
Table analyzed.

SQL> select * from test_skip_scan where id=1;

Execution Plan
———————————————————-
Plan hash value: 2410156502

————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SKIP_SCAN | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_SKIP_SCAN_I | 1 | | 3 (0)| 00:00:01 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access(“ID”=1)
filter(“ID”=1)

B)Index Fast Full Scan
—————————————

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 index scans cannot be performed against bitmap indexes.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.

Example:
—————–
SQL> select /*+INDEX_FFS(test_skip_scan)*/ sex,id from test_skip_scan;

10000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 4280781105

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| TEST_SKIP_SCAN_I | 10000 | 40000 | 7 (0)| 00:00:01 |
—————————————————————————————–


C)Index Joins
————————-

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

You can specify an index join with the INDEX_JOIN hint. For more information on the INDEX_JOIN hint.

SQL> select sex,id from test_skip_scan where id in (select col1 from test_tab);

1000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1059662925

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 999 | 6993 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 999 | 6993 | 9 (12)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| TEST_TAB_I | 1000 | 3000 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST_SKIP_SCAN | 10000 | 40000 | 6 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“ID”=”COL1”)

D)Bitmap Indexes
———————————–

A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

Bitmap indexes and bitmap join indexes are available only if you have purchased the Oracle Enterprise Edition.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »