OracleDBAの技術メモ

最近物覚えが悪くなったので仕事中に調べた後々役立つ情報をメモしております。

[PR]

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

枝番の最大値を取るようなSQL

テーブル定義：T_TEST

名前 NULL? 型
------ -------- -----------
ID NOT NULL NUMBER(10)
SEQNO NOT NULL NUMBER(10)

以下のようなSQLをよく作成する。

SELECT
    tst.id
,   tst.seqno
FROM t_test tst
WHERE tst.seqno = (
--
SELECT
    MAX(tstx.seqno)
FROM t_test tstx
WHERE tstx.id = tst.id
--
);

これは、IDに対して枝番の最大値を取得するSQLだが、このSQLは以下のように変換できる。

SELECT
    tst.id
,   tst.seqno
FROM t_test tst
    INNER JOIN t_test tstx
        ON  tstx.id = tst.id
GROUP BY
    tst.id
,   tst.seqno
HAVING tst.seqno = max(tstx.seqno)
;

Oracle Databaseの実行計画なんかをみると上記のように内部で変換されていることがある。

サブクエリーとして親クエリーに埋め込む際は
Where句にそれぞれのIDに対する結合条件を記述してやるとよいと思われる。

～
(
SELECT
    tst.id
,   tst.seqno
FROM t_test tst
    INNER JOIN t_test tstx
        ON  tstx.id = tst.id
WHERE tst.id = [親クエリーのテーブル].id
  AND tstx.id = [親クエリーのテーブル].id
GROUP BY
    tst.id
,   tst.seqno
HAVING tst.seqno = max(tstx.seqno)
)
～

【日記】心得的なもの

Mさんにプレゼンすると毎回へこむわ～；；

パフォーマンス・レポートに関して心得的なもの。

　使う人の立場で状況をまとめること。

「Parse CPU to Parse Elapsed%:」が急落したよ！
って報告したら。
それって実害あるの？的に切り返されちゃった；；

確かにそこまで調べなかったし、パース時間なんてそんなに実際のSQL実行時間に影響しないよね；；

ワークシート関数でフルパス＋ファイル名からファイル名だけ取り出す

ワークシート関数のみでフルパス＋ファイル名からファイル名だけを取り出す方法。

以下の式を入力する。

=MID(A1, FIND("/", SUBSTITUTE(A1, "\", "/", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))) + 1, LEN(A1) - FIND("/", SUBSTITUTE(A1, "\", "/", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))))

仕組み

簡単に説明すると、文字列中の最後の ”\”（エンマーク）を ”/”（バックスラッシュ）に変換し、
その位置をFINDしてそれより後の文字列を取得する。

SUBSTITUTE関数は最後の引数として［置換対象］を指定できることを利用して、
最後の ”\”（エンマーク）を ”/”（バックスラッシュ）に変換する。

最後の ”\”（エンマーク）は ”\”（エンマーク）の数を数えればよい。
実際には　全文字列長－文字列長（文字列中の ”\”（エンマーク）を ””（長さ0の文字列）に変換）で算出する。

後はFINDで ”/”（バックスラッシュ）の位置を取得すればよい。

Databaseのキャラクタセット（文字コード）の確認方法

構築済みDatabaseのキャラクタセットを確認したい場合は、
NLS_DATABASE_PARAMETER を問い合わせる。
（あまり使わないからすぐに忘れる；）

SELECT * FROM NLS_DATABASE_PARAMETER
WHERE PARAMETER = 'NLS_CHARACTERSET';

サーバ上でOracleエラー番号の内容を調べる方法

全部英語でしか出力されないようですが…
Oracle Databaseがインストールされているサーバ上で「ORA-XXXXX」の簡単な説明を確認することができる。

以下、コマンド実行例（例：ORA-01555）

$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments
$

1つ目の引数はエラーメッセージの接頭辞で、”ORA”以外にも対応している様子。
（例：SP2-0157　SQL*Plusのエラー？）

$ oerr sp2 157
00157,0, "unable to CONNECT to ORACLE after %d attempts, exiting SQL*Plus\n"
// *Cause:  Unable to connect to Oracle after three attempts.
// *Action: Validate login details and re-try.