592- 使用的Hint 无效

SQL> create unique index pk_dept_cp on DEPT_CP(deptno);
create unique index pk_dept_cp on DEPT_CP(deptno)
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop index pk_dept_cp;

Index dropped.

SQL> alter table DEPT_CP modify(loc null);
alter table DEPT_CP modify(loc null)
                           *
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> create unique index pk_dept_cp on DEPT_CP(deptno);

Index created.

SQL> drop index pk_dept_cp;

Index dropped.

SQL> alter table DEPT_CP modify(loc null);
alter table DEPT_CP modify(loc null)
                           *
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> alter table DEPT_CP modify(loc  not null);

Table altered.

SQL> set autot trace exp;
SQL> select /*+ index(dept_cp IDX_DEPT_CP_LOC) */ deptno,dname from dept_cp where deptno=30;

Execution Plan
----------------------------------------------------------
Plan hash value: 4265608153

-----------------------------------------------------------------------------
| Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |    | 1 | 13 | 3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEPT_CP | 1 | 13 | 3   (0)| 00:00:01 |---没建任何索引的时候，走的是全表扫面,当然 hint 失效.
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)

SQL> create index idx_dept_cp_loc on dept_cp(loc);

Index created.

SQL> select /*+ index(dept_cp IDX_DEPT_CP_LOC) */ deptno,dname from dept_cp where deptno=30;

Execution Plan
----------------------------------------------------------
Plan hash value: 1299015680

-----------------------------------------------------------------------------------------------
| Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |     |     1 |    13 |     2 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT_CP      |     1 |    13 |     2 (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN   | IDX_DEPT_CP_LOC |     4 |       |     1(0)| 00:00:01 |---建立索引之后hint 有效，走的是索引快速扫面，其实应该是index full scan

但是通过查看LOC 属性 Not Null,即表dept 的所有行记录对应的rowid 都可以通过索引IDX_DEPT_CP_LOC 获得，那么上述执行计划对索引IDX_DEPT_CP_LOC 

的INDEX FULL SCAN 后再回表其实就相当于对表dept的全表扫面。

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)

SQL> create unique index pk_dept_cp on DEPT_CP(deptno);

Index created.--在deptno建立唯一索引

SQL> select /*+ index(dept_cp IDX_DEPT_CP_LOC) */ deptno,dname from dept_cp where deptno=30;

Execution Plan
----------------------------------------------------------
Plan hash value: 1299015680

-----------------------------------------------------------------------------------------------
| Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |     |     1 |    13 |     2 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT_CP      |     1 |    13 |     2 (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN   | IDX_DEPT_CP_LOC |     4 |       |     1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)
---通过实验看出只建立唯一索引也不行，还得使LOC 可以为NULL.
SQL> alter table DEPT_CP modify(loc null);

Table altered.

SQL> select /*+ index(dept_cp IDX_DEPT_CP_LOC) */ deptno,dname from dept_cp where deptno=30;

Execution Plan
----------------------------------------------------------
Plan hash value: 2779401338

------------------------------------------------------------------------------------------
| Id  | Operation    | Name| Rows  | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    ||     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT_CP |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN   | PK_DEPT_CP |     1 | |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=30)

--这会执行计划终于变了，走了索引PK_DEPT_CP .

SQL>