oracle hint失效,HINT不起作用是为何？
虽然你可以要求ORACLE走哪条路，但是，必须都是能够达到最终目的的路径才行，如果指一条根本不通的路，你让人家怎么走？
SQL> create table t as select * from dba_objects;
表已创建。
SQL> create index t_idx on t(object_id);
索引已创建。
SQL> set autot trace exp stat
SQL> select * from t;
已选择64896行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65561 | 12M| 245 (2)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T | 65561 | 12M| 245 (2)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5181 consistent gets
0 physical reads
0 redo size
7138939 bytes sent via SQL*Net to client
48002 bytes received via SQL*Net from client
4328 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64896 rows processed
SQL> select /*+ index(t t_idx) */ * from t;
已选择64896行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65561 | 12M| 245 (2)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T | 65561 | 12M| 245 (2)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5181 consistent gets
0 physical reads
0 redo size
7138939 bytes sent via SQL*Net to client
48002 bytes received via SQL*Net from client
4328 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64896 rows processed
SQL> select /*+ index(t t_idx) */ * from t where object_id is not null;
已选择64894行。
执行计划
----------------------------------------------------------
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65561 | 12M| 1143 (1)| 00:00:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 65561 | 12M| 1143 (1)| 00:00:16 |
|* 2 | INDEX FULL SCAN | T_IDX | 65561 | | 146 (1)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9728 consistent gets
0 physical reads
0 redo size
7138818 bytes sent via SQL*Net to client
48002 bytes received via SQL*Net from client
4328 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64894 rows processed
SQL> set autot off
SQL> delete from t where object_id is null;
已删除2行。
SQL> commit;
提交完成。
SQL> alter table t modify object_id not null;
表已更改。
SQL> set autot trace exp stat
SQL> select /*+ index(t t_idx) */ * from t;
已选择64894行。
执行计划
----------------------------------------------------------
Plan hash value: 3778778741
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65561 | 12M| 1143 (1)| 00:0:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 65561 | 12M| 1143 (1)| 00:0:16 |
| 2 | INDEX FULL SCAN | T_IDX | 65561 | | 146 (1)| 00:0:02 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9728 consistent gets
0 physical reads
0 redo size
7138818 bytes sent via SQL*Net to client
48002 bytes received via SQL*Net from client
4328 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64894 rows processed