表と一時表の管理

表の概要

ORACLE社の管理マニュアル
CREATE TABLE句の詳細

表のタイプ
  • 通常の(ヒープ構成)表:この表のデータは、順序付けされていないコレクション(ヒープ)として格納される。
  • クラスタ化表:クラスタ化表は、クラスタの一部となっている表である。クラスタとは、同じデータ・ブロックを共有する表のグループである。
  • 索引構成表:通常の(ヒープ構成)表とは異なり、索引構成表のデータはBツリーの索引構造に主キー・ソート方式で格納される。
  • パーティション表:パーティション表では、データをパーティションと呼ばれる管理が容易な単位に分割し、さらにそれをサブパーティションに分割できる。
表のNOLOGGING
  • CREATE TABLE…AS SELECT文でNOLOGGING句を使えば表を最も効率よく作成する。
  • SQL*Loaderを使用した後続のダイレクト・ロードおよびダイレクト・ロードINSERT操作がロギングされなくなる。
  • 後続のデータ操作文(DML)文(UPDATE、DELETEおよび従来型パスの挿入)は、表のNOLOGGING属性の影響を受けず、REDOを生成する
表圧縮

Oracle Databaseの表圧縮は、データベース・ブロック内の重複する値を取り除くことでデータを圧縮する機能である。

  • 表圧縮を使用すると、ディスク使用とバッファ・キャッシュ内のメモリー使用が少なくなり、その結果、読取り専用操作がスケールアップすることになる。
  • 表圧縮によって、問合せ実行速度も向上する。但し、わずかながらCPUオーバーヘッドがかかる。
  • 圧縮は、バルク(ダイレクト・パス)挿入操作でデータが挿入される場合に発生する。
  • 従来のDML操作では、操作後にレコードが圧縮されていない状態で格納され、その操作では表圧縮の性質上、パフォーマンスにわずかなオーバーヘッドが生じる。
  • 頻繁に更新される表に対しては、表圧縮を使用しないほうが良い。

表の作成

CREATE TABLE TOM.USER_INFO
(
    USER_ID                        NUMBER(5,0) NOT NULL,
    USER_NAME                      VARCHAR2(30) NOT NULL,
    AGE                            NUMBER(3,0),
    BIRTH                          DATE,
    ADDRESS                        VARCHAR2(100),
    REMARKS                        VARCHAR2(1000),
    CONSTRAINT PK_USER_INFO PRIMARY KEY (USER_ID) USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        TABLESPACE INDX
        STORAGE(INITIAL 1M NEXT 1M)
        LOGGING
)
PCTFREE 30
PCTUSED 40
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 2M NEXT 1M)
NOCACHE
LOGGING
DISABLE ROW MOVEMENT
STORAGE句

ローカル管理表領域内のオブジェクトへの記憶域パラメータの指定は、下位互換性のためにサポートされています。ローカル管理表領域を使用している場合、ローカル管理表領域にオブジェクトを作成する際にこれらの記憶域パラメータを省略できる。
INITIAL

  • ディクショナリ管理表領域では表領域の作成時にMINIMUM EXTENTが指定されている場合、INITIALの値は、必要に応じて指定したMINIMUM EXTENTのサイズに丸められる。MINIMUM EXTENTが指定されていない場合、その表領域に作成されるセグメントのINITIALエクステント・サイズは最小値(前述の説明を参照)、または要求されたサイズが5ブロックより大きい場合は、5ブロックの倍数に丸められる。
  • ローカル管理表領域では、INITIALの値と表領域に指定されたエクステント・サイズの両方に基づいてオブジェクトの第1エクステントが決定される。例えば、エクステントが5Mの均一なローカル管理表領域の場合、INITIAの値を1Mに指定すると、1Mのエクステントが5つ作成される。

NEXT

  • オブジェクトに割り当てる次のエクステント・サイズをバイト単位で指定する。
  • デフォルトのサイズは5データ・ブロック

PCTINCREASE
通常、設定は0(ゼロ)にすることを薦める。ローカル管理均一エクステント表領域の場合、値が常に「0」に互換される。
ASSM自動管理の場合、下記のFREELISTSとFREELIST GROUPSを指定する必要がなくなる。ASSM自動管理はFREELISTの代わりにビットマップで空きブロックを管理する。
FREELIST GROUPS
作成するデータベース・オブジェクトに対する空きリスト・グループ数を指定する。デフォルトは1。
FREELISTS
表領域およびロールバック・セグメント以外のオブジェクトについて、表、パーティション、クラスタまたは索引の各空きリスト・グループの空きリスト数を指定する。デフォルトは1。

記憶特性オプション

PCTFREE
データベース・オブジェクトの各データ・ブロック内で、オブジェクトの行を将来更新するために確保しておく領域の割合を表す整数値を指定する。デフォルトは10。
PCTUSED
使用済領域のうち、データベース・オブジェクトのデータ・ブロックごとに確保される最小限の割合を表す整数値を指定する。ブロックは、使用済領域がPCTUSEDの値を下回ると、行挿入の対象となる。デフォルトは40。
※ ASSM自動管理の場合は、PCTUSEDを無視される。
INITRANS
データベース・オブジェクトに割り当てられた各データ・ブロックに割り当てられる、同時実行トランザクション・エントリの初期数を指定する。
デフォルトは1だが、下記の例外がある。

  • クラスタのINITRANSのデフォルト値は、クラスタが存在する表領域のINITRANSのデフォルト値と2のいずれか大きい方の値である。
  • 索引のデフォルト値は2である。
CACHE/NOCACHE句

バッファ・キャッシュ内でのブロックの格納方法を指定できる。CREATE TABLE文ではNOCACHEがデフォルトである。

{ENABLE|DISABLE} ROW MOVEMENT句

表の行が移動されるかどうかを指定できる。ENABLEを指定すると、行の移動を許可できる。このとき、ROWIDは変更される

表の変更

表の移動(新規セグメントまた表領域へ)

ALTER TABLE … MOVE

PROD(SYS)>conn tom/tom
接続されました。
PROD(TOM)>select count(*) from test_tab;
 
  COUNT(*)
----------
      9456
 
PROD(TOM)>alter table test_tab move tablespace example;    -- USERS表領域からEXAMPLE表領域へ移動
 
表が変更されました。
 
PROD(TOM)>select count(*) from test_tab;
 
  COUNT(*)
----------
      9456
 
PROD(TOM)>
 
PROD(SYS)>select owner,table_name,tablespace_name from dba_tables where owner='TOM';
 
OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- ----------------------------------------
TOM        TEST_TAB   EXAMPLE
 
PROD(SYS)>
 
PROD(TOM)>alter table test_tab move storage (initial 10M);    -- 同じ表領域の新規セグメントへ移動(初期サイズを10MBにする)
 
表が変更されました。
 
PROD(TOM)>

ALTER TABLE…MOVE文では、文の実行中は表に対するDML操作が許可されない。
表の変更

ALTER TABLE … MODIFY:列のデータ型、デフォルト値、列制約または列の暗号化は変更できる。

PROD(TOM)>desc user_info
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(5)
 USER_NAME                                 NOT NULL VARCHAR2(30)
 AGE                                                NUMBER(3)
 BIRTH                                              DATE
 ADDRESS                                            VARCHAR2(100)
 REMARKS                                            VARCHAR2(1000)
 
PROD(TOM)>alter table user_info modify age not null;
 
表が変更されました。
 
PROD(TOM)>desc user_info
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(5)
 USER_NAME                                 NOT NULL VARCHAR2(30)
 AGE                                       NOT NULL NUMBER(3)
 BIRTH                                              DATE
 ADDRESS                                            VARCHAR2(100)
 REMARKS                                            VARCHAR2(1000)
 
PROD(TOM)>
表の列の追加

ALTER TABLE … ADD

SQL> alter table user_info add (company varchar2(100));
 
Table altered.
 
SQL> desc user_info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(5)
 USER_NAME                                 NOT NULL VARCHAR2(30)
 AGE                                                NUMBER(3)
 BIRTH                                              DATE
 ADDRESS                                            VARCHAR2(100)
 REMARKS                                            VARCHAR2(1000)
 COMPANY                                            VARCHAR2(100)
 
SQL>
表の列名の変更

ALTER TABLE … RENAME COLUMN .. TO ..

SQL> alter table user_info rename column company to comp;
 
Table altered.
 
SQL> desc user_info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(5)
 USER_NAME                                 NOT NULL VARCHAR2(30)
 AGE                                                NUMBER(3)
 BIRTH                                              DATE
 ADDRESS                                            VARCHAR2(100)
 REMARKS                                            VARCHAR2(1000)
 COMP                                               VARCHAR2(100)
 
SQL>
表の列の削除

ALTER TABLE … DROP COLUMN 単一列の削除
ALTER TABLE … DROP (xxx,…) 複数列の削除

SQL> alter table user_info drop (remarks,comp);
 
Table altered.
 
SQL> desc user_info;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(5)
 USER_NAME                                 NOT NULL VARCHAR2(30)
 AGE                                                NUMBER(3)
 BIRTH                                              DATE
 ADDRESS                                            VARCHAR2(100)
 
SQL>
表の削除

DROP TABLE … 表の削除
DROP TABLE … CASCADE CONSTRAINTS 表とその表の関連を削除
DROP TABLE … PURGE リサイクルビンに保存せずにすぐ領域を解放する。

SQL> drop table user_info purge;
 
Table dropped.
 
SQL> show recyclebin;
SQL>

一時表の作成

  • 一時表の定義はすべてのセッションで参照出来るが、一時表内のデータを参照できるのは、そのデータを表に挿入するセッションのみである。
  • CREATE GLOBAL TEMPORARY TABLE … ON COMMIT句で作成する。
ON COMMIT句のオプション

DELETE ROWS:トランザクション固有の一時表を作成する。表は、各コミット後に切捨て(すべての行を削除)が行われる。
PRESERVE ROWS:セッション固有の一時表を作成する。表は、セッション終了時に切り捨てられる。

一時表の作成
SQL> create global temporary table test ( msg varchar2(100) ) on commit delete rows; -- トランザクション固有一時表を作成
 
Table created.
 
SQL> insert into test values('on commit delete rows');
 
1 row created.
 
SQL> select * from test ;
 
MSG
--------------------------------------------------------------------------------
on commit delete rows
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;        -- トランザクションが完了すると、一時表のデータを削除される。
 
no rows selected
 
SQL> drop table test;
 
Table dropped.
 
SQL> create global temporary table test ( msg varchar2(100) ) on commit preserve rows; -- セッション固有一時表を作成
 
Table created.
 
SQL> insert into test values('on commit preserve rows');
 
1 row created.
 
SQL> select * from test;
 
MSG
--------------------------------------------------------------------------------
on commit preserve rows
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;
 
MSG
--------------------------------------------------------------------------------
on commit preserve rows
 
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[prod oracle(PROD)] sqlplus tom/tom
 
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 25 16:24:12 2009
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select * from test;    -- セッション切断されると、データが無くなる。
 
no rows selected
 
SQL>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License