Oracle SQLチューニング（第12回）「スカラーサブクエリの理解と効率的なSQLの作成」(2/5)

DROP TABLE SCALAR_T1 PURGE;
DROP TABLE SCALAR_T2 PURGE;
DROP TABLE SCALAR_T3 PURGE;


< SCALAR_T1>

■生成要件

- SCALAR_T1テーブルのデータは合計500,000行
- C1はUnique値を持って、1 ～ 500,000までの値を有している
- C2は値の種類が26種類
- C3はUnique値を持って、100,000 ～ 599,999までの値を有している

■テーブル生成

CREATE TABLE SCALAR_T1
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;

■各カラムにインデックス生成および統計情報収集

CREATE INDEX SCALAR_T1_IDX_01 ON SCALAR_T1 ( C1 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;



< SCALAR_T2>
  
■生成要件

- SCALAR_T2テーブルのデータは合計500,000行
- C1はUnique値であり、1 ～ 500,000までの値を有している
- C2は値の種類が26種類
- C3は合計10種類値を持って、NULLデータも存在

■テーブル生成

CREATE TABLE SCALAR_T2
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,10),0,NULL,MOD(LEVEL,10)) AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;

■各カラムにインデックス生成および統計情報収集

CREATE INDEX SCALAR_T2_IDX_01 ON SCALAR_T2 ( C1 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;


< SCALAR_T3>

■生成要件

- SCALAR_T2テーブルのデータは合計500,000行
- C1はUnique値であり、1 ～ 500,000までの値を有している
- C2は値の種類が26種類
- C3は合計100種類値を持って、NULLデータも存在

■テーブル生成

CREATE TABLE SCALAR_T3
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;



■各カラムにインデックス生成および統計情報収集

CREATE INDEX SCALAR_T3_IDX_01 ON SCALAR_T3 ( C1 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)

SELECT c1, c2, c3,
       (SELECT t2.C1
          FROM SCALAR_T2 T2
         WHERE t2.c2 = t1.c2) AS t2_c1
 FROM SCALAR_T1 T1
WHERE c2 = 'A'
  AND ROWNUM <= 1 ;

ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

SELECT c1,
       c2,
       c3,
       (SELECT t2.C1
          FROM SCALAR_T2 T2
         WHERE t2.c2 = t1.c2
           AND ROWNUM <= 1 ---> 1件だけ抽出するように条件追加
        ) AS t2_c1
 FROM SCALAR_T1 T1 
WHERE c2 = 'A'
  AND ROWNUM <= 1 ;

          C1 C2         C3      T2_C1
      ------ -- ---------- ----------
          26 A      100025         26