Shulou(Shulou.com)05/31 Report--

This article mainly introduces "oracle access index method what", in daily operation, I believe many people in oracle access index method what problems exist, small make up consult all kinds of data, sort out simple and easy to use operation method, hope to answer "oracle access index method what" doubts helpful! Next, please follow the small series to learn together!

This article summarizes several ways to access indexes.

1. INDEX UNIQUE SCAN

(1) The result returns at most one record.

Examples:

SQL> create table test.t as select * from dba_objects;

Table created

SQL> create unique index t_ius on t(object_id);

Index created.

SQL> select * from t where object_id=99338;

Execution Plan

----------------------------------------------------------

Plan hash value: 3945981348

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | T_IUS | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

2. INDEX RANGE SCAN

(1) The results of an index range scan may return multiple records

Examples:

SQL> create index t_irs on t(object_id);

Index created.

SQL> select * from t where object_id=99338;

Execution Plan

----------------------------------------------------------

Plan hash value: 735526888

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IRS | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

3. INDEX FULL SCAN

(1)Scan all index rows of all leaf blocks of the target index.

(2)The index full scan results are ordered, avoiding the actual sort operation.

(3)Generally, the index full scan is used to read a single block.

(4)The prerequisite for index sweeping is that the attribute of the key value column of the target index is not null.

Examples:

SQL> select object_id from t where object_id is not null order by object_id;

72920 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3775890202

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 92167 | 1170K| 176 (0)| 00:00:03 |

|* 1 | INDEX FULL SCAN | T_IRS | 92167 | 1170K| 176 (0)| 00:00:03 |

--------------------------------------------------------------------------

4. NDEX FAST FULL SCAN

(1) As with the index full scan, scan all index rows of all leaf blocks of the target index.

(2) Index Fast Full Scan is only applicable to CBO.

(3) The index fast full scan can be read in multiple blocks or executed in parallel.

(4) The results of the index fast full scan are not necessarily ordered.

Examples:

SQL> select object_id from t where object_id is not null;

72920 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 723523614

------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 92167 | 1170K| 49 (0)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| T_IRS | 92167 | 1170K| 49 (0)| 00:00:01 |

---------------------------------------d

5. INDEX SKIP SCAN

(1) Applicable to composite B-tree index

(2) SQL that does not specify query conditions for leading columns of the target index in the where condition and specifies query conditions for non-leading columns of the index can still use the index.

Examples:

SQL> create index t_isc on t(object_id,object_name);

Index created.

SQL> select /*+ index_ss(t t_isc) */ object_name from t where object_name is not null;

72920 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2656528524

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 92167 | 5940K| 429 (1)| 00:00:06 |

|* 1 | INDEX SKIP SCAN | T_ISC | 92167 | 5940K| 429 (1)| 00:00:06 |

--------------------------------------------------------------------------

At this point, the study of "what are the methods of accessing indexes in oracle" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.