The research and questions of joint indexes built on multiple columns
This problem has only recently, I don't know if some people have discussed the creation of a federated index, such as CREATE INDEX IDX_EMP ON EMP (ID, Name, Sal, Deptno) found that the use of the joint index is different in different conditions queries. Tube: The optimization mode here is rule-based to avoid using Oracle's own execution plan SQL> Select * from Emp where ID <20 and deptno <5; Execution Plan ---------- ---------------------------------------------- 0 SELECT Statement Optimizer = Rule1 0 Index (Range Scan) Of 'IDX_EMP' (Non-Unique) Perform Planning Display Upellation Used Idx_empsql> Select * from Emp Where Sal> 2000 and Deptno> 9; Execution Plan ------- ------------------------------------------------- 0 Select Statement Optimizer = rule1 0 Table Access (Full) of 'Emp'SQL> SELECT * FROM EMP Where Sal> 2000 and Name Like'% Mark% '; Execution Plan -------------- ---------------------------- 0 Select Statement Optimizer = rule1 0 Table Access (Full) Of 'EMP' The above two queries display: If there is no ID column in the WHERE clause, the execution plan is executed is a full table scan SQL> SELECT / * INDEX (EMP) * / * from Emp WHERE SAL> 2000 and DePTNO> 9; Execution Plan ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- --0 Select Statement Optimizer = Rule (COST = 26 Card = 5 Bytes = 265) 1 0 Index (Full Scan) of 'IDX_EMP' (COST = 26 Card = 5 BYtes = 265) If it is forced by Hint Use index access, although it is used to use index idx_emp, but the index is a "ful Scan", different from A query of Range Scan.
