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