表の管理
◇新規セグメントまたは表領域への表の移動
◇表の記憶域の手動割当て
◇列の削除・表から列を削除する方法
・列に未使用マークを付ける方法
・未使用列の削除
通常、アプリケーション開発者は、表など、アプリケーションの要素を設計する。
データベース管理者は、アプリケーション開発者から得た、アプリケーションの動作と予想されるデータのタイプに関する情報に基づいて、記憶域パラメータを設定し、表のクラスタを定義する。
表が次の要素を含むように、アプリケーション開発者とともに慎重に各表を作成する。
各表を作成するときに、PCTFREE パラメータとPCTUSED パラメータを指定することによって、表のデータ・セグメントのデータ・ブロック内の領域使用の効率、およびカレント・データ更新時の予約域を調整できる。
PCTFREE およびPCTUSED パラメータについては、データ・ブロックの管理を参照。
各表を作成するときにINITRANS パラメータとMAXTRANS パラメータを指定することによって、表のデータ・セグメントのデータ・ブロック内のトランザクション・エントリに、最初に割り当てられる領域とその後割り当てられる領域を調整できる。
INITRANS およびMAXTRANS パラメータの設定方法の詳細は、データ・ブロックの管理を参照。
適当な権限と表領域割当て制限があれば、現在、オンライン状態の表領域内に新しい表を作成することができる。
新しい表を格納する予定の表領域を識別するためには、CREATE TABLE 文にTABLESPACE 句を指定。CREATE TABLE 文で表領域を指定しない場合、自分のデフォルト表領域内に表が作成される。
表領域を適切に指定することによるメリット
表領域が適切でないことによるデメリット
表の作成は、CREATE TABLE 文の副問合せによってパラレル化することができる。
複数のプロセスが同時に動作して表を作成するため、表を作成するときのパフォーマンスが向上する。
REATE TABLE...AS SELECT
文の副問合せを使用して表を作成する際、UNRECOVERABLE
を指定すると、表をリカバリ不能として作成することが出来る。
表をリカバリ不能として作成するメリット
一般に、表をリカバリ不能として作成したとき、小規模な表の場合より大規模な表の方が相対的にパフォーマンスの向上が大きくなる。
小規模な表をリカバリ不能として作成しても、表作成に要する時間にほとんど影響はない。
一方、大規模な表では、特に表作成をパラレル化した時など、フォーマンスが著しく向上する。
表をリカバリ不能として作成すると、表をアーカイブ・ログからリカバリできない（リカバリ不能の表作成では、必要なREDO ログ・レコードが生成されないため）。
したがって、表が失われて問題がある場合は、表が作成された後にバックアップする必要がある。
ただし、一時的に使用するために作成する表など、注意の必要がない場合もある。
作成前に表のサイズを見積ることによるメリット
表を作成する前に表サイズを見積もるかどうかにかかわらず、クラスタ化されていない表を作成するときは記憶域パラメータを明示的に設定することができる。（クラスタ化表はクラスタの記
憶域パラメータが自動的に使用される。）
表を作成するとき、または表を変更するときに記憶域パラメータを明示的に設定しなければ、その表が常駐する表領域に設定されたデフォルト記憶域パラメータを自動的に使用する。記憶域パラメータについては、データファイルの管理の記憶域パラメータの設定を参照。
表のデータ・セグメントのエクステントに記憶域パラメータを明示的に設定する場合、エクステントを小さくしてその数を増やすよりも、エクステントを大きくしてその数を減らして、表のデータを格納することが望ましい。
表とエクステントの物理的なサイズに制限はない。MAXEXTENTS にキーワードUNLIMITED を指定すると、大きなオブジェクトの計画を簡単にし、無駄な領域や断片化を少なくして、領域の再使用率を向上させることができる。
エクステントの数に制限はないが、表の中のエクステントの数があまり多くなると、その表を必要とする操作を実行するときのパフォーマンスに影響するので注意。
注意:許容されるブロックの最大値よりもMAXEXTENTS が大きくなるように、データ・ディクショナリ表を変更することはできない。
データベース内に大規模な表がある場合は、次の推奨事項を検討すること。
表を作成する前に、次の制限を理解しておく必要がある。
ユーザー定義型のデータを含む表を作成すると、ユーザー定義型の列はそれを格納するリレーショナル列にマップされる。これらの「非表示」のリレーショナル列は、DESCRIBE表の文では非表示であり、SELECT *文では戻されない。
したがって、Oracle は非表示列を作成してユーザー定義型のデータを格納するので、オブジェクト表や、REF 列、VARRAY 、NESTED TABLE
またはオブジェクト型を持つリレーショナル表の作成時には、その表に対して実際に作成される列の合計数が指定した値よりも多くなることがあるので注意。
次の公式によって、ユーザー定義型のデータを持つ表に対して作成される列の合計数が決定される。
オブジェクト表の列数:
num_columns(object_table) =
num_columns(object_identifier)
+ num_columns(row_type)
+ number of top-level object columns in the object type of table
+ num_columns(object_type)
リレーショナル表の列数:
num_columns(relational_table) =
number of scalar columns in the table
+ number of object columns in the table
+ SUM [num_columns(object_type(i))] i= 1 -> n
+ SUM [num_columns(nested_table(j))] j= 1 -> m
+ SUM [num_columns(varray(k))] k= 1 -> p
+ SUM [num_columns(REF(l))] l= 1 -> q
特定のリレーショナル表の場合:
object_type(i) is the object type column and n is the total number of such object type columns
nested_table(j) is the jth nested_table column and m is the total number of such nested table columns
varray(k) is the kth varray column and p is the total number of such varray columns,
REF(l) is the lth REF column and q is the total number of such REF columns.
定義:
num_columns(object identifier) = 1
num_columns(row_type) = 1
num_columns(REF) = 1, if REF is unscoped
= 1, if the REF is scoped and the object identifier
is system generated and the REF has no
referential constraint
= 2, if the REF is scoped and the object identifier
is system generated and the REF has a
referential constraint
= 1 + number of columns in the primary key,
if the object identifier is primary key based
num_columns(nested_table) = 2
num_columns(varray) = 1
num_columns(object_type) = number of scalar attributes in the object type
+ SUM[num_columns(object_type(i))] i= 1 -> n
+ SUM[num_columns(nested_table(j))] j= 1 -> m
+ SUM[num_columns(varray(k))] + SUM[num_columns(REF(l))]特定のオブジェクト型の場合:
object_type(i) is an embedded object type attribute and
n is the total number of such object type attributes,
nested_table(j) is an embedded nested_table attribute and
m is the total number of such nested table attributes,
varray(k) is an embedded varray attribute and
p is the total number of such varray attributes,
REF(l) is an embedded REF attribute and
q is the total number of such REF attributes.
オブジェクト表、または、REF 、VARRAY 、NESTED TABLE
またはオブジェクト型の列を持つリレーショナル表の列数についての計算例
例1）
CREATE TYPE physical_address_type AS OBJECT
(no CHAR(4), street CHAR(31), city CHAR(5), state CHAR(3));
CREATE TYPE phone_type AS VARRAY(5) OF CHAR(15);
CREATE TYPE electronic_address_type AS OBJECT
(phones phone_type, fax CHAR(12), email CHAR(31));
CREATE TYPE contact_info_type AS OBJECT
(physical_address physical_address_type,
electronic_address electronic_address_type);
CREATE TYPE employee_type AS OBJECT
(eno NUMBER, ename CHAR(60),
contact_info contact_info_type);CREATE TABLE employee_object_table OF employee_type;
従業員オブジェクト表の列数を計算するには、最初にemployee_type に必要な列数を計算する必要がある。
num_columns(physical_address_type) =
number of scalar attributes = 4
num_columns(phone_type) =
num_columns(varray) = 1
num_columns(electronic_address_type) =
number of scalar attributes
+ num_columns(phone_type)
= 2 + 1 = 3
num_columns(contact_info_type) =
num_columns(physical_address_type)
+ num_columns(electronic_address_type)
= 3 + 4 = 7
num_columns(employee_type) =
number of scalar attributes
+ num_columns(contact_info_type)
= 2 + 7 = 9次に、オブジェクト表に計算式を使用。
num_columns (employee_object_table) =
num_columns(object_identifier)
+ num_columns(row_type)
+ number of top level object columns in employee_type
+ num_columns(employee_type)
= 1 + 1 + 1 + 9 = 12
例2）
CREATE TABLE employee_relational_table (einfo employee_type);
num_columns (employee_relational_table) =
number of object columns in table
+ num_columns(employee_type)
= 1 + 9 = 10
例3）
CREATE TYPE project_type AS OBJECT (pno NUMBER, pname CHAR(30), budget NUMBER);
CREATE TYPE project_set_type AS TABLE OF project_type;
CREATE TABLE department
(dno NUMBER, dname CHAR(30),
mgr REF employee_type REFERENCES employee_object_table,
project_set project_set_type)
NESTED TABLE project_set STORE AS project_set_nt;num_columns(department) =
number of scalar columns
+ num_columns(mgr)
+ num_columns(project_set)
= 2 + 2 + 2 = 6
自分のスキーマに新しい表を作成するには、CREATE TABLE
システム権限が、別のユーザーのスキーマに表を作成するには、CREATE ANY TABLE システム権限が必要。
さらに、表の所有者は、その表を含む表領域に対する割当て制限、またはUNLIMITED TABLESPACE システム権限が必要。
表はSQL 文CREATE TABLE を使用して作成する。
例）emp表を作成する。ユーザーSCOTT で次の文を発行すると、クラスタ化されていない表EMP がSCOTT のスキーマに作成され、表領域USERS に格納される。
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
PCTFREE 10
PCTUSED 40
TABLESPACE users
STORAGE ( INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25 );
一時表を作成するには、CREATE GLOBAL TEMPORARY TABLE 文を使用。
一時表の定義はすべてのセッションで参照できるが、一時表内のデータを参照できるのは、そのデータを表に挿入するセッションのみ。
ON COMMIT キーワードは、表のデータが「トランザクション固有」（デフォルト）であるか、「セッション固有」であるかを示す。
例）トランザクション固有の一時表を作成。
CREATE GLOBAL TEMPORARY TABLE work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
表を変更するためには、その表が自分のスキーマに含まれているか、その表のALTER オブジェクト権限またはALTER ANY TABLE システム権限のどちらかを有しているかのどちらかの条件が必要。
以下のような場合に表を変更する。
表の変更によって既存の列の長さを拡張することができるが、表の中に行がない場合を除いて、列の長さは縮小できない。
また、データ型CHAR の列長を拡張するために表を修正している場合、特に表内の行数が多いのであれば、この操作は時間を浪費する可能性があり、さらに相当な追加記憶領域を必要とする可能性があることに注意。
これは、各行のCHAR 値には空白を埋めて、新しい列長に合わせる必要があるため。
表のデータ・ブロック領域使用パラメータ（PCTFREE とPCTUSED ）を変更するときには、すでに割り当てられているブロックと今後割り当てられるブロックを含めて、その表が使用するすべてのデータ・ブロックに新しい設定が適用される。
ただし、すでに割り当てられているブロックは、領域使用パラメータが変更されてただちに再編成されるのではなく、変更した後で必要に応じて再編成される。
データ・ブロック記憶域パラメータについては、データ・ブロックの管理を参照。
表のトランザクション・エントリ設定（INITRANS 、MAXTRANS
）を変更するときにはMAXTRANS の新しい設定が表のすべてのブロック（すでに割り当てられたブロックとその後割り当てられるブロック）に適用される一方、INITRANS の新しい設定はその後表に割り当てられるデータ・ブロックにのみ適用される。
これらのトランザクション・エントリ設定パラメータの詳細は「トランザクション・エントリの設定（lNITRANS およびMAXTRANS ）」を参照してください。
記憶域パラメータINITIAL とMINEXTENTS
は変更できない。他の記憶域パラメータ（たとえばNEXT 、PCTINCREASE ）の新しい設定はすべて、その後に表に割り当てられるエクステントにのみ影響する。
割り当てられる次のエクステントのサイズは、NEXT とPCTINCREASE
のカレント値によって決まり、前の値に基づいて決まるわけではない。
記憶域パラメータについては、記憶域パラメータの設定を参照。
表を変更するにはALTER TABLE 文を使用。
例）EMP 表のデータ・ブロック記憶域パラメータを変更し、新しい列BONUSを追加する。
ALTER TABLE emp
ADD (bonus NUMBER (7,2))
PCTFREE 30
PCTUSED 60;
ALTER TABLE 文のその他の使用例
◇新規セグメントまたは表領域への表の移動
◇表の記憶域の手動割当て
◇列の削除
ALTER TABLE...MOVE 文を使用すると、非パーティション表のデータを新しいセグメントに再配置できる。
必要な場合は、割当て制限を持つ別の表領域に再配置することもできる。
さらに、ALTER TABLE 文では変更できないものも含め、表の記憶域属性を変更することも可能。
例）新しい記憶域パラメータを指定して、EMP 表を新しいセグメントに移動。
ALTER TABLE emp MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );
表にLOB
列が含まれている場合は、この文を使用して、ユーザーが明示的に指定でき、LOB データとLOB
索引セグメント（この表に関連）を、表とともに移動できる。
特に指定しない場合、デフォルトではLOB データとLOB
索引セグメントは移動されない。
Oracleは必要に応じて表のデータ・セグメントに追加のエクステントを動的に割り当てが、表に追加のエクステントを明示的に割り当てることもできる。
たとえば、Oracle Parallel Server を使用しているとき、表のエクステントを特定のインスタンスに明示的に割り当てることができる。
新しいエクステントは、ALLOCATE EXTENT オプションを指定したALTER TABLE 文を使用して表に割り当てる。
例）
ALTER TABLE EMP
ALLOCATE EXTENT(SIZE 20M
DATAFILE 'F:\ORAWIN95\DATABASE\USERDAT2.ORA');
ALTER TABLE 文のDEALLOCATE UNUSED 句を使用して、未使用領域の割当てを明示的に解除することもできる。
例）
ALTER TABLE EMP DEALLOCATE UNUSED;
◇列の削除
Oracle では、索引構成表などの表から、不要になった列を削除できる。これにより、データベースの領域を解放でき、データをエクスポート/インポートしてから索引と制約を再作成する必要がなくなる。後述する列削除関連の文を発行するには、ターゲット表に対するALTER 権限、またはALTER ANY TABLE システム権限が必要となる。
表からすべての列を削除することはできない。また、SYS が所有している表の列も削除できない。削除しようとするとエラーが発生する。
・表から列を削除する方法
・列に未使用マークを付ける方法
・未使用列の削除
ALTER TABLE...DROP COLUMN 文を発行すると、列記述子およびターゲット列に対応付けられたデータが表の各行から削除される。1 つの文で複数の列を削除できる。
例）SAL 列のみを削除
ALTER TABLE emp DROP COLUMN sal;
例）SAL 列とCOMM 列の両方を削除
ALTER TABLE emp DROP (sal, comm);
大きい表のすべての行から列データを削除するための所要時間が重要な場合は、ALTERTABLE...SET UNUSED
文を使用する。
この文は、1 つ以上の列に未使用マークを付けるが、実際にターゲット列を削除したり該当列が占めるディスク領域をリストアすることはない。
ただし、未使用マークが付けられた列は、問合せやデータ・ディクショナリ・ビューには表示されなくなり、その名前が削除されて新しい列に再使用できるようになる。
その列に定義されている制約、索引および統計も、すべて削除される。
例）SAL 列とCOMM 列に未使用マークを付ける
ALTER TABLE emp SET UNUSED (sal, comm);
後でALTER TABLE...DROP UNUSED COLUMNS
文を発行し、未使用マークが付いている列を削除できる。
表の特定列の明示的な削除文を発行すると、未使用列もターゲット表から削除される。
データ・ディクショナリ・ビューUSER_UNUSED_COL_TABS 、ALL_UNUSED_COL_TABS またはDBA_UNUSED_COL_TABS
を使用すると、未使用の列を含むすべての表を表示することができる。
COUNT フィールドには、表の未使用の列数が表示される。
例）
SELECT * FROM dba_unused_col_tabs;
OWNER TABLE_NAME COUNT
--------------------------- --------------------------- ----------
SCOTT EMP 1
未使用列に実行できるのは、ALTER TABLE...DROP UNUSED COLUMNS 文のみ。
この文では、表から未使用の列が物理的に削除され、ディスク領域が再生される。
後述の例では、オプションのキーワードCHECKPOINT が指定されています。このオプションを指定すると、指定した行数、この場合は250 行が処理された後に、チェックポイントが適用される。
チェックポイントによって、列削除操作中に累積されるUNDO ログの量が減少し、ロールバック・セグメント領域が使い果たされるおそれがなくなる。
例）
ALTER TABLE emp DROP UNUSED COLUMNS CHECKPOINT 250;
表を削除するためには、その表が自分のスキーマに含まれているか、またはDROP ANY TABLE システム権限を持っている必要がある。
DROP TABLE 文を使用して、必要ではなくなった表を削除できる。
例）EMP 表を削除
DROP TABLE emp;
削除する表に、他の表の外部キーが参照している主キーまたは一意キーが含まれていて、その子表のFOREIGN KEY 制約を削除するのであれば、DROP TABLE 文にCASCADE オプションを指定。
例）
DROP TABLE emp CASCADE CONSTRAINTS;
警告:表を削除する前に、以下のような表を削除した結果についてよく理解しておくこと。
表は、削除するかわりに、切り捨てることができる。
TRUNCATE
文を使用すると、表からすべての行を効率よく高速で削除できる。
この操作は、切り捨てる表に対応付けられた構造（列定義、制約、トリガーなど）や認可には影響しない。
例）
TRUNCATE TABLE EMP;
2001/09/10 担当： R.S