【Oracle実行計画】ネステッド・ループにおける結合順序のパフォーマンスの違い

###【概要】
ネステッド・ループで顧客テーブル(100万件)と都道府県テーブル(47件)を結合するときに結合順序によってパフォーマンスが変わることを確認します。

###【環境】
・Windows8.1
・Oracle 11.2.0
・SQL Developer バージョン4.1.5.21
※インストール方法については下記を参照してください
　　[備忘録]vagrantでOracleインストール&SQL Developerで接続する

###【テストデータ作成】

####手順１　顧客テーブルの作成

CREATE TABLE CUSTOMER (
    CUSTOMER_NUM NUMBER(8)
   ,LAST_NAME VARCHAR2(20)
   ,FIRST_NAME VARCHAR2(20)
   ,AGE NUMBER(3)
   ,PREFECTURE_ID VARCHAR2(2)
   ,PRIMARY KEY (CUSTOMER_NUM)
) 

####手順２　都道府県テーブルの作成

CREATE TABLE PREFECTURE (
    PREFECTURE_ID VARCHAR2(2)
   ,PREFECTURE_NAME VARCHAR2(15)
   ,PRIMARY KEY (PREFECTURE_ID)
)

####手順３　顧客テーブルのデータ作成

-- 100万件データを作成するため、少し時間がかかる場合があります
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO CUSTOMER
       SELECT
          i
         ,DBMS_RANDOM.STRING('a', MOD(TRUNC(DBMS_RANDOM.value(1, 21)), 21))
         ,DBMS_RANDOM.STRING('a', MOD(TRUNC(DBMS_RANDOM.value(1, 21)), 21))
         ,TRUNC(DBMS_RANDOM.value(20, 80))
         ,LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.value(1, 48)), 48)), 2, '0')
      FROM DUAL;
  END LOOP;
  COMMIT;
END;
/

####手順４　都道府県テーブルのデータ作成

INSERT ALL
INTO PREFECTURE VALUES ('01', '北海道')
INTO PREFECTURE VALUES ('02', '青森')
INTO PREFECTURE VALUES ('03', '岩手')
INTO PREFECTURE VALUES ('04', '宮城')
INTO PREFECTURE VALUES ('05', '秋田')
INTO PREFECTURE VALUES ('06', '山形')
INTO PREFECTURE VALUES ('07', '福島')
INTO PREFECTURE VALUES ('08', '茨城')
INTO PREFECTURE VALUES ('09', '栃木')
INTO PREFECTURE VALUES ('10', '群馬')
INTO PREFECTURE VALUES ('11', '埼玉')
INTO PREFECTURE VALUES ('12', '千葉')
INTO PREFECTURE VALUES ('13', '東京')
INTO PREFECTURE VALUES ('14', '神奈川')
INTO PREFECTURE VALUES ('15', '新潟')
INTO PREFECTURE VALUES ('16', '富山')
INTO PREFECTURE VALUES ('17', '石川')
INTO PREFECTURE VALUES ('18', '福井')
INTO PREFECTURE VALUES ('19', '山梨')
INTO PREFECTURE VALUES ('20', '長野')
INTO PREFECTURE VALUES ('21', '岐阜')
INTO PREFECTURE VALUES ('22', '静岡')
INTO PREFECTURE VALUES ('23', '愛知')
INTO PREFECTURE VALUES ('24', '三重')
INTO PREFECTURE VALUES ('25', '滋賀')
INTO PREFECTURE VALUES ('26', '京都')
INTO PREFECTURE VALUES ('27', '大阪')
INTO PREFECTURE VALUES ('28', '兵庫')
INTO PREFECTURE VALUES ('29', '奈良')
INTO PREFECTURE VALUES ('30', '和歌山')
INTO PREFECTURE VALUES ('31', '鳥取')
INTO PREFECTURE VALUES ('32', '島根')
INTO PREFECTURE VALUES ('33', '岡山')
INTO PREFECTURE VALUES ('34', '広島')
INTO PREFECTURE VALUES ('35', '山口')
INTO PREFECTURE VALUES ('36', '徳島')
INTO PREFECTURE VALUES ('37', '香川')
INTO PREFECTURE VALUES ('38', '愛媛')
INTO PREFECTURE VALUES ('39', '高知')
INTO PREFECTURE VALUES ('40', '福岡')
INTO PREFECTURE VALUES ('41', '佐賀')
INTO PREFECTURE VALUES ('42', '長崎')
INTO PREFECTURE VALUES ('43', '熊本')
INTO PREFECTURE VALUES ('44', '大分')
INTO PREFECTURE VALUES ('45', '宮崎')
INTO PREFECTURE VALUES ('46', '鹿児島')
INTO PREFECTURE VALUES ('47', '沖縄')
SELECT * FROM DUAL;

###【実行計画】

####①顧客テーブルが駆動表、都道府県テーブルが結合表の場合

SELECT /*+ USE_NL(CUSTOMER, PREFECTURE) LEADING(CUSTOMER, PREFECTURE) */ * FROM CUSTOMER, PREFECTURE WHERE CUSTOMER.PREFECTURE_ID = PREFECTURE.PREFECTURE_ID;

####②顧客テーブルが結合表、都道府県テーブルが駆動表の場合

SELECT /*+ USE_NL(CUSTOMER, PREFECTURE) LEADING(PREFECTURE, CUSTOMER) */ * FROM CUSTOMER, PREFECTURE WHERE CUSTOMER.PREFECTURE_ID = PREFECTURE.PREFECTURE_ID;

ループ回数が①は100万回、②は47回となるため、①より②の方がパフォーマンスが良いことになります。

###【おまけ　～ソート・マージ結合とハッシュ結合の場合～】

####③ソート・マージ結合の場合

SELECT /*+ USE_MERGE(CUSTOMER, PREFECTURE) */ * FROM CUSTOMER, PREFECTURE WHERE CUSTOMER.PREFECTURE_ID = PREFECTURE.PREFECTURE_ID;

####④ハッシュ結合の場合

SELECT * FROM CUSTOMER, PREFECTURE WHERE CUSTOMER.PREFECTURE_ID = PREFECTURE.PREFECTURE_ID;

###【結果】
コストを比較すると以下のようになりました(左からコストが小さい順)。

②(都道府県テーブルが駆動表:1485)<④(ハッシュ結合:1495)<③(ソート・マージ結合:14774)<①(顧客テーブルが駆動表:56891)

②のコストが一番小さいのですが、CBOはヒント句なしの時にはハッシュ結合が最適な実行計画と選択したようです。
今回は顧客テーブルが駆動表の場合のネステッド・ループの実行計画がコストが一番小さかったですが、常にネステッド・ループが最適だとは限らないのでご注意ください。

###【参考書籍】
プロとしてのSQLチューニング入門
　実際に実行した実行計画や統計情報が載っていて、平易な説明で分かりやすい本でした。
　発行日が2007年と古くAmazonでは中古本しかないですが、安く購入できて今勉強しても十分役に立ちました。