備忘録

　これらの結論としてChaffanjon氏は、典型的なオンライントランザクション処理でOracleデータベースを使っている場合、I/O性能がボトルネックになっているケースでは、Seagateの1万RPMのHDD2台を1台のIntel製SSDで置き換えるほうが有利としている。同等のスループットが出せるだけでなく、応答速度が2倍になる。 　また、ピーク時のSSDの性能はメモリに迫るものがあり、SSDでは685TPS（応答時間30ms）、メモリでは821TPS（応答時間24ms）となったという。 　逆にCPU性能がボトルネックになっている場合、SSDへの置き換えでは、容量単価が高くなるばかりで、HDDを置き換えたときの性能向上が限定的になる、としている。

これがその１（極値関数の利用）

DELETE FROM Products P1
WHERE rowid < ( SELECT MAX(P2.rowid)
                   FROM Products P2
                  WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;

EXISTSを使ったパターン（非等値結合の利用）

DELETE FROM Products P1
WHERE EXISTS ( SELECT * FROM Products P2
                 WHERE P1.name = P2.name
                   AND P1.price = P2.price
                   AND P1.rowid < P2.rowid );

つまりは、重複している行のうち、一番デカイROWID以外のレコードは削除！ってことです

かなりウカツだったんですが、いつの間にやらSQLServerでは使えるようになっていたのですな！

[TSQL]再帰クエリ
再帰クエリその2
再帰SQL — Let's Postgres

Oracleでは、共通表式のWITH句は使えますが、WITH RECURSIVEによる再帰SQLはできませんでしたね。
かろうじてCONNECT BYによる階層問い合わせが出来ましたが、件数多いと全然帰ってこなかったんで、今までほとんど遣ったことないです。

Ask Tom "how to dynamically generate cursors with..."

質問した人は、テーブル名をパラメータにして動的にupdate文を生成したいらしい。

やり方は、

CREATE OR REPLACE PROCEDURE populate_email(p_in_table_name VARCHAR2)

AS
    
    TYPE cur_type IS REF CURSOR;
    tmp_cur cur_type;
    
    TYPE email_rec IS RECORD (
        intid internetaddress.intid%TYPE,
        intaddress internetaddress.intaddress%TYPE);    
    query varchar2(100);
    
    tmp_rec email_rec;
BEGIN
    
    query := 'select i.intid, i.intaddress
    from internetaddress i, '||p_in_table_name ||' b 
    where i.intid = b.id';
    
    OPEN tmp_cur  FOR query;
    LOOP
        FETCH tmp_cur INTO tmp_rec;
        EXIT WHEN tmp_cur%NOTFOUND;
    
        EXECUTE IMMEDIATE
        'update '||p_in_table_name ||' a
        set a.ademal = substr(trim('||tmp_rec.intaddress||'),1,50)
        where a.id = '||tmp_rec.intid;

    END LOOP;
    
    EXECUTE IMMEDIATE
    'COMMIT';

    CLOSE tmp_cur;

END;

一方Ｔｏｍの回答は、

execute immediate '
update (select a.ademal, substr( trim( i.intaddress ) ) intaddress
       from ' || dbms_assert.sql_object_name( p_in_table_name ) || ' a,
          internetaddress i
      where i.intid = a.id )
  set ademal = intaddress';

あいまいだったので整理。

連鎖：一行が複数ブロックにまたがって格納されている状態

で、連鎖は以下の２つ、行移行と行連鎖に分かれる。

行移行：
　発生条件：更新によりレコードの長さが拡張し、現在そのレコードが格納されているブロック内に収まる事が出来なくなった場合
　現象：別のブロックにレコードが移行し、元の場所には移行元のROWIDが残される

行連鎖：
　発生条件：１レコードの長さが１ブロックに収まらない場合
　　※なんか項目（列）が多い＋項目長が長い、という組み合わせで発生するケースだけど、たとえばブロックサイズを8KBとしたとき、8KB（正確には違いますが）以上の長さのレコードってすごくないですか？TMの佐藤さんが見たらダメダシ間違いなし

これも、行（レコード）を列（カラム）として表示するやり方。
ありがとうTom！

Ask Tom "Column to rows"

質問をした人は、こんなふうにUNIONするやり方を紹介。
CREATE TABLE rows_to_column AS
SELECT * FROM (
SELECT field_cnst, field_1 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_2 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_3 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_4 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_5 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_6 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_7 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_8 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_9 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_10 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_11 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_12 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_13 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_14 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_15 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_16 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_17 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_18 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_19 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_20 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) ) src

一方Tomの回答は、

scott%ORA10GR2> create or replace view vw
  2  as
  3  select to_char(empno) f1,
  4         ename f2,
  5             to_char(hiredate) f3,
  6             to_char(sal) f4,
  7             to_char(comm) f5,
  8             job f6,
  9             deptno field_cnst
10    from scott.emp;

View created.

scott%ORA10GR2>
scott%ORA10GR2> with data
  2  as
  3  (select level C from dual connect by level <= 6)
  4  select case C when 1 then f1 when 2 then f2 when 3 then f3 when 4 then f4 when 5
then f5 when 6 then f6 end val
  5    from vw, data
  6   where field_cnst in (select deptno from scott.dept)
  7  /

VAL
----------------------------------------
ANALYST

20182.53
09-DEC-82
SCOTT
7788
CLERK

800
17-DEC-80
SMITH
7369
SALESMAN
300
1600
20-FEB-81
ALLEN
7499
SALESMAN
500
....

モトネタはこちら
Ask Tom "Yup, ANOTHER Rows to Columns (or vice-versa) question. Sorry.", version 9.2.0

こういうデータに対して
TestID　　　Name      Phone
-------　-------    ----------
1　　　　　　John 　　　　1234567
2　　　　　　Peter　　　　9876543

こんなふうにしたい
Row1　　　　　Row2
--------    ----------
1　　　　　　　　　2
John　　　　　　 Peter
1234567　　　　9876543

けど、こうなっちゃう↓という質問に対して
Row1　　　　　Row2
--------    ----------
1　　　　　　　　NULL
John　　　　　 NULL
1234567　　　NULL
NULL　　　　　2
NULL　　　　　Peter
NULL　　　　　9876543

Tomの回答は、
10g以前だと

ops$tkyte%ORA9IR2> select rw,

  2         max(decode( rn, 1, decode( rw, 1, to_char(testid), 2, name, 3, phone) )) r1,
  3         max(decode( rn, 2, decode( rw, 1, to_char(testid), 2, name, 3, phone) )) r2
  4    from (select testid, name, phone, row_number() over (order by testid) rn
  5            from test),
  6         (select level RW from dual connect by level <= 3)
  7   group by rw
  8  /

        RW           R1             R2
---------- ---------- ----------
         1              1              2
         2           John          Peter
         3         1234567     9876543

11gだと、pivot/unpivot関数を使って

ops$tkyte%ORA11GR1> with data
  2  as
  3  (
  4  select rn, data, thing
  5    from (select to_char(testid) testid, name, phone, to_char(rownum) rn from test )
  6     unpivot ( data for thing in ( testid , name, phone) )
  7  )
  8  select thing, row1, row2
  9    from data
10  pivot( max(data) for rn in ( '1' as row1, '2' as row2 ))
11  /

THING  ROW1         ROW2
------ ---------- ----------
TESTID 1                2
PHONE  1234567      9876543
NAME   John            Peter

これもAsk Tomに出ていた記事。
/*+ APPEND */は、
insert /*+ APPEND */ into tab_a values .....では無効で
insert /*+ APPEND */ into tab_a SELECT ....でのみ有効。

Ask Tom "NOLOGGING AND REDOSIZE"

こんな感じ。

INSERT /*+ APPEND */ INTO tab_a
SELECT * FROM tab_b

んー、速い！
こちらの環境で、600万件（平均レコード長が105バイト）ほどのテーブルのインサートで2分程度でした（通常の場合は測る気がしないほどかかります）。

注意）アーカイブログモードの場合、tab_aがNOLOGGING属性で作成されていないと、LOGGINGモードでダイレクト・ロード・インサートされます

・ダイレクトロードインサートのメリット、デメリット メリット： 通常のINSERT文とは違い、バッファキャッシュを使用しないで、ダイレクトにデータファイルへの書き込みを行うため、INSERTにかかる時間が短縮される。
パラレル度を設定している場合には、書き込み処理が並列に動作し、さらに、INSERTにかかる時間が短縮される。
デメリット：
　（１）表に対する排他ロックが取られるため、INSERTが終了するまでは、他のトランザクションによるINSERT/UPDATE/DELETE文は同時に実行できない。
　（２）非パーティション表に対するダイレクトロードインサートでは、そのテーブルの既存の空き領域にINSERTするのではなく、そのテーブルのHigh Water Markより先に新しく領域を割り当ててINSERTする。
　　(新しく割り当てた領域はINSERT中はテンポラリセグメントと呼ばれ、INSERT終了後に既存のテーブルにマージされる)。そのため、通常のINSERT文より多くの領域を使うことがある。
　→新規に作成したばかりのテーブルにのみ使ったほうがよいですね。

思うに、ダイレクト・ロード・インサートは、新規にテーブルを作成したタイミング（つまりデータが０件で、どのブロックもまだ使われた事のない状態）で実行するのが、一番ふさわしいでしょうね。

使い始めのときはちょい心配しましたが、たとえばテーブルを作成するときにNOLOGGING属性を指定したとして、
・NOLOGGINGを指定したCREATE TABLEの処理自体はロギングされない（正確には最低限のログのみ書かれる）
・NOLOGGING属性を指定したテーブルに対して、通常のupdate/insertを行った場合、普通どおりロギングされる（REDOログは普通どおり発生する）
・ただし、NOLOGGING属性を指定して作成されたテーブルは、メディアリカバリを行うために必要なログ情報を（たとえCREATE以後のDMLに対してはロギングしていても、一番最初のCREATE処理については）記録していないので、データファイルを戻して復旧するようなケースの場合、エラーとなる
　→ただし、エラーになるのはこのテーブルのみ。他のテーブルは復旧できる
・NOLOGGING属性を指定したテーブルに、ダイレクト・ロード・インサートを実行した場合、そのインサート処理はロギングされない（正確には最低限のREDOログのみ発生する）
　→LOGGING属性だと、普通にREDOが発生する。利点はバッファをバイパスするってことだけとなる
・INDEXに対するNOLOGGING指定は、INDEXに対するDDL文において有効となる。ある表に対してダイレクト・ロードインサート等が実行され、その表に設定されているINDEXがNOLOGGING指定であっても、INDEX操作に関するREDOは生成されるそうな

なので、NOLOGGING属性を指定する場合は、読み取り専用テーブルとか、一日一回バッチでデータがイチから再作成されるようなテーブルとかに限定するのが妥当だと思います。