« MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161709 | トップページ | DTM / 2月に公開した曲 »

2024年2月21日 (水) / Author : Hiroshi Sekiguchi.

Top N Queryの弱点と対策

久々にSQLチューニングネタです。しかも、真面目な感じのタイトルw

11年前に、rownum使って満足しちゃってると.....おまけのおまけ FETCH FIRST N ROWS ONLY編 という、rownumや、fetch first N rows only でTOP N rows を取得する際に、忘れがちな問題として、TOP N rowsの行数未満(空振りを含む)の場合、全行読み込んでしまうので、絞り込みしにくいような検索で、rownumやfetch first N rowsで全表走査を回避したつもりになっていると、痛い目にあうよ!

というネタを書いていました。

この大切な癖、忘れちゃってませんか? 大変なことになりますよ。。ということで、再びこのネタを書くことにしました。

Oracle Database 21cを使っていますが11年も昔から変わったところはないので、塩漬けしている古〜いオラクルでも楽しめる内容にしてありますw

 

まず、今日の準備から

SCOTT@orclpdb1> @fullscanfulness

表が削除されました。

経過: 00:00:01.31
1 create table fullscanfulness
2 (
3 id number not null
4 ,dummy_text varchar2(4000)
5 ,hoge_flg number(1) not null
6 ,constraint pk_fullscanfulness primary key(id)
7* )

表が作成されました。

経過: 00:00:00.29
1 begin
2 for i in 1..100000 loop
3 insert into fullscanfulness values(i,lpad('*',3500,'*'),0);
4 if mod(i,100)=0 then commit; end if;
5 end loop;
6* end;

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:22.45
1* insert into fullscanfulness values(100001,lpad('*',3500,'*'),1)

1行が作成されました。

経過: 00:00:00.01

コミットが完了しました。

経過: 00:00:00.01

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:03.53
1* select count(1) from fullscanfulness

COUNT(1)
----------
100001

経過: 00:00:00.01

SEGMENT_NAME BLOCKS
------------------------------ ----------
FULLSCANFULNESS 51200
PK_FULLSCANFULNESS 256

経過: 00:00:00.19

 

と、クセのありそうな、なんとなく、見る機会の多そうなフラグ列のある表に主キーだけがある状態です。
なお、最後のデータ id=100001だけフラグ列が 1 になっています。最後の行なので、物理的にも最後尾にしてあります(意図的に)

 

フラグ=1になっている行が表どの位置にあるかイメージ図で書くと以下のような感じ

20240221-200436

 

表セグメントのブロック数は上記のとおりですが、まずは、実際にtable full scan させてみましょう physical reads/ consistent gets からもわかるように綺麗にw 読み込まれてます!

SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> @fullscanfulness2
1 SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5* fullscanfulness

100001行が選択されました。

経過: 00:00:03.34

実行計画
----------------------------------------------------------
Plan hash value: 1963277787

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 334M| 13807 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| FULLSCANFULNESS | 100K| 334M| 13807 (1)| 00:00:01 |
-------------------------------------------------------------------------------------


統計
----------------------------------------------------------
137 recursive calls
0 db block gets
53724 consistent gets
50133 physical reads
0 redo size
2322822 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
100001 rows processed

 

では、早速、本題ですw

rownumでも良いのですが、fetch first N rows onlyで WHERE句なし、つまり、全表走査上等な状態ですが、10行取得だけ。つまり、table full scanではありますが、10行取得したところまでで終了させます。
WINDOW NOSORT STOPKEYというオペレーションが増加しています。rownumだと、STOPKEYだけですよね。ここがrownumとは違うところですが、動作としては行数をカウントして、制限値に達したところで走査終了とするためのオペレーションが追加されています。
これで、table full scanであっても、全データブロックを読み取ることはないですよね。みなさんご存知の通りです。 consistent gets = 8 なので、 8 ブロックしか読みこんでません。

ここまではいいですよね。

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35060 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35060 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

では、次の例

WHERE句で条件を指定しておきます。なかなか渋い検索条件ですよね。一般的に索引は作成しにくいですw。 
TOP N クエリーなので一つ前の例のように table full scanは回避できるは。。。。ですよね。

想定通り、必要な行数取得後に、table full scanは止まって必要最小限の表データブロックだけアクセスしています。 いいじゃないですか。。これで。

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 0
8* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35080 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35080 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - filter("HOGE_FLG"=0)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

 

では、Top N クエリーをやめて、WHERE条件だけ指定します。 hoge_flg = 1 である、表データブロックの最後のブロック格納されている行を1行取得してみましょう。

hoge_flg列には索引はないので、table full scanしかできません。当然、全データブロックを読み込み(物理読み込み)して1行取得しています。これも想定通りです。Top N クエリーで制限していないので最後まで読み込んでしまいますよね。

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7* hoge_flg = 1

経過: 00:00:00.19

実行計画
----------------------------------------------------------
Plan hash value: 1963277787

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50001 | 167M| 13807 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FULLSCANFULNESS | 50001 | 167M| 13807 (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
50144 consistent gets
50133 physical reads
0 redo size
673 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

さて、次が問題です。

一つ前の例と同じWHERE条件で、FETCH FIRST 10 ROWS ONLYでTop N クエリーしてみましょう。どうなったかわかりますか?

実行計画上は、これまでと同じく、 table full scanで、STOPKEYによる途中停止オペレーションも含まれていますが、、、、全データブロックを読み込んでしまいました。

 

なぜでしょう?

 

理由は、Top N クエリーの条件に指定された行数に満たない行数しか存在しなかったから、ですね!。 
これが rownum / fetch first N rows onlyの弱点なのです。条件に満たないことが確定するのは、table full scanで全データブロックを読み込み終わるまで確定しません。
最後まで読み切ってしまうんです。途中で止まらないのです。。。

大問題ですね!これw(ワロてますが)

僕たちの Top N クエリー、ダメじゃん。みたいな。(そんなことはないですが、考慮が漏れているだけなのでw)

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 1
8* FETCH FIRST 10 ROWS ONLY

経過: 00:00:00.34

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35080 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35080 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
50153 consistent gets
50082 physical reads
0 redo size
673 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

でも、もう一つ、残念なお知らせ。というか例を。

 

FETCH FIRST N ROWSの条件に満たなかったから、全データブロックを読んでしまったわけだから、条件を満たせば、絶対、俺たちの Top N クエリーは、table full scanの途中で止まってくれるはずだ!

そんなことはないですねw

以下の例では、FETCH FISRT N ROWSの条件を満たせてはいますが、ヒットした1行のデータは、運の悪いことに、全表データブロック中、最も最後のブロックに存在しています。
したがって、索引のないこの表では、最後のデータブロックを読み込むまで、FETCH FIRST N ROWSの条件は満たせないため、全データブロックを読み込むしかなくなっています。残念!
(対象行を最後のデータブロックになるような小細工をしていたのはこれを見せたかったわけです)

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 1
8* FETCH FIRST 1 ROWS ONLY

経過: 00:00:00.33

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 48 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 3508 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 1 | 3508 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
50124 consistent gets
50053 physical reads
0 redo size
673 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

さらに、Top N クエリーの弱点を再確認してみましょう。

 

今度は、空振りする検索条件です。対象データは0件です。そうでう。ここまでついて来れた皆さんなら、もうお気づきだと思いますが。空振りする検索条件だと、0件であることが確定するのはどういう状態になった時でしょうか?

そうです。 変なマウントおじさんが登場してきた時(違w

ではなくて、全データブロックを読み終えた時ですね。 table full scan は Top N クエリーだけでは止まれないケースが存在するんですよ。みなさん!

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 9
8* FETCH FIRST 1 ROWS ONLY

レコードが選択されませんでした。

経過: 00:00:00.20

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 13807 (1)| 00:00:01 |
|* 1 | VIEW | | 1 | 48 | 13807 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 3508 | 13807 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 1 | 3508 | 13807 (1)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1)
3 - filter("HOGE_FLG"=9)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
50145 consistent gets
50133 physical reads
0 redo size
453 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

 

では、追加の確認、Top N クエリーの条件が早期に満たされれば、table full scanは回避できますよね。という念の為の確認です。
以下では、表の先頭ブロックに Top N クエリーの条件を満足させるためのデータを用意しました。


20240221-200459

 

この状態であれば、table full scanは止められるはずです。(常にこんな状態になることは稀なわけですけども)

結果は見ての通り、table full scanは途中で止まりました。当然と言えば当然ですが。

  1* update fullscanfulness set hoge_flg = 1 where id between 1 and 9

9行が更新されました。

経過: 00:00:00.02

コミットが完了しました。

経過: 00:00:00.00
1* select id from fullscanfulness where hoge_flg=1

ID
----------
3
4
5
6
7
8
9
1
2
100001

10行が選択されました。

経過: 00:00:00.21
1 SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 1
8* FETCH FIRST 1 ROWS ONLY

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 48 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 3508 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 1 | 3508 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=1)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
671 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

では、事前準備で、表の先頭ブロックには9行だけ WHERE条件に該当するデータを置きました。ただし、 Top N クエリーの条件は満たせません。
Top N クエリーの条件を満たすための最後のピースは、表データの最後尾のブロックに置いてあります。

20240221-200422

 

結果は、またまた、 Top N クエリーの効果で、table full scanが途中で止まることはできず、全データブロックを読み込んでしまいました。辛いですね(この状況)

  1  SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 1
8* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.34

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35080 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35080 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
50125 consistent gets
50053 physical reads
0 redo size
802 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

次の例では、表データの最後尾のデータブロックに置いたデータのフラグを0にして、WHERE条件を満たす行を 9行にしました。
これだと、表データの先頭にある数ブロックだけでは、 Top N クエリーの条件を満たせません。

 

結果は、見るまでもなく(見てますがw)、表の全データブロックを読み込むまで終了できません。

  1* update fullscanfulness set hoge_flg = 0 where id = 100001

1行が更新されました。

経過: 00:00:00.00

コミットが完了しました。

経過: 00:00:00.01
1 SELECT
2 id
3 ,substr(dummy_text,1,10) as dummy
4 FROM
5 fullscanfulness
6 WHERE
7 hoge_flg = 1
8* FETCH FIRST 10 ROWS ONLY

9行が選択されました。

経過: 00:00:00.34

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35080 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35080 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
50154 consistent gets
50082 physical reads
0 redo size
792 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

 

では、いよいよ。Top N クエリーの弱点に対する対策には何が効くのかみていきましょう。あれしかないですけどもw

 

一般的な状況だと、ほぼ、作成したくない索引を作ってみます。この状況では仕方ないですw(意図的ですがw)

  1* create index ix_fullscanfulness on fullscanfulness(hoge_flg)

索引が作成されました。

経過: 00:00:00.44

 

念の為、索引を強制するヒントを追加しています。

 

条件は一つ前の例と同じです。違いはフラグ列に作成した単一列索引だけです。

 

結果は、ご覧の通り。 狙い通り、 Top N クエリーの条件は満たせていませんが、WHERE条件に一致する行だけを索引経由で取得することで、 table full scanを完全に回避しています。 これが Top N クエリーの弱点回避への最後の希望です :)
状況次第ですが、かなり、何この索引と突っ込まれる系の索引を作成しなければいけなくなることも多いです。ですが、これしかないのです。

  1  SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 1
11* FETCH FIRST 10 ROWS ONLY

9行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3248696394

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 7 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 7 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 10 | 35080 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 10 | 35080 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 50001 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
4 - access("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
792 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

 

さらに、 Top N クエリーの弱点回避の効果を確認してみましょう。

次は、Top N クエリーの条件は満たせていますが、WHERE条件を満たせる行が、表データブロックの先頭と最後尾に存在しているケースです。

表の先頭ブロックに9行の該当データがあるので、最後尾のデータブロックにある行を更新して置きます。索引が存在しない状態では、table full scanを途中で止めることはできませんでしたが、どうなりましか?

 

止まっていますよね!

  1* update fullscanfulness set hoge_flg = 1 where id = 100001

1行が更新されました。

経過: 00:00:00.00

コミットが完了しました。

経過: 00:00:00.01
1 SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 1
11* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 3248696394

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 7 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 7 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 10 | 35080 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 10 | 35080 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 50001 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
4 - access("HOGE_FLG"=1)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
802 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

WHERE条件を変更しました。表データブロックの先頭ブロックに該当行全てが含まれるようにしてみました。もともと、table full scanが途中で止まるケースですが、索引を利用させても遜色ない結果を得られます。唯一の違いは索引の存在ですね。DMLなどには性能的にマイナスの影響はありますが索引の数次第のところもあるので、どちらが大切かをよく検討、検証して決める必要はあります

   1* update fullscanfulness set hoge_flg = 0 where id between 1 and 9

9行が更新されました。

経過: 00:00:00.00

コミットが完了しました。

経過: 00:00:00.00
1 SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 0
11* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 3248696394

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 7 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 7 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 10 | 35080 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 10 | 35080 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 50001 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
4 - access("HOGE_FLG"=0)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

もう一つ、意地悪な確認をしてみましょう。

表データの中間付近に、WHERE条件を満たす行を配置して、索引を利用できないよう NO_INDEXヒントに変更します。


20240221-200451

 

結果は、想定通り、 Top N クエリーは、表データの1/2ほどまで table full scanを行って停止することがわかります。

  1* update fullscanfulness set hoge_flg = 0 where id between 1 and 9 or id = 100001

10行が更新されました。

経過: 00:00:00.00
1* update fullscanfulness set hoge_flg = 1 where id between 50000 and 50010

11行が更新されました。

経過: 00:00:00.01

コミットが完了しました。

経過: 00:00:00.00
1 SELECT
2 /*+
3 NO_INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 1
11* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.24

実行計画
----------------------------------------------------------
Plan hash value: 1910161288

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 5 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 35080 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FULLSCANFULNESS | 10 | 35080 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
3 - filter("HOGE_FLG"=1)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
24739 consistent gets
24672 physical reads
0 redo size
818 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

同じ状態で、ヒントを元に戻し、索引を利用できるようにします。

当然ですが、必要最小限のデータブロックを索引使って取得するようになり、 table full scan は回避できます。

  1  SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 1
11* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3248696394

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 7 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 7 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 10 | 35080 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 10 | 35080 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 50001 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
4 - access("HOGE_FLG"=1)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
818 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

 

次の例は、 空振りのケースでも索引アクセスなら瞬時に空振りが判定できますよね!! という確認です。

WHERE条件に対応した索引を用意するだけで、索引を見たたけで対象データが存在しないことが判定できます。空振りして、table full scanして、全データブロックを総ナメなんて無駄ですよね。

  1  SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 9
11* FETCH FIRST 10 ROWS ONLY

レコードが選択されませんでした。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3248696394

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 480 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 480 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 3508 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 1 | 3508 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
4 - access("HOGE_FLG"=9)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
453 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

 

そんな索引でも索引に含まれていない列でソートされたりすると困ることもあります。という例です。

次の例は、索引でtable full scanを回避できていたTop N クエリーが仕様変更され、索引に含まれないid列のソートが追加されてしまいました。
その結果。。。索引エントリーが読み込まれ、かつ、ソートまでされる結果となってしまいました。ありゃありゃw

  1  SELECT
2 /*+
3 INDEX(fullscanfulness ix_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 0
11 ORDER BY
12 id DESC
13* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.56

実行計画
----------------------------------------------------------
Plan hash value: 3127027845

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 610 | | 61653 (1)| 00:00:03 |
|* 1 | VIEW | | 10 | 610 | | 61653 (1)| 00:00:03 |
|* 2 | WINDOW SORT PUSHED RANK | | 50001 | 167M| 195M| 61653 (1)| 00:00:03 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| FULLSCANFULNESS | 50001 | 167M| | 25103 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_FULLSCANFULNESS | 50001 | | | 92 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC )<=10)
4 - access("HOGE_FLG"=0)


統計
----------------------------------------------------------
49 recursive calls
0 db block gets
50651 consistent gets
50123 physical reads
0 redo size
818 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
10 rows processed

 

前述の例は、索引の効果がなくなってしまったことが原因なので、仕様変更に合わせ、索引も変更しなければなりません!

仕様変更に対応した索引を作成します。新規作成していますが、作り直しでも良いですね。列が増加しただけで他に影響がない索引であれば。

  1* create index ix2_fullscanfulness on fullscanfulness(hoge_flg, id desc)

索引が作成されました。

経過: 00:00:00.51
1 SELECT
2 /*+
3 INDEX(fullscanfulness ix2_fullscanfulness)
4 */
5 id
6 ,substr(dummy_text,1,10) as dummy
7 FROM
8 fullscanfulness
9 WHERE
10 hoge_flg = 0
11 ORDER BY
12 id DESC
13* FETCH FIRST 10 ROWS ONLY

10行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3270412619

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 610 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 610 | 9 (12)| 00:00:01 |
|* 2 | VIEW | | 10 | 610 | 8 (0)| 00:00:01 |
|* 3 | WINDOW NOSORT STOPKEY | | 10 | 35080 | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| FULLSCANFULNESS | 50001 | 167M| 8 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX2_FULLSCANFULNESS | 10 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
3 - filter(ROW_NUMBER() OVER ( ORDER BY SYS_OP_DESCEND("ID"))<=10)
5 - access("HOGE_FLG"=0)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
1 physical reads
0 redo size
818 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

 

フルスキャンそんなにしたいのでしょうか? 無駄なエネルギーを消費させて。。謎は深まる、 

 

フルスキャンフルネスな感じなのだろうか。。

 

では、また。

 


rownum使って満足しちゃってると.....おまけ
rownum使って満足しちゃってると.....おまけのおまけ FETCH FIRST N ROWS ONLY編

 

 

| |

コメント

コメントを書く