遇到一个SB问题，加index hint提升，居然也不走索引，郁闷良久，结果发现该表的定义允许null，这对优化器有影响
SQL> desc emp
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
我在ename上建立了一个index
SQL> select table_name, index_name,column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ -----------------
DEPT PK_DEPT DEPTNO
EMP PK_EMP EMPNO
EMP EMA_ENAME ENAME
SQL> select dbms_metadata.get_ddl('INDEX','EMA_ENAME','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','EMA_ENAME','SCOTT')
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."EMA_ENAME" ON "SCOTT"."EMP" ("ENAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> select /*+ index(emp.ename) */ ename from emp;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
可以看见即使提升了优化器走索引，结果优化器也不走
SQL> alter table emp modify ename not null;
表已更改。
SQL> select /*+ index(emp.ename) */ ename from emp ;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 1224545206
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMA_ENAME | 14 | 84 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
现在将列定义为非null，用hint提升优化器就走了索引了。