One of the wrong misunderstandings using the index: Does not use the composite index to cause the query without the index
In Oracle, we often think that the index is established, and the SQL query will use the index as we hope. In fact, Oracle will only use the index under certain conditions. Here we summarize the first point: Oracle will In the condition contains the index when the leader is used, that is, the first column in the query condition must be used, please see the example below.
SQL> SELECT * from Tab;
TName Tabtype ClusterID
----------------------------------------------
Bonus Table
DEPT TABLE
Dummy Table
EMP TABLE
Salgrade Table
Establish a joint index (pay attention to the index column order of the composite index)
SQL> CREATE INDEX EMP_ID1 ON EMP (Empno, ENAME, DEPTNO);
Index created
Create a single-button index
SQL> CREATE INDEX EMP_ID2 ON EMP (SAL);
Index created
SQL> SELECT TABLE_NAME, INDEX_NAME FROM User_indexes
2 WHERE TABLE_NAME = 'EMP';
TABLE_NAME INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
EMP EMP_ID1
EMP EMP_ID2
SQL> Select * from user_ind_columns
2 /
Index_name Table_name Column_name Column_Position Column_Length CHAR_LENGTH DESCEND
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------
EMP_ID1 EMP EMPNO 1 22 0 ASC
EMP_ID1 EMP ENAME 2 10 10 ASCEMP_ID1 EMP DEPTNO 3 22 0 ASC
EMP_ID2 EMP SAL 1 22 0 ASC
Since the following query is not used to use the leader of the composite index, no index is used.
SELECT JOB, Empno from Emp where ename = 'RICH'
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- ------------------
| 0 | SELECT Statement | | | | | |
| * 1 | Table Access Full | EMP | | | | |
-------------------------------------------------- ------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
1 - Filter ("EMP". "Ename" = 'rich')
NOTE: Rule Based Optimization
14 rows selected
The following query is also due to the leading column of the composite index, so no index is used.
SELECT JOB, Empno from Emp where deptno = 30;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- ------------------ | 0 | SELECT Statement | | | | |
| * 1 | Table Access Full | EMP | | | | |
-------------------------------------------------- ------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
1 - Filter ("emp". "Deptno" = 30)
NOTE: Rule Based Optimization
14 rows selected
The following query uses the leader in the composite index, so the query is indeed
SELECT JOB, Empno from Emp where Empno = 7777;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- -------------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- -------------------------
| 0 | SELECT Statement | | | | | |
| 1 | Table Access by Index RowID | EMP | | | | |
| * 2 | INDEX RANGE SCAN | EMP_ID1 | | | | | |
-------------------------------------------------- -------------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
2 - Access ("EMP". "Empno" = 7777)
NOTE: Rule Based Optimization
15 rows selected
The following query uses the first column and second column in the composite index, so the query is indexed.
Select Job, Empno from Emp where Empno = 7777 and ename = 'rich';
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- -------------------------
| ID | OPERATION | Name | Rows | Bytes | Cost | ------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT Statement | | | | | |
| 1 | Table Access by Index RowID | EMP | | | | |
| * 2 | INDEX RANGE SCAN | EMP_ID1 | | | | | |
-------------------------------------------------- -------------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
2 - Access ("EMP". "Empno" = 7777 and "EMP". "Ename" = 'rich')
NOTE: Rule Based Optimization
15 rows selected
Used the full column of the composite index, so I've been indexed, and the column not included in the index is selected.
So after the index full table scans gets the ROWID that meets the conditions, you have to retrieve the corresponding line in the table.
SELECT JOB, Empno from Emp where Empno = 7777 and ename = 'Rich' and deptno = 30;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- -------------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- -------------------------
| 0 | SELECT Statement | | | | | |
| 1 | Table Access by Index RowID | EMP | | | | |
| * 2 | INDEX RANGE SCAN | EMP_ID1 | | | | | |
-------------------------------------------------- -------------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
2 - Access ("EMP". "Empno" = 7777 and "EMP". "ENAME" = 'Rich' and "EMP". "Deptno" = 30)
NOTE: Rule Based Optimization
16 rows selected
The full column of the composite index is used, so it is indexed, and since all selected columns are included in the index, only index range scanning
SELECT Empno from Emp where Empno = 7777 and ename = 'Rich' and deptno = 30;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- ------------------
| 0 | SELECT Statement | | | | | |
| * 1 | INDEX RANGE SCAN | EMP_ID1 | | | | | |
-------------------------------------------------- ------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
1 - Access ("EMP". "Empno" = 7777 and "EMP". "ENAME" = 'Rich' And "EM
P "." Deptno "= 30)
NOTE: Rule Based Optimization
15 rows selected
