『やさしくわかるSQL』各書店にて好評発売中! オラクルが好き!
Oracle RDBMS入門
Top やさしくわかるSQL RDBMS入門 チューニング SQL相談室 Pro*COBOL入門 COBOL相談室 サブルーチン

Oracle RDBMSに特化した『やさしくわかるSQL
各書店にて好評発売中!

Oracle RDBMS&SQL言語相談室
を開設しました!


初心者編
中級編
上級編
Oracle8
Oracle9i

第一回『Oracleデータベース』セミナーを開催しました。
日時:平成10年3月19日
場所:香川県厚生年金会館

セミナーのスライドを見る


戻る


RDBMSって何?
 ここ数年パソコンが急速に普及し始めました。今まではパーソナルとして個人が趣味で使用していたパソコンも、高性能のワープロや表計算ソフトが出回り、会社の中でも使用するようになってきました。
 会社の中でパソコンを使用する場合、データを一元管理して複数のパソコンでデータを共有したいと考えます。しかし、パソコンやEWSはデータの共有が非常に苦手です。
 そんな時『一番の目的は、複数のユーザがデータを共有すること。そして、コンピュータの専門の人だけでなく誰でも操作できるようにしたい。』という目的から生まれてきた、リレーショナルデータベースが注目されだしました。
 データベースには、ファイルシステム型、階層型、ネットワーク型、リレーショナル型があります。リレーショナル型データベースは、ファイルシステム型や階層型のデータの重複という問題点を解決し、ネットワーク型の難しさを解決して、『人間は生活で表を見るのに慣れているので、リレーショナルデータベースも二次元の表の形にすればよい。』という考えで作られています。
 その、リレーショナルデータベースを管理するソフトをリレーショナルデータベースマネジメントシステム(RDBMS)と言います。また、RDBMSをアクセスする言語をSQLと言います。
 SQLはJISなどで規格化されていますので、どこのメーカのRDBMSでもだいたい同じです。SQLのすごいところは、データを操作することはもとより、データベースに定義されている内容を調べるときにもSQLで行えるところがすごい!SQLを知らなかったら何にもできないし、SQLを知っていたら何でもわかります。


RDBの特徴は何?
 一言で言うと『人間味のあるファイル』かな?私たちはパソコン上でいろいろなソフトを使用していますが、データの中身がどうなっているかまでは知りません。その上あるソフトで作成したデータは、なかなか他のソフトで読むことはできませんでした。リレーショナルデータベースは、統一したアクセス言語がある為、どんなソフトからでもそのデータを読むことができます。
 私は今までコンピュータの仕事をやってきて、お客様のシステムを開発する場合、まずどんな情報が入力できて、どんな資料を出力したいかヒアリングします。そして一番に、開発するシステムのファイルを設計していました。なぜならば、ファイル設計はコンピュータシステムの核になるからです。この設計を誤ると、多くのプログラムを作り直さなければならないからです。
 ファイル設計では、レコード長が何文字で、各項目の桁数が何文字、余分に何文字とっておく、この項目を主キーにする。などさまざまなことを決定しなければなりませんでした。
 しかし、人間にミスは付き物です。RDBはそれらのことをあまり意識しなく、だいたいで設計します。後から必要になった時に追加、変更ができるからです。できることなら最初にきちんと決めていた方が良いが、適当でも動作するという、余裕が開発の能率を向上させます。ファイルをきちんと設計してからプログラミングに入るのでなく、ちょっとこんなかんじでどうかな?という気持ちで取り掛かり、何度もフィードバックして良いシステムに改善して行けるのが私は好きです。
 そのうえ、データの完全性を保証していますので、ユーザのプログラムが途中でダウンしてもデータはおかしくなりません。故意にデータがおかしくなっても、任意のデータ確定位置まで戻すことも可能です。複数ユーザがデータを共有していても、検索でデータをロックしないので検索が早い。ストアドプログラムが使用できでバッチ処理が非常に早いことがあげられます。


RDBMSと聞いて何を思い出しますか?
 Access? Paradox? dBaseIV? SQL Server? Oracle(オラクル)? SYBASE? INFOMIX? EMPRESS? etc...
 簡単なのから難しいのまで、安いのから高いのまでいろいろあります。その中でも、教育機関が整って、基幹システムやエンタープライズなシステムに対応でき、非常に奥が深く、プロとして一押しのOracle RDBMSとRDBMS共通のSQLについて、これからこのページで取り上げていきます。


Oracle(オラクル)の勉強の仕方

開発中困った時は、
Oracleは、なぜ日付6桁で2000年対応できるの?
 日付6桁を DATE 型の列に INSERT する方法をイメージして下さい。INSERT INTO table-name VALUES ( ..., TO_DATE( ymd, 'YYMMDD' ), ... ); ですよね!
 しかし、Oracle には TO_DATE 関数の書式文字に 'YY' に代わる 'RR' という書式があります。INSERT INTO table-name VALUES ( ..., TO_DATE( ymd, 'RRMMDD' ), ... ); と記述すれば2000年対応できます。
 'RR' は、現在の西暦と比較して、適切な上2桁を拡張します。西暦の下2桁でなぜ2000年対応できるか、次の表を見て下さい。
 
RRに対応する値
00〜49
50〜99
現在の年、下2桁
00〜49
現在の年、上2桁を使用
現在の年、上2桁−1を使用
50〜99
現在の年、上2桁+1を使用
現在の年、上2桁を使用


JOINする時FROM句の後ろに記述するテーブル名には、記述する順番があります!
 JOIN(結合)とは、表の掛算のことで、2つの表を JOIN した場合、2つの表の行数を掛け合せた行の表を作ろうとします。これを単純JOIN(結合)といいます。その SELECT 文の WHERE 句に等価条件を追加しているから、等価JOIN(結合)になるわけです。
 この単純結合を作ろうとする時、サーバ側では『ネステッドループ』という手法で行います。(等価条件の列に索引が付いている場合は『ソート・マージJOIN』という手法を使います)。この『ネステッドループ』の時、名前から想像できるように、多重ループで全表走査します。FROM 句の左側の表が外側のループで、FROM 句の右側の表が内側のループになります。ということは、FROM 句の右側の表が小さければ、キャッシュに入ったままで非常に高速に結合が行えます。逆に内側のループの表が大きい場合、キャッシュがあふれ毎回読むことになります。
 結論は、FROM 句の後ろは大きい表順に記述しましょう!ということです。この記述だけで、まったく処理時間が異なることがあります。
 余談:コストベースオプティマイザはこの判断をしてくれますが、ANALYZE コマンドを実行していない表やデータディクショナリを結合する時は、このことを注意しなければいけません。常日頃から大きい順に記述するように心がけましょう。


1行を特定する一番早い方法は?
 SELECT 文で1行だけ読みたい。UPDATE 文で1行だけ更新したい。その時、WHERE 句にどんな記述をしますか?
 プライマリーキーの項目と定数をイコールで結ぶ。と答えた方は、50点(半分正解)です。この方法はルールベースのアクセスパスランキングでいうと4番です。(最低3論理I/O発生する)。もっと良い方法があると言うことです。(1論理I/Oで終わる方法)
 答えは、ROWID を指定することです(この方法はルールベースのアクセスパスランキングが1番です)。プライマリーキーを指定すると、内部で索引を使用して ROWID が求められ、もう一度 ROWID を使用した SELECT 文が発行されています。(ROWID については、
中級編のそっくりな行があります。を参照)
 普通、1行だけ速く読む時には使用しませんが、1行づつ読んで更新していく場合、読む SELECT 文で ROWID を取込んでおきましょう。そして、更新する時 WHERE rowid = :rowid を指定します。(このパターンは絶対にこういうふうにしましょう。埋め込み SQL には、WHERE CURRENT OF cursor と言う句があります。これは同等のことを行っています。)


SQL-DDL文は、SQLスクリプトとして残しましょう!
 CREATE TABLE コマンドなど SQL-DDL 文は、つい SQL*Plus などから直接入力しがちです。
 データベースがひょっと壊れたとき、インストールしなおさなければならなくなった時、本番データベースに同じオブジェクトを作りたい時、サイトを増やしたい時、指定漏れがありもう一度発行しなおしたい場合、前回どのようなコマンドを入力したのか残っていなければ非常に困ります。
 SQL-DDL 文は、拡張子[.SQL]のファイルにまず記述してから、SQL*Plus から@コマンドで実行しましょう。


SQL*Plusで表示させると2行づつ表示される?
 SQL*Plus で SELECT コマンドを発行すると、列見出しも付いて結構見やすく表示してくれます。しかし、時々1行のデータが2行づつ表示されることがあります。もっとこの列を縮めれないのか?と思ったことはありませんか?
 できます!SQL*Plus のコマンドで COLUMN コマンドと言うものがあります。
 たとえば下記のような表があるとします。
CREATE TABLE a (
  x VARCHAR2(32),
  y VARCHAR2(32),
  z VARCHAR2(32)
);
 この表を表示すると (SELECT * FROM a;) たいてい2行になると思います。
 ここで、下記の3コマンドを入力します。
COLUMN x FORMAT A20 [Enter]
COLUMN y FORMAT A20 [Enter]
COLUMN z FORMAT A20 [Enter]
 もう一度 SELECT コマンドを入力してみてください。1行になったでしょ!
 COLUMN コマンドの文法は、COLUMN {列名} FORMAT {パターン} です。{パターン}は、文字の場合は Ann です。数字の場合は 99,990.0 のような感じです。
 SET LINESIZE {1行の文字数} というコマンドも有ります。


SQL*Plusでタイトルは付けれないの?
 SQL*Plus で SELECT コマンドを発行すると、列見出しも付いて結構見やすく表示してくれます。後、タイトルが付けれたらいいのになぁ〜と思ったことはありませんか?
 できます!SQL*Plus のコマンドで TTITLE コマンドと言うものがあります。
TTITLE {タイトル} [Enter]
 SELECT コマンドを入力してみてください。タイトルが付いたでしょ!
 タイトルを解除するコマンドは、TTITLE OFF です。


SQL*Plusでページ長の指定はできないの?
 SQL*Plus で SELECT コマンドを発行すると、列見出しも付いて結構見やすく表示してくれます。報告書として使用したいとき、頻繁に列見出しが表示され、ちょっとのけたいなぁ〜と思ったことはありませんか?
 できます!SQL*Plus のコマンドで SET PAGESIZE コマンドと言うものがあります。
SET PAGESIZE {1ページの行数} [Enter]
 応用用紙に出力するときは、SET PAGESIZE 66 です。列見出しがうっとうしいときは、SET PAGESIZE 1000 です。


Oracle7 R7.2の特徴って何?


Oracle7 R7.3の特徴って何?


Oracle8の特徴って何?


そっくりな値の行があります。片方の行だけ消す方法はありますか?
 表に PRIMARY KEY 制約を付け忘れた場合、主キーになるべきコードが重複して登録することが可能です。後で気が付いても重複するデータが存在する為、PRIMARY KEY 制約を付けれません。まず、重複する片方の行を削除または変更しなければなりません。しかし、WHERE 句で指定する項目が無いまたは一意に決まりそうに無い場合どうしたら良いのでしょうか?
 rowid という列名があります。(SELECT * FROM table-name; では表示されませんが)SELECT rowid, ... FROM table-name; と入力してみて下さい。この rowid は、索引から行を特定するする時に使用されています。この列を WHERE 句 に指定すると、そっくりな行でも片方だけ削除または変更することが可能です。


dual 表を使いこなせ!
 関数の使い方を勉強したい!ROUND 関数の第2引数の意味を忘れた!現在の時刻を知りたい!ちょっと電卓で計算したい!時に、dual 表が非常に役に立ちます。
というふうにネ!


TO_DATETO_CHARどっちを使う方が良いの?
 uriage表にuri_ymdhという年月日と時間が入っているDATE型の項目があります。'97-01-01'だけの売上を表示する場合、どうしますか?
 このように、TO_CHARを使用してもTO_DATEを使用してもSQL文は通ります。さて!どちらが良いのでしょうか?前者の方がSQL文が短いので良いと思った方は×です。後者の方をお勧めします。
 後者のTO_DATE( '97-01-01', 'YY-MM-DD' )などはコンパイルすると定数になりますが、前者のTO_CHAR( ymdh, 'YY-MM-DD' )は列名が入っている為コンパイルできません、行を読むたびにymdhが文字に変換されます。そのため後者のSQL文の方が早く実行できます。
 それだけでなく、uri_ymdhに索引が付いていた場合、後者のSQL文は検索開始値が定数で解る為、索引が使用され高速に処理を行いますが、前者のSQL文は検索開始値が求められないので、索引を使用せず全表検索になります。索引が付いている列名は関数などで加工しないようにしましょう!


データディクショナリを使いこなせ!
 表にどんな制約を付けたか忘れた!データディクショナリの名前を忘れた!こんな時あなたはどうしますか?マニュアルをいつも片手に持っておきますか?
 そんな必要はありません! dictionary 表(別名 dict 表)というものがあります。DESCRIBE dictionary または DESC dict と入力してみて下さい。この表はデータディクショナリ名の表です。この dictionary 表だけ覚えておけば、マニュアルなんか不要です。
 では、表に付けている制約を調べてみましょう。制約は CONSTRAINT 句で指定しますので、キーワードは "CONS" です。SELECT table_name FROM dict WHERE table_name LIKE '%CONS%'; を実行して見てください。6行表示されますが、2種類のデータディクショナリしかないことが解ります。後は、DESCRIBE コマンドで列名を調べると大体検討が付きます。まず、SELECT owner, constraint_name, constraint_type, table_name, search_condition FROM xxx_constraints; を実行し、i コマンドで WHERE 句を追加して、owner = '...' や AND table_name = '...' で絞り込んで下さい。constraint_type や search_condition で制約が解ります。
 どうですか?キーワードさえ解れば、このようにして現場でもすぐデータベースの定義内容を確認できます。これが SQL の特徴ともいえます。


簡単にパフォーマンスを向上させる方法は無いの?
 あります!行数の多い表に ANALYZE TABLE コマンドを実行して下さい。エンドユーザコンピューティングを進めているシステムにおいて、素人が作った SQL 文の処理速度が思いっきり向上する可能性があります。
 ANALYZE コマンドの特徴、
 欠点もあります。実行計画を立てる時参考にする値は ANALYZE コマンドを実行した時の値です。常時変更されません。ということは、逆にパフォーマンスが悪くなる場合があります。ANALYZE コマンドを1度実行した表は一定期間ごとに継続して ANALYZE コマンドを実行しなければなりません。また、ルールーベースに戻したい場合は、ANALYZE TABLE ... DELETE STATISTICS コマンドで戻ります。
 詳しい事を知りたい方は、Oracle7 Server 概要のチューニングの章を読んでみて下さい。


DECODE関数を使いこなせ!
 次の SQL 文を考えて下さい。
 下記のような、正規化されていない(わざと非正規化した場合など)売上入金表(data)がありす。
No. 列名 列名称 備考
1. ymd 日付 NUMBER(8)  
2. denno 伝票番号 NUMBER(4)  
3. kbn 区分 NUMBER(1) 1:売上、2:入金
4. kingaku 金額 NUMBER(9)  

 問題.日別売掛集計表を出力したい。出力する項目は、日付、売上伝票枚数、売上金額、入金伝票枚数、入金金額です。
 このように、1つの表からある区分別に集計した値を列方向に出力する SQL 文をあなたは作れますか?(MS-Accessはクロスクエリーという ANSI に無い SQL コマンドがありますが...)
 普通の SQL 文だけではできませんので、なんらかの言語を使用して集計しなければいけません。(実用に耐えれなくてもいいのなら JOIN で可能だが)

 しかし、DECODE という関数を使用すれば簡単に SQL 文1命令でできます。
 DECODE 関数の文法は DECODE( a, b1, c1, [... bN, cN, ] d ) です。その機能は、
  IF a = b1 THEN return( c1 );
   |
  ELSE IF a = bN THEN return( cN );
  ELSE return( d );
   という機能をする関数です。

 この DECODE 関数をうまく使用すると、複雑な SQL 文が簡単に作れます。先ほどの問題の答えは、
  SELECT COUNT( DECODE( kbn, 1, kbn, NULL )),
      SUM( DECODE( kbn, 1, kingaku, NULL )),
      COUNT( DECODE( kbn, 2, kbn, NULL )),
      SUM( DECODE( kbn, 2, kingaku, NULL ))
    FROM data
    GROUP BY ymd;
  です。この SQL 文は、DECODE 関数と『グループ関数は NULL 値以外が対象』という機能をうまく使用した例です。


SQL*Net V1UNIXOracleサーバとパソコンがつながらない。
 SQL*Net V1.x は、tcpctl start で orasrv を起動することは知っていると思いますが、orasrv はサーバ上で起動していますか?ps コマンドで確認して下さい。
 始めは起動しているのだが、パソコンから接続してエラーが出た後確認すると落ちている場合があります。その場合は、UNIXサーバの /etc/hosts に、パソコンのホスト名も登録してみて下さい。いけるようになったでしょ!
 その他の原因として、パソコン側に SQL*Net V1 の TCP/IP プロトコルアダプタがインストールされていない。TCP/IP 自体がインストールされていないことも考えられます。できることなら、パソコンの Oracleサーバと接続テストしてから、UNIXサーバに接続したいものですネ!
 SQL*Net V1 は、将来なくなり、SQL*Net V2 のみになりますが、SQL*Net V1 って設定がいらないので私は大好きです。マルチスレッドサーバ構成で接続するシステムは必ず SQL*Net V2 ですネ!


列名に備考を付けてわかりやすくしたい!
 SQL の特徴は、データをアクセスする言語でありながら、データベースの定義内容を調べる時にも使用できるのがすばらしいところだ!と前述していますが、テーブル名や列名に日本語を使用するのはちょっとなぁ〜と思います。そのため、テーブル名や列名を英語またはローマ字で命名すると、設計した本人は分かっても他の人がわかりにくくなります。
 OracleにはSQLにCOMMENTコマンドがあります。
このコマンドを CREATE TABLE コマンドを記述する時、同時に記述して発行しておくと、データベースの中に登録されます。そのため、インスタンスチャートやテーブル一覧表を側に置く必要が無くなります。

 例1)テーブル一覧表を出す SQL 文
SELECT SUBSTR( comments||'                ', 1, 16 ) テーブル名称,
SUBSTRB( A.table_name, 1, 20 ) TABLE_NAME,
num_rows, avg_row_len, chain_cnt,
blocks/(initial_extent/2048)*100 USE_RATE
FROM user_tab_comments A, user_tables B
WHERE A.table_name = B.table_name;

 例2)コメント付きの DESCRIBE コマンドのような SQL 文
SELECT SUBSTRB( column_id || '.' || T.column_name, 1, 24 ) 列名,
SUBSTRB( data_type || '(' || DECODE( data_precision, NULL, data_length||'', data_precision ||
DECODE( data_scale, 0, NULL, ',' || data_scale )) || ')', 1, 16 ) データ型,
DECODE( nullable, 'N', 'NN', ' ' ) NN,
SUBSTRB( comments, 1, 50 ) 備考
FROM user_tab_columns T, user_col_comments C
WHERE T.table_name = C.table_name AND
T.column_name = C.column_name AND
T.table_name = UPPER( '&TABLE_NAME' );


Visual Basic からストアドプロシジャーを呼べますか?
 はい。PL/SQL ブロックで記述するとなんでもできます。私は、Oracle7 RDBMS を使用するアプリケーションを Visual Basic で開発する時は、INTERSOLV の ODBC ドライバを使用しています。(RDO ができるから!)
 ストアドプロシジャ abc を実行させる場合は、
sql = "BEGIN abc( x, y ); END;"
xxx.Execute sql
xxx.CommitTrans


ストアドファンクションが呼べない!
 ストアドファンクション abc を呼ぶ場合、
BEGIN
abc( x, y );
END;
/
 になっていませんか?次のように修正して下さい。
DECLARE
ret NUMBER;
BEGIN
ret := abc( x, y );
END;
/
 戻り値を見ていないと、ストアドプロシジャーだと判断するようです。
 Oracle は、サーバ側で管理しているプログラムを総称して『ストアドプログラム』と呼んでいます。そのプログラムは4種類に分類されます。


PL/SQL のデバッグがつらい!
 PL/SQL を覚えると、Oracle RDBMS を使用したシステムの開発が非常に効率良くなります。Visual Basic で、ストアドプロクラムなどで使用するのですが、なにぶん入出力が無くデバッグに苦労します。
 ストアドプログラムを開発する場合、いきなり CREATE PROCEDURE でなく、IS から後ろの行を DECLARE に変更して、SQL*Plus などで実行してみるのは、みんな行っていると思います。それとか & を付けた置換変数も使用していると思います。(入力を聞いてきてくれるので、再実行すると繰り返し入力できる)
 標準添付の(DBMS_OUTPUT)パッケージを使用すると、PL/SQL ブロックから出力ができます。プロシジャー名と使い方は、DBMS_OUTPUT.PUT_LINE( 文字 ); です。このストアドプロシジャーを使用すると PL/SQL ブロックをデバッグ時に非常に役に立ちます。

注意その1:SQL*Plus でその PL/SQL ブロックを実行する場合は、SQL*Plus 上で SET SERVEROUT ON と入力しておいて下さい。
注意その2:DBMS_OUTPUTパッケージの実行権がありません!というエラーが出た人は、DBMS_OUTPUTパッケージの持ち主は SYS さんなので、SYS さんでログオンして、GRANT EXECUTE ON DBMS_OUTPUT TO PUBLIC; を実行して下さい。
注意その3:DBMS_OUTPUTパッケージを使用していて、バッファオーバーフローが出た場合、DBMS_OUTPUT.ENABLE(1000000);を一番始めに実行して下さい。100万が最大値です。

 余談ですが、ストアドプロシジャーなどを作る時、エラーが出た場合、SQL*Plus から SHOW ERRORS と入力すると、エラーの内容と行番号が表示されるよ!


今、誰が何を実行させているのか知りたい!
 LANまたはWAN上でRDBMSを使用したC/SSを構築した時、今、誰が何を実行しているか知りたい時があります。そんな時は、SQL*PlusからDBAロールを持ったユーザでログオンして、下記のコマンドを入力して下さい。SQLスクリプトファイルにしておくと便利です。
COLUMN osuser FORMAT A10
COLUMN machine FORMAT A12
COLUMN program FORMAT A16
COLUMN username FORMAT A10
COLUMN sid FORMAT 990
COLUMN serial# FORMAT 9990
SELECT osuser, machine, sid, serial#, program, username,
TO_CHAR((SYSDATE-logon_time)*24,'000.00') "TIME[H]",
DECODE(status,'ACTIVE','実行中','') STATUSFROM v$session
WHERE program NOT LIKE 'ORACLE%'
ORDER BY osuser, machine;
注意:Oracle7.2までをお使いの方は、logon_time がありませんので、その列を消して下さい。


ソートを早くしたい!
 SELECT 文の中に、DISTINCT, ORDER BY, GROUP BY 句のどれかが入っていると、サーバープロセスはソートを行います。このソート処理を早くする方法があります。
 Oracle のソートは、テンポラリー表領域を使用してソートするのですが、いきなりテンポラリーを作成しません。各サーバープロセスに割り当てられたメモリーを使用してソートします。そして、割り当てられたメモリーをいっぱい使うと、そのメモリー内容をテンポラリーに掃き出して、また残りのソートをメモリーで行います。
 上記の内容からソートの速度を上げる方法は2通りあります。1.サーバプロセスのソート用メモリーを増やす。専用サーバ構成のシステムはサーバ側のメモリーを使用しますので注意して下さい。マルチスレッドサーバ構成のシステムなら思いっきり増やしましょう。設定は、初期パラメータファイルに SORT_AREA_SIZE = [Bytes] を記述します。(私はここの値を10MBにしたことがある。データベースを停止する時、テンポラリー表領域をチェックすると1日で1度もI/Oされていなかった。^^;)
 2.テンポラリー表領域の DEFAULT STORAGE の INITIAL と NEXT の値を SORT_AREA_SIZE + 1 DB_BLOCK にして、PCTINCREASE を 0 に設定する。テンポラリ表領域は、いつでも DROP TABLESPACE できますので、CREATE TABLESPACE で作り直して下さい。(セグメントはエクステントの集まり、エクステントはデータブロックの集まり。最初のエクステントの1データブロックをルートブロックと言い、特殊なデータに使用されます。だから、SORT_AREA_SIZE+1DB_BLOCK=EXTENTS_SIZE にしておくと、ソートの掃き出しが1エクステントぴったしになって、非常に早く行えます。)


DELETE コマンドを実行するとエラーになる?
 DELETE コマンドを実行すると、ORA-01628: 最大エクステント:<名前>に達しました(ロールバック・セグメント:<名前>)。というエラーメッセージが出ることがあります。
 常時オンラインになっている、ロールバックセグメントが小さいからです。方法は、常時オンラインのロールバックセグメントを全て大きく作り直す。または、1つ大きなロールバックセグメントを作り、DELETE FROM ...コマンドの前に、SET TRANSACTION USE ROLLBACK SEGMENT <ロールバックセグメント名>; を実行する。
 現在のロールバックセグメントの情報を知るには、データディクショナリ DBA_ROLLBACK_SEGS を見ます。
 ロールバックセグメントの作り方は、CREATE ROLLBACK SEGMENT <ロールバックセグメント名>DEFAULT STORAGE ( INITIAL {nK} NEXT {nK} PCTINCREASE 0 MINEXTENTS 2 OPTIMAL {nKx2} ); です。


SQL 文で SQL 文を作れ!
 ANALYZE コマンドは便利だが、定期的に実行しなければいけないのでは、表の一覧表が無ければちょっと...とお思いの方!それは未熟です。
 SQL 文で SQL 文が作れます。たとえばあるユーザの表をすべて ANALYZE TABLE コマンドをかける SQL 文を作ってみましょう。
SELECT 'ANALYZE TABLE '||table_name||' COMPUTE STATISTICS;' FROM dba_tables WHERE owner = '...';
です。後は、出力された結果をカット&ペーストでもう一度実行すればOKです。
 このようにすれば、動的 SQL を使って難しいプログラムを組まなくても、ちょっとした操作を付加するだけで可能になります。


簡単にパフォーマンスを向上させる方法は無いの?(REDOログファイル編)
 データベースを使用したシステムをとりあえず開発したのだが、ちょっとおそいなぁ!と思っている方に簡単なデータベースチューニング方法があります。
 それは、REDOログファイルのサイズをもうちょっと大きくするのです。変更方法は下記の通りです。
1.SQL*Plus を使用して SYSTEM さんでログオンして下さい。
CONNECT system/manager2.SELECT * FROM v$log; と入力して下さい。
SELECT * FROM v$log; または
SELECT * FROM v$logfile; こっちは物理ファイル名が表示される。二重化していると倍の行が表示される。3.サイズはいくつですか?200KB ぐらいのファイルが 2 つ表示された方は、20MB ぐらいのファイルを 2 つ追加します。
ALTER DATABASE ADD LOGFILE GROUP 3 'c:\orawin95\xxxx3.log' SIZE 20M;
ALTER DATABASE ADD LOGFILE GROUP 4 'c:\orawin95\xxxx4.log' SIZE 20M;
SELECT * FROM v$log; で確認して下さい。
4.LOG SWITCH を発生させます。
ALTER SYSTEM SWITCH LOGFILE; を 2 回ぐらい入力する。5.200KB のファイルを切り離します。
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
SELECT * FROM v$log; で確認して下さい。6.SQL*Plus から抜けます。
7.入らなくなった 2 つのファイルを削除します。
 これで、CREATE, INSERT, UPDATE, DALETE コマンドが結構早くなると思います。


CRAETE USER コマンドの注意事項。
 Windows 上の User Manager でユーザを定義している場合は問題ありませんが、SQL*Plus や SQL*DBA で CREATE USER コマンドでユーザを作成する場合の注意事項があります。
 CREATE USER コマンドには、省略可能な句として、DEFAULT TABLESPACE 句と TEMPORARY TABLESPACE 句があります。これらの句を省略した場合は、共に SYSTEM 表領域が設定されます。
 SYSTEM 表領域は、すべてのオブジェクトを管理している大事な表領域です。できるだけオブジェクトを作らない方が良いと思います。(ファイルはアクセスが無ければ壊れることもありません。得に write が、)
 後で気が付いて、オブジェクトを他の表領域に移すのは面倒ですし、CREATE TABLE コマンドでいちいち表領域を指定するのも面倒です。最初のちょっとした設定が後の作業を簡単にします。
 気になる方は、SELECT table_name, tablespace_name FROM dba_tables; や SELECT username, default_tablespace, temporary_tablespace FROM dba_users; を実行してみて下さい。表領域が SYSTEM になっているのなら、すぐに ALTER USER コマンドで修正してあげて下さい。自分のスキーマ内にオブジェクトを作成しないユーザでも SELECT コマンドで TEMPORARY 表領域は自動的に使用されてしまいます。


マルチスレッドサーバって何?
 Oracle7 RDBMSが動作するサーバ上のバックグラウンドプロセスをイメージして下さい。インスタンスを1つ起動すると、dbwr, lgwr, pmon, smonなどが起動されます。その他にユーザアプリケーションと1対1でサーバプロセスが起動します。サーバの物理メモリは限られています。RDBのシステムはスワップさせないのが第一条件なのに、サーバプロセスがどんどん起動したのではメモリが減り、スワップされだします。
 その上、バッチ処理はともかく、入力プログラムなどはそう頻繁にSQL文を発行しません。そのプログラムの為にバッチ処理と対等なサーバプロセスが起動するのはちょっと考え物です。
 そこで、複数のユーザプロセスに対応できるサーバプロセス『共有サーバプロセス』というものがあります。この機能は SQL*Net Version 2 で対応しています。共有サーバプロセスは、あらかじめ決められた数だけ起動しておきます。SQL*Net Version 2 のデイスパッチャプロセスが SGA の共有プール領域を使用して限られた共有サーバプロセスとやり取りを行います。このような構成を『マルチスレッドサーバ構成』といいます。それに対して、ユーザプロセスとサーバプロセスが1対1で接続する構成を『専用サーバ構成』といいます。
 専用サーバ構成とマルチスレッドサーバ構成は共存できます。RDBMSに接続する時に指定します。バッチ処理プログラムは専用サーバ構成で接続し、入力プログラムなどはマルチスレッドサーバ構成で接続する。というふうに使い分けることで、パフォーマンスを落とさずサーバの資源(メモリ)を有効活用できます。
 サーバ上にメモリが十分あるのなら、すべて専用サーバ構成にするより、マルチスレッドサーバ構成にして1つ1つのサーバプロセスが使用できるメモリサイズ(SORT_AREA_SIZEなど)を広げたり SGA を広げた方が、もっとパフォーマンスが向上するでしよう。


処理を中止したいのだが・・・
 SQL*PlusでSELECT文を発行した。JOINしているのに、WHERE句に結合条件を忘れた。2つの表はでかく、JOINするととんでもなくなる。早く処理を中止したい!そんな時、Session Managerを使用するのですが、タスク切替えが効かずSession Managerが起動できない!データベースのパスワードがわからない!など、Session Managerが使用できない時どうすればいいのでしょうか?



異様にメモリが減るのだか・・・
 Oracle 7.2 for Solarisを使用していて、データベースを起動する前と、起動後のswap -sコマンドのavailableが設定したSGA以上に減る!Oracle 7.2からSGAをメモリ上に効率良くとる為、メモリをSGAの2倍使用します。Oracle RDBMSはスワップしないようにSGAを調節するのが一番のポイントです。しかし、かってに2倍のメモリを使用されたのでは物理空きメモリの半分しかSGAとして使用できません。この2倍使用するのを止めさせたい場合は、初期パラメータファイルに use_ism=FALSE という行を追加します。省略時は use_ism=TRUEです。sqldbaなどのshow paramater useコマンドでチェックしてみて下さい。


OCIについて知りたい!
 手続き型言語を使用してOracleアプリケーションを開発する場合、Pro*C, Pro*COBOL, Pro*FORTRANなどのプリコンパイラを使用します。COBOLやFORTRAN言語は構造型でないためGOTO文を平気で使用できますが、C言語は構造型プログラミングでプログラムを組む人が多いです(C言語でgoto文が禁止されているわけではない)。Pro*Cの中のSQL文のエラー処理はgoto文で処理されます。そのため、階層(ネストやレベル)立てて組む人には非情にソースが見にくくなります。その上、Pro*Cのエラー処理は位置的であり、ボトムアップでプログラムを組む人にはトップダウンにしなければならないので不便です。
 これらの理由だけではないのですが、純粋なCプログラムを組みたい方にはOCI(Oracle Call Interface)をお勧めします。OCIはCのサブルーチンライブラリの形で提供されます(Pro*Cに入っている。Pro*COBOLやPro*FORTRANにもある)。そのため、エラー処理が関数のリターンコードで解るため、構造型プログラミングが可能で、ボトムアップのプログラミングも可能です。
 その他、OCIの特徴として、無駄な処理が無い為実行が早い、遅延解析モードが使用できる。プリコンパイルしなくてよい、ソースがみやすいなどです。当然すべての機能を備えています。
 最後に、OCIを使用してアプリケーションを開発する方は、PL/SQLのRDBMS_SQLパッケージなど動的SQLについて理解していなければなりません。
 詳しくは、『
Oracle Call Interface入門』を参考にして下さい。


パーティションド・オブジェクト(テーブルとインデックス)について知りたい!
 Oracle8 は、大規模データベース(VLDB:Very Large DataBase)に対応する為に、さまざまな機能の追加と改善が行われました。その追加された機能の一つが、パーテーションド・オブジェクトです。パーティションとは、大規模なオブシェクト(実表や索引)を複数のパーティションに分割して管理をし易くすることです。パーティションは別々のセグメントになる為、別々の表領域に配置することができます。(Oracle7R7.3でもパーティション・ビューという物はあります)
 パーティションド・オブジェクトには、バーティショニング・キーという列の集まりを指定し、どのパーティションに書きこむかをパーティショニング・レンジを指定することで決まります。
 また、パーティションごとに登録される索引をローカル・インデックス、すべてのパーティション分の索引をグローバル・インデックスと言います。
 詳しくは、『
Oracleアーキテクチャー』を参考にして下さい。


うまくインストールできない
 Oracle8i をCDよりインストールすると、うまくインストールできないことがあります。その場合は、CDを丸ごとCドライブ等ハードディスクに一度コピーして、コピーした先からインストールするとうまくいく場合があります。


データベースを起動/停止する sqldba または svrmgr が無い?
 Oracle9i より、svrmgr は SQL*PLus に統合されました。SQL*Plus を svrmgr として使用するためには、下記のような引数にて起動します
   sqlplus "sys/change_on_install AS SYSDBA"  (←起動オプションがダブルクォーテーションにてくくられていることに注意してください。)
 SQL*Plus にてデータベースに接続した後に、shutdown や startup が行えます。


初期パラメータファイル(initoracle.ora)が無い?または有効にならない
 Oracle9i より、初期パラメータファイルの内容を動的に変更できるようになったため、バイナリのパラメータファイルが追加されました。下記の優先順位で Oracle はパラメータを認識します。
   @.startup pfile= にて指定したファイル
   A.spfile{SID}.ora
   B.spfile.ora
   C. init{SID}.ora
   D. init.ora
 すなわち、今までの initoracle.ora(C) を使用したい場合は、AとBをリネームして、データベースを再起動すると有効になります。
 また、initoracle.ora(C) のテンプレートは、Oracle のホームディレクトリを検索すると init.ora(D) があります。


気軽に質問して下さい。お問い合わせは、buchi@t3.rim.or.jpまでお願いします。
あまりかたっくるしいことはなしですが、サーバとクライアントの機種、OS、メモリ、ORACLEのバージョン、SQL*Netのバージョンなども参考程度に教えてネ!


戻る

Last update (C) 2004 HiroLaboratory. All rights reserved.