SQL>DROP INDEX TESTIDX_IDX;
/* 以總合建一個INDEX */
SQL>CREATE INDEX TESTIDX_I ON TESTIDX(CATEGORY+X+Y);
/* 使用SQLPLUS並開啟TRACE */
SQL> set autotrace traceonly explain
/* 看總和為條件的查詢結果 */
SQL> select * from testidx where category+x+y=9;
執行計畫
----------------------------------------------------------
Plan hash value: 3683099191
----------------------------------------------------------------------------
| Id | Operation | Name |Rows| Bytes |Cost(%CPU)|Time |
----------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 4 | 208 | 7 0)|00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID|TESTIDX | 4 | 208 | 7 (0)|00:00:01 |
|* 2 |INDEX RANGE SCAN |TESTIDX_I| 40| | 1 (0)|00:00:01|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CATEGORY"+"X"+"Y"=9)
Note
-----
- dynamic sampling used for this statement
/* 再看看各別為條件的查詢結果 */
SQL> select * from testidx where category=99 and x=99 and y=99;
執行計畫
----------------------------------------------------------
Plan hash value: 3565063929
----------------------------------------------------------------------
| Id |Operation | Name |Rows|Bytes|Cost(%CPU)| Time |
----------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 52 | 8 (0) | 00:00:01 |
|* 1 |TABLE ACCESS FULL | TESTIDX | 1 | 52 | 8 (0) | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CATEGORY"=99 AND "X"=99 AND "Y"=99)
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
