目次
はじめに
複数のテーブルを何度も結合するような処理を実行する場合、処理の途中のデータセットが大きいと、大きなデータセットのまま後続の処理も実行するため、SQL実行時間が比較的長くなるケースがあります。このような状況を避けるためには、処理の初期段階で出来る限りデータセットを小さくする（WHERE句で抽出データを絞る）ことが大切です。
しかしながら、SQLの記述内容によっては、WHERE句で条件を指定しても初期段階でデータセットが絞り込まれないケースがあります。
本記事では、上記のケースに該当するSQL記述内容を紹介します。
データセットが絞り込まれているか確認するためには、実行計画の「Rows」を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/*** Rowsが4M（約400万行の読み込み）の実行例 ***/ dbadmin=> EXPLAIN dbadmin-> SELECT count(*) FROM lineorder WHERE lo_orderdate = 19920101; QUERY PLAN --------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT count(*) FROM lineorder WHERE lo_orderdate = 19920101; Access Path: +-GROUPBY NOTHING [Cost: 2M, Rows: 1] (PATH ID: 1) | Aggregates: count(*) | +---> STORAGE ACCESS for lineorder [Cost: 2M, Rows: 4M] (PATH ID: 2) /* Rowsが4M */ | | Projection: public.LINEORDER_DBD_2_rep_d1223 | | Filter: (lineorder.LO_ORDERDATE = 19920101) ＜以降、省略＞ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/*** Rowsが750M（約7億5000万行の読み込み）の実行例 ***/ dbadmin=> EXPLAIN dbadmin-> SELECT count(*) FROM lineorder WHERE lo_orderdate != 19920101; QUERY PLAN --------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT count(*) FROM lineorder WHERE lo_orderdate != 19920101; Access Path: +-GROUPBY NOTHING [Cost: 2M, Rows: 1] (PATH ID: 1) | Aggregates: count(*) | +---> STORAGE ACCESS for lineorder [Cost: 2M, Rows: 750M] (PATH ID: 2) /* Rowsが750M（約7億5000万行の読み込み） */ | | Projection: public.LINEORDER_DBD_2_rep_d1223 | | Filter: (lineorder.LO_ORDERDATE <> 19920101) ＜以降、省略＞ |
SQL記述の留意事項
否定演算子を使用する場合
否定演算子を使用している場合は、読み込みデータ量が増加します。|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderdate = 19920101; |
|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderdate != 19920101; |
WHERE句で指定する条件の左辺に関数を使用する場合
WHERE句で指定する条件の左辺に関数を使用する場合は、読み込みデータ量が増加します。|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderdate = 19920101; |
|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE TO_CHAR(lo_orderdate) = '19920101'; |
LIKE演算子を使用して中間一致または後方一致で検索する場合
LIKE演算子を使用して中間一致または後方一致で検索する場合は、読み込みデータ量が増加します。|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderpriority LIKE '1-URGEN%'; |
|
1 2 3 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderpriority LIKE '%-URGENT'; dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderpriority LIKE '%-URGEN%'; |
IS NULL演算子を使用する場合
IS NULL演算子を使用する場合は、読み込みデータ量が増加します。|
1 |
dbadmin=> SELECT count(*) FROM lineorder WHERE lo_orderdate IS NULL; |
ビューを作成し、ビューに対して条件を指定する場合（１）
ビュー内のSELECTリストに検索条件項目への関数を含む場合、読み込みデータ量が増加します。| 種類 | オブジェクト名 |
|---|---|
| トランザクションテーブル | 「lineorder」 |
| マスタテーブル | 「date1」 |
| ビュー１ | 「lo_view」 |
| ビュー２ | 「lo_view2」 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
dbadmin=> /***** ビュー１の内容 *****/ dbadmin=> CREATE OR REPLACE VIEW lo_view as dbadmin-> SELECT dbadmin-> l.lo_orderdate, dbadmin-> l.lo_orderpriority, dbadmin-> d.d_datekey, dbadmin-> d.d_date dbadmin-> FROM lineorder AS l dbadmin-> LEFT OUTER JOIN date1 AS d ON l.lo_orderdate = d.d_datekey; CREATE VIEW dbadmin=> /***** ビュー２の内容 *****/ dbadmin=> CREATE OR REPLACE VIEW lo_view2 as dbadmin-> SELECT dbadmin-> l.lo_orderdate, dbadmin-> SUBSTR(l.lo_orderpriority, 1, 8) AS lo_orderpriority, /*** ビュー１と比較して関数を使用 ***/ dbadmin-> d.d_datekey, dbadmin-> d.d_date dbadmin-> FROM lineorder AS l dbadmin-> LEFT OUTER JOIN date1 AS d ON l.lo_orderdate = d.d_datekey; CREATE VIEW |
|
1 |
dbadmin=> SELECT count(*) FROM lo_view WHERE lo_orderpriority='1-URGENT'; |
|
1 |
dbadmin=> SELECT count(*) FROM lo_view2 WHERE lo_orderpriority='1-URGENT'; |
ビューを作成し、ビューに対して条件を指定する場合（２）
外部結合を行うビューを作成し、そのビューに対して条件を指定する場合、以下の動作をとります。・トランザクションテーブル（結合元）の項目を条件に指定した場合 … 読み込みデータ量が小さい
・マスタテーブル（結合先）の項目を条件に使用した場合 … 読み込みデータ量が増加
詳細は以下の記事をご参照ください。
＜外部結合を行っているViewに条件を付けて検索する際の注意点＞
http://vertica-tech.ashisuto.co.jp/outer_view/
参考
＜SQLの実行計画を確認する方法＞http://vertica-tech.ashisuto.co.jp/sql-plan/
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。更新履歴
2020/02/28 誤植を修正（「IS NULL演算子を使用する場合」の箇所）2019/12/26 本記事を公開
