explain plan文 De 索引サイズ見積 / FAQ Tweet

久々の投稿ですw

というか、Oracle ACEのKPIを考えるとどうしても、こうなってしまう大人の事情。

今期一発目の投稿は、意外と知られていない？　explain plan文 De 索引サイズ見積。

統計情報などに依存はしますが、100億年に一度ぐらい、索引サイズどれぐらいになるかねぇ。みたいな聞かれかたしたときに、サクっとタイプして、ほれ！

と、Slackでなげかえしちゃって、飲みに行きましょうね。そこ必死にやるところじゃない時代なわけで。

では、21cもあるのですが、データ仕込むのめんどくさかったので、ありもの 19cの環境で試してみましょう。ちなみに、explain plan で索引サイズを見積もるのは私の記憶によると10gぐらいから使ってた記憶はあるので、昔からのOraclerだと知ってる方は多いはず。(もっと前からあるよーというツッコミ歓迎w）

表の存在とデータを大量に登録してあるtest表を使います。統計は最新化

[oracle@localhost ~]$ sqlplus scott/tiger

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 22:59:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 06 2022 21:39:58 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> desc test
Name　　　Null?                    Type
-------  ------------------------ -------- ----------------------------
NUM      NUMBER

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST',cascade=>true,no_invalidate=>false);

PL/SQL procedure successfully completed.

explain plan文でcreate index文を解析します。索引は作成されないので、躊躇なくタイプしちゃってくださいw
解析が終わったら、utlxpls.sqlを実行すれば見積もりサイズを確認できます。

10m行登録してるのでそれなりのサイズになるようですね。243MB という見積もりがでました！

SQL> explain plan for create index test on test(num);

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2829245909

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |      |    10M|    57M|  9958  (1) | 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| TEST |       |       |            |          |
|   2 |   SORT CREATE INDEX    |      |    10M|    57M|            |          |
|   3 |    TABLE ACCESS FULL   | TEST |    10M|    57M|  4414  (2) | 00:00:01 |
-------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- estimated index size: 243M bytes

14 rows selected.

SQL>

Explain plan文以外では、使い勝手が悪いというかタイプする文字数多くて嫌いな、DBMS_SPACE.CREATE_INDEX_COST()　があります。
以下のような無名PL/SQLブロックを書いておくか、あらかじめ俺俺関数(UDFね)として登録しておくと便利ですが、explain planでいいかなぁ。私はw

set serveroutput on
DECLARE
  used_bytes NUMBER;
  segment_bytes NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST (
    ddl=> 'CREATE INDEX test ON test(num)'
    , used_bytes => used_bytes
    , alloc_bytes =>  segment_bytes
  );
  DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
  DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
END;
/

Explain planより情報量は多いですが、セグメントサイズがどれぐらいになるか知りたいわけなので、他の情報は捨てちゃうことが多い感じはします。
DBMS_SPACE.CREATE_INDEX_COST()パッケージプロシージャでは 232MB という見積もり結果となりました。

SQL> set serveroutput on
SQL> l
  1  DECLARE
  2    used_bytes NUMBER;
  3    segment_bytes NUMBER;
  4  BEGIN
  5    DBMS_SPACE.CREATE_INDEX_COST (
  6    ddl=> 'CREATE INDEX test ON test(num)'
  7    , used_bytes => used_bytes
  8    , alloc_bytes =>  segment_bytes
  9    );
 10    DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
 11    DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
 12* END;
SQL> /
Segment Size (MB) :232
Index data Size (MB) :57.220458984375

PL/SQL procedure successfully completed.

SQL>

実際のセグメントサイズはどれぐらいでしょう？　実際に索引を作ったあとセグメントサイズをみてみました。

SQL> select segment_name,bytes/1024/1024 "MB" from user_segments where segment_name='TEST' and segment_type='INDEX';

SEGMENT_NAME                   MB
------------------------------ ----------
TEST                           192

SQL>

---

今年も半年すぎたけど、アドベントカレンダー全部俺をやるべきか悩む。まとめてアウトプットするので、ちびちびアウトプットするのとどちらがよいか。。。w

では、また。