16:16:34 sys@prodadg1>select /*+ index(dept idx_dept_loc) */ * from scott.dept where loc='CHICAGO';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3511320296
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOC"='CHICAGO')
Statistics
----------------------------------------------------------
167 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
16:17:18 sys@prodadg1>select /*+ index(dept idx_dept_loc) */ * from scott.dept where deptno=10;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4024456447
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX_DEPT_LOC | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
218 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
16:15:03 sys@prodadg1>desc scott.dept;
Name Null? Type
-------------------------------------------------------------------------- -------- --------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
3,hint失效
16:16:58 sys@prodadg1>select /*+ index(dept idx_dept_loc) */ * from scott.dept where deptno=10;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
464 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3511320296
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DEPT_LOC | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOC"='CHICAGO')
Statistics
----------------------------------------------------------
167 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
16:17:18 sys@prodadg1>select /*+ index(dept idx_dept_loc) */ * from scott.dept where deptno=10;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4024456447
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX_DEPT_LOC | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
218 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
16:15:03 sys@prodadg1>desc scott.dept;
Name Null? Type
-------------------------------------------------------------------------- -------- --------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
16:16:58 sys@prodadg1>select /*+ index(dept idx_dept_loc) */ * from scott.dept where deptno=10;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
464 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108064/viewspace-2136415/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29108064/viewspace-2136415/