PK指定のクエリでフルスキャンが発生
PK指定のクエリがフルスキャンを起こしていたので、原因を調査しました。
問題となっていたSQLは、以下のとおりです。
pk同士の内部結合を行うviewと、そのviewに対して主キー検索するクエリです。
このクエリの実行計画がtable1、table2それぞれをフルスキャンし、ハッシュ結合していました。
問題のクエリ（サンプル）
select
col1
, col2
, col3
from
view1
where
pk = '100';
問題のクエリで使用されているview1のクエリ（サンプル）
select
t1.col1
, t2.col2
, rownum as col3
from
table1 t1
inner join table2 t2
on t1.pk = t2.pk;
原因はview1のSELECT句のrownum
view1のSELECT句からrownumを除外した状態で、再実行するとインデックスを使用した高速スキャンになる。
1. フラットなクエリのSELECT句にrownumを指定する
以下のクエリのように、ネストしないクエリのSELECT句にrownumが入っていてもフルスキャンは発生しません。
これはフルスキャンにならない
select pk, col, rownum from table1 where pk = '105';
実行結果と実行計画
PK COL ROWNUM
---------- ------ ----------
105 05 1
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 17 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C007532 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
2. WHERE句の条件がない、ネストしたクエリブロックのSELECT句にrownumを指定する
WHERE句の条件がなく、ネストしたクエリブロックのSELECT句にrownumが指定されている場合に、フルスキャンとなります。
これはフルスキャンになる
select * from (select pk, col, rownum from table1) where pk = '105';
実行結果と実行計画
PK COL ROWNUM
---------- ------ ----------
105 05 6
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 330 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 11 | 330 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TABLE1 | 11 | 187 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
ここで、1のフラットなクエリと、2のネストしたクエリのrownumの実行結果を見ると、以下のように違いが出ています。
| 1.フラットなクエリ | 2.ネストしたクエリ |
|---|---|
| 1 | 6 |
つまり、以下のクエリにrownumが指定されていることでtable1内の全レコードにrownumの結果が付与されることになります。
結果として、2のクエリはtable1全体の中で、pkが105となるレコードのrownumは6となるという結果を返します。
ネストしたクエリブロック
select pk, col, rownum from table1
これらより、WHERE句の条件がなく、ネストしたクエリブロック内にrownumを指定するとフルスキャンが発生することになります。
以降の実行結果は参考です。
WHERE句の条件がある場合
これはフルスキャンにならない
select * from (select pk, col, rownum from table1 where pk = '105');
実行結果と実行計画
PK COL ROWNUM
---------- ------ ----------
105 05 1
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 30 | 1 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 17 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C007532 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
rownumを指定しない場合
これはフルスキャンにならない
select * from (select pk, col from table1) where pk = '105';
実行結果と実行計画
PK COL
---------- ------
105 05
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 17 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007532 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------