帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される! (23ai〜) Tweet
ちょっと意地悪してみました。
前回のエントリで、以下のようにindex only scanにできるBOOLEAN型の単列索引を作成したのを覚えていますか?
SCOTT@freepdb1> create index ix_bool_example2 on example2(b1);
Index created.
SCOTT@freepdb1> set autot trace exp stat
SCOTT@freepdb1> select count(1) from example2 where b1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1159143718
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
586 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
この状態で、述語のBOOLEAN列(索引列)に暗黙型変換が発生する意地悪をしてみました。
BOOLEAN列を数値として計算した後に、falseと比較しているので、TO_NUMBER() の後に、TO_BOOLEAN()されています。当然、索引は使えなくなるので index only scan から table access fullに変わっています! (狙い通りですね。こんなことしないと思いますけどもw
で、みなさん、SQL*Plusのautoraceに見慣れない情報が出力されているのに気づきませんか!?
そう、Oracle Database 23ai free developerでは、SQL*PlusのautotraceでSQL Analysis reportが表示され、index range scan できるよう、述語の書き換えをご検討くさい! とレコメンドされています!(赤字部分)
このメッセージカスタマイズできたりしたらw 「チッ、少しは考えたSQL書けよな〜。」と上から目線のメッセージに変えてみたいw(無理でしょうけどもw)
ということで、おまけでした!
SCOTT@freepdb1> select b1 from example2 where (b1 - 1) = false;
18 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1894430233
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXAMPLE2 | 1 | 1 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_BOOLEAN(TO_NUMBER("B1")-1)=FALSE)
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SEL$1 / "EXAMPLE2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"B1"
Statistics
----------------------------------------------------------
9 recursive calls
6 db block gets
11 consistent gets
0 physical reads
1012 redo size
914 bytes sent via SQL*Net to client
133 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
この例だと的確なレコメンドしてるんだけど、もっとムズイやつだとどうなるんだろうね。誰か本番で使って結果公開して欲しい :)
Enjoy SQL!
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)
| 固定リンク | 1
コメント