(cache) is not null で count する SQL を Index Only "Fast Full" Scan で高速化する

where句に is not null 条件を使い、count する SQL を Index Only "Fast Full" Scan で仕事（計算）量を減らして高速化してみた。インデックスを作るだけでオプティマイザが仕事（計算）量が期待していた実行計画を選択してくれた。テーブルとインデックスのセグメントサイズにあまり差がなく、レスポンスタイムの差がほとんどないので、後で、もう少しわかり易い結果になるよう検証手順に変更します。

チューニング結果

ビフォー

17:36:30 SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.88
17:36:51 SQL> select count(c2) from test1 where c5 is not null;

 COUNT(C2)
----------
    100000

Elapsed: 00:00:05.70
17:37:11 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6wksv9bxncxa5, child number 0
-------------------------------------
select count(c2) from test1 where c5 is not null

Plan hash value: 3896847026

-----------------------------------------------------------------------------------------------
| Id  | Operation	   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	   |	  1 |	     |	    1 |00:00:05.65 |	5057 |	 5053 |
|   1 |  SORT AGGREGATE    |	   |	  1 |	   1 |	    1 |00:00:05.65 |	5057 |	 5053 |
|*  2 |   TABLE ACCESS FULL| TEST1 |	  1 |  21065 |	  100K|00:00:05.57 |	5057 |	 5053 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C5" IS NOT NULL)


19 rows selected.

Elapsed: 00:00:00.51

アフター

17:37:25 SQL> create index idx_test1 on test1(c2,c5);

Index created.

Elapsed: 00:00:43.33
17:45:46 SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.26
17:46:04 SQL> select count(c2) from test1 where c5 is not null;

 COUNT(C2)
----------
    100000

Elapsed: 00:00:00.66
17:46:14 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6wksv9bxncxa5, child number 2
-------------------------------------
select count(c2) from test1 where c5 is not null

Plan hash value: 2671621383

------------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name	  | Starts | E-Rows | A-Rows |	 A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	  |	 1 |	    |	   1 |00:00:00.66 |    1032 |	1025 |
|   1 |  SORT AGGREGATE       | 	  |	 1 |	  1 |	   1 |00:00:00.66 |    1032 |	1025 |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 |	 1 |  21065 |	 100K|00:00:00.58 |    1032 |	1025 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C5" IS NOT NULL)


19 rows selected.

Elapsed: 00:00:00.32

準備手順

export LANG=C
export NLS_LANG=American_America.AL32UTF8
sqlplus /nolog
conn scott/tiger
set time on
set timing on
set pagesize 50000
set linesize 200

create table test1(c1 number, c2 char(30), c3 char(30), c4 char(30), c5 char(30));

begin
    for i in 1..1000000 loop
        insert into test1(c1, c2,c3,c4,c5) values(i,null,null,null,null);
    end loop;
end;
/
commit;

begin
for i in 100001..200000 loop
insert into test1(c1, c2,c3,c4,c5) values(i,i,i,i,i);
end loop;
end;
/
commit;

alter session set statistics_level=all;

環境

SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production