Oracle index access method

For the stored procedure of displaying the execution plan in the Oracle index access mode, see http://www.bkjia.com/database/201303/192688.html (1) unique index scan of index unique scan. A single match is performed for a unique index. In a unique index, each non-null key value has only one unique key and the primary key is also the unique index. Example: www.2cto.com [SQL] SQL> exec SQL _explain ('select * from emp where empno = 8888 '); Plan hash value: 2949544139 Bytes ------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes ------ | 0 | select statement | 1 | 39 | 1 (0) | 00:00:01 | 1 | table access by index rowid | EMP | 1 | 39 | 1 (0) | 00:00:01 | * 2 | index unique scan | PK_EMP | 1 | 0 (0) | 00:00:01 | monitoring ------ Predicate Information (identified by operation id): --------------------------------------------------- 2-access ("EMPNO" = 8888) PL/SQL process completed successfully. (2) index range scan is a non-unique index scan that corresponds to a unique index scan. The INDEX performs RANGE matching (for example,>, <, like, etc.) or a single match (for example, = ), example: [SQL] SQL> create table t_xyc as select * from emp; the table has been created. SQL> insert into t_xyc select * from emp; 15 rows have been created. SQL> commit; submitted completely. SQL> create index xyc_index on t_xyc (empno); the index has been created. --- Use the equal sign (=) for a single match SQL> exec SQL _explain ('select * from t_xyc where empno = 8888 '); Plan hash value: 767710755 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 | 78 | 4 (0) | 00:00:01 | | 1 | table access by index rowid | T_XYC | 2 | 78 | 4 (0) | 00:00:01 | * 2 | index range scan | XYC_INDEX | 2 | 1 (0) | 00:00:01 | your Predicate Information (identified by operation id): --------------------------------------------------- 2-access ("EMPNO" = 8888) PL/SQL process has been completed successfully. ---- Match SQL with a range greater than (>)> exec SQL _explain ('select * from t_xyc where empno> 8888 '); Plan hash value: 767710755 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 | 78 | 4 (0) | 00:00:01 | 1 | table access by index rowid | T_XYC | 2 | 78 | 4 (0) | 00:00:01 | * 2 | index range scan | XYC_INDEX | 2 | 1 (0) | 00:00:01 | your Predicate Information (identified by operation id): --------------------------------------------------- 2-access ("EMPNO"> 8888) PL/SQL process has been completed successfully. (3) index range scan (MIN/MAX) scans the index range to obtain the maximum or minimum value of the INDEX field. Example: [SQL] SQL> exec SQL _explain ('select min (empno) from t_xyc where empno> 100'); Plan hash value: 2706514164 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 4 | 1 (0) | 1 | 1 | sort aggregate | 1 | 4 | 2 | first row | 1 | 4 | 1 (0) | 00:00:01 | * 3 | index range scan (MIN/MAX) | XYC_INDEX | 1 | 4 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("EMPNO"> 8888) PL/SQL process has been completed successfully. (4) index fast full scan: INDEX data blocks are read in physical order rather than in the logic order of the INDEX ). Example: [SQL] SQL> begin 2 for I in 1 .. 10000 loop 3 insert into fast_xyc values (I, ''); 4 end loop; 5 commit; 6 end; 7/PL/SQL process completed successfully. SQL> create index fast_idx on fast_xyc (id); the index has been created. SQL> exec SQL _explain ('select id from fast_xyc where id> 5'); Plan hash value: 1029382659 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | interval | 0 | select statement | 9995 | 126K | 9 (0) | 00:00:01 | * 1 | index fast full scan | FAST_IDX | 9995 | 126K | 9 (0) | 00:00:01 | your Predicate Information (identified by operation id): ------------------------------------------------- 1-filter ("ID"> 5) note ------dynamic sampling used for this statement (level = 2) PL/SQL process has been completed successfully. (6) index full scan full index scan: full scan of indexes. The difference between full scan and quick full scan of indexes is: ①: It reads the INDEX data in a logical order, fast full scan reads data in the order of physical storage. ②: It can only read one database at a time, and multiple data blocks can be read through quick full scan. Example: [SQL] SQL> set pagesize 0 SQL> set lines 400 SQL> set serveroutput on SQL> exec SQL _explain ('select empno from emp'); Plan hash value: 179099197 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 15 | 60 | 1 (0) | 00:00:01 | 1 | index full scan | PK_EMP | 15 | 60 | 1 (0) | 00:00:01 | the --------------------------------------------------------------------------- PL/SQL process is successfully completed. (7) index sample fast full scan index scan is used to quickly SCAN part of data blocks by reading multiple data blocks and physical storage data. Example: [SQL] ---- sample (10) indicates sample 10%; SQL> exec SQL _explain ('select id from fast_xyc sample (10) where id> 5'); Plan hash value: 3595809218 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 100 0 | 14000 | 7 (0) | 00:00:01 | * 1 | index sample fast full scan | FAST_IDX | 1000 | 14000 | 7 (0) | 00:00:01 | specified Predicate Information (identified by operation id): --------------------------------------------------- 1-filter ("ID"> 5) (8) index full scan (MIN/MAX) scans all index fields to obtain the maximum and minimum values of the index fields. Example: [SQL] SQL> exec SQL _explain ('select max (empno) from emp'); Plan hash value: 1707959928 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 4 | 1 (0) | 00:00:01 | 1 | sort aggregate | 1 | 4 | 2 | index full scan (MIN/MAX) | PK_EMP | 1 | 4 | 1 (0) | 00:00:01 | the ------------------------------------------------------------------------------------- PL/SQL process has been completed successfully. (9) index full scan descending performs a full scan in the reverse order of the INDEX logic. Example: [SQL] SQL> exec SQL _explain ('select * from emp order by empno desc '); plan hash value: 3088625055 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | SELECT STAT EMENT | 15 | 585 | 2 (0) | 00:00:01 | 1 | table access by index rowid | EMP | 15 | 585 | 2 (0) | 00:00:01 | 2 | index full scan descending | PK_EMP | 15 | 1 (0) | 00:00:01 | the -------------------------------------------------------------------------------------- PL/SQL process is successfully completed. (10) index skip scan index skip scanning. In composite indexes, if the subsequent INDEX is more unique than the first INDEX, and the subsequent INDEX is used as the filter condition, an index skip scanning will occur. Example: [SQL] ---- create name as the first index, but the id field must be unique. SQL> create index fh_index1 on fast_xyc (name, id); the index has been created. SQL> exec SQL _explain ('select * from fast_xyc where id =: A'); Plan hash value: 3991949787 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 15 | 4 (0) | 00:00:01 | * 1 | index skip scan | FH_INDEX1 | 1 | 15 | 4 (0) | 00:00:01 | identified Predicate Information (identified by operation id): ------------------------------------------------- 1-access ("ID" = TO_NUMBER (: )) the filter ("ID" = TO_NUMBER (: A) PL/SQL process has been completed successfully. (11) domain index access domain index (for example, full-text INDEX) Example: [SQL] SQL> exec SQL _explain ('select * from qw_xyc where contains (name,: A)> 0 '); Plan hash value: 2774494995 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | SELECT STATEMENT | 460 | 19780 | 91 (0) | 00:00:02 | 1 | table access by index rowid | QW_XYC | 460 | 19780 | 91 (0) | 00:00:02 | * 2 | domain index | QW_INDEX | 4 (0) | 00:00:01 | descripredicate Information (identified by operation id): descri2-access ("CTXSYS ". "CONTAINS" ("NAME" ,: A)> 0) Note ------dynamic sampling used for this statement (level = 2) the PL/SQL process has been completed successfully. (12) bitmap index single value, that is, access to a key VALUE (refer to the above B-tree INDEX, not for example ). (13) bitmap index range scan bitmap range scan (refer to the above B-tree INDEX, not for example ). (14) full scan of bitmap index fast full scan bitmap index (refer to the above B-tree INDEX, not for example ). (15) bitmap index fast full scan: fast full scan of BITMAP indexes (refer to the above B-tree INDEX, not for example ).