SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_VAL2_I |
------------------------------------------------
1 - filter("VAL1"=11)
2 - access("VAL2"=11)
如果你不想回表做filter,只要index也包含val2这列就行了。
SQL> create table t(val1 number,val2 number);
Table created.
SQL> create indexT_VAL2_I on t(val1,val2);
Index created.
SQL> alter table t add(val3 number);
Table altered.
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 161426604
------------------------------------------------
| Id| Operation
| Name |
------------------------------------------------
| 0 | SELECT STATEMENT
|
|
| 1 |TABLE ACCESS BY INDEX ROWID| T|
|*2 | INDEX RANGE SCAN
| T_VAL2_I |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VAL1"=11 AND "VAL2"=11)
SQL> alter table t add(val3 number);
Table altered.
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 161426604
------------------------------------------------
| Id| Operation
| Name |
------------------------------------------------
| 0 | SELECT STATEMENT
|
|
| 1 |TABLE ACCESS BY INDEX ROWID| T|
|*2 | INDEX RANGE SCAN
| T_VAL2_I |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VAL1"=11 AND "VAL2"=11)