explain plan for文で、rebuild 後の索引サイズも見積れる！ / FAQ Tweet

Jonathan Lewisが、索引rebuild後のサイズ見積もりで昔からある方法を紹介していた. そういえば、私が以前紹介していた方法で見積もれるよね！
ということで、簡単な例で紹介しておきますね。

この方法、通常は、create indexの時にしか使えないのですが、結局のところ、alter index ...rebuildも、create indexもindexを作成することには違いはないので、rebuilddでもcreateでも最終的な索引サイズには同じだよね。という単純な発想です。どうせ、ballpark figureなわけで、厳密性不要なわけですし、リーズナブルだと思います。

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

では、試してみましょう！

索引の無い表を作ります！

  1  CREATE TABLE foobar
  2  (
  3    key_code CHAR(10) NOT NULL
  4* )

表が作成されました。

データを登録します。この例では　10万行登録してあります。（単純なぐるぐる方式ですが、大した量ではないので気にしないでくださいw）

 1  BEGIN
  2    FOR i IN 1..100000 LOOP
  3      INSERT INTO foobar VALUES(TO_CHAR(i,'FM0000000009'));
  4      IF MOD(i,100) = 0
  5      THEN
  6        COMMIT;
  7      END IF;
  8    END LOOP;
  9* END;

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

統計情報を取得します。

  1  BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
  3* END;

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

まずは、create index文で作成される索引サイズの見積もりを行います。(見積もりだけなので実際には作成されません！）
3145KBとの見積もりです

  1* EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)

解析されました。

  @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           |   100K|  1074K|   143   (1)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IX_FOOBAR |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           |   100K|  1074K|            |          |
|   3 |    TABLE ACCESS FULL   | FOOBAR    |   100K|  1074K|    69   (2)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 3145K bytes

14行が選択されました。

では、見積もりとの乖離を確認するために実際に索引を作成して、セグメントサイズを確認してみましょう。
3072KBとなりました。見積もりは、3145KBでした。大きな差はないですよね。これで十分でしょう。

  1* CREATE INDEX ix_foobar ON foobar(key_code)

索引が作成されました。

統計情報を取得して、セグメントサイズを確認します。

  1  BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
  3* END;

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

  1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME                   SEGMENT_TYPE                                                   KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR                      INDEX                                                        3072

次に rebuild したサイズを見積もるため、50%程度のデータを削除します。

  1* DELETE FROM foobar WHERE key_code BETWEEN '0000000000' AND '0000050000'

50000行が削除されました。

  1* COMMIT

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

統計を取得します。

  1  BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
  3* END;

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

50%削除しましたが、rebuild前なのでサイズはcreate index時と同じですよね。

  1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME                   SEGMENT_TYPE                                                   KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR                      INDEX                                                        3072

この状態で、alter index .... rebuild文ではなく、create index文でサイズを見積もります。
2097KBになると見積もられました。

  1* EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)

解析されました。

  @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           | 50000 |   537K|    80   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IX_FOOBAR |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           | 50000 |   537K|            |          |
|   3 |    TABLE ACCESS FULL   | FOOBAR    | 50000 |   537K|    43   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 2097K bytes

14行が選択されました。

alter index ... rebuildは、expla plan forでは索引サイズを見積もれないことも、念の為に確認しておきましょう。
見積もられないですよね。間違いなく！

  1* EXPLAIN PLAN FOR ALTER INDEX ix_foobar REBUILD

解析されました。

  @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4144366834

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |           | 50000 |   537K|    43   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IX_FOOBAR |       |       |            |          |
|   2 |   SORT CREATE INDEX    |           | 50000 |   537K|            |          |
|   3 |    TABLE ACCESS FULL   | FOOBAR    | 50000 |   537K|    43   (0)| 00:00:01 |
------------------------------------------------------------------------------------

10行が選択されました。

最後に、実際に alter index ... rebuildしてみるとサイズはどうなるでしょうか！　確認してみましょう！
はい！　　2048KBとreuildしてコンパクトになりました！。　見積もりサイズは、2097KB でした。この程度の誤差は問題にもならないでしょう。

  1* ALTER INDEX ix_foobar REBUILD

索引が変更されました。

  1* select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type

SEGMENT_NAME                   SEGMENT_TYPE                                                   KB
------------------------------ ------------------------------------------------------ ----------
IX_FOOBAR                      INDEX                                                        2048

今回使用したSQLスクリプト

[oracle@localhost ~]$ cat estimate_rebuild_index_size.sql
DROP TABLE foobar PURGE
.
l
/

CREATE TABLE foobar
(
  key_code CHAR(10) NOT NULL
)
.
l
/

BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO foobar VALUES(TO_CHAR(i,'FM0000000009'));
    IF MOD(i,100) = 0 
    THEN 
      COMMIT; 
    END IF;
  END LOOP;
END;
.
l
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/


EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

DELETE FROM foobar WHERE key_code BETWEEN '0000000000' AND '0000050000'
.
l
/

COMMIT
.
l
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'FOOBAR', cascade=>true, no_invalidate=>false);
END;
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

EXPLAIN PLAN FOR CREATE INDEX ix_foobar ON foobar(key_code)
.
l
/
@?/rdbms/admin/utlxpls

EXPLAIN PLAN FOR ALTER INDEX ix_foobar REBUILD
.
l
/
@?/rdbms/admin/utlxpls

ALTER INDEX ix_foobar REBUILD
.
l
/

select segment_name,segment_type,sum(bytes)/1024 AS "KB" from user_segments where segment_name = 'IX_FOOBAR' group by segment_name,segment_type
.
l
/

DROP TABLE foobar PURGE
.
l
/

ちょっと、涼しくなったと思ったら、残暑がキツイ。

では、また。