SELECT句にrownumを指定するとフルスキャンとなる場合がある

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