１．INDEXを有効に使う

大抵の予想以上に遅いSQLの要因は「INDEX（索引）」がうまく働いていないという理由です。 では、「INDEX（索引）」はどのように張れば良いのかというと。。。
（１）．INDEX列に関数は使用しない例えば、
×　SELECT a FROM emp WHERE TO_CHAR(day,'YYYMMDD') = '19800111'と、WHEREを指定すると、例えdayにインデックスが張ってあってもそれは利用されません。 この場合は
○　SELECT a FROM emp WHERE day = TO_DATE('19800111','YYYMMDD')とすべきです。 この他にも多くの関数がORACLEには用意されていますが、使用するのはカラムではなく値の方にしましょう。
（２）．比較条件方法の改善比較条件詞によってはインデックスを使用しないものがあります。 下記に変更一覧を記述しますので参考にして下さい。
・「IS」を使うとインデックスを使用しない
×　SELECT a FROM emp WHERE a IS NOT NULL; ×　SELECT a FROM emp WHERE a IS NULL; ○　SELECT a FROM emp WHERE a >= 0;
・「NOT」「<>」「!=」を使うとインデックスを使用しない否定形(NOT EQUAL, NOT IN)はインデックスを使用できません。
×　SELECT a FROM emp WHERE a != 0; ○　SELECT a FROM emp WHERE a > 0;
・複合インデックスはその順番どおりでないとインデックスを使用しないCREATE INDEX id_emp ON emp (a,b) UNRECOVERABLE;
×　SELECT a FROM emp WHERE b = 0 AND a = 12; ○　SELECT a FROM emp WHERE a = 12 AND b = 0; ×　SELECT a FROM emp WHERE b = 0; ○　SELECT a FROM emp WHERE a = 12;
・INDEX列では「LIKE '%C%'」「LIKE '%C'」の使用は避ける
×　SELECT a FROM emp WHERE c LIKE '%ACCOUNT%'; ×　SELECT a FROM emp WHERE c LIKE '%ACCOUNT'; ○　SELECT a FROM emp WHERE c LIKE 'ACCOUNT%';
・条件の左側で式を用いている
×　SELECT a FROM emp WHERE a * 1.1 > 100; ○　SELECT a FROM emp WHERE a > 100 / 1.1;
・条件でORを用いている
×　SELECT a FROM emp WHERE a > 100 OR b = 'abc';どうしても使いたいならビットマップ索引を張りましょう。
（３）．範囲検索時は上限・下限を指定する上限・下限を指定できる場合は、双方を指定する事でインデックスの無駄な読み込みを回避できます。
×　SELECT a FROM emp WHERE a <= 2800; ○　SELECT a FROM emp WHERE a BETWEEN 0 AND 2800;
（４）．無駄なインデックスをベタベタ張らない管理の容易さ、領域の節減、負荷の減少の為にもテキトーにベタベタとインデックスを張らないようにしましょう。
（５）．インデックス作成時には可能ならUNRECOVERABLE句をUNRECOVERABLE句を指定すると作成時・作成後にソート処理およびログ取得処理が割愛され作成時間が最速になります。 しかし、ログがないため、何かあったら復元できません。。。 頻繁にバッチなどでインデックスを張り替えてる処理にはお勧めです。
（６）．コストベース・オプティマイザで運用インデックスの能力を最大限に引き出すにはコストベースでの運用を心がけましょう。 方法はインデックス作成後にはANALYZE文もしくはDBMS_STATSパッケージによりインデックスを張った表の統計情報を取ります。 ANALYZE文には以下の2パターンあります。
・完全な統計情報取得
ANALYZE TABLE emp COMPUTE STATISTICS;
・全データの10%だけ抽出して統計情報取得
ANALYZE TABLE emp STATISTICS SAMPLE 10 PERCENT;これだけです。 もし、全ての表をコストベースで運用すると明示的に宣言する場合は、初期化パラメータで
optimizer_mode=ALL_ROWSと、指定します。 ANALYZE文を実行した場合は実行された表のみコストベースになります。
（７）．定期的にインデックスの再作成を行なうインデックスは，使いつづけると断片化をおこします。 よって，断片化が発生しているかを調べて，必要であればインデックススの再作成を行いましょう。
・インデックスを分析する
ANALYZE INDEX id_emp VALIDATE STRUCTURE; SELECT lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len FROM index_status WHERE name = 'id_emp';
LF_ROWS
LF_ROWS_LEN
DEL_ROWS
DEL_LF_ROWS_LEN
2589
92340
97
3432
☆　現在のインデックスが2589行分ある。☆　その物理消費量が92340バイトとなる。 ☆　削除された行に対する空のインデックスが97個。 ☆　その空が3432バイトの領域を使っている。 ☆　97/2580で3.7%のが無駄に使われている。 ☆　この値が10％を越えたら，インデックスの再作成を行う。
・インデックス再作成方法
ALTER INDEX id_emp REBUILD;もしくはDROP・CREATEするだけです。

[ORACLE10g] SQL正規表現関数および条件

REGEXP_LIKE
---------------------------------------------------------------

ある文字列において特定パターンが検索されます。問合せのWHERE句でこの関数を使用すると、正規表現と一致する行が戻されます。条件は、制約内、またはブール値を戻すPL/SQLファンクションとしても有効です。
次のWHERE句では、StevenまたはStephenという名を持つ従業員がフィルタリングされます。WHERE REGEXP_LIKE(first_name, '^Ste(vph)en$')

REGEXP_REPLACE
---------------------------------------------------------------
文字列内の特定パターンが検索され、そのパターンの各出現箇所が指定した文字列と置き換えられます。
次の関数コールでは、country_name列の各文字の後に空白が1つ挿入されます。REGEXP_REPLACE(country_name, '(.)', '\1 ')

REGEXP_INSTR
---------------------------------------------------------------
正規表現パターン（サブストリング）の指定の出現箇所で文字列またはサブストリングが検索され、一致した文字列またはサブストリングの検出位置を示す整数が戻されます。検索する出現箇所および検索開始位置を指定します。
次の関数コールでは、email列で有効な電子メール・アドレスのブール・テストが実行されます。REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0

REGEXP_SUBSTR
---------------------------------------------------------------
正規表現パターン（サブストリング）の指定の出現箇所で文字列またはサブストリングが検索され、サブストリング自体が戻されます。検索する出現箇所および検索開始位置を指定します。
次の関数コールでは、正規表現内の空白が無視され、xフラグを使用して最初の文字列が一致として処理されます。REGEXP_SUBSTR('oracle', 'o r a c l e', 1, 1, 'x')

REGEXP_COUNT
---------------------------------------------------------------
文字列でのパターンの出現回数が戻されます。文字列とパターンを指定します。開始位置と一致オプション（たとえば、cの大/小文字区別）も指定できます。
次の関数コールでは、文字列'Albert Einstein'における開始文字位置7からのe（Eではない）の出現回数（つまり1）が戻されます。REGEXP_COUNT('Albert Einstein', 'e', 7, 'c')

階層構造の作成

今回は階層構造を作成します。
会社の体制、商品のカテゴリのような親子関係のデータを奇麗に取得する方法です。

SQLPLUS scott/tiger --少しすっきりと見るために.. セッティング先にしてましょう。
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 100
SQL> COL ename FORMAT A20

SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM emp
START WITH EMPNO=7839
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO MGR JOB
-------------------- ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK


逆順で表示する場合にはCONNECT BY empno=PRIOR mgrのように修正します。