2021.08.26
SQLチューニング（第12回）「スカラーサブクエリの理解と効率的なSQLの作成」(2/5)
今回は、スカラーサブクリの実行回数に関するお話です。
３.１.１.２ 抽出されるデータは常に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 ^^
