Analysis of 5 kinds of index access methods in Oracle CBO optimization model _oracle

This article mainly discusses the following kinds of index access methods:

1. Indexing unique Scan (Index unique SCAN)
2. Indexing range Scan (Index range SCAN)
3. Index full Scan (SCAN)
4. Indexing skip Scan (index skipping SCAN)
5. Index fast full Scan (index fast fully SCAN)

Indexed unique Scan (index unique SCAN)

The characteristic of accessing data through this index is that only one row of data is returned for a particular value, which is usually selected if the column in the query predicate uses the UNIQE and primary key indexes as a condition; the height of the access is always the height of the index plus one, except for some special cases, such as a different stored lob object.

Copy Code code as follows:

Sql> Set Autotrace traceonly explain


Sql> SELECT * from hr.employees where employee_id = 100;

Execution Plan
----------------------------------------------------------
Plan Hash value:1833546154

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


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


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


| 0 |               SELECT STATEMENT |     |    1 |     69 | 1 (0) | 00:00:01 |


|  1 | TABLE ACCESS by INDEX rowid|     EMPLOYEES |    1 |     69 | 1 (0) | 00:00:01 |


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


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

predicate information (identified by Operation ID):
---------------------------------------------------

2-access ("employee_id" =100)

Index range Scan (index range SCAN)

An index range scan is used when the predicate contains conditions that will return a certain range of data, and the index can be unique or not unique; The specified condition can be an operator such as (<,>,like,between,=), but when using like, if you use a wildcard character%, It is very likely that the scope scan will not be used because the conditions are too broad, and here is an example:

Copy Code code as follows:

Sql&gt; SELECT * from hr.employees where department_id = 30;

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:2056577954

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


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


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


| 0 |                   SELECT STATEMENT |     |   6 |     414 | 2 (0) | 00:00:01 |


|  1 | TABLE ACCESS by INDEX rowid|     EMPLOYEES |   6 |     414 | 2 (0) | 00:00:01 |


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


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

predicate information (identified by Operation ID):
---------------------------------------------------

2-access ("department_id" =30)

Statistics


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


8 Recursive calls


0 db Block gets


7 Consistent gets


1 physical Reads


0 Redo Size


1716 Bytes sent via sql*net to client


523 Bytes received via sql*net from client


2 sql*net roundtrips To/from Client


0 Sorts (memory)


0 Sorts (disk)


6 Rows processed

The condition of the range scan requires accurate analysis of the number of returned data, and the greater the scope, the more likely it is to perform a full table scan;

Copy Code code as follows:

Sql&gt; Select Department_id,count (*) from Hr.employees GROUP by department_id The ORDER by Count (*);

department_id COUNT (*)


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


10 1


40 1


1


70 1


20 2


110 2


90 3


60 5


30 6


100 6


80 34


50 45

Rows selected.

--Use the most 50来 to perform a range scan here
Sql> Set Autotrace traceonly explain
Sql> SELECT * from hr.employees where department_id = 50;

Selected rows.

Execution Plan
----------------------------------------------------------
Plan Hash value:1445457117

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 |           SELECT STATEMENT |    |  45 |     3105 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS full|    EMPLOYEES |  45 |     3105 | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter ("department_id" =50)

Statistics


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


0 Recursive calls


0 db Block gets


Ten consistent gets


0 physical Reads


0 Redo Size


4733 Bytes sent via sql*net to client


545 Bytes received via sql*net from client


4 sql*net roundtrips To/from Client


0 Sorts (memory)


0 Sorts (disk)


Processed rows

You can see that the optimizer performs a full table scanning method when obtaining a larger range of data.

One optimization method for index-range scans is to use ascending indexes to get rows of data in descending order, which occurs when the query contains an ORDER BY clause on an indexed column, which avoids sorting operations once, as follows:

Copy Code code as follows:

Sql&gt; Set Autotrace traceonly explain


Sql&gt; SELECT * from Hr.employees


2 where department_id in (90, 100)


3 ORDER BY department_id Desc;

Execution Plan
----------------------------------------------------------
Plan Hash value:3707994525

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


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


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


| 0 |                   SELECT STATEMENT |     |   9 |     621 | 2 (0) | 00:00:01 |


|  1 |                   INLIST Iterator |       |       |            |          | |


|   2 | TABLE ACCESS by INDEX ROWID |     EMPLOYEES |   9 |     621 | 2 (0) | 00:00:01 |


|* 3 | INDEX RANGE SCAN descending|     Emp_department_ix |       9 |     | 1 (0) | 00:00:01 |


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

predicate information (identified by Operation ID):
---------------------------------------------------

3-access ("department_id" =90 OR "department_id" =100)

In the example above, index entries are read in reverse order, avoiding sorting operations.

Index full Scan (SCAN)

An index full scan will scan every leaf block of the index structure, read the row number of each line, and remove the rows of data, since it is access to each index leaf block, so what is the advantage of its relative full table scan? Actually, because there are fewer columns of information in the index block, are usually index keys and rowid, so for the same block and index block, the number of entries in the index key is usually the majority of the index block, so if all the fields in the Query field list are part of the index, You can completely skip access to the table data, which makes it more efficient to index the full scan method.

There are a number of cases in which an index full scan occurs, with several typical scenarios:

1, the query always lacks predicates, but the obtained columns can be obtained directly from the index

Copy Code code as follows:

Sql&gt; Select email from hr.employees;

Execution Plan
----------------------------------------------------------
Plan Hash value:2196514524

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 |              SELECT STATEMENT |   |   107 |     856 | 1 (0) | 00:00:01 |
|  1 | INDEX Full SCAN |   Emp_email_uk |   107 |     856 | 1 (0) | 00:00:01 |
---------------------------------------------------------------------------------

2, the query predicate contains a condition that is located on the non-boot column of the index (in fact, depending on the base size of the boot column value, and if the boot column has fewer unique values, a jump scan may occur)

Copy Code code as follows:

Sql&gt; Select First_Name, last_name from Hr.employees


2 where first_name like ' a% ';

Execution Plan
----------------------------------------------------------
Plan Hash value:2228653197

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 |             SELECT STATEMENT |     |    3 |     45 | 1 (0) | 00:00:01 |
|* 1 | INDEX Full SCAN |     Emp_name_ix |    3 |     45 | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("first_name" like ' a% ')
Filter ("first_name" like ' a% ')

sql> SET LONG 2000000
Sql> Select Dbms_metadata.get_ddl (' INDEX ', ' Emp_name_ix ', ' HR ') from dual;

Dbms_metadata. GET_DDL (' INDEX ', ' Emp_name_ix ', ' HR ')
--------------------------------------------------------------------------------

CREATE INDEX "HR". Emp_name_ix "on" HR. " EMPLOYEES "(" Last_Name "," first_name ")
)
PCTFREE Initrans 2 Maxtrans 255 nologging COMPUTE STATISTICS
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT flash_cache DE
FAULT Cell_flash_cache DEFAULT)
Tablespace "EXAMPLE"
--you can see that the Emp_name_ix index is built on columns (("Last_Name", "first_name"), using predicates with the first_name of the non-boot column

3, the data is obtained by an already sorted index, thereby eliminating individual sorting operations

Copy Code code as follows:

Sql&gt; SELECT * from Hr.employees order by employee_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:2186312383

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


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


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


| 0 |               SELECT STATEMENT |   |  107 |     7383 | 3 (0) | 00:00:01 |


|  1 | TABLE ACCESS by INDEX rowid|   EMPLOYEES |  107 |     7383 | 3 (0) | 00:00:01 |


|   2 | INDEX Full SCAN |   EMP_EMP_ID_PK |       107 |     | 1 (0) | 00:00:01 |


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

--You can also use ascending indexes to return descending data
Sql> Select employee_id from hr.employees to employee_id desc;

Execution Plan
----------------------------------------------------------
Plan Hash value:753568220

--------------------------------------------------------------------------------------------
| id  | operation                   | name          | rows  | Bytes | Cost (%CPU) | time     |
--------------------------------------------------------------------------------------------
|    0 | SELECT statement           |                |   |   428 |      1   (0) | 00:00:01 |
|   1 |  INDEX full SCAN descending| EMP_EMP_ID_PK |   |   428 |     1   (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

As you can see in the example above, an index full scan can also think of a range scan, returning descending data by ascending index, and its optimization is more than that, when we query a column for the maximum or minimum value and this column is an indexed column, the full scan of the index will have a very significant advantage. Because this time the optimizer does not have the index data for all the leaf node retrieval, but only a root block, the first or last leaf block scan, which will undoubtedly significantly improve performance!!

Copy Code code as follows:

--index full scan for minimum value


sql&gt; Select min (department_id) from Hr.employees;

Execution Plan
----------------------------------------------------------
Plan Hash value:613773769

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


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


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


| 0 |                   SELECT STATEMENT |     |     1 |     3 | 1 (0) | 00:00:01 |


|  1 |                   SORT AGGREGATE |     |     1 |            3 |          | |


|   2 | INDEX full SCAN (Min/max) |     Emp_department_ix |     1 |     3 | 1 (0) | 00:00:01 |


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

-If both Max and Min values are included, the optimizer does not actively choose a more efficient index full scan method
sql> Select min (department_id), Max (department_id) from Hr.employees;

Execution Plan
----------------------------------------------------------
Plan Hash value:1756381138

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


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


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


| 0 |           SELECT STATEMENT |     |     1 |     3 | 3 (0) | 00:00:01 |


|  1 |           SORT AGGREGATE |     |     1 |            3 |          | |


|   2 | TABLE ACCESS full|   EMPLOYEES |   107 |     321 | 3 (0) | 00:00:01 |


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


--An alternative optimization scheme


Sql&gt; Select


2 (select min (department_id) from Hr.employees) min_id,


3 (select Max (department_id) from Hr.employees) max_id


4 from dual;

Execution Plan
----------------------------------------------------------
Plan Hash value:2189307159

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


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


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


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


|  1 |                   SORT AGGREGATE |     |     1 |            3 |          | |


|   2 | INDEX full SCAN (Min/max) |     Emp_department_ix |     1 |     3 | 1 (0) | 00:00:01 |


|  3 |                   SORT AGGREGATE |     |     1 |            3 |          | |


|   4 | INDEX full SCAN (Min/max) |     Emp_department_ix |     1 |     3 | 1 (0) | 00:00:01 |


|  5 |                   FAST DUAL |     |       1 |     | 2 (0) | 00:00:01 |


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

Index jump Scan (index skip SCAN)

This type of scanning is also a special case, because in earlier versions, the optimizer refused to use indexes because of the use of a non-boot column. The premise of the jump scan has a corresponding scenario, when the predicate contains conditions on the non-boot column of the index, and when the unique value of the boot column is small, it is highly likely to use the index jump scan method; As with index full scan, range scan, it can also access indexes in ascending or descending order ; The jump scan divides a composite index into smaller logical indexes based on the number of unique values of the boot column, and the smaller the number of unique values in the boot column, the less the number of sub indexes to split, the more likely it is to achieve a higher computational efficiency of relative full table scans.

Copy Code code as follows:

--Create a test table, take the Dba_objects table as an example


Sql&gt; CREATE TABLE Test as SELECT * from Dba_objects;

Table created.

--Create a composite index, where you select an owner column with fewer unique values as the bootstrap column
Sql> CREATE index i_test on test (owner,object_id,object_type);

Index created.

--Analyze tables to collect statistical information
sql> exec dbms_stats.gather_table_stats (' SYS ', ' TEST ');

Pl/sql procedure successfully completed.

--First look at the comparison of the unique values of the boot column
Sql> Select COUNT (*), COUNT (distinct owner) from test;

COUNT (*) count (Distinctowner)
---------- --------------------
72482 29

--Using a conditional query that is not a bootstrap column to access the trigger skip SCAN
Sql> SELECT * FROM test where object_id = 46;

Execution Plan
----------------------------------------------------------
Plan Hash value:1001786056

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


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


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


| 0 |        SELECT STATEMENT |     |    1 |    97 | 31 (0) | 00:00:01 |


|  1 | TABLE ACCESS by INDEX rowid|     TEST |    1 |    97 | 31 (0) | 00:00:01 |


|* 2 | INDEX SKIP SCAN |     I_test |       1 |    | 30 (0) | 00:00:01 |


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

predicate information (identified by Operation ID):
---------------------------------------------------

2-access ("object_id" =46)
Filter ("object_id" =46)

Statistics
----------------------------------------------------------
         101  Recursive calls
          0  db block Gets
         38  consistent gets
           0  Physical reads
          0   Redo size
       1610  bytes sent via sql*net to client
  &nb sp;     523  Bytes received via sql*net from client
      & nbsp;   2  sql*net roundtrips to/from client
           3  Sorts (memory)
          0  sorts (disk)
          1  rows processed

--Take a look at the efficiency of this statement full scan
Sql> Select/*+ Full (test)/* from Test where object_id = 46;

Execution Plan
----------------------------------------------------------
Plan Hash value:1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |     |    1 |   97 | 282 (1) | 00:00:04 |
|* 1 | TABLE ACCESS full|     TEST |    1 |   97 | 282 (1) | 00:00:04 |
--------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter ("object_id" =46)

Statistics


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


1 Recursive calls


0 db Block gets


1037 consistent gets


0 physical Reads


0 Redo Size


1607 Bytes sent via sql*net to client


523 Bytes received via sql*net from client


2 sql*net roundtrips To/from Client


0 Sorts (memory)


0 Sorts (disk)


1 rows processed

Analysis of the above query can be seen, we use the index of the boot column has 29 unique values, that is, when performing an index jump scan, split into 29 logical sub indexes to query, only 38 logical read, and the relative full table scan 1037 times logical reading, performance promotion is very obvious!

Index fast full Scan (index fast fully SCAN)

This access method is identical to the full table scan on the fetch data. is done through unordered, multiple-block reads, so it's not possible to use it to avoid sorting costs; Index quick full scan usually occurs when the query column is in the index and a column in the index has non-null constraints, of course, this condition is also prone to index full scan, It can be used in lieu of full table scans, and comparison data acquisition does not require access to data blocks on the table.

Copy Code code as follows:

--still use the test table created above


Sql&gt; desc Test


Name Null? Type


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


OWNER VARCHAR2 (30)


object_name VARCHAR2 (128)


Subobject_name VARCHAR2 (30)


OBJECT_ID not NULL number


DATA_OBJECT_ID number


Object_type VARCHAR2 (19)


CREATED DATE


Last_ddl_time DATE


TIMESTAMP VARCHAR2 (19)


STATUS VARCHAR2 (7)


Temporary VARCHAR2 (1)


Generated VARCHAR2 (1)


Secondary VARCHAR2 (1)


NAMESPACE number


Edition_name VARCHAR2 (30)

--Create an index on the object_id column
Sql> CREATE index Pri_inx on test (object_id);

Index created.

--Perform a full table scan directly
Sql> select object_id from test;

72482 rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1357081020

--------------------------------------------------------------------------
| id  | operation         | Name | rows  | Bytes | Cost (%CPU) | time     |
--------------------------------------------------------------------------
|   0 | SELECT statement  |      | 72482 |   353k|   282   (1) | 00:00:04 |
|   1 |  TABLE ACCESS full| TEST | 72482 |   353k|   282   (1) | 00:00:04 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
           1  Recursive calls
          0  DB block gets
       5799  consistent gets
     & nbsp;    0  Physical reads
          0  Redo size
    1323739  bytes sent via sql*net to client
      53675& nbsp Bytes received via sql*net from client
       4834  sql*net roundtrips to/from CLI ENT
          0  sorts (memory)
           0  Sorts (disk)
      72482  rows processed

--Modify object_id to NOT NULL
Sql> ALTER TABLE test modify (object_id not NULL);

Table altered.

--Use the OBJECT_ID column query again to see the use of a fast full scan
Sql> select object_id from test;

72482 rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:3806735285

--------------------------------------------------------------------------------
| id  | operation            | name    | rows  | Bytes | Cost (%CPU) | time     |
--------------------------------------------------------------------------------
|   0 | SELECT statement     |         | 72482 |   353k|    45   (0) | 00:00:01 |
|   1 |  INDEX FAST full scan| Pri_inx | 72482 |   353k|    45   (0) | 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
167 Recursive calls
0 db Block gets
5020 Consistent gets
161 physical Reads
0 Redo Size
1323739 Bytes sent via sql*net to client
53675 bytes received via sql*net from client
4834 sql*net roundtrips To/from Client
4 Sorts (memory)
0 Sorts (disk)
72482 rows processed

PS, this example of the index FAST full scan is a bad simulation, and the example above has been a long time ...