CREATE TABLE TESTIDX
(
CATEGORY INTEGER,
X INTEGER,
Y INTEGER,
TOTAL INTEGER
);
/*新增測試資料*/
INSERT INTO TESTIDX
SELECT X,X,Y,X*Y
FROM
(SELECT level x FROM DUAL CONNECT BY LEVEL <= 100),
(SELECT level y FROM DUAL CONNECT BY LEVEL <= 100);
/*建立index*/
SQL>CREATE INDEX TESTIDX_IDX ON TESTIDX(CATEGORY,X,Y);
/*使用Sql Explain Tool*/
SQL> set autotrace traceonly explain
/*按所鍵index來查詢*/
SQL> select * from testidx where category=99 and x=99 and y=99;
執行計畫
----------------------------------------------------------
Plan hash value: 3158742342
------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 52 |2 (0) | 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID|TESTIDX | 1 | 52 |2 (0) | 00:00:01 |
|*2 |INDEX RANGE SCAN |TESTIDX_IDX | 1 | |1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CATEGORY"=99 AND "X"=99 AND "Y"=99)
Note
-----
- dynamic sampling used for this statement
/*測試使用部份index來查詢是否有用到index*/
SQL> select * from testidx where category = 10;
執行計畫
----------------------------------------------------------
Plan hash value: 3158742342
----------------------------------------------------------------------------------
| Id |Operation | Name |Rows|Bytes|Cost(%CPU)| Time |
----------------------------------------------------------------------------------
| 0|SELECT STATEMENT | |100| 5200 | 3 (0) | 00:00:01 |
| 1|TABLE ACCESS BY INDEX ROWID |TESTIDX |100| 5200 | 3 (0) | 00:00:01 |
|* 2|INDEX RANGE SCAN |TESTIDX_IDX |100| | 2 (0) | 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CATEGORY"=10)
Note
-----
- dynamic sampling used for this statement
/*測試使用部份index來查詢是否有用到index*/
SQL> select * from testidx where category = 10 and y = 5;
執行計畫
----------------------------------------------------------
Plan hash value: 3158742342
-----------------------------------------------------------------------------------
| Id |Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 52 | 3 (0) |00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID |TESTIDX | 1 | 52 | 3 (0) |00:00:01 |
|* 2 |INDEX RANGE SCAN | TESTIDX_IDX | 1 | | 2 (0) |00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CATEGORY"=10 AND "Y"=5)
filter("Y"=5)
Note
-----
- dynamic sampling used for this statement
/*測試使用總合來查詢是否有用到index*/
SQL> select * from testidx where category+x+y=9;
執行計畫
----------------------------------------------------------
Plan hash value: 3565063929
------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes |Cost(%CPU)|Time |
------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 4 | 208 | 9 (12) | 00:00:01 |
|* 1 |TABLE ACCESS FULL | TESTIDX | 4 | 208 | 9 (12) | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CATEGORY"+"X"+"Y"=9)
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
