What's New Qiita

20190307のSQLに関する記事は6件です。

  • 投稿日：2019-03-07T21:35:49+09:00

pivot句のinにインライン埋め込めない（埋め込みたい）

ハードで数値リテラル列挙（したくない）

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
            )
        )
ORDER BY
    grp
;

in句にインライン（埋め込みたい）

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (
                SELECT
                    level
                FROM
                    dual
                CONNECT BY
                    level <=:divisor
                )
        )
ORDER BY
    grp
;

妥協して組み立てた。

--divisor:19
--dividend:78

with presni as(
select 'with sub as( select dense_rank() over (order by decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) - 1 as grp ,dense_rank() over (partition by decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)) order by rownum) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(min(rn) for seq in (' as pre_sni  from dual
),liz as(
select listagg(rn,',') within group (order by rownum) as liz from (select level as rn from dual connect by level <= :divisor)
),postsni as(
select ')) order by grp;' as post_sni from dual
)
select s1.pre_sni || s2.liz || s3.post_sni as build_sql from presni s1,liz s2,postsni s3;

あとがき

埋め込みたいなー。ただの遊びで気づいたので、記事upしました。。

妥協して組み立て直した。

こぴって実行したら、結果違って、ぇ！ってなったので、組み立てなおしました。。
しょーもなぃ。

--divisor:19
--dividend:500

WITH presni AS (
    SELECT
        'with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in ('
AS pre_sni
    FROM
        dual
), liz AS (
    SELECT
        LISTAGG(rn, ',') WITHIN GROUP(
            ORDER BY
                ROWNUM
        ) AS liz
    FROM
        (
            SELECT
                level AS rn
            FROM
                dual
            CONNECT BY
                level <=:divisor
        )
), postsni AS (
    SELECT
        ')) order by grp;' AS post_sni
    FROM
        dual
) SELECT
    s1.pre_sni
    || s2.liz
    || s3.post_sni AS build_sql
  FROM
    presni s1
    , liz s2
    , postsni s3;


with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)) order by grp;

image.png

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-07T19:28:50+09:00

SQLで重複除去の重複条件に複数カラムを使用する際

結構色々情報があって迷ってしまうんだけど、以下が一番楽コスパ良いと思う。

条件に使うカラムを文字結合して擬似的に1カラムと見なして、その擬似カラムにDISTINCTをかける。

SELECT DISTINCT column1 || ',' || column2, column3, column4
FROM table1;
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-07T17:47:59+09:00

今さら聞けないPlaceholder

問題

golangの標準パッケージ "database/sql"を使って、以下のようにPlaceholder付のクエリ書いたら、Syntax errorが発生してしまう、なぜか。

参考
- https://golang.org/pkg/database/sql/#example_DB_Query_multipleResultSets
- https://play.golang.org/p/D-5ZVSzhcXx

func main() {
    age := 27
    q := `
create temp table uid (id bigint); -- Create temp table for queries.
insert into uid
select id from users where age < ?; -- Populate temp table.

-- First result set.
select
    users.id, name
from
    users
    join uid on users.id = uid.id
;

-- Second result set.
select 
    ur.user, ur.role
from
    user_roles as ur
    join uid on uid.id = ur.user
;
    `
    rows, err := db.Query(q, age)
    if err != nil {
        log.Fatal(err) // Syntax error
    }

原因

PostgresDB使ってたため、?を$1に修正すればエラーは解消できる。

プレースホルダ付きのSQLをDBにあらかじめ送信しているため、DBごとに記法が異なる。

スクリーンショット 2019-03-07 17.45.34.png

引用: http://go-database-sql.org/prepared.html

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-07T12:57:27+09:00

AWRのお掃除

はじめに

とあるDBでAWR関連の問題あってAWRデータをクリーンアップしたときの備忘録的なもの、です。

このあたりを参考にしています。

How to Remove Workload Repository Information Related to an old DBID From the Automatic Workload Repository (Doc ID 1251795.1)
How to Recreate the Automatic Workload Repository (AWR)? (Doc ID 782974.1)

テストデータベースの作成

「AWR内の不要なDBIDをクリーンアップするテスト」をするために、まずはRMAN等でクローンデータベースを作るかNIDでDBIDを変更して、意図的に不要DBIDの登録状態を作りだします (^^)。

RMANで簡単にACTIVE DUPLICATEした例

参考：Active Database Duplicateで静的リスナーが不要になっていた件

RMANで複製
$ rman TARGET sys/mysystem@serverA/DB12C AUXILIARY sys/mysystem NOCATALOG

RMAN> STARTUP CLONE NOMOUNT

RMAN> DUPLICATE TARGET DATABASE TO TESTDB FROM ACTIVE DATABASE NOFILENAMECHECK;
...
...
database opened
Finished Duplicate Db at 2019/03/01
NIDでDBIDだけ変更した例

参考： Changing Only the Database ID

NIDでDBIDの変更
$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDAITE
SQL> STARTUP MOUNT

$ nid TARGET=SYS
Password:
Connected to database TESTDB (DBID=2785434983)
...
...
Database ID for database TESTDB changed to 2785526799.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus / as sysdba
SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN RESETLOGS;

DBIDとAWRの状態確認

まずは、DBA_HIST_WR_CONTROLで登録されているDBIDを確認します。AWRのDBID登録は自動なので、古いDBIDが残ったまま新しいDBIDが登録されています。

SQL> SELECT name, c.dbid, snap_interval, retention FROM dba_hist_wr_control c, v$database d WHERE c.dbid = d.dbid(+);

NAME            DBID SNAP_INTERVAL        RETENTION
--------- ---------- -------------------- --------------------
TESTDB    2785519794 +00000 01:00:00.0    +00008 00:00:00.0
          3899828635 +00000 01:00:00.0    +00008 00:00:00.0     <-- 古いDBID

現在のDBIDでスナップショットを作成してからAWRを覗いてみます。新しいDBIDと古いDBIDのエントリが混在しています。

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.

SQL> SELECT dbid, MIN(snap_id), MAX(snap_id), COUNT(*) FROM sys.WRH$_SYSMETRIC_HISTORY GROUP BY dbid;
      DBID MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
---------- ------------ ------------ ----------
3899828635         7643         7644        460     <-- 古いスナップショット
2785519794            1            1       1173

古いデータを削除しDBIDを登録解除

DBIDを登録解除すると、そのDBIDに関連したデータの削除ができなくなるのでDBID削除の前にデータを削除します。まぁ、手動で削除しなくても期限が来れば削除されるはずですが、問題が起きたときの対処用クリーンナップなのでしっかり削除しておきます。

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(7643, 7644, 3899828635);
PL/SQL procedure successfully completed.

SQL> SELECT dbid, MIN(snap_id), MAX(snap_id), COUNT(*) FROM sys.WRH$_SYSMETRIC_HISTORY GROUP BY dbid;
      DBID MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
---------- ------------ ------------ ----------
2785519794            1            1       1173

さらに、関連データが消去されてもDBID自体は登録されたまま残っているのでこれも削除します。

SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794
3899828635

-- AWRを制限モードに設定してデータベースを再起動
SQL> ALTER SYSTEM SET "_AWR_RESTRICT_MODE"=TRUE SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

-- AWR制限モードを確認
SQL> SHOW PARAMETER _AWR_RESTRICT_MODE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_awr_restrict_mode                   boolean     TRUE

-- 古いDBIDの登録解除
SQL> EXEC DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(3899828635);
PL/SQL procedure successfully completed.

-- AWRモードを解除設定してデータベースを再起動
SQL> ALTER SYSTEM RESET "_AWR_RESTRICT_MODE" SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

-- 登録DBIDの確認
SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794

_AWR_RESTRICT_MODEはAWRのスナップショットを作成しないようにする隠しパラメータです。AWRを利用するライセンスがないとかAWRそのものが不要な場合は、これを設定してデータベースのオーバーヘッドを低下させることができます。その他にもAWRやASH等を止める隠しパラメータがいくつかあるのですが、詳しくは以下を参考にしてください。

How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)

あと、間違って現行のDBIDを登録解除してもデータベース起動時に自動で再登録されるので問題ありません。

SQL> EXEC DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(2785519794);
PL/SQL procedure successfully completed.

SQL> select dbid from dba_hist_wr_control;
no rows selected

-- ここでデータベースを再起動

SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794

AWRの再構築

上記の方法で問題が解決しないときは、AWRそのものを一度削除して再構築します。

まずは、SPFILEのバックアップをとってパラメータを変更します。なんだかいろいろ設定するより単にAWRを無効化すればいいような気もするんですが、オラクルのインストラクションがそのようになっているので、従っておきます。

データベースを制限モードで再起動しますが、12.2の場合は以後実行するスクリプトがエラーになるので、アプリケーションのアクティビティに気をつけながらノーマルモードで起動しておきます。

-- SPFILEのバックアップ
$ cd $ORACLE_HOME/dbs
$ cp -p spfileTESTDB.ora spfileTESTDB.ora.bk
-- または
SQL> CREATE PFILE='pfile.ora' FROM SPFILE;

-- パラメータの変更。エラーは気にしない。
SQL> ALTER SYSTEM SET shared_pool_size = 200m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET db_cache_size = 300m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET java_pool_size = 100m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET large_pool_size = 50m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET cluster_database = FALSE SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET sga_target SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET memory_target SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET memory_max_target SCOPE = SPFILE;

-- 制限モードで再起動 (12.2はstartup)
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP RESTRICT

SQL> ALTER SYSTEM SET statistics_level=basic SCOPE=BOTH;

AWRオブジェクトを一旦削除し再生成します。

-- AWRオブジェクトの削除
SQL> @?/rdbms/admin/catnoawr.sql

-- もしAWR関連テーブルが残っていれば、追加で削除
SQL> SELECT table_name FROM dba_tables WHERE table_name LIKE 'WRM$_%' OR table_name LIKE 'WRH$_%';
TABLE_NAME
--------------------------------------------------------------------------------
WRH$_SYSMETRIC_HISTORY_BL

SQL> DROP TABLE WRH$_SYSMETRIC_HISTORY_BL;
Table dropped.

-- AWRオブジェクトの再生成とコンパイル
SQL> @?/rdbms/admin/catawrtb.sql
SQL> @?/rdbms/admin/utlrp.sql

-- AWR関連プログラムの再生成
SQL> @?/rdbms/admin/execsvrm.sql

あとはSPFILEをもとに戻し再起動して終了です。

-- SPFILEのリストア
$ mv spfileTESTDB.ora.bk spfileTESTDB.ora
-- または
SQL> CREATE SPFILE FROM PFILE='pfile.ora';

-- データベースの再起動
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

おわりに

ということで、クローンを作るときには自動で掃除しておくのがいいのかもしれません。

以上です。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-07T11:28:06+09:00

Access/SQL SELECT結果でINSERTする

Access/SQL SELECT結果でINSERTする

qiita.rb
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim strSQL As String

strSQL = "INSERT INTO テーブル1  (カラム1, カラム2, カラム3) "
strSQL = strSQL & "SELECT カラムA,カラムB,カラムC "
strSQL = strSQL & "FROM テーブル2 WHERE Not カラムA Is Null"

Debug.Print strSQL
db.Execute strSQL
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-03-07T11:06:27+09:00

AccessVBAでSQL文を実行する方法

qiita.rb
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim strSQL As String

'//SQL文記載
strSQL = "SELECT*****"
strSQL = strSQL & "FROM****"

Debug.Print strSQL

'//SQL文実行
db.Execute strSQL
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧