SQL> select /*+index(range idx_pre)*/ id,name,address from range where name='OUTLN'; --没有使用索引
21 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 184025858
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 672 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 |
|* 2 | TABLE ACCESS FULL | RANGE | 21 | 672 | 4 (0)| 00:00:01 | 1 | 4 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='OUTLN')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
SQL> create index idx_nonpre on range(name,address) local;
Index created.
SQL> select id,name,address from range where address='Y' and name='OUTLN';
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1326523914
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 224 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| RANGE | 7 | 224 | 2 (0)| 00:00:01 | 4 | 4 |
|* 3 | INDEX RANGE SCAN | IDX_NONPRE | 1 | | 1 (0)| 00:00:01 | 4 | 4 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("NAME"='OUTLN' AND "ADDRESS"='Y')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client