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;
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 ;
/
for i in 5001 .. 10000 loop
('m', i) ;
gender,
count (*)
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
employee_id
scott.employee;
--強制也不走
/*+ index(employee idx_emp_1) */
為什麼?
索引idx_emp_1是個單鍵值b樹索引,是以null值不會存儲其中,但是一旦employee_id有了null(盡管此實驗沒有null),此索引會跳過null,導緻執行結果不對,執行計劃就有可能不會走,是以選擇走全表掃描
處理辦法:alter table scott.employee modify (employee_id not null);