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)
No comments:
Post a Comment