L
o
a
d
i
n
g
.
.
.

ホーム

お知らせ

製品・ソリューション

サービス

導入事例・パートナー

EXEM Academy・ブログ

会社情報

採用情報

2021.08.26

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

今回は、スカラーサブクリの実行回数に関するお話です。


3.1.1.2 抽出されるデータは常に1件だけ有効である

スカラーサブクエリで抽出されるデータが1件を超過する場合、SQLは実行エラーが発生する。 理解を助けるためにテストを通じて調べてみる。

Script. スカラーサブクエリテスト用

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) ;

次のテスト[1]でスカラーサブクエリのT2.C2はUnique値ではないので、T1.C2と結合を実行する場合、抽出データが2件以上である。 このような場合ORA-01427エラーが発生する。

テスト[1].

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

ORA-01427はスカラーサブクエリで抽出されるデータが2件以上である時発生するエラーメッセージである。 テスト[1]でエラーが発生しないようにするためにはスカラーサブクエリの抽出データが1件を超過してはいけない。 したがってスカラーサブクエリのT2.C2で抽出されるデータが1件を超過するならば、“ROWNUM <= 1”条件を追加すれば良い。

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

今回は、ここまでです。次回は、データが抽出されないことについてのお話です。では See You ^^