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
