ソートに関する検証　その４

～ソートに関する検証　その４～　　ペンネーム　ちゃむ

前回は、主に一時表領域に関して説明した。
今回は、一時表領域と専用一時表領域との違いについてさらに検証する。

検証

複数のソート処理によって本当に一時セグメントのセグメント数に一時表領域
と専用一時表領域とで違いが出るのか？

「一時表領域」

１_１．ソート処理を行なう際、その処理に対応した一時セグメントを割り当てる。
（複数のソート処理で複数の一時セグメントを使用する。）

＜一時表領域の作成＞

CREATE TABLESPACE TEMP1 DATAFILE 'D:TEMP1.ORA' SIZE 20M
DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED);

＜SCOTTのソート領域をtemp1に変更＞

alter user scott temporary tablespace temp1;

＜2つのセッションから別々にSCOTTで10万件のソート処理を実行＞

SELECT * FROM T10MAN_ORG ORDER BY SAL;

＜ソートの最中にdba_segmentsを検索 BLOCKSは徐々に2900ブロックまで増加＞

select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,HEADER_FILE,HEADER_BLOCK from
dba_segments where tablespace_name = 'TEMP1';
SEGMENT_NAME    SEGMENT_TYPE          BLOCKS HEADER_FILE HEADER_BLOCK
--------------- ------------------ --------- ----------- ------------
9.2             TEMPORARY               2900           9            2
9.1402          TEMPORARY               2900           9         1402

ソート処理別に一時セグメント（SEGMENT_TYPE=TEMPORARY）が割当てられている
のがわかる。また、SEGMENT_NAMEは、「HEADER_FILE.HEADER_BLOCK」で命名され
る様子もわかる。これは、セグメントヘッダの場所を示している。

＜ソート処理終了後のdba_segmentsの検索＞
ソート処理終了後は、dba_segmentsを検索してもデータは何も検索されない。
これがソート後すぐに一時セグメントが解放される裏付けである。

「専用一時表領域」

１_２．ソート処理を行なう際、必ず、単一の一時セグメントを割り当てる。
（複数のソート処理で一つの一時セグメントを使用する。）

＜専用一時表領域の作成＞

CREATE TABLESPACE TEMP2 DATAFILE 'D:TEMP2.ORA' SIZE 20M
DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED)
TEMPORARY;

＜SCOTTのソート領域をtemp2に変更＞

alter user scott temporary tablespace temp2;

＜2つのセッションから別々にSCOTTで10万件のソート処理＞

SELECT * FROM T10MAN_ORG ORDER BY SAL;

＜ソートの最中にdba_segmentsを検索 BLOCKSは徐々に5800ブロックまで増加＞

select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,HEADER_FILE,HEADER_BLOCK from
dba_segments where tablespace_name = 'TEMP2';
SEGMENT_NAME    SEGMENT_TYPE          BLOCKS HEADER_FILE HEADER_BLOCK
--------------- ------------------ --------- ----------- ------------
10.2            TEMPORARY               5800          10            2

一時セグメントが一つしか存在しないのが、確認できるだろう。また、
5800=2900+2900であり、大きさ的にも１_１．で行なったソート処理の大きさ
である。

＜ソート処理終了後のdba_segmentsの検索＞
ソート処理終了後にdba_segmentsを検索すると、上記のSQLの結果とまったく
同じである。これがソート後すぐに一時セグメントが解放されない裏付けである。

また、解放されるタイミングは、OracleのSHUTDOWN処理時だが実はもう一つある。
それは、以下のコマンドで永続表領域にすることである。これにより、単なる
一時表領域になるので、エクステントが解放されるのは理解しやすい。

ALTER TABLESPACE TEMP2 PERMANENT;

ちなみに専用一時表領域に再び戻すコマンドは以下のとおり。

ALTER TABLESPACE TEMP2 TEMPORARY;

このように、1つのセグメントヘッダが複数のソート処理を一手に引き受けるのが、
専用一時表領域の内部的な動きである。解放しっぱなしの一時セグメント
が他のソート処理を行なうプロセスから効率的に再利用されるために、このよう
に全体の領域を管理する一つのセグメントヘッダが必要だったためと思われる。
以下にそのイメージを示す。

以上カフェオレタイヤキ茅ヶ崎にて

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜Oracle入門＞ペンネームモンキーターン

前回は「制約の追加・削除・変更」で、それぞれの方法について理解していた
だけたと思う。
今回は「整合性制約の管理」についてお送りしようと思う。

— それでは、どっぷり入門生活スタート！ —————————–

「整合性制約の管理」とは、制約の実行タイミングや制約の状態を設定するこ
とである。

＜制約の実行タイミング＞
制約の実行タイミングは、二通り存在する。即時制約と遅延制約である。指定
方法は、制約定義時である。
・即時制約とは、表に対するDML実行時に制約をチェックすることである。
・遅延制約とは、表に対するDML実行後、COMMIT時に制約をチェックすること
である。言い換えると、制約のチェックをトランザクションの最後まで遅延
するのである。

* DML実行（ Data Manipulate Language ）
SELECT,UPDATE,INSERT,DELETE文のことである。表データに対してアクセスし、
処理を行うことである。

それでは、即時制約と遅延制約の動きをみてみよう。

表：TEST_LEST01 ・・・カラム ID に即時制約設定
表：TEST_LEST02 ・・・カラム ID に遅延制約設定

表を作る。

SQL> CREATE TABLE TEST_LEST01
(ID number constraint PK_ID01 PRIMARY KEY
NOT DEFERRABLE INITIALLY IMMEDIATE ) ;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
↑
遅延不可能かつ初期即時制約指定（デフォルト）

表を作る。

SQL> CREATE TABLE TEST_LEST02
(ID NUMBER CONSTRAINT PK_ID02 PRIMARY KEY
DEFERRABLE INITIALLY DEFERRED ) ;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
↑
遅延可能かつ初期遅延の制約指定

遅延制約の設定に関する内容は、次回で詳しく説明する。

それでは、２つの表に対して同じ値を挿入してみよう。
制約がチェックされるタイミングの違いがみれるはずである。

表に対して

SQL> INSERT INTO TEST_LEST01(ID) VALUES(1) ;  ← 制約のチェック
1行が作成されました。
SQL> INSERT INTO TEST_LEST01(ID) VALUES(1) ;  ← 制約のチェック
INSERT INTO TEST_LEST01(ID) VALUES(1)
*
エラー行: 1: エラーが発生しました。
ORA-00001: 一意制約: (SCOTT.PK_ID01) に反しています。

INSERT文を実行時にチェックされているのが確認できる。
これが即時制約である。

表に対して                           （トランザクション開始）
│
SQL> INSERT INTO TEST_LEST02(ID) VALUES(1) ;   │
│
1行が作成されました。                          │
│
SQL> INSERT INTO TEST_LEST02(ID) VALUES(1) ;   │
│
1行が作成されました。                          │
↓
SQL> COMMIT ;                       （トランザクション終了）
COMMIT                                   ? 制約のチェック
*
エラー行: 1: エラーが発生しました。
ORA-02091: トランザクションがロールバックされました。
ORA-00001: 一意制約: (SCOTT.PK_ID02) に反しています。

INSERT文を実行時にチェックされず、トランザクション終了時（COMMIT時）に
制約のチェックが実行されているのが確認できる。これが遅延制約である。

今回は、この辺で「どっぷり入門生活」おしまい ————————-

次回は、「整合性制約の管理」のつづきを、お送りしようと思う。

「どっぷり入門生活」では、基本的な質問をお待ちしています。また、このような
テーマでやってほしい等の要望がございましたら、メールでどしどしお寄せく
ださい。

以上 23日誕生日モンキー・ターンより（ジャイアント馬場）
ちなみに、「つけまい」は本日17日が誕生日です（山口百恵、坂本龍一と同じ）

ソートに関する検証 その４