Hatena::ブログ(Diary)

ねら〜ITエンジニア雑記

2017-03-24

SQL の FETCH FIRST n ROWS構文で Top n や 同ソートキー値のレコードを抽出する。(Oracle Database 12c)


Oracle Database 12c で追加された FETCH FIRST n ROWS構文を使うと、
Top n や 同ソートキー値のレコードを抽出できるんやで彡(゚)(゚)

サンプルは以下のデータ

SELECT MIN(C1)            AS C1_MIN
     , MAX(C1)            AS C1_MAX
     , COUNT(C1)          AS C1_CNT
     , COUNT(DISTINCT C1) AS C1_NUM_DISTINCT
  FROM TBL_A;

    C1_MIN     C1_MAX     C1_CNT C1_NUM_DISTINCT
---------- ---------- ---------- ---------------
         1      10000      10001           10000

SELECT C1, C2
  FROM TBL_A
 WHERE C1 = 3;

        C1 C2
---------- ----------
         3 REC3
         3 RECCHOFUKU ★C1=3のレコードのみ重複

以下のSQLでTop n(例はTop 3)を抽出彡(゚)(゚)

-- Top 3 を抽出
SELECT C1, C2
  FROM TBL_A
 ORDER BY C1 FETCH FIRST 3 ROWS ONLY;

        C1 C2
---------- ----------
         1 REC1
         2 REC2
         3 REC3

OFFSET句を使って6個目〜10個目のレコードを抽出彡(゚)(゚)
※OFFSET句はORDER BYのキー値重複を考慮しないから、ランキングには使い辛いのか。。。彡(-)(-)

-- 6個目〜10個目のレコードを抽出
SELECT C1, C2
  FROM TBL_A
 ORDER BY C1 OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY;

        C1 C2
---------- ----------
         5 REC5
         6 REC6
         7 REC7
         8 REC8
         9 REC9

※◎の5レコードを飛ばして★の部分のレコードを抽出している。
        C1 C2
---------- ----------
         1 REC1   ◎
         2 REC2   ◎
         3 REC3   ◎
         3 RECCHOFUKU◎
         4 REC4   ◎
         5 REC5 ★
         6 REC6 ★
         7 REC7 ★
         8 REC8 ★
         9 REC9 ★
        10 REC10
        11 REC11

下記のように3位3個目のレコードのみ抽出も可能やで彡(゚)(゚)

-- 3個目のみ抽出
SELECT C1, C2
  FROM TBL_A
 ORDER BY C1 OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY;

        C1 C2
---------- ----------
         3 REC3


上記例だと同ソートキー値の他レコードは出ていないんやけど、
ONLY ⇒ WITH TIES に変更すると同ソートキー値のレコードも抽出されるで彡(゚)(゚)

-- Top3(同順位含む)
SELECT C1, C2
  FROM TBL_A
 ORDER BY C1 FETCH FIRST 3 ROWS WITH TIES;

        C1 C2
---------- ----------
         1 REC1
         2 REC2
         3 REC3
         3 RECCHOFUKU

-- 3個目のみ抽出(同キーの他レコード含む)
SELECT C1, C2
  FROM TBL_A
 ORDER BY C1 OFFSET 2 ROWS FETCH FIRST 1 ROWS WITH TIES;

        C1 C2
---------- ----------
         3 REC3
         3 RECCHOFUKU

マニュアルも見てクレメンス彡(^)(^)

Oracle Database SQL言語リファレンス 12cリリース1 (12.1) B71278-10SELECT
row_limiting_clause
http://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_10002.htm#BABHFGAA

> row_limiting_clauseを使用すると、問合せから返される行数を制限できます。
> オフセットと、返される行数または行数の割合を指定できます。
> この句は、上位N番のレポートを実装するために使用できます。
> 結果の一貫性を保つには、order_by_clauseを指定して、ソート順序を決定的にします。


Qiitaにも書いたやで彡(゚)(゚)

SQL の FETCH FIRST n ROWS構文で Top n や
同ソートキー値のレコードを抽出する。(Oracle Database 12c)
http://qiita.com/ora_gonsuke777/items/7ac5d4579b7c3c322dcc

2017-03-01

NEXT_DAY関数、TRUNC関数を使って、向こう1000日分のプレミアムフライデーを求めてみる。(Oracle Database)

これでワイもプレミアムや!彡(゚)(゚)

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
 SELECT TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM')      AS TUKI
      , MAX(TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'DD')) AS PREMIUM
   FROM DUAL
CONNECT BY LEVEL <= 1000
  GROUP BY TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM')
  ORDER BY TUKI;

TUKI       PREMIUM
---------- ----------
2017/03/01 2017/03/31
2017/04/01 2017/04/28
2017/05/01 2017/05/26
2017/06/01 2017/06/30
2017/07/01 2017/07/28
2017/08/01 2017/08/25
2017/09/01 2017/09/29
2017/10/01 2017/10/27
2017/11/01 2017/11/24
2017/12/01 2017/12/29
2018/01/01 2018/01/26

TUKI       PREMIUM
---------- ----------
2018/02/01 2018/02/23
2018/03/01 2018/03/30
2018/04/01 2018/04/27
2018/05/01 2018/05/25
2018/06/01 2018/06/29
2018/07/01 2018/07/27
2018/08/01 2018/08/31
2018/09/01 2018/09/28
2018/10/01 2018/10/26
2018/11/01 2018/11/30
2018/12/01 2018/12/28

TUKI       PREMIUM
---------- ----------
2019/01/01 2019/01/25
2019/02/01 2019/02/22
2019/03/01 2019/03/29
2019/04/01 2019/04/26
2019/05/01 2019/05/31
2019/06/01 2019/06/28
2019/07/01 2019/07/26
2019/08/01 2019/08/30
2019/09/01 2019/09/27
2019/10/01 2019/10/25
2019/11/01 2019/11/29

33 rows selected.

再帰WITH句の書き方も追記したやで彡(゚)(゚)

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
WITH rec(lv) AS (
  SELECT 1 FROM DUAL
   UNION ALL
  SELECT lv + 1
    FROM REC
   WHERE lv < 1000
)
SELECT TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM')      AS TUKI
     , MAX(TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'DD')) AS PREMIUM
  FROM rec
 GROUP BY TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM')
 ORDER BY TUKI;

TUKI       PREMIUM
---------- ----------
2017/03/01 2017/03/31
2017/04/01 2017/04/28
2017/05/01 2017/05/26
2017/06/01 2017/06/30
2017/07/01 2017/07/28
2017/08/01 2017/08/25
2017/09/01 2017/09/29
2017/10/01 2017/10/27
2017/11/01 2017/11/24
2017/12/01 2017/12/29
2018/01/01 2018/01/26

TUKI       PREMIUM
---------- ----------
2018/02/01 2018/02/23
2018/03/01 2018/03/30
2018/04/01 2018/04/27
2018/05/01 2018/05/25
2018/06/01 2018/06/29
2018/07/01 2018/07/27
2018/08/01 2018/08/31
2018/09/01 2018/09/28
2018/10/01 2018/10/26
2018/11/01 2018/11/30
2018/12/01 2018/12/28

TUKI       PREMIUM
---------- ----------
2019/01/01 2019/01/25
2019/02/01 2019/02/22
2019/03/01 2019/03/29
2019/04/01 2019/04/26
2019/05/01 2019/05/31
2019/06/01 2019/06/28
2019/07/01 2019/07/26
2019/08/01 2019/08/30
2019/09/01 2019/09/27
2019/10/01 2019/10/25
2019/11/01 2019/11/29

33 rows selected.

Qiitaにも書いたやで彡(^)(^)

NEXT_DAY関数、TRUNC関数を使って、向こう1000日分のプレミアムフライデーを求めてみる。
(Oracle Database)
http://qiita.com/ora_gonsuke777/items/a4e7c1d5e4c5c7a462c3

2017-02-20

Oracle DB のファンクション索引を作成すると、拡張統計(式統計)のエントリが自動で作成される。(※Oracle Database 11gR2 で検証)

超マニアックなネタ彡(゚)(゚)

Oracle Database のファンクション索引を作成して、作成したファンクション索引を使用する
SQLが実行されると、拡張統計(式統計)のエントリが作成されて COL_USAGE$ に格納されます。

まずは準備/確認から。この時点では拡張統計のエントリはありません。

-- COL_USAGEの確認
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
:
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
###############################################################################

ファンクション索引を作成しますが、この時点でも拡張統計のエントリはありません。

-- Function索引の作成
CREATE INDEX TBL_B_I1 ON TBL_B(TO_CHAR(P_DATE, 'YYYYMMDD'));

Index created.

-- COL_USAGEの確認 ※この時点では拡張統計のエントリは作成されていない。
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
:
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
###############################################################################

ファンクション索引を使用するSQLを実行してみます。

-- SQL実行
SET AUTOTRACE TRACEONLY;
SELECT /*+ MONITOR */
       A.*
  FROM TEST_TABLE_A A
     , TBL_B B
 WHERE A.P_NO2  = B.P_NO
   AND A.P_CHAR = B.P_CHAR
   AND TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801';
SET AUTOTRACE OFF;

1102 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4015380392

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    81 |  2187 |     7   (0)| 00:00:01 |
|*  1 |  HASH JOIN                   |              |    81 |  2187 |     7   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | TEST_TABLE_A |    26 |   416 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| TBL_B        |   300 |  3300 |     4   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TBL_B_I1     |   120 |       |     3   (0)| 00:00:01 | ★ファンクション索引
---------------------------------------------------------------------------------------------

ここでCOL_USAGEを確認すると……拡張統計のエントリがあるやね彡(^)(^)

-- COL_USAGEの確認
SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 1000;
SET PAGESIZE 0;
SELECT DBMS_STATS.REPORT_COL_USAGE('AYSHIBAT', 'TBL_B') FROM DUAL;

LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
:
###############################################################################

COLUMN USAGE REPORT FOR AYSHIBAT.TBL_B
......................................

1. P_CHAR                              : EQ_JOIN
2. P_NO                                : EQ_JOIN
3. SYS_NC00004$                        : EQ ★拡張統計のエントリ
###############################################################################

エントリの中身をDBA_STAT_EXTENSIONSで確認すると、ファンクション索引の拡張統計(式統計)なのが解るやで彡(゚)(゚)

-- 拡張統計のエントリの確認
SET PAGESIZE 100;
COLUMN EXTENSION FORMAT A30;
SELECT OWNER, TABLE_NAME, EXTENSION_NAME, EXTENSION FROM DBA_STAT_EXTENSIONS
 WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_B';

OWNER      TABLE_NAME   EXTENSION_NAME  EXTENSION
---------- ------------ --------------- ------------------------------
AYSHIBAT   TBL_B        SYS_NC00004$    (TO_CHAR("P_DATE",'YYYYMMDD'))

バージョンは11gR2(11.2.0.4.x)で検証したやで彡(゚)(゚)

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

11gR1 や 11gR2 の拡張統計エントリは、通常は拡張統計を明示的に作成したり、
SEED_COL_USAGE で明示的に収集しない限りは作成されんのですが、
この記事で紹介したような例外ケースも有るということで彡(゚)(゚)

12cR1の動きを知りたい方は、去年の DDD 2016資料を見てクレメンス彡(-)(-)

DD2-5
オラクルコンサルが語る! SQL性能を最大限に引き出す
DB 12cクエリー・オプティマイザ 新機能活用と統計情報運用の戦略
http://otndnld.oracle.co.jp/ondemand/ddd-2016/DD2-5.pdf


Qiitaにも書いたやで彡(^)(^)

Oracle DB のファンクション索引を作成すると、拡張統計(式統計)のエントリが自動で作成される。(※Oracle Database 11gR2 で検証)
http://qiita.com/ora_gonsuke777/items/11f95ea41372270c8864

2017-01-14

Oracle Database の DUMP関数 と CONVERT関数 で 文字列のバイトコードを調べる。

はてぶコメントで

【「野球」に対応するEUC文字コードは「cceeb5e5」なのが判らん。】

と頂いたので、書くやで彡(゚)(゚)

文字コードバイトコードは、Oracle Database だと DUMP関数 で確認できます。
AL32UTF8環境で「野球」のバイトコードを調べると↓(e9878ee79083)のようになります。

SET LINESIZE 170
SET PAGESIZE 100
COLUMN PARAMETER FORMAT A30
COLUMN VALUE FORMAT A30
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_CHARACTERSET               AL32UTF8                                0
NLS_NCHAR_CHARACTERSET         UTF8                                    0

-- '野球' の AL32UTF8 の バイトコード
SELECT DUMP('野球', 16) FROM DUAL;

DUMP('野球',16)
-------------------------------
Typ=96 Len=6: e9,87,8e,e7,90,83

文字コードの変換はCONVERT関数で出来るので、
CONVERT関数とDUMP関数を組み合わせると「野球」のバイトコードが確認できます。

-- '野球' の JA16EUC の バイトコード
SELECT DUMP(CONVERT('野球', 'JA16EUC' , 'AL32UTF8'), 16) FROM DUAL;

DUMP(CONVERT('野球','JA1
------------------------
Typ=1 Len=4: cc,ee,b5,e5

-- '野球' の JA16SJIS の バイトコード
SELECT DUMP(CONVERT('野球', 'JA16SJIS', 'AL32UTF8'), 16) FROM DUAL;

DUMP(CONVERT('野球','JA1
------------------------
Typ=1 Len=4: 96,ec,8b,85

野球」に対応するバイトコードは、JA16EUCだと「cceeb5e5」、
JA16SJISだと「96ec8b85」なのが判るやで!

野球元年や!彡(゚)(゚) (しつこい) マニュアルは↓やで。

Oracle Database SQL言語リファレンス 12cリリース1 (12.1) B71278-10
DUMP
http://docs.oracle.com/cd/E57425_01/121/SQLRF/functions063.htm#i77278
CONVERT http://docs.oracle.com/cd/E57425_01/121/SQLRF/functions041.htm#i77037

Qiitaにも書いたやで!彡(^)(^)
Oracle Database の DUMP関数 と CONVERT関数文字列バイトコードを調べる。
http://qiita.com/ora_gonsuke777/items/c410f75f0a7a3dfd1872

2017-01-12

Oracle Database に 新しい元号(年号)「野球」を追加してみる。(NLSカレンダ・ユーティリティlxegen)

f:id:gonsuke777:20170112143918j:image
時代は野球や!彡(゚)(゚) Oracle Database の NLSカレンダ・ユーティリティで
新しい元号(年号)「野球」を追加してみるやで。マニュアルは下記の通り。

Oracle Databaseグローバリゼーション・サポート・ガイド
12cリリース1 (12.1) B71319-05
12 ロケール・データのカスタマイズ
NLSカレンダ・ユーティリティを使用したカレンダのカスタマイズ
http://docs.oracle.com/cd/E57425_01/121/NLSPG/ch13custlocale.htm#BABIBFBA
 > Oracle Databaseは、複数のカレンダをサポートしています。
 > 場合によっては、その中に、今後の元号の追加が必要なものや、
 > 地域的な要件に合せて、うるう年の日数の増減が必要なものがあります。

まずはサンプルの定義ファイルを見てみるやで。マニュアルは下記の通り。
http://docs.oracle.com/cd/E57425_01/121/NLSPG/ch13custlocale.htm#BABIBFBA
テキスト定義ファイルの名前とlxegenユーティリティに対する位置は、プラットフォーム依存の値でハードコード化されています。
UNIXプラットフォームの場合、ファイル名はlxecal.nltで、$ORACLE_HOME/nlsディレクトリにあります。
テキスト定義ファイルのサンプルは、$ORACLE_HOME/nls/demoディレクトリに含まれています。
デモ・ファイルのインストール方法の詳細は、『Oracle Database Examplesインストレーション・ガイド』』を参照してください。

Oracle Database Examples Linux x86-64版 の ダウンロード は 下記から。
※下記のリンクは 2017/1/12時点のものです。
Oracle Database 12c Release 1 Examples (12.1.0.2.0) for Linux x86-64
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html?ssSourceSiteId=otnjp

Oracle Database Examplesをダウンロードしてインストールすると、
$ORACLE_HOME/nls/demo配下にサンプルの定義ファイルが展開されます。下記は サンプルlxecal.nlt の抜粋。

-- ***************************************************************************
-- Here is an example on defining Emperors for a Imperial Calendar
-- ***************************************************************************
--
--      DEFINE calendar
--
--         calendar_name              = "Japanese Imperial"
--
--         DEFINE calendar_era
--            era_full_name           = bebccfc2
--            era_abbr_name           = 53
--            start_date              = "DEC-25-1926 AD"
--            end_date                = "jAN-07-1989 AD"
--         ENDDEFINE calendar_era
--
--         DEFINE calendar_era
--            era_full_name           = cabfc0ae
--            era_abbr_name           = 48
--            start_date              = "JAN-08-1989 AD"
--            end_date                = "DEC-31-2099 AD"
--         ENDDEFINE calendar_era
--
--      ENDDEFINE calendar
--

「bebccfc2」はEUC文字コードで「昭和」、「cabfc0ae」は「平成」に対応します。

SQL> select CONVERT(CHR(TO_NUMBER('bebccfc2', 'xxxxxxxx')), 'AL32UTF8', 'JA16EUC') from dual;

CONVERT(CHR(TO_NUMBE
--------------------
昭和

SQL> select CONVERT(CHR(TO_NUMBER('cabfc0ae', 'xxxxxxxx')), 'AL32UTF8', 'JA16EUC') from dual;

CONVERT(CHR(TO_NUMBE
--------------------
平成

野球」に対応するEUC文字コードは「cceeb5e5」なので、
こいつを lxecal.nlt に記述して、$ORACLE_HOME/nls配下に配置します。
※「野球」のバイトコードの調べ方を別記事に興しました。
http://d.hatena.ne.jp/gonsuke777/20170114/1484347347

$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/nls
$ ls -la lxecal.nlt
-rw-r--r-- 1 oracle oinstall 301 Jan 12 11:50 lxecal.nlt
$ cat lxecal.nlt
DEFINE calendar
    calendar_name         = "Japanese Imperial"
    DEFINE calendar_era
        era_full_name     = "cceeb5e5"
        era_abbr_name     = "59"
        start_date        = "JAN-01-2019 AD"
        end_date          = "DEC-31-2200 AD"
    ENDDEFINE calendar_era
ENDDEFINE calendar

「lxegen」コマンドを起動すると、「lxecal.nlt」がコンパイルされて
「lxecalji.nlb」と云うバイナリファイルが作成されます。

$ lxegen

NLS Calendar Utility: Version 12.1.0.2.0 - Production

Copyright (c) Oracle 1994, 2014.  All rights reserved.

CORE    12.1.0.2.0      Beta

$ ls -la lxecal*
-rw-r--r-- 1 oracle oinstall 301 Jan 12 11:50 lxecal.nlt
-rw-r--r-- 1 oracle oinstall 144 Jan 12 14:20 lxecalji.nlb ←コイツ
$

いよいよやで…DBをシャットダウン/再起動して、2019年1月1日を取得してみると……

SQL> SHUTDOWN IMMEDIATE;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL> STARTUP
ORACLEインスタンスが起動しました。

Total System Global Area 2399141888 bytes
Fixed Size                  2927192 bytes
Variable Size             654312872 bytes
Database Buffers         1728053248 bytes
Redo Buffers               13848576 bytes
データベースがマウントされました。
データベースがオープンされました。
SQL> ALTER SESSION SET NLS_CALENDAR="Japanese Imperial";

セッションが変更されました。

SQL> SELECT SYSDATE+719 FROM DUAL;

SYSDATE+719
---------------------------------------------
野球010101日

SQL>

野球元年や!彡(゚)(゚) Qiita にも書いたやで彡(^)(^)

Oracle Database に 新しい元号(年号)「野球」を追加してみる。(NLSカレンダ・ユーティリティlxegen)
http://qiita.com/ora_gonsuke777/items/dc21ee3f2abf718098b9