七転八起SEの備忘録

SUBSTR関数とINSTR関数は、単一行関数のうちの文字関数に分類される。
文字関数とは、文字データを受け取り、文字データを返す関数。



引数の文字列を、指定した位置から指定文字数分返す関数。

　SUBSTR(引数文字列, 指定位置(m), 指定文字数(n))

・引数で指定した文字列のm文字目からn文字分の文字列を返す。
・nが省略された場合はm文字目から末尾までの文字列を返す。
・mに負の値が指定された場合は、文字列の末尾から数えてm文字目からn文字分の文字列を返す。



引数の文字列から、検索文字列が何文字目にあるかを返す関数。
なお、検索文字列が見つからなかった場合は0（ゼロ）を返す。

　INSTR(引数文字列, 検索文字列, 検索開始位置(m), 指定回数(n))

・引数で指定された文字列のm文字目以降からn回目に出現した検索文字列の先頭の位置を返す。
・m,nが省略された場合は、どちらも1がデフォルト値となる。
・mに負の値が指定された場合は、文字列の末尾から（先頭に向けて）検索する。
　ただし、負の値が指定されても先頭からの位置を返す。



その他、主な文字関数には次のものがある。

関数	説明
UPPER	文字列を大文字に変換
LOWER	文字列を小文字に変換
INITCAP	先頭の文字を大文字に変換
CONCAT	2つの文字列を結合
SUBSTR	文字列から部分的に文字列を取得
LENGTH	文字列の長さを取得
INSTR	検索文字が何文字目にあるかを数値で返す
LPAD	文字列の左側に指定した文字を埋め込む
RPAD	文字列の右側に指定した文字を埋め込む
TRIM	文字列前後の指定文字を削除
REPLACE	文字列の一部を別の文字列に置換

暗黙的なデータ変換とは、データ型の変換が意味を持つ場合に、Oracle Databaseが自動的に行う変換。
意味を持たない変換の場合にはエラーとなる。

ただし、暗黙的なデータ変換には様々な問題があるため、常に明示的に変換する事が推奨される。


以下のような場合に変換が行われる。


文字列を数値に変換



デフォルトの日付表示書式にしたがい日付値に変換



数値を文字列に変換



日付値を文字列に変換



明示的なデータ変換とは、変換関数を使用して行うデータ変換のこと。
Oracle Databaseにはさまざまな変換関数が用意されているが、数値を日付値に変換したり、日付値を数値に変換する関数は存在しない。


以下は主な変換関数。

変換関数	説明
TO_CHAR	数値や日付値を文字列に変換
TO_NUMBER	文字列を数値に変換
TO_DATE	文字列を日付値に変換

・CHARからNUMBERへの変換はTO_NUMBER
・CHARからDATEへの変換はTO_DATE
・NUMBERからCHARへの変換はTO_CHAR
・DATEからCHARへの変換はTO_CHAR

※NUMBERからDATE、DATEからNUMBERへの変換は不可

データベースに格納できる表やビューなどを総称してデータベース・オブジェクトと言う。データベース・オブジェクトは必ずいずれかのユーザーに所有されており、スキーマ・オブジェクトとも呼ばれる。

ユーザーが人間であるならば、スキーマは人間がオラクルから借りたオフィスの一室のようなもの。
借りたオフィスに何を置こうが、改修しようが他には全く影響がない。
また、他の人を自分のオフィスに招待することもでき、オフィスに置いてあるものには利用制限をかけることもできる。

主なスキーマ・オブジェクトは以下のとおり。

要素	説明
表	データを格納
索引	データ取得の速度を向上させる
順序	一意な整数値を生成
ビュー	1つ以上の表を基にして作成する仮想的な表
シノニム	オブジェクトのエイリアス(別名)

なお、スキーマ・オブジェクトは特定のユーザーに所有されるものであり、記憶域やロール、ユーザー等システム全体で共有されるものはスキーマ・オブジェクトではない。

要素	説明	入力例	表示
9	指定された桁数の値を表示	999999	1234
0	先行または後続を0埋めして表示	099999	001234
$	$記号の表示	$99999	$1234
L	ローカル通貨記号の表示 (日本語環境の場合は\)	L99999	\1234
.	指定された位置に小数点を表示	999.999	1.234
D	指定された位置に小数点を表示	999D999	1.234
,	指定された位置にカンマを表示	999,999	1,234
G	指定された位置にカンマを表示	999G999	1,234
S	+または-記号を表示	S999999	+1234 -1234
V	値に10のn乗を掛けて表示 (nはV後の9で示される)	999V99	123400
MI	負の値の文末に-記号を表示	999999MI	1234-
PR	負の値の前後を<>で囲んで表示	999999PR	<1234>

リレーショナルデータベースは関係データベースとも呼ばれ、データを表形式で管理するデータベースのこと。
テーブル（表）は、レコード（行）とフィールド（列）によって形成され、テーブル同士を結合することができる。

リレーショナルデータベース管理システムは、ACID特性という概念に基づいて設計されており、トランザクション処理が保持すべき4つの要素を頭文字で表したもの。


「処理が全て（ALL）実行されたか、全く実行されない（nothing）かのいずれかである」という特性。
（仮にトランザクションが失敗したとしても、データが中途半端な状態にはならない）



「トランザクションの実行前後で、データの整合性が補償される」という特性。
（制約に反するような処理があれば、トランザクションを中断しロールバックする）



「同時に実行されたトランザクション同士が、相互に干渉しない」という特性。
（複数のプログラムが同時に同じデータを更新しないよう排他制御を行う）



「記録されたデータが、簡単には失われない」という特性。
（何らかの障害が発生した場合は、ログを利用して障害発生前の状態に復旧できる）

　ALTER TABLE <テーブル名> ADD COLUMN <列名> <データ型>;





　ALTER TABLE <テーブル名> MODIFY <列名> DEFAULT <値>;





　ALTER TABLE <テーブル名> DROP COLUMN <列名>;





　ALTER TABLE <テーブル名> SET UNUSED COLUMN <列名>;





　ALTER TABLE <テーブル名> READ ONLY;





　ALTER TABLE <現テーブル名> RENAME TO <新テーブル名>;

NVL関数は値がNULL値であった場合に、別の値を返す関数。

　NVL(第1引数, 第2引数)

第1引数の値がNULL値の場合、第2引数の値を返す。
第1引数の値がNULL値以外の場合、第1引数の値を返す。



NVL2関数は値がNULL値だった場合と、NULL値以外の場合で返す値を変えることができる関数。

　NVL2(第1引数, 第2引数, 第3引数)

第1引数の値がNULL値以外の場合、第2引数の値を返す。
第1引数の値がNULL値の場合、第3引数の値を返す。

なお、第1引数にはNULLリテラル以外の値を指定しなければならない。
（※指定した列の値が結果としてNULL値となる場合は問題ない。）



NULLIF関数は第1引数と第2引数を比較して、等しい場合にNULLを返す関数。

　NULLIF(第1引数, 第2引数)

第1引数と第2引数が等しい場合はNULL値を、等しくない場合は第1引数の値を返す。



COALESCE関数は引数の値を順に判定し、最初に見つかったNULL値以外の値を返す関数。

　COALESCE(第1引数, 第2引数, … )

指定された引数全てがNULL値の場合は、NULL値を返す。

COALESCE関数の引数は、すべて同じデータ型でなければならない。
異なるデータ型の値を指定するとエラーとなる(暗黙的なデータ変換は行われない)。
少なくとも2つの引数がなければならない。

ERモデルとは、システムの扱う対象を、「エンティティ」（実体）、「アトリビュート」（属性）、「リレーションシップ」（関連）という3つの構成要素を用いてモデル化したもの。



エンティティはデータベースに含まれるテーブルのことを指す。
属性の意味がある「アトリビュート」が入ってくる。



アトリビュートは、データベース上のテーブルが持つフィールドのことを指す。



リレーションシップは参照整合性制約のことで、物理データベースでは外部キーが設定される。
単にリレーションと呼ばれる場合もある。

WHERE句で「(+)」と記述されたSQLをたまに見かけることがある。

この「(+)」は外部結合演算子と呼ばれるもの。

外部結合演算子(+)を右側の項目に付与するか、左側の項目に付与するかで「LEFT OUTER JOIN」と「RIGHT OUTER JOIN」どちらの意味になるかが変わってくる。



　　WHERE テーブルA.カラム名 = テーブルB.カラム名(+)


FROM テーブルA LEFT OUTER JOIN テーブルB ON テーブルA.カラム名 = テーブルB.カラム名




　　WHERE テーブルA.カラム名(+) = テーブルB.カラム名


FROM テーブルA RIGHT OUTER JOIN テーブルB ON テーブルA.カラム名 = テーブルB.カラム名

通常から重かった検索がついに10分を超え、タイムアウトが発生してしまった。
ヒント句を利用し、改善を図る。



通常はOracleが最適な実行計画を選択してくれている。(注1)
ただし、複雑なSQLの場合（7つ以上の表を結合するなど）は最適な実行計画とならないことがある。
そのような場合に実行計画に対するヒントを利用する。

(注1)
複数のテーブルを結合するSQLを実行すると、ORACLEはネステッドループ、ハッシュ、マージの3種類のいずれかの結合方法を選択し実行計画を立てる。



一般的に結合する件数が多くなるほどHASH結合のほうが早くなる可能性が高い。
最も大きな要因は「ネステッドループ結合は物理読み込みが多いとパフォーマンス劣化が激しい」ため。




SELECT /*+ ヒント句 */ 項目,… FROM 〜


・コメントの記号「/*」の直後に「+」を付けるとヒント句となる
・「SELECT」の後に記載する


一見すると普通にコメントの様に見える。実際にヒントの書き方が間違っていたとしても、コメントとして無視されるためエラーにならない。したがって、ヒントを書いたら思い通りの実行計画になったか確認すること。

テーブル名の指定方法

ヒントの対象となるテーブルを指定する場合、FROM句に書かれているテーブル名をそのまま指定する。
よって、別名を定義している場合はその別名を書くこと。※別名を定義していない場合はテーブル名を指定する。


SELECT /*+ USE_HASH(B) */ *
FROM xxxxx A
INNER JOIN yyyyy B
ON A.zzz = B.zzz

今回試した主なヒント

USE_NL
　指定したテーブルをネステッドループ結合にてスキャンさせる
　＜使用例＞
　　　/*+ USE_NL(A B) */


USE_HASH
　指定したテーブルをハッシュ結合にてスキャンさせる
　＜使用例＞
　　　/*+ USE_HASH(A B) */


FULL
　指定したテーブルに対して、フルスキャンさせる
　＜使用例＞
　　　/*+ FULL(A) FULL(B) */
　　　※1テーブルずつ分けて書くこと(「FULL(A B)」のようにまとめて書かない)


ORDERED
　FROM句に記述した順番通りに結合する
　＜使用例＞
　　　/*+ ORDERED */



結果、10分以上かかっていた検索が13秒前後へ落ち着いた。
まだ改善は見込めそうだが、本事象はタイムアウトが発生し検索が出来ない、そのことでの影響が大きい。
リリースまでのスピードが優先されるため、今回はこれで良しとする。