SQL> select count(*),ID from test_2 group by id; COUNT(*) ID ---------- ---------- 131072 1 1179648 2 select count(*) from test_2 where id <>2;------利用函数索引优化: select count(*) from test_2 where id <>2;SQL> SQL> select count(*) from test_2 where id <>2; Execution Plan ---------------------------------------------------------- Plan hash value: 1548797762 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 801 (6)| 00:00:10 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | TABLE ACCESS FULL| TEST_2 | 655K| 1920K| 801 (6)| 00:00:10 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<>2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3463 consistent gets 1182 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 创建函数索引: create index idx2 on test_2(case when ID=2 then null else '@' end); select /*+ index(test_2 idx2) */ count(*) from test_2 where (case when ID=2 THEN NULL ELSE '@' END)='@'; select /*+ index(test_2 idx2) */ count(*) from test_2 where (case when ID=2 THEN NULL ELSE '@' END)='@'; SQL> Execution Plan ---------------------------------------------------------- Plan hash value: 2399724746 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 242 (2)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| IDX2 | 131K| 384K| 242 (2)| 00:00:03 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "ID" WHEN 2 THEN NULL ELSE '@' END ='@') Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 241 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读大大降低 使用不等于优化 必须保证<>过滤后的数据占表比例数据少才可以。