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

# PK指定のクエリでフルスキャンが発生
PK指定のクエリがフルスキャンを起こしていたので、原因を調査しました。
問題となっていたSQLは、以下のとおりです。
pk同士の内部結合を行うviewと、そのviewに対して主キー検索するクエリです。

このクエリの実行計画がtable1、table2それぞれをフルスキャンし、ハッシュ結合していました。

```sql:問題のクエリ（サンプル）
select
    col1
  , col2
  , col3
from
    view1
where
    pk = '100';
```
```sql:問題のクエリで使用されている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が入っていてもフルスキャンは発生しません。

```sql:これはフルスキャンにならない
select pk, col, rownum from table1 where pk = '105';
```

```sql:実行結果と実行計画
        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が指定されている場合に、フルスキャンとなります。

```sql:これはフルスキャンになる
select * from (select pk, col, rownum from table1) where pk = '105';
```

```sql:実行結果と実行計画
         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となるという結果を返します。

```sql:ネストしたクエリブロック
select pk, col, rownum from table1
```

これらより、WHERE句の条件がなく、ネストしたクエリブロック内にrownumを指定するとフルスキャンが発生することになります。
以降の実行結果は参考です。

### WHERE句の条件がある場合

```sql:これはフルスキャンにならない
select * from (select pk, col, rownum from table1 where pk = '105');
```

```sql:実行結果と実行計画
        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を指定しない場合

```sql:これはフルスキャンにならない
select * from (select pk, col from table1) where pk = '105';
```

```sql:実行結果と実行計画
        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 |
-------------------------------------------------------------------------------------------
```