Extended Describe

Describeコマンドはテーブルの定義を確認する時によく使用します。僕はDescribeコマンドをチューニングツールの範疇に入れます。性能問題を扱って見ればオブジェクトと明確な定義を知るべき時か多いからです。

SQL*PlusのDescribeの結果に1)DDL文章自体、2)インデックスの構成、3)インデックス定義DDLたちを一緒に見るべきの場合がたくさんあります。僕はこんな機能が追加されたDescribeコマンドをExtended Describeだと呼びます。

簡単な例を通じて見ましょう。次のように8個のインデックスを持っているテーブルT1を作ります。

create table t1(c1 int, c2 int, c3 int, c4 int, c5 int);

create index t1_n1 on t1(c1);
create unique index t1_n2 on t1(c1, c3);
create index t1_n3 on t1(c1, c4);
create index t1_n4 on t1(c1, c5);
create index t1_n5 on t1(c3, c4);
create index t1_n6 on t1(c3, c4, c5);
create index t1_n7 on t1(c4, c5);
create unique index t1_n8 on t1(c5, c1);

@desc ukja t1

次のようにExtended Describe(desc.sql)コマンドを利用してテーブルの定義を見ましょうか。

UKJA@ukja1021> @desc ukja t1
UKJA@ukja1021> set echo off

OBJECT_TYPE
-------------------
TABLE

Elapsed: 00:00:00.01
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      C1                                       NUMBER(38)
    2      C2                                       NUMBER(38)
    3      C3                                       NUMBER(38)
    4      C4                                       NUMBER(38)
    5      C5                                       NUMBER(38)

Index               1...3...4...5...
T1_N1...............1
T1_N2...............1U  2U
T1_N3...............1       2
T1_N4...............1           2
T1_N5...............    1   2
T1_N6...............    1   2   3
T1_N7...............        1   2
T1_N8...............2U          1U

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14

DDL
--------------------------------------------------------------------------------

  CREATE TABLE "UKJA"."T1"
   ( "C1" NUMBER(*,0),
 "C2" NUMBER(*,0),
 "C3" NUMBER(*,0),
 "C4" NUMBER(*,0),
 "C5" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"



Elapsed: 00:00:00.45

DDL
--------------------------------------------------------------------------------

  CREATE INDEX "UKJA"."T1_N1" ON "UKJA"."T1" ("C1")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE UNIQUE INDEX "UKJA"."T1_N2" ON "UKJA"."T1" ("C1", "C3")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE INDEX "UKJA"."T1_N3" ON "UKJA"."T1" ("C1", "C4")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE INDEX "UKJA"."T1_N4" ON "UKJA"."T1" ("C1", "C5")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE INDEX "UKJA"."T1_N5" ON "UKJA"."T1" ("C3", "C4")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE INDEX "UKJA"."T1_N6" ON "UKJA"."T1" ("C3", "C4", "C5")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE INDEX "UKJA"."T1_N7" ON "UKJA"."T1" ("C4", "C5")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

  CREATE UNIQUE INDEX "UKJA"."T1_N8" ON "UKJA"."T1" ("C5", "C1")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "UKJA_TBS"

8 rows selected.

Elapsed: 00:00:01.28

desc.sqlファイルはここにあります。

要点はオブジェクトの定義を正確に知ることが本当に重要なものだということです。