count(*)とcount(1)では性能は変わらない
ORACLEの組み込み関数の一つであるCOUNTは引数を一つとることができ、「count(*)」と、「count(1)」といった書き方がされますが、 count(*)とcount(1)では性能は変わらず結果も同一となります。この理由及び性能が向上する書き方について解説します。
何故COUNT(*)とCOUNT(1)の性能は変わらないか
これはCOUNT(*)とCOUNT(1)は全く同じ結果が戻る構文であり、これにより採択される実行計画のバリエーションも同じであるためとなります。 実行計画が同じであればパース以降の実際に処理する処理時間も基本的に変わりません。
性能が向上するCOUNT関数の書き方
いくつかの条件を満たした場合に「count(<列名>)」という記載に変更すると性能が向上するケースがあります。 また、COUNT(*)と結果が同一になる条件もあることには注意してください。
性能が向上し、COUNT(*)と結果が同一になる条件- NOT NULL制約付きの列をキーとした索引やPKが存在しない(または、存在していても表のサイズよりも索引のサイズのほうが大きい)
- セグメントサイズが表サイズと比較して小さい索引があり、索引列(①)にはNOT NULL制約が付与されていない
- 上記①の索引列をCOUNT引数に指定している
- 上記①の索引列にはNULL値が存在しない(存在する場合NULLの件数分COUNT(*)と異なる結果が戻される)
SQL> set timing on SQL> select count(*) from cnt; COUNT(*) ---------- 500000 経過: 00:00:12.87 SQL> select count(1) from cnt; COUNT(1) ---------- 500000 経過: 00:00:12.76 SQL> select count(col1) from cnt; COUNT(COL1) ----------- 500000 経過: 00:00:00.12
COUNT(<列名>)とすると何故性能が向上するのか?
上記例ではCOUNT(*)、COUNT(1)とCOUNT(COL1)では異なる実行計画が採択されているためです。 COUNT(*)やCOUNT(1)が表の全件数を戻すのに対し、COUNT(COL1)はCOL1がNULLではないレコードの件数を戻します。 COL1にはNOT NULL制約がないのでNULLを許容します。したがってNULLレコードの情報が含まれないCOL1の索引を見てもCOUNT(*)で求められる表の全件数が取得できません。 これに対してCOUNT(COL1)とした場合はCOL1がNULLの件数を除いた結果が求められる結果であるためCOL1索引のインデックスファストフルスキャンの実行計画を 選択することが可能になります。COL1の索引のセグメントサイズは非常に小さい為COUNT(*)としたときに選択されるTABLE FULL SCAN と比較して読み込みブロック数が極端に削減し、性能が向上します
DDL及びデータの内容--col2のみサイズが大きい列長とすることでcol1の索引サイズと表サイズが大きく異なる状態を作り出す create table cnt(col1 number, col2 char(2000)); begin for i in 1..500000 loop insert into cnt values(i,i); end loop; commit; end; / exec dbms_stats.gather_table_stats(null,'CNT'); create index cnt_ix1 on cnt(col1);各SQLの実行計画
SQL> select count(*) from cnt; COUNT(*) ---------- 500000 経過: 00:00:12.87 実行計画 ---------------------------------------------------------- Plan hash value: 4290980810 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46450 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| CNT | 500K| 46450 (1)| 00:00:02 | ------------------------------------------------------------------- 統計 ---------------------------------------------------------- 0 recursive calls 0 db block gets 167054 consistent gets 167047 physical reads 0 redo size 553 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(1) from cnt; COUNT(1) ---------- 500000 経過: 00:00:12.76 実行計画 ---------------------------------------------------------- Plan hash value: 4290980810 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46450 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| CNT | 500K| 46450 (1)| 00:00:02 | ------------------------------------------------------------------- 統計 ---------------------------------------------------------- 0 recursive calls 0 db block gets 167054 consistent gets 167036 physical reads 0 redo size 553 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(col1) from cnt; COUNT(COL1) ----------- 500000 経過: 00:00:00.12 実行計画 ---------------------------------------------------------- Plan hash value: 3583107423 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 305 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FAST FULL SCAN| CNT_IX1 | 500K| 2441K| 305 (1)| 00:00:01 | --------------------------------------------------------------------------------- 統計 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1125 consistent gets 1117 physical reads 0 redo size 556 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed