Index B-Tree
Index的叶节点指向RowID,然后根据RowID再找对应的Block
语法:CREATE INDEX NAME_SALARY_IDX ON PERSON(LAST_NAME ASC,SALARY DESC);
Index Unique Scan
对于unique index来说,如果where条件后面是=,那么就会执行index unique scan。
AskTom:well, the optimizer can look at an index that is unique and say "ah-hah, if you use "where x =:x and y = :y and ...." I'm going to get ONE row back, I can cost that much better" (refer)
Index Unique Scan的条件:
1.Index是唯一性索引;
2.where条件类似于 x = :x and y=: y ...;
Example:
CREATE TABLE PT_TEST AS SELECT * FROM DBA_OBJECTS;
CREATE UNIQUE INDEX UNIQUE_IDX ON PT_TEST(OBJECT_ID); --UNIQUE_IDX是唯一索引
EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS','PT_TEST', CASCADE=>TRUE ); --重做统计
SQL> set auto trace
SQL> select * from pt_test where object_id=10;
Elapsed: 00:00:00.47
Execution Plan
----------------------------------------------------------
Plan hash value: 2398730171
--------------------------------------------------