已建索引执行计划为何不走索引？

DROP INDEX scott.IDX_UNI_EMP;


CREATE INDEX scott.IDX_EMP_1 ON scott.EMPLOYEE (EMPLOYEE_ID);


--
SELECT
*
FROM
scott.EMPLOYEE
WHERE
EMPLOYEE_ID = 100;


SELECT
*
FROM
TABLE (
DBMS_XPLAN.DISPLAY_CURSOR (NULL, NULL, 'ADVANCED')
);


--
TRUNCATE TABLE scott.EMPLOYEE;


BEGIN
FOR I IN 1 .. 5000 LOOP
INSERT INTO scott.EMPLOYEE
VALUES
('F', I) ;
END LOOP ;
COMMIT ;
END ;
/


BEGIN
FOR I IN 5001 .. 10000 LOOP
INSERT INTO scott.EMPLOYEE
VALUES
('M', I) ;
END LOOP ;
COMMIT ;
END ;
/


--
SELECT
GENDER,
COUNT (*)
FROM
scott.EMPLOYEE
GROUP BY
GENDER;


EXEC DBMS_STATS .GATHER_TABLE_STATS(ownname=>'scott',tabname=>'EMPLOYEE',estimate_percent=>100,CASCADE=>TRUE,method_opt=>'for all columns size 1',no_invalidate=>FALSE);


SET AUTOT TRACE


--
SELECT
EMPLOYEE_ID
FROM
scott.EMPLOYEE;

--强制也不走
SELECT
/*+ INDEX(EMPLOYEE IDX_EMP_1) */
EMPLOYEE_ID
FROM

scott.EMPLOYEE;

为什么？

索引IDX_EMP_1是个单键值B树索引，因此null值不会存储其中，但是一旦EMPLOYEE_ID有了null（尽管此实验没有null）,此索引会跳过null,导致执行结果不对，执行计划就有可能不会走,因此选择走全表扫描

处理办法：ALTER TABLE scott.EMPLOYEE MODIFY (EMPLOYEE_ID NOT NULL);