【SQL】高速化

インデックス

データの並び順をDBが保持する。
データ件数が多いテーブルの検索を早くすることができる。
初期状態では主キーに対してインデックスが割り当てられている。
UPDATE、INSERT、DELETEの速度は遅くなる。

--確認
SELECT * FROM DBA_INDEXES;

--作成
CREATE INDEX インデックス名 ON スキーマ名.テーブル名(項目名1, 項目名2);

--削除
DROP INDEX インデックス名;

実行計画

インデックスが使用されているか等確認できる

--select文の解析
explain plan for 確認したいselect文;

--実行計画表示
select * from table(DBMS_XPLAN.DISPLAY);

統計情報

統計情報はデフォルトでは一日一回自動で収集される。
コマンドで手動で更新することもできる。

--確認（テーブル単位）
SELECT TABLE_NAME,
 TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') AS LAST_ANALYZED
FROM USER_TABLES
ORDER BY TABLE_NAME;

--確認（インデックス単位）
SELECT TABLE_NAME, INDEX_NAME,
 TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') AS LAST_ANALYZED
FROM USER_TABLES
ORDER BY TABLE_NAME, INDEX_NAME;

--更新
ANALYZE TABLE テーブル名 COMPUTE STATISTICS;

--削除
ANALYZE TABLE テーブル名 DELETE STATISTICS;

--自動更新の確認
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
--※AUTOTASKが「ENABLE」であれば自動実行が有効

ヒント

オプティマイザの自動ロジックによる決定ではなく、ソース・コードに明示的に記述して指定した方法

Select

--結合順序
--※結合結果の行数ができるだけ少なくなる順にする
--1.FROM句に書いたテーブルの並びで結合する
SELECT /*+ORDERED*/ a.* FROM t1 a, t2 b WHERE a.COL1 = b.COL1;

--2.結合の順序を任意に決める
SELECT /*+LEADING(b,a)*/ a.* FROM t1 a, t2 b WHERE a.COL1 = b.COL1;

--インデックスを指定
SELECT /*+INDEX(a インデックス名)*/ * FROM t1 a WHERE a.col1 = 'AAA';

Insert

--ダイレクト・パス・インサート
--※制約としてトランザクションが終わるまで
--INSERTするテーブルをロックしてしまう(selectも不可)
INSERT /*+ APPEND*/ INTO t1 SELECT ～

nologging

REDOログ（更新ログ）を出力しない
REDOログによる復元ができないため注意

INSERT INTO nologging TABLE_A SELECT～

ExistよりInnerJoin

--Exists
SELECT * FROM t1 WHERE EXISTS
    (SELECT 1 FROM t2 WHERE t1.id = t2.id AND t2.created >= '2019-06');

--InnerJoin
SELECT * FROM t1 INNER JOIN
 (SELECT DISTINCT id FROM t2 WHERE t2.created >= '2019-06') AS sub
ON sub.id = t1.id;

豆知識

・IN の引数は、最もヒットする値から書く
・INよりEXISTS
・UNION より UNION ALL