Analysis of five index access methods in Oracle CBO optimization mode

  • 2021-12-13 10:05:39
  • OfStack

This article mainly discusses the following index access methods:

1. Index only 1 scan (INDEX UNIQUE SCAN)
2. Index Range Scan (INDEX RANGE SCAN)
3. Index Full Scan (INDEX FULL SCAN)
4. Index Skip Scan (INDEX SKIP SCAN)
5. Index Fast Full Scan (INDEX FAST FULL SCAN)

Index only 1 scan (INDEX UNIQUE SCAN)

The characteristic of accessing data through this index is that only one row of data is returned for a specific value, which is usually selected if the columns of UNIQE and PRIMARY KEY indexes are used as conditions in query predicates; The accessed height is always the height of the index plus 1, except in some special cases, such as the LOB object stored separately.


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)

When the predicate contains a condition that will return 1 range data, the index range scan will be selected, and the index can be only 1 or not only 1; The specified condition can be ( < , > , LIKE, BETWEEN, =) and so on, but when using LIKE, if you use the wildcard%, it is very likely that you will not use range scanning because the conditions are too broad. Here is an example:


SQL> 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 range scanning needs to accurately analyze the number of returned data, and the larger the range, the more likely it is to perform full table scanning;


SQL> select department_id,count(*) from hr.employees group by department_id 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 12 rows selected. -- The one with the most values used here 50 To perform a range scan
SQL> set autotrace traceonly explain
SQL> select * from hr.employees where DEPARTMENT_ID = 50; 45 rows selected. 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
         10  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)
         45  rows processed

It can be seen that the optimizer still performs the full table scan method when the retrieval range data is large.

One optimization method for index range scanning is to use ascending indexes to obtain descending data rows, which mostly occurs when the query contains ORDER BY clauses on index columns, thus avoiding one sort operation, as follows:


SQL> set autotrace traceonly explain
SQL> 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 above example, the index entries are read in reverse order, avoiding sorting operation.

Index Full Scan (INDEX FULL SCAN)

The operation of index full scan will scan every leaf block of index structure, read the row number of each entry, and fetch the data row. Since it accesses every index leaf block, what is its advantage over full table scan? In fact, because the number of information columns contained in the index block is small, Are usually index keys and ROWID, Therefore, for the same data block and index block, the number of index key entries is usually the majority in the index block. Therefore, if all fields in the query field list are part 1 of the index, the access to table data can be completely skipped. In this case, the index full scan method will obtain higher efficiency.

There are many situations in which index full scanning occurs, and several typical scenarios:

1. The query always lacks predicates, but the obtained columns can be obtained directly through the index


SQL> 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 a non-bootstrap column in the index (in fact, it also depends on the cardinality of the bootstrap column value. If the only 1 value of the bootstrap column is small, skip scanning may also occur)


SQL> 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 10 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 EMP_NAME_IX Indexes are built on columns (("LAST_NAME", "FIRST_NAME") With non-booted columns FIRST_NAME Predicate of

3. The data is obtained through a sorted index, thus eliminating the separate sorting operation


SQL> 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 an ascending index to return descending data
SQL> select employee_id from hr.employees order by employee_id desc ; Execution Plan
----------------------------------------------------------
Plan hash value: 753568220 --------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |   107 |   428 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK |   107 |   428 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

As can be seen from the above example, Index full scanning can also be like range scanning, Returns descending data through ascending index, And there are more than one kind of optimization, When we query the maximum or minimum value of a 1 column and this 1 column is an index column, Full index scanning will gain a very significant advantage, because the optimizer at this time does not search all leaf nodes of the index data, but only scans one root block, the first or the last leaf block, which will undoubtedly improve the performance significantly! !


-- Index full scan to get the minimum value
SQL> 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 you also include MAX And MIN The optimizer does not actively select the index full scan method with higher efficiency
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 |
--------------------------------------------------------------------------------
-- 1 Optimization scheme of species substitution
SQL> 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 Skip Scan (INDEX SKIP SCAN)

This scanning method is also a special case, because in earlier versions, the optimizer would refuse to use indexes because it used non-bootable columns. The premise of skip scanning has corresponding scenarios. When the predicate contains the conditions on the non-boot column in the index and the only value of the boot column is small, it is very possible to use the index skip scanning method; Same as index full scan and range scan, it can also access the index in ascending or descending order; The difference is that skip scanning will divide the composite index into several smaller logical sub-indexes according to the number of only 1 values of the boot column. The smaller the number of only 1 values of the boot column, the smaller the number of sub-indexes divided, and the more likely it is to achieve higher operation efficiency than full table scanning.


-- Create a test table to dba_objects Table as an example
SQL> create table test as select * from dba_objects; Table created. -- Create 1 Complex index, where the 1 Individual only 1 Less valuable owner Column as the boot column
SQL> create index i_test on test(owner,object_id,object_type) ; Index created. -- Analyze tables to collect statistics
SQL> exec dbms_stats.gather_table_stats('SYS','TEST'); PL/SQL procedure successfully completed. -- Look first 1 Only of the lower boot column 1 Comparison of values
SQL> select count(*),count(distinct owner) from test;   COUNT(*) COUNT(DISTINCTOWNER)
---------- --------------------
     72482                   29 -- Use conditional queries with non-bootable columns to access triggers 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
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed -- Let's take a look at the efficiency of full scanning of this statement
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

By analyzing the above query, we can see that there are 29 only 1 values in the boot column in the index we use, that is to say, when performing the index jump scan, it is divided into 29 logical sub-indexes to query, and only 38 logical readings are generated; Compared with 1037 logical readings of full table scanning, the performance improvement is very obvious!

Index Fast Full Scan (INDEX FAST FULL SCAN)

This access method is the same as full table scanning in obtaining data, which is carried out by unordered multi-block reading, so it can not be used to avoid sorting costs; Fast index full scan usually occurs when all query columns are in the index and 1 column in the index has non-null constraints. Of course, this condition is also prone to index full scan, which can be used to replace full table scan, and comparative data acquisition does not need to access data blocks on the table.


-- Still use the one created above test Table
SQL> 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) -- In object_id Create an index on the 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
          0  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
          0  sorts (memory)
          0  sorts (disk)
      72482  rows processed -- Modify object_id For not null
SQL> alter table test modify (object_id not null); Table altered. -- Reuse object_id Column query can see that fast full scan is used
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 INDEX FAST FULL SCAN is really difficult to simulate. The above example has been done for a long time. . . . .


Related articles: