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);
