Mac De Oracle: PostgreSQL

2023年2月
日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28

良いお年を

投稿日時 2021年12月25日 (土) 00時00分 Oracle, お知らせ, PostgreSQL | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　おまけ SQL de 湯婆婆やるにも癖がでるw

恒例の標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020のおまけですw

忙しすぎて、この手の遊びが疎かになっておりました。完全に乗り遅れておりましたが、湯婆婆やりましたw

スタジオジブリさん、「画像は常識の範囲でご自由にお使いください。」とのことでありがたく使わせていただきます！
https://www.ghibli.jp/works/chihiro/#frame

では、Oracle (19c)を使って、Oracle 湯婆婆　から
Advent Calendar 標準はあるにはあるが癖の多いSQL 全部俺でも登場したSUBSTR/DBMS_RANDOM.VALUEや文字列連結の違い。
それに、SQLスクリプトでインタラクティブにパラメータを渡せるかという点にも違いがあります。

SQL*Plus/psqlではインタラクティブにパラメータを渡せますが、mysqlにはなさそう（あったらコメントください）

インタラクティブにパラメータを渡せるSQL*Plus/psqlでは、それぞれ、ACCEPTや\promptなどで名前を入力しています。
mysqlでは仕方ないのでSETコマンドで設定する方法をとりました。

ORACLE> @ora_yubaba
契約書だよ。そこに名前を書きな。山田千尋

湯婆婆
--------------------------------------------------------------------------------
フン 山田千尋 というのかい。贅沢な名だねぇ
今からお前の名前は 田 だ。いいかい、田 だよ。
わかったら返事をするんだ、
!!

$ cat ora_yubaba.sql
SET LINESIZE 80
SET TAB OFF
SET VERIFY OFF
ACCEPT fullname CHAR PROMPT '契約書だよ。そこに名前を書きな。'
WITH yourname
AS
(
  SELECT
    SUBSTR(
       '&&fullname'
       , DBMS_RANDOM.VALUE(1,LENGTH('&&fullname')), 1
    ) AS newname
  FROM
    dual
)
SELECT
  'フン '
  ||'&&fullname'
  ||' というのかい。贅沢な名だねぇ'
  ||CHR(13)||CHR(10)
  ||'今からお前の名前は '
  ||newname
  ||' だ。いいかい、'
  ||newname
  ||' だよ。'
  ||CHR(13)||CHR(10)||'わかったら返事をするんだ、'
  ||CHR(13)||CHR(10)||'!!' AS "湯婆婆"
FROM
  yourname;

undefine fullname
SET VERIFY ON

PostgreSQL (12)

次は、PostgreSQL 湯婆婆

postgres=> \i postgresql_yubaba.sql
契約書だよ。そこに名前を書きな : 山田千尋
                    湯婆婆
----------------------------------------------
 フン　山田千尋 というのかい。贅沢な名だねぇ +
 今からお前の名は 田　だ。いいかい、田 だよ。+
 わかったら返事をするんだ、                  +
 !!
(1 row)

$ cat postgresql_yubaba.sql
\prompt '契約書だよ。そこに名前を書きな : ' fullname

WITH yourname
AS
(
  SELECT
    SUBSTR(
      :'fullname'::TEXT
      , CEIL(RANDOM() * LENGTH(:'fullname'::TEXT))::INTEGER, 1::INTEGER
    ) AS newname
)
SELECT
  'フン　'
  ||:'fullname'
  ||' というのかい。贅沢な名だねぇ'
  ||E'\n'
  ||'今からお前の名は '
  ||newname
  ||'　だ。いいかい、'
  ||newname
  ||' だよ。'
  ||E'\n'||'わかったら返事をするんだ、'
  ||E'\n'||'!!' AS "湯婆婆"
FROM
  yourname;

MySQL 8.0

最後に、MySQL 湯婆婆

mysql> SET @契約書だよ。そこに名前を書きな = '山田千尋';
Query OK, 0 rows affected (0.13 sec)

mysql> \! vi mysql_yubaba.sql
mysql> \. mysql_yubaba.sql
Query OK, 0 rows affected (0.02 sec)

*************************** 1. row ***************************
湯婆婆: フン　山田千尋 というのかい。贅沢な名だねぇ
今からお前の名は 尋　だ。いいかい、尋 だよ。
わかったら返事をするんだ、
!!
1 row in set (0.02 sec)

$ cat mysql_yubaba.sql
SET sql_mode = 'ANSI';
WITH yourname
AS
(
  SELECT
    SUBSTRING(
      @契約書だよ。そこに名前を書きな
      , CEIL(RAND() * CHAR_LENGTH(@契約書だよ。そこに名前を書きな)), 1
    ) AS newname
)
SELECT
  'フン　'
  ||@契約書だよ。そこに名前を書きな
  ||' というのかい。贅沢な名だねぇ'
  ||'\r\n'
  ||'今からお前の名は '
  ||newname
  ||'　だ。いいかい、'
  ||newname
  ||' だよ。'
  ||'\r\n'||'わかったら返事をするんだ、'
  ||'\r\n'||'!!' AS "湯婆婆"
FROM
  yourname\G

こういう遊びはみなさん好きですよね？　
では、また。

似たようなネタのエントリー
・Oracle de Fizzbuzz #1 -　いまごろ・・・ですが・・
・Oracle de Fizzbuzz #2
・Mac de Caché というか MUMPS というか Objectscript か - fizzbuzz
・PL/SQL de ケンブリッジ関数
・こんなのでいいのかなぁ。ズンドコキヨシ　　ObjectScript / MUMPS

投稿日時 2020年12月26日 (土) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#25 SQL de Fractalsにも癖がある:)

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の25日目です。

５年前にクリスマスのお遊び - SQL de Fractals :)というネタを書いてました。

EXPLAIN EXTENDED - Happy New Year!
元ネタは、ARRAY_TO_STRINGとARRAY_AGGの組み合わせ、とgenerate_seriesを利用した再帰問合せを利用したPostgreSQLバージョンのSQL

今であれば、以下のように書き換え、STRING_AGGでけにした方が良いのではないだろうか。
また、generate_series部分の方言の影響を最小にするのであれば、この部分も再帰問合せを利用した一連番号のセット生成にするなどの変更は可能ですね。

では、オリジナルのPostgreSQLの構文でARRAY_TO_STRINGとARRAY_AGGをSTRING_AGGに変更した例から（generate_seriesを階層再帰問合せにすることも可能）

PostgreSQL

WITH RECURSIVE
  q (r, i, rx, ix, g) AS
  (
    SELECT
      r::DOUBLE PRECISION * 0.02
      , i::DOUBLE PRECISION * 0.02
      , .0::DOUBLE PRECISION
      , .0::DOUBLE PRECISION
      , 0
    FROM
      generate_series(-60, 20) r
      , generate_series(-50, 50) i
    UNION ALL
    SELECT
      r
      , i
      , CASE
        WHEN ABS(rx * rx + ix * ix) <= 2
          THEN rx * rx - ix * ix
        END + r
      , CASE
        WHEN ABS(rx * rx + ix * ix) <= 2
          THEN 2 * rx * ix
        END + i
      , g + 1
    FROM
      q
    WHERE
      rx IS NOT NULL AND g < 99
  )
SELECT
  STRING_AGG(s, '' ORDER BY r) AS  Mandelbrot
FROM
(
  SELECT
    i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
  FROM
    q
  GROUP BY i, r
) q
GROUP BY i
ORDER BY i;

Oracle

では、上記、非互換の多いSQLをOracle向けに書き換えてみましょう。一連番号生成はOracleの方言である階層問合せにしてあります。あえてw
また、PostgreSQLのSTRING_AGG部分は、OracleのLISTAGGで代替しています。

WITH
	q (r, i, rx, ix, g)  AS
	(
		SELECT
			CAST(r.r AS DOUBLE PRECISION) * 0.02 AS r
			, CAST(i.i AS DOUBLE PRECISION) * 0.02 AS i
			, CAST(.0 AS DOUBLE PRECISION) AS rx
			, CAST(.0 AS DOUBLE PRECISION) AS ix
			, 0 AS g
		FROM
		(
			SELECT
				LEVEL - 61 AS r
			FROM
				DUAL
			CONNECT BY
				LEVEL <= 80
		) r,
		(
			SELECT
				LEVEL - 51 AS i
			FROM
				DUAL
			CONNECT BY
				LEVEL <= 100
		) i
		UNION ALL
		SELECT
			r
			, i
			, CASE
				WHEN ABS(rx * rx + ix * ix) <= 2
				THEN
					rx * rx - ix * ix
			END + r AS rx
			, CASE
				WHEN ABS(rx * rx + ix * ix) <= 2
				THEN
					2 * rx * ix
			END + i AS ix
			, g + 1 AS g
		FROM
			q
		WHERE
			rx IS NOT NULL
			AND g < 99
	)
SELECT
	LISTAGG(s,'') WITHIN GROUP ( ORDER BY r ) AS  Mandelbrot
FROM
	(
		SELECT
      i, r, SUBSTR(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
		FROM
			q
		GROUP BY i, r
	) q
GROUP BY i
ORDER BY i;

MySQL 8.0

さて、最後は、これまで一連番号生成が辛かったMySQLです。
MySQL 8.0から再帰問合せが利用できるようになったおかげてMySQLのSQLでもこんな遊びができるようになりました！！！！

すげーーーーーーーっ！

再帰問合せを駆使し、PostgreSQLのSTRING_AGG、OracleのLISTAGGの代替としてGROUP_CONCAT関数を利用しています。
部分文字列はSUBSTRINGですね。
そしてもう一つの非互換対応が　TRUNCATE(MAX(g) / 10 + 1, 0)　部分です。
OracleとPostgreSQLは　MAX(g) / 10 + 1　だけでも問題ないですが、MySQLでは MAX(g) / 10 + 1　の結果は整数にはなりません。
その対策としてTRUNCATEを追加しています。

なかなか痺れますね。これまで紹介してきた非互換対応の総まとめは大げさですが、それらを有効に組み合わせて書き換えてみました。

WITH RECURSIVE
  q (r, i, rx, ix, g)
  AS
  (
    SELECT
      CAST(r.v AS DOUBLE PRECISION)  * 0.02 AS r
      , CAST(i.v AS DOUBLE PRECISION) * 0.02 AS i
      , CAST(0.0 AS DOUBLE PRECISION) AS rx
      , CAST(0.0 AS DOUBLE PRECISION) AS ix
      , 0 AS g
    FROM
      (
        WITH RECURSIVE gen_nums(v)
        AS
        (
          SELECT -60
          UNION ALL
          SELECT v + 1
          FROM
            gen_nums
          WHERE v + 1 < 20
        )
        SELECT v from gen_nums
      ) r
      ,(
        WITH RECURSIVE gen_nums(v)
        AS
        (
          SELECT -50
          UNION ALL
          SELECT v + 1
          FROM
            gen_nums
          WHERE v + 1 < 50
        )
        SELECT v from gen_nums
      ) i
    UNION ALL
    SELECT
      CAST(r AS DOUBLE PRECISION) AS r
      , CAST(i AS DOUBLE PRECISION) AS i
      , CAST(
          CASE
            WHEN ABS(rx * rx + ix * ix) <= 2
              THEN rx * rx - ix * ix
            END + r
          AS DOUBLE PRECISION
        ) AS rx
      , CAST(
          CASE
            WHEN ABS(rx * rx + ix * ix) <= 2
              THEN 2 * rx * ix
            END + i
          AS DOUBLE PRECISION
        ) AS ix
      , g + 1 AS g
    FROM
      q
    WHERE
      rx IS NOT NULL
      AND g < 99
  )
SELECT
  GROUP_CONCAT(s,'' ORDER BY r SEPARATOR '') AS Mandelbrot
FROM
  (
    SELECT
      i, r, SUBSTRING(' .:-=+*#%@', TRUNCATE(MAX(g) / 10 + 1, 0), 1) s
    FROM
      q
    GROUP BY
      i, r
  ) q
GROUP BY q.i
ORDER BY q.i;

ちなみに、Redshiftは再帰的なCTEは現時点では未サポートなので再帰問合せが必要なお遊びは今のところできません。

Amazon Redshift - サポートされていないPostgreSQL機能
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-features.html

さてさて、なんとか25個の窓を開けることができました。

今年は、コロナ禍の中、大変厳しい一年になりましたが、みなさま、お身体を大事に、そして、ご家族と過ごす時間を大切に。

メリークリスマス。（何年か前に作った Pipeline function で christmas treeのムービーで）

投稿日時 2020年12月25日 (金) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#24 乱数作るにも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の24日目です。

たまに、乱数が必要になることがあるのですが、その乱数生成にも癖があるんですよね。

簡単ですが今日はこれぐらいでw　（ラストスパートで息切れ中w

いつものようにOracleから

131.4 DBMS_RANDOMサブプログラムの要約
131.4.7 VALUEファンクション
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_RANDOM.html#GUID-AAD9E936-D74F-440D-9E16-24F3F0DE8D31

Oracleの場合は組み込み関数ではなく、パッケージ関数として提供されています。この点が大きな違いですよね。

ORACLE> SELECT (DBMS_RANDOM.VALUE(1,10)) FROM dual;

(DBMS_RANDOM.VALUE(1,10))
-------------------------
	       3.37937063

ORACLE>  SELECT DBMS_RANDOM.VALUE FROM dual;

     VALUE
----------
.853380477

PostgreSQL

表9.6 乱数関数
https://www.postgresql.jp/document/12/html/functions-math.html

postgres=> select random();
      random
-------------------
 0.774311667308211
(1 row)

MySQL

三者三様で皆関数名も違ったり、提供されている機能も差がありますね。

RAND([N])
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6516789492700984 |
+--------------------+
1 row in set (0.06 sec)

クリスマス、何食べようか考えているところで、年越しそば予約してないことに気づくw　（なんとかなるさw

投稿日時 2020年12月24日 (木) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#23 複数行INSERTにも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の23日目です。

クリスマスイブイブですね。なんとかここまできたw

ネタ尽きた感がなくもないですが、ほぼ使っている方はいないのではないかという、複数行INSERTにも癖があるというお話

では、Oracleからみてください

Oracle

INESRT
https://docs.oracle.com/cd/F19136_01/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423

この構文実は、マルチテーブルインサートの変形パターンで同一表へ複数インサートするようにしたもの。。。
そもそもこの構文が方言ではあるのですが、。。。。

ORACLE> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 STR                                                VARCHAR2(100)

ORACLE> INSERT ALL
  2  INTO a VALUES(2,'yama')
  3  INTO a VALUES(3,'kawa')
  4  INTO a VALUES(4,'umi')
  5  SELECT * FROM dual;

PostgreSQL

PostgreSQLにはやはりない。そりゃ、Oracleの方言だからねw　ですが、一応同一表なら可能です。

INSERT
https://www.postgresql.jp/document/12/html/sql-insert.html

同じく、MySQLも同一構文。。。。

postgres=> \d a
                          Table "bill.a"
 Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
 id     | integer                |           |          |
 str    | character varying(100) |           |          |

postgres=>
postgres=> INSERT
postgres-> INTO a VALUES
postgres-> (2,'yama')
postgres-> ,(3,'kawa')
postgres-> ,(4,'umi');
INSERT 0 3

MySQL(8.0)

INSERT
https://dev.mysql.com/doc/refman/8.0/en/insert.html

mysql> desc a;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| str   | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> INSERT
    -> INTO a VALUES
    -> (2,'yama')
    -> ,(3,'kawa')
    -> ,(4,'umi');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

クリスマスイブのネタ何にしよう。本気で浮かばないw　（最終日のネタはほぼできているのにw）

投稿日時 2020年12月23日 (水) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#22 集合演算にも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の22日目です。

さて、残すところ、今日をいれてあと4回

なんとなくネタが１つ足りてない気がするがw　なんとかするw

ということで、集合演算の癖を見てみましょう。Oracle 20cがリリースされていたら試せた機能もありますが、使える環境がないので、マニュアルだけ貼っておきます。

Oracle 20c - SQL set演算子の拡張 - union [all] / minus [all] / intersect [all] / except[all]
https://docs.oracle.com/cd/F32587_01/ftnew/enhanced-sql-set-operators1.html

19cまではこんな感じ

Oracle 19c - UNION [ALL]、INTERSECTおよびMINUS演算子
https://docs.oracle.com/cd/F19136_01/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE

MINUSはOracleの方言です。そのうちシノニム扱いされて、EXCEPTが一般的に利用されることになっていくのでしょうね。

Oracle

ORACLE> r
  1  SELECT *
  2  FROM
  3  (
  4    WITH gen_nums(v)
  5    AS
  6    (
  7	     SELECT 1
  8	     FROM
  9	       dual
 10	     UNION ALL
 11	     SELECT v + 1
 12	     FROM
 13	       gen_nums
 14	     WHERE v + 1 <= 10
 15    )
 16    SELECT v FROM gen_nums
 17  )
 18  UNION
 19  SELECT *
 20  FROM
 21  (
 22    WITH gen_nums(v)
 23    AS
 24    (
 25	     SELECT 5
 26	     FROM
 27	       dual
 28	     UNION ALL
 29	     SELECT v + 1
 30	     FROM
 31	       gen_nums
 32	     WHERE v + 1 <= 15
 33    )
 34    SELECT v FROM gen_nums
 35  )
 36* ORDER BY 1

	 V
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
	12
	13
	14
	15

15 rows selected.

ORACLE> r
  1  SELECT *
  2  FROM
  3  (
  4    WITH gen_nums(v)
  5    AS
  6    (
  7	     SELECT 1
  8	     FROM
  9	       dual
 10	     UNION ALL
 11	     SELECT v + 1
 12	     FROM
 13	       gen_nums
 14	     WHERE v + 1 <= 10
 15    )
 16    SELECT v FROM gen_nums
 17  )
 18  UNION ALL
 19  SELECT *
 20  FROM
 21  (
 22    WITH gen_nums(v)
 23    AS
 24    (
 25	     SELECT 5
 26	     FROM
 27	       dual
 28	     UNION ALL
 29	     ELECT v + 1
 30	     FROM
 31	       gen_nums
 32	     WHERE v + 1 <= 15
 33    )
 34    SELECT v FROM gen_nums
 35  )
 36* ORDER BY 1

	 V
----------
	 1
	 2
	 3
	 4
	 5
	 5
	 6
	 6
	 7
	 7
	 8
	 8
	 9
	 9
	10
	10
	11
	12
	13
	14
	15

21 rows selected.

ORACLE> r
  1  SELECT *
  2  FROM
  3  (
  4    WITH gen_nums(v)
  5    AS
  6    (
  7	     SELECT 1
  8	     FROM
  9	       dual
 10	     UNION ALL
 11	     SELECT v + 1
 12	     FROM
 13	       gen_nums
 14	     WHERE v + 1 <= 10
 15    )
 16    SELECT v FROM gen_nums
 17  )
 18  INTERSECT
 19  SELECT *
 20  FROM
 21  (
 22    WITH gen_nums(v)
 23    AS
 24    (
 25	     SELECT 5
 26	     FROM
 27	       dual
 28	     UNION ALL
 29	     SELECT v + 1
 30	     FROM
 31	       gen_nums
 32	     WHERE v + 1 <= 15
 33    )
 34    SELECT v FROM gen_nums
 35  )
 36* ORDER BY 1

	 V
----------
	 5
	 6
	 7
	 8
	 9
	10

6 rows selected.

ORACLE> r
  1  SELECT *
  2  FROM
  3  (
  4    WITH gen_nums(v)
  5    AS
  6    (
  7	     SELECT 1
  8	     FROM
  9	       dual
 10	     UNION ALL
 11	     SELECT v + 1
 12	     FROM
 13	       gen_nums
 14	     WHERE v + 1 <= 10
 15    )
 16    SELECT v FROM gen_nums
 17  )
 18  MINUS
 19  SELECT *
 20  FROM
 21  (
 22    WITH gen_nums(v)
 23    AS
 24    (
 25	     SELECT 5
 26	     FROM
 27	       dual
 28	     UNION ALL
 29	     SELECT v + 1
 30	     FROM
 31	       gen_nums
 32	     WHERE v + 1 <= 15
 33    )
 34    SELECT v FROM gen_nums
 35  )
 36* ORDER BY 1

	 V
----------
	 1
	 2
	 3
	 4

PostgreSQL

union [all] / intersect[all] / except[all]
https://www.postgresql.jp/document/12/html/queries-union.html

これらの構文に関しては、PostgreSQLの方が一歩先でしたね：）

Oracle 20c以降ではこんなことができるようになるよ、というイメージをPostgreSQLで掴んでおくと良いかもしれないですねー

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> UNION
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
(15 rows)

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> UNION ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
----
  1
  2
  3
  4
  5
  5
  6
  6
  7
  7
  8
  8
  9
  9
 10
 10
 11
 12
 13
 14
 15
(21 rows)

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> INTERSECT
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
----
  5
  6
  7
  8
  9
 10
(6 rows)

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> INTERSECT ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
----
  5
  6
  7
  8
  9
 10
(6 rows)

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> EXCEPT
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
---
 1
 2
 3
 4
(4 rows)

postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 1
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 10
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) a
postgres-> EXCEPT ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(>   WITH RECURSIVE gen_nums(v)
postgres(>   AS
postgres(>   (
postgres(>     SELECT 5
postgres(>     UNION ALL
postgres(>     SELECT v + 1
postgres(>     FROM
postgres(>       gen_nums
postgres(>     WHERE v + 1 <= 15
postgres(>   )
postgres(>   SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
 v
---
 1
 2
 3
 4
(4 rows)

MySQL (8.0)

union [all]

13.2.10.3 UNION Clause
https://dev.mysql.com/doc/refman/8.0/en/union.html

MySQLはこれらの部分では少々遅れてますね。実際同じ結果を得ようとすると工夫しないといけないです。
これまで実装されてこなかったということは、恐そのような用途で利用されることが少なかったということなのでしょうか。（想像でしかないけど。。なんとなく今後は実装されそうな方向に向かっているような雰囲気も感じつつ。）

mysql> SELECT *
    -> FROM
    -> (
    ->   WITH RECURSIVE gen_nums(v)
    ->   AS
    ->   (
    ->     SELECT 1
    ->     UNION ALL
    ->     SELECT v + 1
    ->     FROM
    ->       gen_nums
    ->     WHERE v + 1 <= 10
    ->   )
    ->   SELECT v FROM gen_nums
    -> ) a
    -> UNION
    -> SELECT *
    -> FROM
    -> (
    ->   WITH RECURSIVE gen_nums(v)
    ->   AS
    ->   (
    ->     SELECT 5
    ->     UNION ALL
    ->     SELECT v + 1
    ->     FROM
    ->       gen_nums
    ->     WHERE v + 1 <= 15
    ->   )
    ->   SELECT v FROM gen_nums
    -> ) b
    -> ORDER BY 1;
+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
+------+
15 rows in set (0.02 sec)

mysql> SELECT *
    -> FROM
    -> (
    ->   WITH RECURSIVE gen_nums(v)
    ->   AS
    ->   (
    ->     SELECT 1
    ->     UNION ALL
    ->     SELECT v + 1
    ->     FROM
    ->       gen_nums
    ->     WHERE v + 1 <= 10
    ->   )
    ->   SELECT v FROM gen_nums
    -> ) a
    -> UNION ALL
    -> SELECT *
    -> FROM
    -> (
    ->   WITH RECURSIVE gen_nums(v)
    ->   AS
    ->   (
    ->     SELECT 5
    ->     UNION ALL
    ->     SELECT v + 1
    ->     FROM
    ->       gen_nums
    ->     WHERE v + 1 <= 15
    ->   )
    ->   SELECT v FROM gen_nums
    -> ) b

    -> ORDER BY 1;
+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
|    6 |
|    6 |
|    7 |
|    7 |
|    8 |
|    8 |
|    9 |
|    9 |
|   10 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
+------+
21 rows in set (0.02 sec)

来年は全部俺、どうするかな。ネタの一気放出みたいな時間取りやすければいいんだけど。先週までは本気で辛かった。時間ギリギリでw

投稿日時 2020年12月22日 (火) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#21 演算結果にも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の21日目です。

以前取り上げた内容にも近いですが、演算だけに着目した方がわかりやすいだろうということでネタにしてみました。

SQLを移植する場合は、このようなところでも注意しないと。。。ね。デフォルトの挙動に任せたままでは危険ですね

Oracle

ORACLE> SELECT 1 / 3 FROM dual;

       1/3
----------
.333333333

ORACLE> SELECT 1 / 3 * 3 FROM dual;

     1/3*3
----------
         1

ORACLE> SELECT (1 / 3) * 3 FROM dual;

   (1/3)*3
----------
         1

ORACLE> SELECT CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) * CAST(3 AS DOUBLE PRECISION) FROM dual;

CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION)*CAST(3ASDOUBLEPRECISION)
--------------------------------------------------------------------------
                                                                         1

ORACLE> SELECT DUMP(CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION)) FROM dual;

DUMP(CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION))
--------------------------------------------------------------------------------
Typ=2 Len=21: 192,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34

ORACLE> SELECT DUMP(CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) * CAST(3 AS DOUBLE PRECISION)) FROM dual;

DUMP(CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION)*CAST(3ASDOUBLEPRECISION))
--------------------------------------------------------------------------------
Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10
0,100,100,100,100

MySQL

Oracleに近いですが、若干差異がありますね。

mysql> SELECT 1 / 3;
+--------+
| 1 / 3  |
+--------+
| 0.3333 |
+--------+
1 row in set (0.02 sec)

mysql> SELECT 1 / 3 * 3;
+-----------+
| 1 / 3 * 3 |
+-----------+
|    1.0000 |
+-----------+
1 row in set (0.02 sec)

mysql> SELECT CAST(1 AS DOUBLE PRECISION) / 3 * 3;
+---------------------------+
| cast(1 as double) / 3 * 3 |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.26 sec)

mysql> SELECT CAST(1 AS DOUBLE) / CAST(3 AS DOUBLE) * 3;
+-------------------------------------------+
| cast(1 as double) / cast(3 as double) * 3 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT CAST(1 AS DOUBLE) / CAST(3 AS DOUBLE) * CAST(3 AS DOUBLE);
+-----------------------------------------------------------+
| cast(1 as double) / cast(3 as double) * cast(3 as double) |
+-----------------------------------------------------------+
|                                                         1 |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

PostgreSQL

PostgreSQLはデフォルトの挙動が明らかに異なります。

postgres=> SELECT 1 / 3;
 ?column?
----------
        0
(1 row)

postgres=> SELECT 1 / 3 * 3;
 ?column?
----------
        0
(1 row)

postgres=> SELECT 1::DOUBLE PRECISION / 3::DOUBLE PRECISION * 3::DOUBLE PRECISION;
 ?column?
----------
        1
(1 row)

postgres=> SELECT PG_TYPEOF(1::DOUBLE PRECISION / 3::DOUBLE PRECISION * 3::DOUBLE PRECISION);
    pg_typeof
------------------
 double precision
(1 row)

postgres=> SELECT PG_TYPEOF(1 / 3 * 3);
 pg_typeof
-----------
 integer
(1 row)

あと少し :)

投稿日時 2020年12月21日 (月) 04時02分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#20 結果セットを単一列に連結するにも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の20日目です。

また、本記事はJPOUG Advent Calendar 2020の20日目の窓へクロスポストしています。
JPOUG Advent Calendar 2020の19日目はNaotaka ShinogiさんのNutanix Eraで描くDatabase管理とOracleSEのデータ同期でした。

さて、今日は、最終日のネタ作りの途中でどうしても、非互換なところと格闘しないといけないので、その部分の対応をかねてw

今回は、結果セット（複数行）を単一列に連結する集約関数 Oracleでは LISTAGG()という関数の非互換です。
同様の関数はあるものの関数名は違うし多少引数も違うのでぱっと見、どう書き換えるかってなると迷うわけです。知ってれば別ですけども。

では、早速みてみましょう。

Oracle

LISTAGG

LISTAGG( [ ALL ] [ DISTINCT ] measure_expr [, 'delimiter'] [listagg_overflow_clause] ) [ WITHIN GROUP ] (order_by_clause) [OVER query_partition_clause]
https://docs.oracle.com/cd/F19136_01/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

使ったことがある方なら、ああ、アレかと思い出せると思います。あまり使う機会はないので私もマニュアル見て思出すことが多いですね。この関数w

ORACLE>
 *1 SELECT * FROM list;

        ID STR
---------- -------------
         1 foo 
         1 bar 
         1 tiger 
         1 scott
         2 bill
         2 steve

ORACLE> r
  1  SELECT
  2    id
  3    , LISTAGG(str, ',')
  4	 WITHIN GROUP
  5	 (
  6	   ORDER BY str
  7	 )
  8    AS  lists
  9  FROM
 10    list
 11  GROUP BY id
 12* ORDER BY id

	      ID LISTS
---------- ----------------------------------------
	 1 bar,foo,scott,tiger
	 2 bill,steve

次は、MySQL(8.0)

MySQLでは　GROUP_CONCAT()関数が該当します。使い方は似ていますが、セパレータの指定方法に特徴がありますね。

GROUP_CONCAT(expr)
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

mysql> SELECT
    ->   id
    ->   , GROUP_CONCAT(
    ->       str
    ->       ORDER BY str
    ->       SEPARATOR ','
    ->   )
    ->   AS  lists
    -> FROM
    ->   list
    -> GROUP BY id
    -> ORDER BY id;
+------+---------------------+
| id   | lists               |
+------+---------------------+
|    1 | bar,foo,scott,tiger |
|    2 | bill,steve          |
+------+---------------------+
2 rows in set (0.03 sec)

PostgreSQL

PostgreSQLには類似する機能を持つ関数が複数ありますが、LISTAGG()と同じ結果を得る場合には、array_agg＋array_to_string関数の組み合わせか、LISTAGGに近いSTRING_AGG()関数を単独で利用します。

array_agg(expression)
string_agg(expression, delimiter)
https://www.postgresql.jp/document/12/html/functions-aggregate.html

array_to_string(anyarray, text [, text])
https://www.postgresql.jp/document/12/html/functions-array.html

postgres=> SELECT
postgres->   id
postgres->   , STRING_AGG(
postgres(>       str, ','
postgres(>       ORDER BY str
postgres(>   )
postgres->   AS  lists
postgres-> FROM
postgres->   list
postgres-> GROUP BY id
postgres-> ORDER BY id;
 id |        lists
----+---------------------
  1 | bar,foo,scott,tiger
  2 | bill,steve

postgres=> SELECT
postgres->   id
postgres->   , ARRAY_TO_STRING(
postgres(>       ARRAY_AGG(str ORDER BY str)
postgres(>       , ','
postgres(>   )
postgres->   AS  lists
postgres-> FROM
postgres->   list
postgres-> GROUP BY id
postgres-> ORDER BY id;
 id |        lists
----+---------------------
  1 | bar,foo,scott,tiger
  2 | bill,steve

最終日のエントリーはこの集約関数やSUBSTR()など、標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020で紹介した複数のSQL構文や関数を利用したネタを予定しています。（ネタが厳しくなったら早めに公開するかもしれませんが）

明日の12月21日のJPOUG Advent Calendar 2020は Yohei Azekatsu さんです。何か、やらかして。。。いや、何か、やってくれることでしょうw　よろしくおねがいします！

投稿日時 2020年12月20日 (日) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#19 帰ってきた、部分文字列の扱いでも癖w

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の19日目です。

標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
で、すっかり忘れてた。非互換ではその手のが多いw

部分文字列の扱いの癖、盲点というかなんというか、小数の扱いの違いを忘れてましたw

Oracle

Positionが大きい場合の挙動では　Oracle以外は空文字を返します。これも非互換は非互換ですが。

注目してもらいたいのは、整数じゃないとき。Oracleは、小数点以下切り捨てで動きます。

ORACLE> set tab off
ORACLE> set null [NULL]
ORACLE> col str for a30

ORACLE> SELECT SUBSTR('1234567890', 10, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890', 11, 1) AS str FROM dual;

STR
------------------------------
[NULL]

ORACLE> SELECT SUBSTR('1234567890',10.4, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890',10.5, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890',10.9, 1) AS str FROM dual;

STR
------------------------------
0

MySQL (8.0)

Positionが範囲外であれば空文字を返すのは冒頭で説明した通りですが、Oracleと明らかに違うのは、小数点以下は四捨五入
整数以外も受け付けてくれますが、デフォルトの挙動で、切り捨てか、四捨五入という違いにより取り出される結果に差異が出ます。ハマりますよね。これw



mysql> SELECT SUBSTRING('1234567890', 10, 1) AS str;
+-----+
| str |
+-----+
| 0   |
+-----+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING('1234567890', 11, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
+-----+
| str |
+-----+
| 0   |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.06 sec)

mysql> SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
+-----+
| str |
+-----+
| 1   |
+-----+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
+-----+
| str |
+-----+
| 2   |
+-----+
1 row in set (0.02 sec)

mysql> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
+-----+
| str |
+-----+
| ''  |
+-----+
1 row in set (0.02 sec)

PostgreSQL

こちらPostgreSQLは単純、Positionに指定できるのは整数のみです。ある意味わかりやすいですw　文字列の位置が　1.9とかなかなかですからね。

postgres=> SELECT SUBSTRING('1234567890', 10, 1) AS str;
 str
-----
 0
(1 row)

postgres=> SELECT SUBSTRING('1234567890', 11, 1) AS str;
 str
-----

(1 row)

postgres=> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 11, 1)),'''') AS str;
 str
-----
 ''
(1 row)

postgres=> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
ERROR:  function pg_catalog.substring(unknown, numeric, integer) does not exist
LINE 1: SELECT SUBSTRING('1234567890', 10.4, 1) AS str;

おまけ

Redshift

PostgreSQLの血筋のはずですが、少数はエラーにもならず、そんなのねーよ。的な空文字が帰ってきます。なかなか男前です。素直に考えれば、1.9のところって文字の途中な訳で。。。

これもなかなか気づかいないです。エラーにはならないタイプなので、結果をみて？？？？　としばらく悩むタイプですね。非互換としては事前に判断が難しいタイプ。リテラルで少数指定されていれば気づきやすいですが、バインド変数だったりすると気づくのは、かなり辛いです。

redshift=# SELECT SUBSTRING('1234567890', 10, 1) AS str;
 str
-----
 0
(1 row)

redshift=# SELECT SUBSTRING('1234567890', 11, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
 str
-----
 ''
(1 row)

hr>
さあ、カウントダウンだw　(^^;;;;;

投稿日時 2020年12月19日 (土) 16時46分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#18 (+)の外部結合は方言

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の18日目です。

今日はこまけーことに気づいてしまい、ブログ忘れそうだったw

ということで、２時間を切ったところで書いてますw

今日は、(+)を使った外部結合はOracle以外で通るのか（まあ、通らないですけどねー、Oracleの方言なのでw

と言った結合関連ネタを軽めで m(_ _)m

Oracle

(+)を使ったOracleの外部結合は、方言として有名ですよね。私は随分前から使わなくなってしまったので、今日は久々なにタイプした気がしますw
ANSI構文より(+)をオススメされる時があることはあるのですが、大抵の場合、実行計画がイケてるない時の対策としてだったりします。最近のは調べてないですが、。。。時間があったらネタにしてみたいと思います。。。w

ORACLE> SELECT *
  2  FROM
  3    m, d
  4  WHERE
  5    m.id = d.id(+);

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          2          1
         3


ORACLE> SELECT *
  2  FROM
  3    m
  4    LEFT OUTER JOIN d
  5    ON m.id = d.id;

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          2          1
         3

ORACLE> SELECT *
  2  FROM
  3    m, d;

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1
         1          2          1
         2          2          1
         3          2          1

ORACLE> SELECT *
  2  FROM
  3    m
  4    CROSS JOIN d;

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1
         1          2          1
         2          2          1
         3          2          1

ORACLE> SELECT *
  2  FROM
  3    m, d
  4  WHERE
  5    m.id = d.id;

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          2          1

ORACLE> SELECT *
  2  FROM
  3    m
  4    INNER JOIN d
  5    ON m.id = d.id;

        ID         ID      SUBID
---------- ---------- ----------
         1          1          1
         2          2          1

PostgreSQL

お次はPostgreSQL、通りませんよね！

postgres=> SELECT *
postgres-> FROM
postgres->   m
postgres->   LEFT OUTER JOIN d
postgres->   ON m.id = d.id;
 id | id | subid
----+----+-------
  1 |  1 |     1
  2 |  2 |     1
  3 |    |
(3 rows)

postgres=> SELECT *
postgres-> FROM
postgres->   m, d
postgres-> WHERE
postgres->   m.id = d.id(+);
ERROR:  syntax error at or near ")"
LINE 5:   m.id = d.id(+);
                       ^
postgres=> SELECT *
postgres-> FROM
postgres->   m, d;
 id | id | subid
----+----+-------
  1 |  1 |     1
  1 |  2 |     1
  2 |  1 |     1
  2 |  2 |     1
  3 |  1 |     1
  3 |  2 |     1
(6 rows)

postgres=> SELECT *
postgres-> FROM
postgres->   m
postgres->   CROSS JOIN d;
 id | id | subid
----+----+-------
  1 |  1 |     1
  1 |  2 |     1
  2 |  1 |     1
  2 |  2 |     1
  3 |  1 |     1
  3 |  2 |     1
(6 rows)

postgres=> SELECT *
postgres-> FROM
postgres->   m, d
postgres-> WHERE
postgres->   m.id = d.id;
 id | id | subid
----+----+-------
  1 |  1 |     1
  2 |  2 |     1
(2 rows)

postgres=> SELECT *
postgres-> FROM
postgres->   m
postgres->   INNER JOIN d
postgres->   ON m.id = d.id;
 id | id | subid
----+----+-------
  1 |  1 |     1
  2 |  2 |     1
(2 rows)

MySQL (8.0)

それは、MySQLでも同じ。。。で(+)はエラーですよねー

mysql> SELECT *
    -> FROM
    ->   m
    ->   LEFT OUTER JOIN d
    ->   ON m.id = d.id;
+------+------+-------+
| id   | id   | subid |
+------+------+-------+
|    1 |    1 |     1 |
|    2 |    2 |     1 |
|    3 | NULL |  NULL |
+------+------+-------+
3 rows in set (0.07 sec)

mysql> SELECT *
    -> FROM
    ->   m, d
    -> WHERE
    ->   m.id = d.id(+);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5


mysql> SELECT *
    -> FROM
    ->   m, d;
+------+------+-------+
| id   | id   | subid |
+------+------+-------+
|    1 |    2 |     1 |
|    1 |    1 |     1 |
|    2 |    2 |     1 |
|    2 |    1 |     1 |
|    3 |    2 |     1 |
|    3 |    1 |     1 |
+------+------+-------+
6 rows in set (0.06 sec)

mysql> SELECT *
    -> FROM
    ->   m
    ->   CROSS JOIN d;
+------+------+-------+
| id   | id   | subid |
+------+------+-------+
|    1 |    2 |     1 |
|    1 |    1 |     1 |
|    2 |    2 |     1 |
|    2 |    1 |     1 |
|    3 |    2 |     1 |
|    3 |    1 |     1 |
+------+------+-------+
6 rows in set (0.04 sec)

mysql> SELECT *
    -> FROM
    ->   m, d
    -> WHERE
    ->   m.id = d.id;
+------+------+-------+
| id   | id   | subid |
+------+------+-------+
|    1 |    1 |     1 |
|    2 |    2 |     1 |
+------+------+-------+
2 rows in set (0.05 sec)

mysql>
mysql> SELECT *
    -> FROM
    ->   m
    ->   INNER JOIN d
    ->   ON m.id = d.id;
+------+------+-------+
| id   | id   | subid |
+------+------+-------+
|    1 |    1 |     1 |
|    2 |    2 |     1 |
+------+------+-------+
2 rows in set (0.03 sec)

Redshift

Redshiftでは〜、通じるんですよね。　(+)　の外部結合

WHERE 句の Oracle スタイルの外部結合
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WHERE_oracle_outer.html

ギリギリ 18個目の窓を開けたw　(^^;;;;;

投稿日時 2020年12月18日 (金) 22時36分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#17 その空白は許されないのか？

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の17日目です。

もう少しだ頑張れ、自分w

ということで、今日は、そうなの？　みたいな違いをみてみます。
関数と()の間に空白が入るとどうなるか。。。

では、いつもの通り Oracle から初めて、PostgreSQL , MySQLの順に見てみます。

Oracle

まあ、普通ですよね

ORACLE> SELECT COUNT(1) FROM dual;

  COUNT(1)
----------
	 1

ORACLE> SELECT COUNT( 1 ) FROM dual;

  COUNT(1)
----------
	 1

ORACLE> SELECT COUNT (1) FROM dual;

  COUNT(1)
----------
	 1
¥
ORACLE> SELECT COUNT ( 1 ) FROM dual;

  COUNT(1)
----------
	 1

PostgreSQL (Redshiftも同じ)

これもなんとことはない。。。

postgres=> SELECT COUNT(1);
 count
-------
     1
(1 row)

postgres=> SELECT COUNT( 1 );
 count
-------
     1
(1 row)

postgres=> SELECT COUNT (1);
 count
-------
     1
(1 row)

postgres=> SELECT COUNT ( 1 );
 count
-------
     1
(1 row)

MySQL (8.0)

おおおおおー。これは！

mysql> SELECT COUNT(1);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec)
¥
mysql> SELECT COUNT( 1 );
+------------+
| count( 1 ) |
+------------+
|          1 |
+------------+
1 row in set (0.03 sec)

mysql> SELECT COUNT (1);
ERROR 1046 (3D000): No database selected

mysql> SELECT COUNT ( 1 );
ERROR 1046 (3D000): No database selected

ところが、sql_modeをANSIにすると。。。。。

ここ知らないと？？ってなりますよね。関数と()の間にスペース入れるかどうかって、私はスペースなし派ですが、流派によってはありそうな。。。知らんけど。

5.1.11 Server SQL Modes
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

MySQLに絵文字を保存しようとしたら文字列が消える問題
http://soudai1025.blogspot.com/2016/03/"

mysql> set sql_mode = 'ANSI';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT COUNT(1);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT( 1 );
+------------+
| count( 1 ) |
+------------+
|          1 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT COUNT (1);
+-----------+
| count (1) |
+-----------+
|         1 |
+-----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT ( 1 );
+-------------+
| count ( 1 ) |
+-------------+
|           1 |
+-------------+
1 row in set (0.04 sec)

眠いw

投稿日時 2020年12月17日 (木) 19時52分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#16 SQLのレントゲンを撮る方法

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の16日目です。

さて、別の関数ネタをやろうと思うと予定外の打ち合わせが多く、今日も癖の多いSQLネタのはずが、その癖の多いSQLのレントゲン（実行計画）の撮り方の違いをネタにしてみました。m(_ _)m

大きな進化を遂げたのはいうまでも無く、MySQLですね。8.0になって大幅に機能格納した感があります。チューニングもしやすくなることでしょうね:)

こうやって、CTEの再帰問合せの実行計画見ながらハードリカー飲むのもいいものですw

まず、Oracleの実行計画確認方法はEEオプション含め3つ。SQL*Plusのauto trace以外はActual Planが確認できます。一手間かかりますが。

8 SQL*Plusのチューニング
https://docs.oracle.com/cd/F19136_01/sqpug/tuning-SQL-Plus.html#GUID-233D9103-017C-4832-B5E1-E38D32F9B00D

Oracle その1 / auto trace :　SQL*Plusの機能で、オプションなしで利用できますが Actual Plan は見ることができません＞＜実行統計は見れるのですけども

ORACLE> set tab off
ORACLE> set linesize 300
ORACLE> set autot trace exp
ORACLE> r
  1  WITH gen_nums(v)
  2  AS
  3  (
  4    SELECT 1
  5    FROM
  6      dual
  7    UNION ALL
  8    SELECT v + 1
  9    FROM
 10  gen_nums
 11    WHERE v + 1 <= 10
 12  )
 13  SELECT v from gen_nums
 14*

Execution Plan
----------------------------------------------------------
Plan hash value: 1492144221

--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |     2 |    26 |     4   (0)| 00:00:01 |
|   1 |  VIEW                                     |      |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |       |            |          |
|   3 |    FAST DUAL                              |      |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    RECURSIVE WITH PUMP                    |      |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("V"+1<=10)

Oracleその２ / DBMS_XPLAN.DISPLAY_CURSOR()を利用したActual Plan取得（これはSEでも使えるオプション不要の機能

DBMS_XPLAN.DISPLAY_CURSOR
https://docs.oracle.com/cd/F19136_01/tgsql/generating-and-displaying-execution-plans.html#GUID-83F88700-3902-4D19-8182-AF2B92AEA7EB

ORACLE> r
 1  WITH gen_nums(v)
 2  AS
 3  (
 4    SELECT /*+ GATHER_PLAN_STATISTICS */ 1
 5    FROM
 6      dual
 7    UNION ALL
 8    SELECT v + 1
 9    FROM
10  gen_nums
11    WHERE v + 1 <= 10
12  )
13* SELECT v from gen_nums
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10

10 rows selected.

ORACLE> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));
SQL_ID  a8yzv4a008jvr, child number 0
-------------------------------------
WITH gen_nums(v) AS (   SELECT /*+ GATHER_PLAN_STATISTICS */ 1   FROM
 dual   UNION ALL   SELECT v + 1   FROM gen_nums   WHERE v + 1 <= 10 )
SELECT v from gen_nums

Plan hash value: 1492144221

--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |      1 |        |     10 |00:00:00.01 |
|   1 |  VIEW                                     |      |      1 |      2 |     10 |00:00:00.01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |      1 |        |     10 |00:00:00.01 |
|   3 |    FAST DUAL                              |      |      1 |      1 |      1 |00:00:00.01 |
|   4 |    RECURSIVE WITH PUMP                    |      |     10 |        |      9 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Oracle その3 / Real Time SQL　Monitor (EEオプション)

21 データベース操作の監視
https://docs.oracle.com/cd/F19136_01/tgsql/monitoring-database-operations.html#GUID-C941CE9D-97E1-42F8-91ED-4949B2B710BF

ORACLE> set pages 0
ORACLE> set linesize 1000
ORACLE> set long 1000000
ORACLE> set longchunksize 1000000
 r
 1  WITH gen_nums(v)
 2  AS
 3  (
 4    SELECT /*+ MONITOR */ 1
 5    FROM
 6      dual
 7    UNION ALL
 8    SELECT v + 1
 9    FROM
10  gen_nums
11    WHERE v + 1 <= 10
12  )
13* SELECT v from gen_nums
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10

10 rows selected.

ORACLE> select dbms_sqltune.report_sql_monitor(type=>'text') from dual;
SQL Monitoring Report

SQL Text
------------------------------
WITH gen_nums(v) AS ( SELECT /*+ MONITOR */ 1 FROM dual
UNION ALL SELECT v + 1 FROM gen_nums WHERE v + 1 <= 10 ) SELECT v from gen_nums

Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session             :  SCOTT (25:48803)
SQL ID              :  9g75y7v030mbt
SQL Execution ID    :  16777216
Execution Started   :  12/15/2020 15:58:45
First Refresh Time  :  12/15/2020 15:58:45
Last Refresh Time   :  12/15/2020 15:58:45
Duration            :  .000232s
Module/Action       :  SQL*Plus/-
Service             :  orcl
Program             :  sqlplus@localhost.localdomain (TNS V1-V3)
Fetch Calls         :  2

Global Stats
=============================
| Elapsed |   Cpu   | Fetch |
| Time(s) | Time(s) | Calls |
=============================
|    0.00 |    0.00 |     2 |
=============================

SQL Plan Monitoring Details (Plan Hash Value=1492144221)
=================================================================================================================================================
| Id |                  Operation                  | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                             |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=================================================================================================================================================
|  0 | SELECT STATEMENT                            |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  1 |   VIEW                                      |      |       2 |    4 |         1 |     +0 |     1 |       10 |          |                 |
|  2 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  3 |     FAST DUAL                               |      |       1 |    2 |         1 |     +0 |     1 |        1 |          |                 |
|  4 |     RECURSIVE WITH PUMP                     |      |         |      |         1 |     +0 |    10 |        9 |          |                 |
=================================================================================================================================================

MySQL

なんと、MySQL8.0から実行計画みやすいし、Actualまで出るじゃないですかーーーーーーー〜。

8.8.2 EXPLAIN Output Format
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

mysql> explain analyze WITH RECURSIVE gen_nums(v)
    -> AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT v + 1
    ->   FROM
    ->     gen_nums
    ->   WHERE v + 1 <= 10
    -> )
    -> SELECT v from gen_nums;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                        |
+----------------------------------------------------------------------------------------------------------------+
| -> Table scan on gen_nums  (actual time=0.000..0.001 rows=10 loops=1)
    -> Materialize recursive CTE gen_nums  (actual time=0.019..0.020 rows=10 loops=1)
        -> Rows fetched before execution  (actual time=0.000..0.000 rows=1 loops=1)
        -> Repeat until convergence
            -> Filter: ((gen_nums.v + 1) <= 10)  (cost=2.73 rows=2) (actual time=0.002..0.003 rows=4 loops=2)
                -> Scan new records on gen_nums  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=5 loops=2)
|
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

PostgreSQL

そういえば、なんと無くMySQLの実行計画の見え方とPostgreSQLのは似てる気がします:)

14.1. EXPLAINの利用
https://www.postgresql.jp/document/12/html/using-explain.html

postgresql=> explain (analyze, buffers, verbose) WITH RECURSIVE gen_nums(v)
AS
(
  SELECT 1
  UNION ALL
  SELECT v + 1
  FROM
    gen_nums
  WHERE v + 1 <= 10
)
SELECT v from gen_nums;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on gen_nums  (cost=3.21..3.83 rows=31 width=4) (actual time=0.004..0.016 rows=10 loops=1)
   Output: gen_nums.v
   CTE gen_nums
     ->  Recursive Union  (cost=0.00..3.21 rows=31 width=4) (actual time=0.003..0.013 rows=10 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
                 Output: 1
           ->  WorkTable Scan on gen_nums gen_nums_1  (cost=0.00..0.26 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=10)
                 Output: (gen_nums_1.v + 1)
                 Filter: ((gen_nums_1.v + 1) <= 10)
                 Rows Removed by Filter: 0
 Planning Time: 0.055 ms
 Execution Time: 0.039 ms
(12 rows)

さて、rebootしますよ。何かを。という話はもう少しあとでw

投稿日時 2020年12月16日 (水) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#15 SQL command line client

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の15日目です。

最初に、これSQLじゃないじゃん！　はい。その通りです m(_ _)m

如何にもこうにも、時間取れなくて、安易な差異の紹介に走りました。　
とはいえ、SQL command line clentの使い勝手の違いって意外に無視できなかったりします
全ては紹介できないですが、個人的にどのエンジンのSQL command line clientでも使う機能だけですが:)

SQL command line clientでSQL叩いて、一旦、exitしてなんて面倒なことしたくないのでホストコマンドを実行したくなった時は ! とか \!　です。

Oracle

OracleのSQL*Plusでは ! でホストコマンドを実行できます(Windowsは host or $)

Use the following command to execute operating system commands. - ! [ command ]
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpqr/index.html#SQPQR108

ORACLE> ! date
2020年 12月15日 火曜日 0時34分38秒 JST

PostgreSQL

psql - \! [ command ]
https://www.postgresql.jp/document/12/html/app-psql.html

postgres=> \! date
2020年 12月15日 火曜日 0時35分34秒 JST

Mysql

mysql - \! [ command ]
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

mysql> \! date
2020年 12月15日 火曜日 0時39分00秒 JST

そして、無くてはならない編集コマンド, edit や \e と言ったショートカットなどがありますね。一通り覚えておくと便利です。

ORACLE

ED[IT] [file_name[.ext]]
https://docs.oracle.com/cd/F19136_01/sqpug/EDIT.html#GUID-25BC5CA1-4B03-4186-8ED3-715B5C6A6C42

ORACLE> select 1 from dual;

	 1
----------
	 1

ORACLE> edit

PostgreSQL

\e, \edit [ filename ] [ line_number ]
https://www.postgresql.jp/document/12/html/app-psql.html

postgres=> select 1;
 ?column?
----------
        1
(1 row)

postgres=> \e

MySQL

edit, \e
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.02 sec)

mysql> edit

これ、書いてて思い出した、explainの違いも書いておいた方がいいか。。。。これもSQLそのものではないけど、重要なはず。

投稿日時 2020年12月15日 (火) 11時53分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#14 連番の集合を返すにも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の14日目です。

今回は大作（軽めにしたかったけど、少々難しのでそのまま載せることにしました）

業務上あまり多くないですが、一連番号の集合を作りたくなることがあります。シーケンスを使わずに。。

Oracleには昔から比較的簡単なクエリーで一連番号の集合を作り出せる（本来その目的のた目のクエリーではないですが）クエリーがいくつかあります。
PostgreSQLには、8.0(7.xぐらいから存在していたのか調べきれず。間違っていたらコメントいただけると助かります)ぐらいから集合を返す関数として、generate_series()が組み込まれています。
MySQLは調べた限りですが、その手の便利関数やクエリーはあまりなさそうでした。

でそれを救う救世主w　と言うのは大げさですが、WITH句を使った再帰問い合わせを使うと比較的互換の高い利用ができるようになってきました。
完全に同一構文ということではないのですけども。。。書き換える部分は少ない方だと思います：）

では、

Oracleから

まず、方言から古い順に紹介していきます。

Oracle/その1：階層問合せとlevel擬似列を利用する方法

他の方法に比べると処理時間なども有利ではあるのですが、Oracleだけでしか利用できない方法です。昔から利用している方は手グセでこちらをタイプすることも多いはずw
実行計画もシンプルで、Oracleで利用可能な方法の中では負荷は軽め（私が検証した範囲では）

階層問合せか、再帰問合せか、それが問題だ #2
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2011/01/2-8488.html

階層問合せ
https://docs.oracle.com/cd/F19136_01/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E

ORACLE> set tab off
ORACLE> set linesize 300
ORACLE> r
 1  SELECT
 2    LEVEL AS r
 3  FROM
 4    dual
 5  CONNECT BY
 6    LEVEL <= 10
 7*

        R
----------
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10

10 rows selected.
Elapsed: 00:00:00.10
ORACLE>ORACLE> set autot trace exp
ORACLE> r
...略...

Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(LEVEL<=10)

Oracle/その2：CUBEとrownum擬似列を利用する方法

これOracle 8iぐらいから拡張されたという記憶（間違ってたらごめんなさい）が、CUBEというクロス集計が簡単に書ける構文で長いクエリー書かなくて済むようになったーーーと
リリースされた当時は嬉しかった拡張の一つです。こんな使い方もできるのなーというところですが、実行計画を見るとかなり重めなんですよね。実際階層問合せより重いので、一連番号生成目的で利用することはほぼないですが、できるということで紹介しておきます。

20.3 CUBE(GROUP BYの拡張)
https://docs.oracle.com/cd/F19136_01/dwhsg/sql-aggregation-data-warehouses.html#GUID-C5FDD050-DCE0-4FE1-9741-420E2F970A36

ROWNUM疑似列
https://docs.oracle.com/cd/F19136_01/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

ORACLE> r
  1  SELECT rownum
  2  FROM
  3  (
  4    SELECT 1
  5    FROM
  6      dual
  7    GROUP BY
  8      CUBE(1,1,1,1,1)
  9  )
 10  WHERE
 11*   rownum <= 10

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.
Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 2264780677

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                            |     1 |       |    66   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                                |                            |       |       |            |          |
|   2 |   VIEW                                        |                            |     1 |       |    66   (0)| 00:00:01 |
|   3 |    TEMP TABLE TRANSFORMATION                  |                            |       |       |            |          |
|   4 |     MULTI-TABLE INSERT                        |                            |       |       |            |          |
|   5 |      SORT GROUP BY NOSORT ROLLUP              |                            |     1 |       |     2   (0)| 00:00:01 |
|   6 |       FAST DUAL                               |                            |     1 |       |     2   (0)| 00:00:01 |
|   7 |      DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D698E_276CE9B |       |       |            |          |
|   8 |      DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D698F_276CE9B |       |       |            |          |
|   9 |     VIEW                                      |                            |    32 |   416 |    64   (0)| 00:00:01 |
|  10 |      VIEW                                     |                            |    32 |   416 |    64   (0)| 00:00:01 |
|  11 |       UNION-ALL                               |                            |       |       |            |          |
|  12 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  14 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  15 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  16 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  18 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  19 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  20 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  21 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  22 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  23 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  24 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  25 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  26 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  27 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  28 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  29 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  30 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  31 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  32 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  33 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  34 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  35 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  36 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  37 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  38 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  39 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  40 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  41 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  42 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698E_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
|  43 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9D698F_276CE9B |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)

Oracle/その3：再帰問合せを利用する方法

冒頭で紹介した階層問合せはOracleの方言ばりばりですが、階層問合せをサポートしているエンジンも多くなってきたこともあり、同一構文とまでは行きませんがかなり互換性は高い方法です。
汎用性のある方法にしたい場合は階層問合せを利用しておくと良いかもしれませんね。

Oracleの再帰問合せ構文では、recursive　がないのが大きな違いです。また、dual表の利用も必要なのでこの点が他のエンジンと違うところと思っておけば大丈夫だと思います。
意外と構文はシンプルです。階層問合せに比べるとタイプする文字列は多いですけどw

再帰的副問合せのファクタリング
https://docs.oracle.com/cd/F19136_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

ORACLE> r
  1  WITH gen_nums(v)
  2  AS
  3  (
  4    SELECT 1
  5    FROM
  6      dual
  7    UNION ALL
  8    SELECT v + 1
  9    FROM
 10  gen_nums
 11    WHERE v + 1 <= 10
 12  )
 13  SELECT v from gen_nums
 14*

	 V
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10

10 rows selected.
Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 1492144221

--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |     2 |    26 |     4   (0)| 00:00:01 |
|   1 |  VIEW                                     |      |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |       |            |          |
|   3 |    FAST DUAL                              |      |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    RECURSIVE WITH PUMP                    |      |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("V"+1<=10)

MySQL

調べた限りですが、MySQLにはPostgreSQLのような集合を返す関数として、generate_series()関数やOracleのような多くの方言はなさそうで
MySQL8.0からサポートされた再帰問合せが利用できます。Oracleで紹介した3つめの方法です。

多少構文は異なりますが、違うのは dual表がないのとrecursiveが必要なところ（MySQLの場合dual表を利用することも可能なので差異はrecursiveだけにすることもできます）

Recursive Common Table Expressions
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

mysql> WITH RECURSIVE gen_nums(v)
    -> AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT v + 1
    ->   FROM
    ->     gen_nums
    ->   WHERE v + 1 <= 10
    -> )
    -> SELECT v from gen_nums;
+------+
| v    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.06 sec)

mysql> explain analyze WITH RECURSIVE gen_nums(v)
    -> AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT v + 1
    ->   FROM
    ->     gen_nums
    ->   WHERE v + 1 <= 10
    -> )
    -> SELECT v from gen_nums;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                        |
+----------------------------------------------------------------------------------------------------------------+
| -> Table scan on gen_nums  (actual time=0.000..0.001 rows=10 loops=1)
    -> Materialize recursive CTE gen_nums  (actual time=0.019..0.020 rows=10 loops=1)
        -> Rows fetched before execution  (actual time=0.000..0.000 rows=1 loops=1)
        -> Repeat until convergence
            -> Filter: ((gen_nums.v + 1) <= 10)  (cost=2.73 rows=2) (actual time=0.002..0.003 rows=4 loops=2)
                -> Scan new records on gen_nums  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=5 loops=2)
|
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

さて、最後は、
PostgreSQL

PostgreSQL/その1：generate_series()関数を利用する方法

PostgreSQL方言の関数ですが、使いやすいですねタイプする文字数は一番少ない：）

9.24. 集合を返す関数 - 表9.61 連続値生成関数 - generate_series
https://www.postgresql.jp/document/12/html/functions-srf.html

postgresql=> SELECT r FROM generate_series(1, 10) r;
 r
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=> explain (analyze,buffers,verbose) SELECT r FROM generate_series(1, 10) r;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series r  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.007..0.008 rows=10 loops=1)
   Output: r
   Function Call: generate_series(1, 10)
 Planning Time: 0.025 ms
 Execution Time: 0.022 ms
(5 rows)

PostgreSQL/その2：再帰問合せを利用する方法

見ての通り、MySQLで利用した構文がそのまま利用できます。全体で見る再帰問合せを利用する方法がもっとも差異の少ないことがわかります。微妙なさなんですけどね。　無くせないものか。。そこw

7.8. WITH問い合わせ（共通テーブル式）
https://www.postgresql.jp/document/12/html/queries-with.html

postgresql=> WITH RECURSIVE gen_nums(v)
postgresql-> AS
postgresql-> (
postgresql(>   SELECT 1
postgresql(>   UNION ALL
postgresql(>   SELECT v + 1
postgresql(>   FROM
postgresql(>     gen_nums
postgresql(>   WHERE v + 1 <= 10
postgresql(> )
postgresql-> SELECT v from gen_nums;
 v
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
postgresql=> explain (analyze, buffers, verbose) WITH RECURSIVE gen_nums(v)
AS
(
  SELECT 1
  UNION ALL
  SELECT v + 1
  FROM
    gen_nums
  WHERE v + 1 <= 10
)
SELECT v from gen_nums;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on gen_nums  (cost=3.21..3.83 rows=31 width=4) (actual time=0.004..0.016 rows=10 loops=1)
   Output: gen_nums.v
   CTE gen_nums
     ->  Recursive Union  (cost=0.00..3.21 rows=31 width=4) (actual time=0.003..0.013 rows=10 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
                 Output: 1
           ->  WorkTable Scan on gen_nums gen_nums_1  (cost=0.00..0.26 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=10)
                 Output: (gen_nums_1.v + 1)
                 Filter: ((gen_nums_1.v + 1) <= 10)
                 Rows Removed by Filter: 0
 Planning Time: 0.055 ms
 Execution Time: 0.039 ms
(12 rows)

ということで、今日は、役に立つような立たないような、でも何かの役に立ちそうなネタにしてみました。:)

投稿日時 2020年12月14日 (月) 01時18分 FAQ, SQL, Opera, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#13 あると便利ですが意外となかったり

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の13日目です。

ゼェゼェ。半ばに差し掛かりネタはあるものの書くことに疲れつつあるw　（頑張れ自分w

と言うことで、今日は、あれば便利なので使ったりしますが、意外とありそうでなかったりする INITCAP()

この関数単語の先頭を大文字にしてくれる関数ですが、　MySQLの組み込み関数にはなかったりします。PostgreSQLにはあったり。

最初は
Oracleから。

INITCAP(char)
https://docs.oracle.com/cd/F19136_01/sqlrf/INITCAP.html#GUID-9FE9E0EE-D6B6-4C2C-BDEF-4FF4E1314560

ORACLE> SELECT INITCAP('oracle') FROM dual;

INITCA
------
Oracle

ORACLE> SELECT INITCAP('oracle-elison') FROM dual;

INITCAP('ORAC
-------------
Oracle-Elison

ORACLE> SELECT INITCAP('oracle,elison') FROM dual;

INITCAP('ORAC
-------------
Oracle,Elison

ORACLE> SELECT INITCAP('oracle|elison') FROM dual;

INITCAP('ORAC
-------------
Oracle|Elison

PostgreSQL

PostgreSQLにはOracleと同じ関数がサポートされています。

initcap(string)
https://www.postgresql.jp/document/12/html/functions-string.html

postgres=> SELECT INITCAP('oracle');
 initcap
---------
 Oracle
(1 row)

postgres=> SELECT INITCAP('oracle-elison');
    initcap
---------------
 Oracle-Elison
(1 row)

postgres=> SELECT INITCAP('oracle,elison');
    initcap
---------------
 Oracle,Elison
(1 row)

postgres=> SELECT INITCAP('oracle|elison');
    initcap
---------------
 Oracle|Elison
(1 row)

MySQL

個人的に意外だったのはMySQL. INITCAP()サポートされてません。UDFで作り込むしかないですね。

N/A
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

おまけ、
PostgreSQLを祖先にもつRedshiftにはPostgreSQL同様の関数がありました。

Redshift

INITCAP(string)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_INITCAP.html

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

投稿日時 2020年12月13日 (日) 03時10分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#12 文字[列]探すにも癖がある

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の12日目です。

私も癖多めですw

とうことで、アドベントカレンダーも約半分の折り返し地点です。ふー。

今日は、INSTR()

Oracleでは、SUBSTR()同様のバリエーションと挙動が見られます。ある意味分かり易ですね。
と言うことは、方言になりやすいはず。とも言えるわけです。はい。

では、見ていきましょう。

Oracle

positionは、SUBSTR()同様に負の値が使えます。0は、0しか返しません。
occurrenceは、正の値のみを取ります。

{ INSTR| INSTRB| INSTRC| INSTR2| INSTR4}(string , substring [, position [, occurrence ] ])
https://docs.oracle.com/cd/F19136_01/sqlrf/INSTR.html#GUID-47E3A7C4-ED72-458D-A1FA-25A9AD3BE113

ORACLE> SELECT INSTR('1234a56789a', 'a') FROM dual;

INSTR('1234A56789A','A')
------------------------
		                   5

ORACLE>  SELECT INSTR('1234a56789a', 'a', 1) FROM dual;

INSTR('1234A56789A','A',1)
--------------------------
			                   5

ORACLE> SELECT INSTR('1234a56789a', 'a', -1) FROM dual;

INSTR('1234A56789A','A',-1)
---------------------------
			                   11

ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 1) FROM dual;

INSTR('1234A56789A','A',1,1)
----------------------------
			   　　　　　　　　　　　5

ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 2) FROM dual;

INSTR('1234A56789A','A',1,2)
----------------------------
			  　　　　　　　　　　　11

ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 2) FROM dual;

INSTR('1234A56789A','A',-1,2)
-----------------------------
			    　　　　　　　　　　　5

ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 1) FROM dual;

INSTR('1234A56789A','A',-1,1)
-----------------------------
			   　　　　　　　　　　　11

ORACLE> SELECT INSTR('1234a56789a', 'a', 0) FROM dual;

INSTR('1234A56789A','A',0)
--------------------------
			 　　　　　　　　　　　0

ORACLE> set null [NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', null) FROM dual;

INSTR('1234A56789A','A',NULL)
-----------------------------
[NULL]

ORACLE> SELECT INSTR('1234a56789a', 'a', '') FROM dual;

INSTR('1234A56789A','A','')
---------------------------
[NULL]

ORACLE> SELECT INSTR('1234a56789a', '') FROM dual;

INSTR('1234A56789A','')
-----------------------
[NULL]

ORACLE> SELECT INSTR('1234a56789a', NULL) FROM dual;

INSTR('1234A56789A',NULL)
-------------------------
[NULL]

ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual
                                            *
ERROR at line 1:
ORA-01428: argument '0' is out of range

ORACLE> SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual
                                             *
ERROR at line 1:
ORA-01428: argument '-1' is out of range

MySQL

SUBSTR()はOracleに類似した挙動を持つ部分が多かったMySQLもINSTR()についてはそうでもないですね。positionやoccurrenceなどの引数がありません。
ただ、LOCATE()と言う類似した関数があります。LOCATE()と言う関数ではposition引数がありますが、０以上の整数でのみOracleと同じ挙動で負の値は、常に０ゼロを返すようです。

positionやoccurrence を利用している場合の移行は要注意と言うところですね。

INSTR(str,substr)
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_instr

mysql> SELECT INSTR('1234a56789a', 'a');
+---------------------------+
| instr('1234a56789a', 'a') |
+---------------------------+
|                         5 |
+---------------------------+
1 row in set (0.04 sec)

mysql> SELECT INSTR('1234a56789a', '');
+--------------------------+
| instr('1234a56789a', '') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.04 sec)

mysql> SELECT INSTR('1234a56789a', null);
+----------------------------+
| instr('1234a56789a', null) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.03 sec)

mysql> SELECT INSTR('1234a56789a', 'a', 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'
mysql> SELECT INSTR('1234a56789a', 'a', 1, 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'

LOCATE(substr,str), LOCATE(substr,str,pos)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate

mysql> SELECT LOCATE('a', '1234a56789a');
+----------------------------+
| locate('a', '1234a56789a') |
+----------------------------+
|                          5 |
+----------------------------+
1 row in set (0.08 sec)

mysql> SELECT LOCATE('a', '1234a56789a', 2);
+-------------------------------+
| locate('a', '1234a56789a', 2) |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.05 sec)

mysql> SELECT LOCATE('a', '1234a56789a', 5);
+-------------------------------+
| locate('a', '1234a56789a', 5) |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.08 sec)

mysql> SELECT LOCATE('a', '1234a56789a', 6);
+-------------------------------+
| locate('a', '1234a56789a', 6) |
+-------------------------------+
|                            11 |
+-------------------------------+
1 row in set (0.09 sec)

mysql> SELECT LOCATE('a', '1234a56789a', -1);
+--------------------------------+
| locate('a', '1234a56789a', -1) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.35 sec)

mysql> SELECT LOCATE('a', '1234a56789a', 1);
+-------------------------------+
| locate('a', '1234a56789a', 1) |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.13 sec)

mysql> SELECT LOCATE('a', '1234a56789a', 0);
+-------------------------------+
| locate('a', '1234a56789a', 0) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.35 sec)

PostgreSQL

42.13.3. 付録本節には、移植作業を簡略化するために使用できる、Oracle互換のinstr関数のコードがあります。
https://www.postgresql.jp/document/12/html/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX

INSTR()はないのですが、類似関数として以下があるようです。また、position()と言う関数もあります。ですが、どちらもpositionやoccurrenceといった引数はない。マニュアルにOracleからの移植作業向けUDFの解説がある点は興味深いところ。

strpos(string, substring)
https://www.postgresql.jp/document/7.4/html/functions-string.html

postgres=> SELECT STRPOS('1234a56789a', 'a');
 strpos
--------
      5
(1 row)

postgres=> SELECT STRPOS('1234a56789a', '');
 strpos
--------
      1
(1 row)

postgres=> \pset null [NULL]
Null display is "[NULL]".
postgres=> SELECT STRPOS('1234a56789a', NULL);
 strpos
--------
 [NULL]
(1 row)

position(substring in string)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html

postgres=> SELECT POSITION('a' in '1234a56789a');
 position
----------
        5
(1 row)

postgres=> SELECT POSITION('' in '1234a56789a');
 position
----------
        1
(1 row)

postgres=> SELECT POSITION(NULL in '1234a56789a');
 position
----------
   [NULL]
(1 row)

postgres=> SELECT POSITION(0 in '1234a56789a');
ERROR:  function pg_catalog.position(unknown, integer) does not exist
LINE 1: SELECT POSITION(0 in '1234a56789a');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres=>

Redshift

PostgreSQL系の流れをくむRedshiftに、CHARINDEX()なる関数がある。同名の関数名が見つかるのはSQL Serverですね。それはそれで興味深い。

STRPOS(string, substring )
CHARINDEX( substring, string )
POSITION(substring IN string )
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

投稿日時 2020年12月12日 (土) 06時14分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#11 デュエル、じゃなくて、デュアル

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の10日目です。

ネタがなくてw　そこ狙って来たか！　

と思われるかもしれませんが、これを語らずして、非互換語るなかれw

とうことで、Oracleでは、当たり前に利用している　dual について

定数式をSELECT文で計算する場合などに指定する表です。dual表に、おイタしたりしたネタも過去あったような気がしますw

Oracleの方言なので、単純ですが、非互換では有名ですね。

では、本家から

Oracle

DUAL表からの選択
https://docs.oracle.com/cd/F19136_01/sqlrf/Selecting-from-the-DUAL-Table.html#GUID-0AB153FC-5238-4E79-8522-C9E2A04AB5E4

ORACLE> select 1 from dual;

	 1
----------
	 1

ORACLE> select 1;
select 1
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

MySQL

昨日のエントリで使ってしまったw　ので気づいた方もいると思いますが、MySQLは dual 付けられるんですよね。

13.2.10 SELECT Statement
https://dev.mysql.com/doc/refman/8.0/en/select.html

mysql> select 1 from dual;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.06 sec)

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.04 sec)

PostgreSQL

PostgreSQLには dual を使うような習慣もないですし、文法的に用意されていません。無理やり dual 表を定義すれば別でしょうけど、無駄なだけなので移行するなら、素直に dualを削除が潔いと思いますw

SELECT
https://www.postgresql.jp/document/12/html/sql-select.html

postgres=> select 1 from dual;
ERROR:  relation "dual" does not exist
LINE 1: select 1 from dual;
                      ^
postgres=> select 1;
 ?column?
----------
        1
(1 row)

おまけ

Redshift

Redshiftにも　dual はありません。

SELECT
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SELECT_synopsis.html

redshift=# select 1 from dual;
ERROR:  relation "dual" does not exist


redshift=# select 1;
 ?column?
----------
        1
(1 row)

また、Athenaも同様です

SELECT
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html

新しい年には、何か変化が欲しいと感じる今日この頃w　

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

投稿日時 2020年12月11日 (金) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#10 文字列連結の罠（有名なやつ）

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の10日目です。

なんとか10日目の窓をあけましたw

今回は、有名な非互換なので、まさか、これに引っかかる方はいないと思いますが、定番のお約束みたいな非互換ネタなので書かないといけないですよね！！

では、いつも通り Oracle から。

NULLと空文字(マニュアルでは長さゼロの文字列値と記載されています。有名な非互換)
https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071

CONCAT(char1, char2)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CONCAT.html#GUID-D8723EA5-C93A-45C3-83FB-1F3D2A4CEAF2

連結演算子
https://docs.oracle.com/cd/F19136_01/sqlrf/Concatenation-Operator.html#GUID-08C10738-706B-4290-B7CD-C279EBC90F7E

空文字をNULLとして扱う点と、||による文字列連結(CONCATと同意）の挙動がOracle以外の世界と違うんですよね。これも有名です。

ORACLE> SELECT 'foo' || 'bar' FROM dual;

'FOO'|
------
foobar

ORACLE> SELECT 'foo' || '' FROM dual;

'FO
---
foo

ORACLE> SELECT 'foo' || NULL FROM dual;

'FO
---
foo

ORACLE> SELECT CONCAT('foo','bar') FROM dual;

CONCAT
------
foobar

ORACLE> SELECT CONCAT('foo','') FROM dual;

CON
---
foo

ORACLE> SELECT CONCAT('foo',null) FROM dual;

CON
---
foo

PostgreSQL

PostgreSQLでは、Oracleと異なり文字列連結子でNULLを結合すると結果は、NULLになります。ここがOracleと異なる挙動ですね。
これを回避するにはconcat() or concat_ws()のいずれかを利用できます。

string || string
concat(str "any" [, str "any" [, ...] ])
concat_ws(sep text, str "any" [, str "any" [, ...] ])
https://www.postgresql.jp/document/12/html/functions-string.html

そして、これまた、悩ましいのは、 ||でNULLを結合した場合と、CONCAT_WS()でNULLを結合した挙動が異なるんですね。
||でNULLの場合はNULLですが、CONCATでNULLを結合するとOracleと同じ挙動になるんですよ。

postgres=> SELECT 'foo' || 'bar';
 ?column?
----------
 foobar
(1 row)

postgres=> SELECT 'foo' || '';
 ?column?
----------
 foo
(1 row)

postgres=> SELECT 'foo' || null;
 ?column?
----------
 [NULL]
(1 row)

postgres=> SELECT CONCAT('foo', '');
 concat
--------
 foo
(1 row)

postgres=> SELECT CONCAT('foo', NULL);
 concat
--------
 foo
(1 row)

postgres=>
postgres=> SELECT CONCAT_WS('','foo','bar' );
 concat_ws
-----------
 foobar
(1 row)

postgres=> SELECT CONCAT_WS('','foo','' );
 concat_ws
-----------
 foo
(1 row)

postgres=> SELECT CONCAT_WS('','foo',NULL );
 concat_ws
-----------
 foo
(1 row)

postgres=>

MySQL

MySQLでは、||　は文字列連結子ではなく、なんと、論理演算子！！！！！！　

え〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜~っ。

12.4.3 Logical Operators - OR, ||
https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html

mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
|              0 |
+----------------+
1 row in set, 3 warnings (0.03 sec)

mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
|           0 |
+-------------+
1 row in set, 2 warnings (0.04 sec)

mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
|          NULL |
+---------------+
1 row in set, 2 warnings (0.06 sec)

実は逃げ道があるようで、sql_mode='PIPES_AS_CONCAT'　にすると文字列連結子に早変わり！w

ですが、挙動はPostgreSQL同様に、NULLと連結したり演算子すると結果はNULLになると言う一般的な動きをします。
Oracleは演算に関してはNULLが絡むとNULLになりますが、文字列連結だけはNULLが空文字のような扱いを受けると言う挙動を示します。理解しちゃえばあれですが、エラーにならないだけに混乱するタイプの非互換ですね。

PostgreSQLとは異なり、CONCAT_WS()だけがOracleと同じ挙動を示します。

CONCAT(str1,str2,...)
CONCAT_WS(separator,str1,str2,...)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

mysql> set sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
| foobar         |
+----------------+
1 row in set (0.02 sec)

mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
| foo         |
+-------------+
1 row in set (0.04 sec)

mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
| NULL          |
+---------------+
1 row in set (0.01 sec)

mysql> SELECT CONCAT('foo','bar');
+---------------------+
| concat('foo','bar') |
+---------------------+
| foobar              |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT CONCAT('foo','');
+------------------+
| concat('foo','') |
+------------------+
| foo              |
+------------------+
1 row in set (0.02 sec)

mysql> SELECT CONCAT('foo',NULL);
+--------------------+
| concat('foo',null) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.03 sec)

mysql> SELECT CONCAT_WS('foo',NULL);
+-----------------------+
| concat_ws('foo',null) |
+-----------------------+
|                       |
+-----------------------+
1 row in set (0.02 sec)

mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo                      |
+--------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONCAT_WS('','foo','bar');
+---------------------------+
| concat_ws('','foo','bar') |
+---------------------------+
| foobar                    |
+---------------------------+
1 row in set (0.02 sec)

mysql> SELECT CONCAT_WS('','foo','');
+------------------------+
| concat_ws('','foo','') |
+------------------------+
| foo                    |
+------------------------+
1 row in set (0.02 sec)

mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo                      |
+--------------------------+
1 row in set (0.01 sec)

mysql>

ややこしやー、ややこしやー。

みなさん、ついてこれてますか？　この手の内容が25日まで続きますからね。（私が続けられるか次第だが。。。。頑張りマッス！

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

投稿日時 2020年12月10日 (木) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, NULL | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の9日目です。

それでいいのだ！　バカボンのパパより。

いや、めんどくさいw

と言うことで、今日は単純そうにみえる部分文字列取得の違い。関数名の違いもあれば挙動の違いもあります。

そして、ものすごくバリエーションが多い。多すぎるので、SUBSTR()だけに絞りますw

Oracle

Unicodeキャラクタ向けSUBSTRCやUCS2コードポイント対応、UCS4コードポイント対応などのバリエーションが豊富ですが、それが方言になってますよね。

{ SUBSTR| SUBSTRB| SUBSTRC| SUBSTR2| SUBSTR4}(char, position [, substring_length ])
https://docs.oracle.com/cd/F19136_01/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E

SUBSTR()はこんな感じ。 positionをマイナスにすると一回りして切り出してきます。

ORACLE> select substr('hoge1234',1,4) from dual;

SUBS
----
hoge

ORACLE> select substr('hoge1234',-4,4) from dual;

SUBS
----
1234

MySQL

Oracleと同じ関数名、同じ引数をサポートしています。引数に特徴がありますね。from forを使った方が可読性は良いかもしれませんが、逆にウザがれる可能性も否定できません。その点個人の志向次第か。
positionにマイナスを指定した場合、Oracleと同じ挙動になりますね。興味深い。
そして、SUBSTR()はSUBSTRING()のシノニムと言うことなんですね。と言うことはオリジナルはSUBSTRING()なのか。。

SUBSTR() is a synonym for SUBSTRING().
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substr

mysql> select substr('hoge1234',1,4);
+------------------------+
| substr('hoge1234',1,4) |
+------------------------+
| hoge                   |
+------------------------+
1 row in set (0.08 sec)

mysql> select substr('hoge1234',-4,4);
+-------------------------+
| substr('hoge1234',-4,4) |
+-------------------------+
| 1234                    |
+-------------------------+
1 row in set (0.07 sec)

PostgreSQL

最後にPostgreSQL、こちらはMySQLのオリジナルと同じ関数名SUBSTRING()となっています。ぱっと見、Oracleから関数名さえ変更すれば移行できそうですが、実はpositionにマイナスを指定した場合の挙動に違いがあります！
一回りせずに、空文字を返してきます！！！！　ここ要注意です。

substring(string [from int] [for int])
https://www.postgresql.jp/document/12/html/functions-string.html

postgres=> \pset null [NULL]
Null display is "[NULL]".
postgres=> select substring('hoge1234',1,4);
 substring
-----------
 hoge
(1 row)

postgres=> select substring('hoge1234',-4,4);
 substring
-----------

(1 row)

おまけですが、PostgreSQLのSUBSTRING()関数では、パターンマッチングが行えるようです。Oraclerの私には、REGEXP_SUBSTR()をイメージしちゃうのですが、
調べてみると、PostgreSQLにはregexp_matches()関数もあります。@@ 違いがわからなくなってきたので、この辺りはあとでゆっくり勉強しておきます。w
(MySQL8.0になると、regexp_substr()関数がサポートされているようですね...終わりのない世界w)

substring(string from pattern)
substring(string from pattern for escape)

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

投稿日時 2020年12月 9日 (水) 00時00分 Oracle, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#8 翌月末日って何日？

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の8日目です。

日付関連の非互換はネタが沢山w　

翌月末日って何日？　を求める場合にも違いがあります。ほんとにもうww

Oracle

Oracleには、last_day()関数ががあります。date型の引数をとるので期間リテラルと演算可能。
では、Oracleで翌月末日を求めてみる。シンプルですね。

LAST_DAY(date)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LAST_DAY.html#GUID-296C7C02-7FB9-4AAC-8927-6A79320CE0C6

ORACLE> alter session set nls_date_format = 'yyyy-mm-dd';

Session altered.

ORACLE> select last_day(sysdate + interval '1' month) from dual;

LAST_DAY(S
----------
2021-01-31

Mysql

MySQLにもOracle同様にlast_day()関数を使って、簡単に「翌月末日って何日？」を求めることができますよね。

LAST_DAY(date)
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

mysql> select last_day(curdate() + interval 1 month);
+----------------------------------------+
| last_day(curdate() + interval 1 month) |
+----------------------------------------+
| 2021-01-31                             |
+----------------------------------------+
1 row in set (0.03 sec)

Postgresql

なぜか、昔から頑なに　last_day()関数がありません。UDFで頑張るか、ちょいと頑張って「翌月末日って何日？」を求めなければいけません。
翌々月１日の前日が「翌月末日って何日？」なので、その方法で求めてみます。やれやれ、last_day()ってUDF作った方が楽そうですねw

N/A
https://www.postgresql.jp/document/12/html/functions-datetime.html

postgres=> select cast(date_trunc('month', current_date + interval '2 month') - interval '1 day' as date);
    date
------------
 2021-01-31
(1 row)

Redshift

PostgreSQLの流れを汲むRedshiftですが、last_day()関数ありました！ PostgreSQLに、もしあればこんな構文だろうと想像します。

LAST_DAY ( { date | timestamp } )
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_LAST_DAY.html

redshift=# select last_day(current_date + interval '1 month');
  last_day
------------
 2021-01-31
(1 row)

ブログ書きながら寝落ちしてたw

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！

投稿日時 2020年12月 8日 (火) 00時32分 Oracle, FAQ, SQL, MySQL, PostgreSQL, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の7日目です。

さて、日付関連は非互換の宝庫ではありますが、ほんとなんで違うの。。。と言う微妙な違いだったり、キーーーーってなりますよね。なぜ合わせられないw

今日は期間リテラル。

Oracle

期間リテラル
https://docs.oracle.com/cd/F19136_01/sqlrf/Literals.html#GUID-49FADC66-794D-4763-88C7-B81BB4F26D9E

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

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

SQL> SELECT SYSDATE + INTERVAL '10' DAY FROM DUAL;

SYSDATE+IN
----------
2020-12-16

MySQL

Temporal Intervals
https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals

mysql> SELECT CURDATE() + INTERVAL 10 DAY;
+-----------------------------+
| curdate() + interval 10 day |
+-----------------------------+
| 2020-12-16                  |
+-----------------------------+
1 row in set (0.00 sec)

PostgreSQL

8.5. 日付/時刻データ型
https://www.postgresql.jp/document/12/html/datatype-datetime.html

sql=> SELECT CAST(CURRENT_DATE + INTERVAL '10 DAY' AS DATE);

    date
------------
2020-12-16
(1 row)

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2020

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!/a>
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！

投稿日時 2020年12月 7日 (月) 00時00分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の6日目です。

日付、日時関連も非互換の多い部分ですね。

該当関数の挙動を見てみると違いと、以外と違いがあるので面白いですよ。日付、日時関数って多くて全てを取り上げてると大変なので、今回は、OracleのSYSDATEを取り上げて、PostgreSQLではどれ使うのがいいのかなぁ

と言うところを見ていきたいと思います。

まず、このエントリー内で利用している、関数の特性を示す単語の意味を定義しておきたいと思います。

・同一SQL文の上で何度callされても同じ日時を返す特性：文内同一
・同一SQL文の上でcallされる毎に異なる日時を返す特性：文内非同一
・同一トランザクション内で何度callしても同一日時を返す特性：トランザクション内同一
・同一トランザクション内でcallされる毎に異なる日時を返す特性：トランザクション内非同一

関数としては沢山ありすぎて個別に調査した内容を個別に書こうと思うと、アドベントカレンダーの一エントリーとして終わる気がししないのでw

軽めに書いてもこんな感じw

SYSDATEはタイムゾーンを持たないので、PostgreSQLのCURRENT_DATEかなー、と思ってしまうと大きな間違いで、もっとも挙動として近いのは、Oracleで利用していたタイムゾーンに合わせる形で利用するPostgreSQLのstatement_timestamp()と言うことになる。
有名なOrafceで提供されている oracle.sysdate()　も中を覗いてみると、statement_timestamp() が利用されている。

このエントリーの後半で挙動の確認方法と oracle.sysdate()　が　statement_timestamp()　を利用しているソースを記載しています。

Oracle Database

SYSDATE
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSDATE.html#GUID-807F8FC5-D72D-4F4D-B66D-B0FE1A8FA7D2

年月日時分秒タイムゾーンなし

特性
文内同一、トランザクション内非同一

SYSTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSTIMESTAMP.html#GUID-FCED18CE-A875-4D5D-9178-3DE4FA956516

年月日時分秒.秒未満精度(9) タイムゾーンあり

特性
文内同一、ランザクション内非同一

CURRENT_DATE
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_DATE.html#GUID-96795097-D6F0-4288-90E7-9D7C49B4F6E5

年月日時分秒タイムゾーンなし

特性
文内同一、トランザクション内非同一

CURRENT_TIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_TIMESTAMP.html#GUID-CBD42B84-869D-45C7-9FFC-001DD7712097

年月日時分秒.秒未満精度(9) タイムゾーンあり

特性
文内同一、トランザクション内非同一

LOCALTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/LOCALTIMESTAMP.html#GUID-3C3D1F29-5F53-41F2-B2D6-A3767DFB22CA

年月日時分秒.秒未満精度(9) タイムゾーンなし

特性
文内同一、トランザクション内非同一

PostgreSQL
PostgreSQLのtimestamp型の秒未満精度は最大6桁であるため、Oracleのtimestamp型の最大精度より低いことに注意
https://www.postgresql.jp/document/12/html/functions-datetime.html

CURRENT_DATE
年月日（PostgreSQLのDATE型で返す）タイムゾーンなし

特性
文内同一、トランザクション内同一

CURRENT_TIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内同一、トランザクション内同一

LOCALTIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンなし

特性
文内同一、トランザクション内同一

clock_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内非同一、トランザクション内非同一

transaction_timestamp()
CURRENT_TIMESTAMPに同じ
関数名が具体的に何を返すか明確になっている点の違いのみ

特性
文内同一、トランザクション内同一

statement_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内同一、トランザクション内非同一
statement_timestamp()が、もっともOracleのSYSDATE/SYSTIMESTAMPに近い挙動を示す

now()
transaction_timestamp()の別名
利用することは推奨されていない（何を返すかわかりにくい関数名である影響と思われる)

特性
文内同一、トランザクション内同一

例

Oracle Database

トランザクション内同一性の確認（1秒間隔で3回実行）
Oracle Databaseの日付時刻関数は、トランザクション内非同一。

SYSDATE              SYSTIMESTAMP
-------------------- ----------------------------------------
2020/11/08 16:12:03  2020/11/08 16:12:03.345275 +00:00
2020/11/08 16:12:04  2020/11/08 16:12:04.397601 +00:00
2020/11/08 16:12:05  2020/11/08 16:12:05.475719 +00:00

LOCALTIMESTAMP                 CURRENT_DATE         CURRENT_TIMESTAMP
------------------------------ -------------------- ----------------------------------------
2020/11/09 01:12:03.345277     2020/11/09 01:12:03  2020/11/09 01:12:03.345277 +09:00
2020/11/09 01:12:04.397603     2020/11/09 01:12:04  2020/11/09 01:12:04.397603 +09:00
2020/11/09 01:12:05.475721     2020/11/09 01:12:05  2020/11/09 01:12:05.475721 +09:00

文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
(ore_sleep()と言うUDFを作成し、内部で（Oracle 18cまで）dbms_lock.sleep(1) or （Oracle 19c以降）dbms_session.sleep(1) を実行)

以下の結果から、Oracle Databaseの日付時刻関数は、すべて文内同一。

SQL> SELECT
  sysdate, sysdate, ORE_SLEEP(1), sysdate
FROM
  dual;

SYSDATE             SYSDATE             ORE_SLEEP(1)   SYSDATE
------------------- ------------------- -------------- -------------------
2020/11/21 05:49:46 2020/11/21 05:49:46              0 2020/11/21 05:49:46

SQL> r
  1  SELECT
  2    systimestamp, systimestamp, ORE_SLEEP(1), systimestamp
  3  FROM
  4*   dual

SYSTIMESTAMP                             SYSTIMESTAMP                             OREO_SLEEP(1)  SYSTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/05:48:19.459180 +00:00        2020/11/21/05:48:19.459180 +00:00                     0 2020/11/21/05:48:19.459180 +00:00

SQL> r
  1  SELECT
  2    localtimestamp,localtimestamp,ORE_SLEEP(1),localtimestamp
  3  FROM
  4*   dual

LOCALTIMESTAMP                           LOCALTIMESTAMP                           ORE_SLEEP(1)   LOCALTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21 14:58:13.350655               2020/11/21 14:58:13.350655                            0 2020/11/21 14:58:13.350655

SQL> r
  1  SELECT
  2    current_date,current_date,ORE_SLEEP(1),current_date
  3  FROM
  4*   dual

CURRENT_DATE        CURRENT_DATE        ORE_SLEEP(1)   CURRENT_DATE
------------------- ------------------- -------------- -------------------
2020/11/21 14:59:47 2020/11/21 14:59:47              0 2020/11/21 14:59:47

SQL> r
  1  SELECT
  2    current_timestamp,current_timestamp,ORE_SLEEP(1),current_timestamp
  3  FROM
  4*   dual

CURRENT_TIMESTAMP                        CURRENT_TIMESTAMP                        ORE_SLEEP(1)   CURRENT_TIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/15:00:41.661495 +09:00        2020/11/21/15:00:41.661495 +09:00                     0 2020/11/21/15:00:41.661495 +09:00

PostgreSQL

トランザクション内同一性の確認（1秒間隔で3回実行）
PostgreSQLの日付時刻関数は、トランザクション内同一と非同一が混在

CURRENT_DATEは時刻を持たないためこの方法では検証不能だが、
マニュアルではトランザクション内で同一と記載されている。
clock_timestamp(),statement_timestamp()はトランザクション内非同一、それ以外は、トランザクション内同一。

 current_date |       current_timestamp       |
--------------+-------------------------------+
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |

       clock_timestamp        |       localtimestamp       |
------------------------------+----------------------------+
2020-11-08 16:12:05.936468+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:07.304506+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:08.532788+00 | 2020-11-08 16:12:05.897343 |

             now              |      statement_timestamp      |     transaction_timestamp
------------------------------+-------------------------------+-------------------------------
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:05.936423+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:07.304408+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:08.532704+00 | 2020-11-08 16:12:05.897343+00

文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
以下の結果から、PostgreSQL日付時刻関数は、文内同一/非同一混在。

current_dateについてはこの方法では検証できないが、ドキュメントより文内同一であると判断。

sql=> SELECT
sql->   current_date, current_date, pg_sleep(1), current_date;

 current_date | current_date | pg_sleep | current_date
--------------+--------------+----------+--------------
 2020-11-21   | 2020-11-21   |          | 2020-11-21

文内同一

sql=> SELECT
sql->   current_timestamp,current_timestamp,pg_sleep(1),current_timestamp;

       current_timestamp       |       current_timestamp       | pg_sleep |       current_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:05:48.930432+00 | 2020-11-21 06:05:48.930432+00 |          | 2020-11-21 06:05:48.930432+00

文内非同一

sql=> SELECT
sql->   clock_timestamp(),clock_timestamp(),pg_sleep(1),clock_timestamp();

        clock_timestamp        |        clock_timestamp        | pg_sleep |        clock_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:08:48.920466+00 | 2020-11-21 06:08:48.920466+00 |          | 2020-11-21 06:08:49.925492+00

文内同一

sql=> SELECT
sql->   localtimestamp,localtimestamp,pg_sleep(1),localtimestamp;

       localtimestamp       |       localtimestamp       | pg_sleep |       localtimestamp
----------------------------+----------------------------+----------+----------------------------
 2020-11-21 06:19:19.547055 | 2020-11-21 06:19:19.547055 |          | 2020-11-21 06:19:19.547055

文内同一

sql=> SELECT
sql->   now(),now(),pg_sleep(1),now();

              now              |              now              | pg_sleep |              now
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:20:07.457373+00 | 2020-11-21 06:20:07.457373+00 |          | 2020-11-21 06:20:07.457373+00

文内同一

sql=> SELECT
sql->   statement_timestamp(),statement_timestamp(),pg_sleep(1),statement_timestamp();

      statement_timestamp      |      statement_timestamp      | pg_sleep |      statement_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:20:52.502137+00 | 2020-11-21 06:20:52.502137+00 |          | 2020-11-21 06:20:52.502137+00

文内同一

sql=> SELECT
sql->   transaction_timestamp(),transaction_timestamp(),pg_sleep(1),transaction_timestamp();

     transaction_timestamp     |     transaction_timestamp     | pg_sleep |     transaction_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:21:24.562833+00 | 2020-11-21 06:21:24.562833+00 |          | 2020-11-21 06:21:24.562833+00

orafce によるエミュレーション関数（参考)
https://github.com/orafce/orafce/blob/master/README.asciidoc

oracle.sysdate()
PostgreSQLのstatement_timestamp()をラップしているため属性はstatement_timestamp()と同じ
sysdate関数のエミュレーションとしては最も近い属性を持っていると見られる。
OracleのSYSDATE代替関数とされている。
特性としては、問題ないと考えられ、文内同一 (OracleのSYSDATEと同じ挙動）,トランザクション内非同一（OracleのSYSDATEと同じ挙動）

実装を見てみると、

https://github.com/orafce/orafce/blob/master/orafce--3.14.sql
の関数定義を見るとC言語の関数であり、statement_timestampをと言うコメントがある.　
statement_timestamp()がOracleのSYSDATEの挙動に近いと言う理由からなのだろうと想像する。納得感あり！！！！

CREATE FUNCTION oracle.sysdate()
RETURNS oracle.date
AS 'MODULE_PATHNAME','orafce_sysdate'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION oracle.sysdate() IS 'Ruturns statement timestamp at server time zone';

さらに、orafceのoracle.sysdate()のC言語のソースを追ってみる。。。
datefce.cのorafce_sysdateが本体であることがわかる
https://github.com/orafce/orafce/blob/master/builtins.h

extern PGDLLEXPORT Datum orafce_sysdate(PG_FUNCTION_ARGS);

https://github.com/orafce/orafce/blob/master/datefce.c

/********************************************************************
*
* ora_sysdate - sysdate
*
* Syntax:
*
* timestamp sysdate()
*
* Purpose:
*
* Returns statement_timestamp in server time zone
*   Note - server time zone doesn't exists on PostgreSQL - emulated
*   by orafce_timezone
*
********************************************************************/

Datum
orafce_sysdate(PG_FUNCTION_ARGS)
{
	Datum sysdate;
	Datum sysdate_scaled;

	sysdate = DirectFunctionCall2(timestamptz_zone,
					CStringGetTextDatum(orafce_timezone),
					TimestampTzGetDatum(GetCurrentStatementStartTimestamp()));

	/* necessary to cast to timestamp(0) to emulate Oracle's date */
	sysdate_scaled = DirectFunctionCall2(timestamp_scale,
						sysdate,
						Int32GetDatum(0));

	PG_RETURN_DATUM(sysdate_scaled);
}

および、GetCurrentStatementStartTimestamp()よりstatement_timestamp()であることがわかる
https://docs.huihoo.com/doxygen/postgresql/backend_2utils_2adt_2timestamp_8c_source.html#l01239

01239 statement_timestamp(PG_FUNCTION_ARGS)
01240 {
01241     PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
01242 }

Oracleの検証に利用したコード、昔は、DBMS_LOCK.SLEEP()ってなんでDBMS_LOCKパッケージにあるの？　と言う感じだったが、最近はわかりやすいDBMS_SESSIONパッケージが推奨で、DBMS_LOCK.SLEEP()は非推奨なのでご注意を

ore_seep(seonds) - UDF sample
CREATE OR REPLACE FUNCTION ore_sleep
(
 seconds NUMBER
)
RETURN NUMBER
AS
BEGIN
 $IF DBMS_DB_VERSION.VER_LE_12 $THEN
   DBMS_LOCK.SLEEP(seconds);
 $ELSE
   DBMS_SESSION.SLEEP(seconds);
 $END
 RETURN 0;
END;
/

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2020

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!/a>
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派

投稿日時 2020年12月 6日 (日) 12時49分 Oracle, FAQ, SQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の5日目です。

これもあるのが当たり前だよねーと思っていると、ないの！！　と言うありがちなネタで、日付の和暦変換など、

Oracleだとグローバリゼーション対応で組み込まれてます。昨年令和に切り替わったばかりなので、Oracleが中心の方々は、MySQLやPostgreSQL界隈は大丈夫なの？間に合うの？なんて話題も多かったことだろうと思います。
私もそうでした。でも心配する必要のあるのはOracleだけだったw と言う取り越し苦労でした。はい。

まず、Oracle

SCOTT@orcl> r
  1  SELECT
  2    TO_CHAR(
  3      SYSDATE
  4      , 'EEYY/MM/DD'
  5      , 'NLS_CALENDAR = ''JAPANESE IMPERIAL'''
  6    )
  7  FROM
  8*   DUAL

TO_CHAR(SYSDATE,'EEYY/MM/DD','NLS_CALE
--------------------------------------
令和02/12/05

経過: 00:00:00.01
SCOTT@orcl>

Oracle 19c - Databaseグローバリゼーション・サポート・ガイド 3.7.1.4 紀元の年
https://docs.oracle.com/cd/F19136_01/nlspg/setting-up-globalization-support-environment.html#GUID-9674F2F3-D3A2-436A-83D1-7A8AC0D2B1ED

実は、年号が平成から令和切り替わると言う時になって始めて知ったのですが、みんな対応しているわけではないのですね。。和暦。
そう言う意味では、顧客要求をしっかり拾っていたと言うことなのでしょうね。

まだまだ、知らないことが多いSQLな世界w　

PostgreSQL

ないのでUDF等で対応ですよね

PostgreSQLの新元号への対応について
https://www.ashisuto.co.jp/support/gengo/product/postgresql.html

MySQL

ないのでUDF等で対応ですよね

と言うことで、RDS/Auroraにつても同様 この記事、現在は　Snowflake - Principal Cloud Support Engineer の松崎さんの記事ですね！
https://aws.amazon.com/jp/blogs/news/how-to-determine-whether-kaigen-japan-era-name-transition-affects-your-mysql-compatible-engines-running-on-rds/"

Redshift
ないので、必要ならUDF等で対応ですよね

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!/a>

投稿日時 2020年12月 5日 (土) 13時40分 Oracle, SQL, MySQL, PostgreSQL, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の4日目です。

モルダー、あなた疲れてるのよ！　的なタイトルになってしまったw

エラーにはならないけど、この手の差異もなかなか嫌らしい差異ですよね。

結論から言うと、この差異の影響を避けるためにはデフォルトの挙動任せは危険かな。と言うことになるのですが、まあ、色々ありますよね。長いこと同じエンジン使って他ところへ乗り換えるとなると。

Oracle Database

なお、SQL*plusのnumformatパラメータは未設定の状態（デフォルト）です。

SQL> SELECT CAST((1.0/6.0) AS NUMBER(38,37)) FROM DUAL;

リテラル値の除算結果は小数点以下1桁の状態でデフォルトのキャストを利用した場合、内部的に小数点以下37桁精度。
実は、表示されている桁数は、SQL*Plusのnumformatの影響を受け、numformat(デフォルト小数点以下7桁)で丸る

一見小数点以下8桁に見えるが、

SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

CAST((9/7)ASNUMBER(38,37))
--------------------------
		1.28571429

numformatの桁数を大きくすると内部的は小数点以下37桁精度！

SQL> set numformat "0.0999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

	      CAST((9/7)ASNUMBER(38,37))
----------------------------------------
 1.2857142857142857142857142857142857143

ね！

SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;

	  CAST((9.0/7.0)ASNUMBER(38,37))
----------------------------------------
 1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
 1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143

Oracleの場合、返却時の精度が全ての計算精度に影響

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,37))
--------------------------------------------------------------------
1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,8)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,8))
-------------------------------------------------------------------
1.2857142900000000000000000000000000000

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,8)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,8))
---------------------------------------------------------------------
1.2857142900000000000000000000000000000

SQL> set numformat "0.09999999999999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

			CAST((9/7)ASNUMBER(38,37))
--------------------------------------------------
 1.28571428571428571428571428571428571430000000000

SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;

		    CAST((9.0/7.0)ASNUMBER(38,37))
--------------------------------------------------
 1.28571428571428571428571428571428571430000000000

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
 1.28571428571428571428571428571428571430000000000

PostgreSQLだとどうなるかと言うと、

整数として扱われ、除算結果もinteger型！

sql=> SELECT 9/7;

 ?column?
----------
        1

 pg_typeof
-----------
 integer

リテラル値は小数点以下1桁の精度だが、除算結果は小数点以下16桁精度のnumeric型で返されるようだ。
PostgreSQLのマニュアルには記載されていないようだが、どこかに記載されてるのだろうか。。。知ってる方がいたらコメントお待ちしております。:)

sql=> SELECT 9.0/7.0;

      ?column?
--------------------
 1.2857142857142857

 pg_typeof
-----------
 numeric

小数点以下の精度0でnumeric型にキャスト**した場合も、除算結果は小数点以下16桁精度のnumeric型となるようだが、これもPostgreSQLのマニュアルには記載されていないようだ。
なお、PostgreSQLの場合numeric型へのキャストで指定できる最大精度は1000と記載され内部的にはそれ以上の精度となっている
詳細は　

8.1.2. 任意の精度を持つ数
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

表8.2 数値データ型
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
を参照のこと

sql=> SELECT 9::NUMERIC(100,0)/7::NUMERIC(100,0);

      ?column?
--------------------
 1.2857142857142857

 pg_typeof
-----------
 numeric

小数点以下精度1桁でnumeric型へキャスト**した場合も同様に、除算結果は小数点以下16桁のnumeric型になるのな。これ

sql=> SELECT 9::NUMERIC(100,1)/7::NUMERIC(100,1);

      ?column?
--------------------
 1.2857142857142857

 pg_typeof
-----------
 numeric

同様に小数点以下2桁でnumeric型へキャストし、除算した結果も小数点以下16桁精度のnumeric型として返される模様。

sql=> SELECT 9::NUMERIC(100,2)/7::NUMERIC(100,2);

      ?column?
--------------------
 1.2857142857142857

 pg_typeof
-----------
 numeric

小数点以下15桁のnumeric型にキャストした場合も結果は小数点以下16桁精度のnumeric型！

sql=> SELECT 9::NUMERIC(100,15)/7::NUMERIC(100,15);

      ?column?
--------------------
 1.2857142857142857

 pg_typeof
-----------
 numeric

小数点以下16桁のnumber型にキャストした場合は、指定したscaleで返された。

sql=> SELECT 9::NUMERIC(100,16)/7::NUMERIC(100,16);

      ?column?
--------------------
 1.2857142857142857

   pg_typeof
 --------------------
 numeric

小数点以下17桁精度でnumeric型へキャストすると、指定した小数点以下17桁で除算結果が返された。
(どうやら、小数点以下16桁未満は小数点以下の精度を16桁として扱っているように見える

sql=> SELECT 9::NUMERIC(100,17)/7::NUMERIC(100,17);

      ?column?
---------------------
 1.28571428571428571

 pg_typeof
-----------
 numeric

表示される桁数が16桁なので内部も16桁かと思ったら、少々多めに持っているらしい。このあたりマニュアルに記載がない....
内部でも小数点以下16精度なのか？という疑問を確認するための確認の結果、小数点以下20桁なので多少大きめの精度で持っているだが、この辺りもPostgreSQLのマニュアルを見る限り記載されていないようだ。

sql=> SELECT CAST((1.0/6.0) AS NUMERIC(38,37));

                 numeric
-----------------------------------------
0.1666666666666666666700000000000000000

しかし、小数点以下1桁のリテラル値については、小数点以下16桁の精度で計算されてしまう。（ルールはあるようだがドキュメントに明確な記載が見当たらないのは辛いとこと。

sql=> SELECT CAST((9.0/7.0) AS NUMERIC (38,37));

                 numeric
-----------------------------------------
 1.2857142857142857000000000000000000000

結局のところ、デフォルトの挙動に任せず、必要な精度にキャストすることで必要な精度を持たせるようにすることでOracleのデフォルトの挙動と同様の精度になりそう。これが無難な対応なんだろうね。

sql=> SELECT CAST((9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37)) AS NUMERIC(38,37));

                 numeric
-----------------------------------------
 1.2857142857142857142857142857142857143

PostgreSQLでは可能な精度の検証。Oracleからの移行ではここまでの精度は必要としない、よね。多分。

sql=> SELECT 9::NUMERIC(100,99)/7::NUMERIC(100,99);

                                               ?column?
-------------------------------------------------------------------------------------------------------
 1.285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714286

 pg_typeof
-----------
 numeric

PostgreSQLで指定可能な最大精度（内部的な精度は1000を超えるがして可能な精度は1000)　　

試して！　Oracler皆びっくり！　な感じ

sql=> SELECT 9::NUMERIC(1000,999)/7::NUMERIC(1000,999);

そして、
計算結果とそれぞれのリテラル値の精度ではどちらが優先されるのか確認したところ、Oracleとは異なり、最大精度ではなく、リテラル値の制度が優先される結果となった。この点、Oracleからの移行では注意が必要かな。

以下は、小数点以下の精度を37桁に統一した結果だが、この場合Oracleと同様の精度の結果が得られる

sql=> SELECT (9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37))::NUMERIC(38,37);

                 numeric
-----------------------------------------
 1.2857142857142857142857142857142857143

しかし、リテラル側の精度を落とし8桁にすると、16桁以下は全て16桁の精度になるという挙動が見られ流・

sql=> SELECT (9.0::NUMERIC(38,8)/7.0::NUMERIC(38,8))::NUMERIC(38,37);

                 numeric
-----------------------------------------
 1.2857142857142857000000000000000000000

sql=> SELECT (9.0::NUMERIC(38,16)/7.0::NUMERIC(38,16))::NUMERIC(38,37);

                 numeric
-----------------------------------------
 1.2857142857142857000000000000000000000

リテラル値の精度を17桁にすると、指定精度で結果を返すが、やはり計算結果の精度にはならない

sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,37);

                 numeric
-----------------------------------------
 1.2857142857142857100000000000000000000

さらに、17桁の精度で計算し結果を16桁精度で返す例。

sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,16);

      numeric
--------------------
 1.2857142857142857

おまけ

Redshiftのnumeric型はPostgreSQL由来ではあるが、デフォルトの精度に関する挙動はPostgreSQLに近い。ただし、多少異なる部分もある。以下の例はPostgreSQL 11.xでは、小数点以下 20桁精度だったが、Redshiftでは16桁精度となっている。
微妙な違いだが違いがあるのは確かなので注意した方が良さげ

sql=# select cast((1.0/6.0) as numeric(38,37));

                 numeric
-----------------------------------------
0.1666666666666667000000000000000000000

噂のプログラム発動中だったのか。そんなの気にしてなかった。全くwwww

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです

投稿日時 2020年12月 4日 (金) 22時20分 Oracle, SQL, MySQL, PostgreSQL, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の3日目です。

忙しい、今年も勢いで始めた割には、事前にネタの構想があったわけでもないというギリギリの状態で書いてますがw
なんとか3つ目の窓をあけました。:)

今回は、データ型確認したいことありませんか？　という話。
Oracleを長年使い、PostgreSQL（互換含む）への移行という時に、調査していた時のこと、。。。

データ型見たいよね、これ。Oracleだとdump()で間接的にデータ型見れたなと。。。で他のエンジンではどうなのよ。。。と調べ始めたら結構大変でした。。
という２年半ぐらい前の苦労を思い出しつつ書いてみますw

調べていくと、わかりやすい関数名だったり、ありそうでないものもあるんですよね。。。。まさに、非互換の多い部分だった。。。

まず、Oracle
おなじみのdump()関数ですね。typ=nn の数字でデータ型を判断します. typ=2は、NUMBER型、typ=96は、CHAR型ですね。

SQL> select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(123,8) from dual;

DUMP(123,8)
---------------------
Typ=2 Len=3: 302,2,30

SQL> select dump(123,10) from dual;

DUMP(123,10)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(123,16) from dual;

DUMP(123,16)
--------------------
Typ=2 Len=3: c2,2,18

SQL> select dump(123,17) from dual;

DUMP(123,17)
---------------------
Typ=2 Len=3: c2,^B,^X

SQL>
SQL> select dump('123',1016) from dual;

DUMP('123',1016)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: 31,32,33

次は、Postgresql

pg_typeof()って関数が使えます。結果がデータ型名で返されるのでわかりやすい！

sql=> select pg_typeof(123);
 pg_typeof
-----------
 integer
(1 row)

sql=> select pg_typeof('123'::text);
 pg_typeof
-----------
 text
(1 row)

sql=> select pg_typeof(now());
        pg_typeof
--------------------------
 timestamp with time zone
(1 row)

Athena

Prestoで使える関数なので、そのまま typeof()って関数でこれもデータ型名で返されます。
なぜ、Athenaかって？　勢いですかねぇ。

% aws athena start-query-execution --query-string "select typeof(123);" --result-configuration OutputLocation=s3://xxxx-athena-results
--------------------------------------------------------------
|                     StartQueryExecution                    |
+-------------------+----------------------------------------+
|  QueryExecutionId |  f658de1e-b711-433d-b603-15835b6e5de5  |
+-------------------+----------------------------------------+
%
% aws athena get-query-results --query-execution-id 92f46e33-0b2e-4e94-90b7-8acb3d6fce3b --output text | grep DATA
DATA	_col0
DATA	integer

Redshift

N/A

MySQL
もし、あったらツッコミよろしくお願いします。 m(_ _)m

N/A
　

参考

Oracle - DUMP
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DUMP.html#GUID-A05793C9-B35D-4BA7-B68C-E3693BCF47A5

Oracle Built-in Data Types
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

PostgreSQL - 表9.63 システムカタログ情報関数 - pg_typeof
https://www.postgresql.jp/document/11/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

Athena - 6.4. Conversion Functions - typeof
https://prestodb.io/docs/0.172/functions/conversion.html

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！

投稿日時 2020年12月 3日 (木) 20時16分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の2日目です。

今日は、心と時間の余裕がないので、軽めですw

いきなりですがタイトルの通り、関数名が同じなら引数の並びや数も一緒だ！

と決めつけちゃいけないw　案件です。　数時間ハマった挙句、マニュアルを読み直すという王道で解決した事案ですはい。マニュアル読みましょうね。読みづらいのもあるけど。。

現在の私、何をやってるかピンボケ感満載なロール名ではあるのですが、その名の通り、OracleのSQLで質問もうけるわ、たまには、SparkSQLでも質問受けたりしますw

そのSparkSQLでハマったのがrtrim(),ltrim()。

長年Oracleを使ってきたので、手癖でタイプしちゃうわけですよ！　思い込み、ダメ絶対！w

では、その大切なマニュアルの解説でRDBMSの有名どころのltrim/rtrimとSparkSQLのltrim/rtrimのシンタックスを確認してみましょう。

まず、Oracleは以下の通り。見たなれ安心感w

LTRIM( str [, trimStr] )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

次、PostgreSQL

RTRIM( str [, trimStr] )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
https://www.postgresql.jp/document/11/html/functions-string.html

ついでなので、Redshift
PostgreSQLと同じですね。

RTRIM( string, trim_chars )　string : 切り捨てる文字列の列または式。 trim_chars : 末尾から切り捨てる文字を表す、文字列の列または式。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_RTRIM.html

MySQL
MySQLのRTRIMには第二引数は無い! 単体では同じことができないので、他の関数と組み合わせるんでしょうね。（試してないですが）

RTRIM( str )str : トリムされるソースの文字列型または、リテラル文字列
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_rtrim

そして、最後に、今回ハマった
SparkSQL

RTRIM( [trimStr ,] str )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
トリムしたい文字列の引数位置が、Oracle/PostgreSQL/Redshiftとは逆なので引数が少ないMySQLと異なりシンタックスエラーにならない＞＜
https://spark.apache.org/docs/2.3.1/api/sql/index.html#rtrim

rtrim()も同じです。

Oracle
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

PostgreSQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

Redshift
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

MySQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

SparkSQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA

簡単な例を

SparkSQL

>>> SQL="select rtrim('　', 'SparkSQL　') as d"
>>> spark.sql(SQL).show()
+--------+
| d      |
+--------+
|SparkSQL|
+--------+

Oracle

SQL> select '|' || rtrim('SparkSQL ', ' ') || '|' as d from dual;
D
----------
|SparkSQL|

PostgreSQL / Redshift

test=> select '|' || rtrim('SparkSQL ', ' ') || '|' as d;
  d
------------
|SparkSQL|

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination

投稿日時 2020年12月 2日 (水) 18時31分 Oracle, FAQ, SQL, MySQL, PostgreSQL, Advent Calendar, AWS | 固定リンク | 0 | コメント (0)

標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination

いよいよ始まりました。Advent Calendar 2020　
今年も勢いで全部俺ですw

JPOUG Advent Calendar 2020もよろしくお願いします！
（最後まで書けるのか、書き抜ける喜びw　どこかのCMっぽくなってしまいましたが、第一日めの窓を開けましょうw）

今年の全部俺はレントゲンではなく、標準はあれど、非常に癖の多いSQLを25回に渡り、眠い目を擦りながら書きづつけて行きたい（すでに希望になってるw）と思っております。はい。

先日、AthenaでLIMIT句使おうとして、エラーに遭遇したところからw

Oracle/MySQL/PostgreSQL/Redshiftとか LIMIT OFFSET的ところから違うわけですが（同じ部分もあります）、何も考えずに、Athenaに投げて、撃沈w
Prestoの311以降だとLIMIT OFFSETでPagination可能なわけですが、Amazon Athenaは今の所(2020/12/1現在）Presto 0.172なのをすっかり忘れてたわけです。はい。さーせん。

あ〜、SQLってバージョンでもそうですが、エンジン違えば、気が狂うw程度に違う時があって、き〜〜〜って。なることしばしば。
で、イラっとしたSQLの違いを Oracleの構文と比較しながら自分の備忘録として書いて残しておこうと思った次第です。

（なーんだ、自分の為か、と思わないでくださいね。明日はわが身かもしれませんよw）

以下、どの構文がどのエンジンで通るのかをざっとまとめたもの。

1.ROWNUMとWHERE句によるPagination
Oracle

SELECT
  id
FROM (
  SELECT
    ROWNUM as ln
    ,id
  FROM
    hoge
  ORDER BY id
)
WHERE
  ln BETWEEN 1 AND 100;

2.ROW_NUMBER()ウィンドウ関数とWHERE句によるPagination
意外に使えるw　可読性悪いけどねー

Oracle / PostgreSQL / Redshift / Athena

SELECT
  id
FROM (
  SELECT
    ROW_NUMBER() OVER(
      ORDER BY id
    ) AS ln
    ,id
  FROM
    hoge
)
WHERE
  ln BETWEEN 1 AND 100;

3.OFFSET句とFETCH FIRST n ROWS ONLYのPagination
Oracle / PostgreSQL

SELECT
  id
FROM
  hoge
ORDER BY
  id
OFFSET 0 ROW
FETCH FIRST 100 ROWS ONLY;

4.LIMIT句とOFFSET句のPagination
PostgreSQL / MySQL / Redshift

SELECT
  id
FROM
  hoge
ORDER BY
  id
LIMIT 100 OFFSET 0;

ちなみに、Oracleでは、2.のROW_NUMBER()と3.のOFFSET + FETCHの構文の実行計画は同じなので、可読性が高い2.と3.いずれかと言われれば3.がおすすめ。

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    10 |   260 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                  |         |    10 |   260 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|         |    10 |    50 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | HOGE_PK |  1000K|  4882K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("LN">=1 AND "LN"<=100)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    10 |   390 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                  |         |    10 |   390 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|         |    10 |    50 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | HOGE_PK |  1000K|  4882K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
             "from$_subquery$_002"."rowlimit_$$_rownumber">0)
  2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)

参考
Oracle
https://docs.oracle.com/cd/E82638_01/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726
https://docs.oracle.com/cd/F19136_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

MySQL
https://dev.mysql.com/doc/refman/8.0/en/select.html

PostgreSQL
https://www.postgresql.jp/document/11/html/queries-limit.html

Reshift
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ORDER_BY_clause.html

Amazon Athena
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

寝ぼけながら書いているので、誤り等ございましたらご指摘願います。
仕事忙しいのに全部俺始めてしまい、かなり不安なスタートw（この時間だし）

投稿日時 2020年12月 1日 (火) 01時32分 Oracle, SQL, MySQL, PostgreSQL, Advent Calendar | 固定リンク | 0 | コメント (0)

MySQL/PostgreSQL/Oracleのクライアント

macdeoracle:~ discus$ brew install mysql-client

...

macdeoracle:~ discus$ mysql --version
mysql Ver 14.14 Distrib 5.7.23, for osx10.14 (x86_64) using EditLine wrapper
macdeoracle:~ discus$ psql --version
psql (PostgreSQL) 11.5
macdeoracle:~ discus$ sqlplus -v

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

そういえば、10年ぐらい前は、複数のDBと戯れつつ、Index Only Acesssネタなんかもやってたっけw （時代は繰り返すw のかw）

ひとまず、ここまで.

ずーっと前のエントリー
Index Only Accessネタのおまけ

今日は、防寒具とか見に行かないと。

投稿日時 2019年11月 9日 (土) 08時29分 Oracle, Mac, MySQL, PostgreSQL | 固定リンク | 0 | コメント (0)

Wait Events

データベース関連で待機イベントと言えば、これまでは、Oracle Database しか浮かばなかったわけですが、今は、PostgreSQL、そして、MySQL にも実装された。

待機イベントを知らずして、どうするの？　でも大丈夫。今までOracleの待機イベントに親しんできたデータベースエンジニアの活躍の場が広がるんじゃないかなぁ。。。と遠くをみている。。。

Oracle Database Wait Events

PostgreSQL Wait Events

MySQL : 25.12.15.1 Wait Event Summary Tables

投稿日時 2019年2月21日 (木) 21時29分 FAQ, Oracle Database 11g, Oracle Database 11g Release 2, MySQL, PostgreSQL, Oracle Database 12c release 1, Oracle Database 12c Release 2, Oracle Database 18c | 固定リンク | 0 | コメント (0) | トラックバック (0)

\copy コマンド de CSVファイルからのロード

今回はOracleネタではなく、Aurora PostgreSQLネタです。(RDS PostgreSQLでも同じ

csvファイルをロードしてみます。

csvファイルは前回SQL*Plusで作成したファイルを使います:)

discus-mother:~ oracle$ echo $LANG
ja_JP.UTF-8
discus-mother:~ oracle$ 
discus-mother:~ oracle$ cat loaddata_test.csv
1,"テスト","note"
2,"平成","note"
3,"abcdbef","note"
4,"あ","note"
5,"A","note"
6,,"note"

つづいて、Aurora PostgreSQLへcsvデータをロードする準備。
事前に表を作成しておきました。

discus-mother:˜ oracle$ psql --host=xxxxxxxxxxxxxxxxxxxxx.rds.amazonaws.com --port=5432 --username=hoge --password --dbname=testdb

testdb=> select aurora_version();
 aurora_version 
----------------
 2.1.0
(1 row)

testdb=> select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
(1 row)

testdb=> \l testdb
                        List of databases
  Name  | Owner | Encoding | Collate | Ctype | Access privileges 
--------+-------+----------+---------+-------+-------------------
 testdb | hoge  | UTF8     | C       | C     | 
(1 row)

testdb=> 
testdb=> create schema hoge;
CREATE SCHEMA
testdb=> set search_path=hoge,public;
SET
testdb=> 
testdb=> create table test (
testdb(>   id numeric not null primary key
testdb(>   ,data character varying(10)
testdb(>   ,foo character varying(10) not null
testdb(> );
CREATE TABLE
testdb=>  \d+ test
                                 Table "hoge.test"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | numeric               | not null  | main     |              | 
 data   | character varying(10) |           | extended |              | 
 foo    | character varying(10) | not null  | extended |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

testdb=>

csvからのロードは \copy コマンドを利用しました:)

testdb=>  \copy test(id,data,foo) from 'loaddata_test.csv' with csv
COPY 6
testdb=>  select * from test order by id;
 id |  data   | foo  
----+---------+------
  1 | テスト  | note
  2 | 平成    | note
  3 | abcdbef | note
  4 | あ      | note
  5 | A       | note
  6 |         | note
(6 rows)

testdb=>  
testdb=>  \pset null [null]
Null display is "[null]".
testdb=>  select * from test order by id;
 id |  data   | foo  
----+---------+------
  1 | テスト  | note
  2 | 平成    | note
  3 | abcdbef | note
  4 | あ      | note
  5 | A       | note
  6 | [null]  | note
(6 rows)

目がしょぼしょぼするが、花粉症ではない（キッパリ

投稿日時 2019年2月11日 (月) 15時44分 PostgreSQL, AWS | 固定リンク | 0 | コメント (0) | トラックバック (0)

PostgreSQL向け、俺よう便利メモ

testdb=> select version();
                                   version         
                          -----------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit

以降のスクリプトの動作確認用の表定義

testdb=> \d+ hoge
                                 Table "public.hoge"
 Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
 id     | numeric(10,0)          | not null  | main     |              | 
 status | numeric(2,0)           | not null  | main     |              | 
 note   | character varying(100) |           | extended |              | 
Indexes:
    "hoge_pkey" PRIMARY KEY, btree (id)

PL/pgSQLでPL/SQLっぽい感じの無名ブロックを書いてみた。commit/rollbackは含めふことができないってところはちょいとハマるね。Oraclerには。（なれれば問題ないって感じはするが）

testdb=> \! cat ins10000000.sql
DO $$BEGIN
  FOR i IN 1..10000000 LOOP
    INSERT INTO hoge
    (id, status, note)
    VALUES(
     i, 0, LPAD('hoge',100,'x')
    );
  END LOOP;
END$$;
testdb=> \i ins10000000.sql

まあ、いろいろ勝手が違うので、よく使いそうなSQL文も作り置きしておかないと。
以下のSQLは、Oracle Databaseだと表と索引のセグメントサイズの確認で dba_segmentsを問い合わせるのと同じイメージな。ビューは違うけど。

testdb=> \! cat show_segment_size.sql
SELECT
  objectname
  , TO_CHAR(pg_relation_size(objectname::regclass), '999,999,999,999') AS bytes
FROM 
(
  SELECT
    tablename AS objectname
  FROM
    pg_tables
  WHERE 
    schemaname = 'public'
  UNION
  SELECT
    indexname AS objectname
  FROM
    pg_indexes
  WHERE
    schemaname = 'public'
) AS objects
ORDER BY
  bytes DESC
;

testdb=> \i show_segment_size.sql 
 objectname |      bytes       
------------+------------------
 hoge       |    1,412,415,488
 hoge_pkey  |      224,632,832
(2 rows)

Oracle Databaseとは異なるアーキテクチャを採用しているPostgreSQLの特徴がよく見える部分。全体の30％ぐらいの行を削除して、dead tupleを確認してみたところ。
Inside vacuum - 第一回PostgreSQLプレ勉強会

testdb=> \! cat show_dead_tup_ratio.sql
SELECT
  relname
  , n_live_tup
  , n_dead_tup
  , CASE
    WHEN n_live_tup > 0
      THEN ROUND(n_dead_tup * 100 / n_live_tup, 2)
      ELSE NULL
  END AS ratio
FROM
  pg_stat_user_tables;

testdb=> delete from hoge where id between 1 and 3000000;
DELETE 3000000

testdb=> \i show_dead_tup_ratio.sql 
 relname | n_live_tup | n_dead_tup | ratio 
---------+------------+------------+-------
 hoge    |    6973030 |    3020431 | 43.00
(1 row)

最後は、Lockの状態を確認するSQL文。この部分もOracle Databaseとは異なるとこも多いので、Lockがどうなってるか見えるようなSQL文は作り置きしておくと便利。
宣言的パーティションのロックもOracleのPartitionとは異なる部分も多いので事前に確認しておくと、あとから驚くようなことがなくて安心できる、かも。

testdb=> \! cat show_locked_objects.sql
SELECT
  clock_timestamp()
  , pc.relname
  , pl.locktype
  , pl.database
  , pl.relation
  , pl.page
  , pl.tuple
  , pl.virtualtransaction
  , pl.pid
  , pl.mode
  , pl.granted
FROM
  pg_locks pl
  INNER JOIN pg_class pc
  ON
    pl.relation = pc.oid
WHERE
  pc.relname !~ '^pg_'
  AND pc.relname <> 'activelocks';

testdb=> BEGIN;
BEGIN
testdb=> SELECT * FROM hoge FOR UPDATE SKIP LOCKED;
 id | status |                                                 note   
----+--------+------------------------------------------------------------------------------------------------------
  1 |      0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxhogedkdkhoge
(1 row)

testdb=> \i show_locked_objects.sql
        clock_timestamp        |  relname  | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted 
-------------------------------+-----------+----------+----------+----------+------+-------+--------------------+-------+-----------------+---------
 2019-01-07 07:45:33.742858+00 | hoge_pkey | relation |    16401 |    16405 |      |       | 8/11426            | 18445 | AccessShareLock | t
 2019-01-07 07:45:33.742879+00 | hoge      | relation |    16401 |    16402 |      |       | 8/11426            | 18445 | RowShareLock    | t
(2 rows)

投稿日時 2019年1月 7日 (月) 18時27分 FAQ, SQL, PostgreSQL, AWS, RDS | 固定リンク | 0 | コメント (0) | トラックバック (0)

SELECT ~ FOR UPDATE　その後

あけましておめでとうございます。本年もよろしくお願いいたします。

今年の初エントリーは、随分昔のネタを引っ張り出してみましたw
というのも、最近は、Oracle以外のRDBMSに関わる機会が多くなり、調べていると昔のネタに繋がっていた！　という状況も多々あり、ついでなので他のネタを織り交ぜながら書いていったほうがよいのではないか？　と遠くを眺めながら思っていますw

さて、本題です。

SELECT ~ FOR UPDATE SKIP LOCKED　その１ - @sh2ndさんのエントリの復習など
って
もう5年前のネタですが、世の中もDB業界的にもいろいろな動きがって、MySQLやPostgreSQLを利用する機会も多くなってきた。。。方々（私も含むw）多くなってきたようなので忘れかけてたことを思い出すのための確認など

最近のリリースでSELECT ~ FOR UPDATEの動きを確認

結果
Oracle READ COMMITTED

12.1.0.2.0 ID=2を取得

12.2.0.1.0 ID=2を取得

18.3.0.0.0 ID=2を取得

ところで、PostgreSQL でも　9.5からFOR UPDATE SKIP LOCKEDがサポートされていて、PostgreSQLのSKIP LOCKED動きが、Oracleとおなじだったのは興味深い発見だった :)

・SELECT ~ FOR UPDATE SKIP LOCKED　その１ - @sh2ndさんエントリの復習など
 ・SELECT ~ FOR UPDATE SKIP LOCKED　その２
 ・SELECT ~ FOR UPDATE SKIP LOCKED　その３
 ・SELECT ~ FOR UPDATE SKIP LOCKED　その4 - もしもITL不足だったら...

投稿日時 2019年1月 7日 (月) 14時18分 Oracle, SQL, PostgreSQL | 固定リンク | 0 | コメント (0) | トラックバック (0)

db tech showcase 2014 Osaka に行ってきた

I love your data（どこかのパクリw) な人たちが、いろいろなDBMSを見聞きし、それぞれの思いで、何年か先の未来に、それぞれの思いを馳せる

そんな、集まりが、 db tech showcase 2014 Osaka　

(ん....俺っぽくない出だしを書いててワロタ....）

に臨時休業（自分ではこれも仕事のうちなんだがw 仕事って思ってないだけw）して参加した。
https://www.facebook.com/db.tech.showcase

Slideshare : セッション資料はここ

話を聞いていたら、オレオレレプリケーションできそうな気になるから不思議 :) .
オレオレ、ゴルゲやオレオレ、attunityとか、自分で作って試してみると、面倒くさいポイントとか見えていいかもね。
Attunity Replicateの画面を初めてみたけど、シンプルで好きなデザイン。
・B31 : LogMinerってレプリケーションソフトで使われてるけどどうなってる？ / 森田俊哉(インサイトテクノロジー)

[B31] LOGMinerってレプリケーションソフトで使われているけどどうなってる？ by Toshiya Morita from Insight Technology, Inc.

Oracle以外の話も聴きたくてNoSQL系などをチョイス。　割り切った実装で特定用途でその力を発揮する。割り切り大事。
・D32 : Amazon Redshift Deep Dive / 大久保順(アマゾンデータサービスジャパン)

20140620 dbts osaka_redshift_v1.0_slideshare from Jun Okubo

・B33 : Riak: 本物の高可用性を実現する仕組みとは？ / 佐藤貴彦 (Bashoジャパン)

Riak: 本物の高可用性を実現する仕組みとは？ from Takahiko Sato

・D34 : データウェアハウス・エンジンTeradataのご紹介とビッグデータ統合アーキテクチャー / 山本泰史(日本テラデータ)

The Machine!にも関連するのだろうけど、Memristorの話題も!
・D35 : インメモリーデータベース徹底比較 / 小森博之(日本HP)

[D35] インメモリーデータベース徹底比較 by Komori from Insight Technology, Inc.

そしてスペシャルセッション、遠い未来じゃないはなし
・A36 : ウエアラブルとO2Oが切り拓くICTの新地平 / 村上憲郎

vessylもそんな”もの”の一つかもしれない。飲みものの分析ができるんだからトイレにも応用できるんじゃないか的な :)
日本のトイレがそうなるかは分からないけど、先にやってくれたら面白いかもね。
毎日が健康診断、データはかかりつけの医師に共有されていて、気になるデータが見つかると、洗面台のミラー風マルチタッチデバイスに情報がプッシュされ...必要なら、その場で通院予約、その後待たされることなく診察なんて時間の無駄がなくていいな〜と、ぼーっと妄想していたり。

楽しいやね。 :)

そういえばそんなシーンのある映画で思い出したのがこれ

T-シャツ、ありがとうございました。

Bqilsptcyaae4vjjpglarge

東京から大阪への新幹線で日帰りだと電池切れ感が半端ないので一泊することをおすすめしますw

投稿日時 2014年6月21日 (土) 12時37分 Oracle, Oracle以外のデータベース, 勉強会, イベント, MySQL, PostgreSQL, DB Tech Showcase, NoSQL, Riak | 固定リンク | 0 | コメント (0) | トラックバック (0)

Unconference at db tech showcase 2012の資料公開 :)

db tech showcase 2012
Unconference at db tech showcase 2012

db tech showcaseの一角をJPOUGが占拠してUnconferenceを開催しました。 db tech showcase関係者の皆様、このような機会を与えて頂き大変感謝します。
そして、お疲れさまでした。

Index Only Access ３部作の最終回？! として「Index Only Accessが実装されるたった一つの理由」と題したセッションを行いました。
実行計画を取得するために操作したデータベースの中には人生二度目のデータベース複数もあり、かなりの時間を裂いて調べた割にはセッション時間が少々短めになってしまいました。m(_ _)m

なぜ、このテーマを選んだか.

PostgreSQLがリリースされてから9.1まで実装されなかったIndex Only Accessでしたが、9.2でついに実装されました。

そして..db tech showcase 2012は...

SQL> select dbms_name from all_dbms where dbms_category like "%";

DBMS_NAME
------------------
Oracle
DB2
MySQL
PostgreSQL
SQL Server
Vecterwise
MongoDB
Symfoware
Clustrix
InfiniDB
.
.
.
.

的な雰囲気となっていることもあり、Index Only Accessの魚拓をあつめて比較、Index Only Accessが実装される理由について今一度、考えてみたいな..と。
タイトル見ただけで理由が想像できた方は、資料見なくても大丈夫だと思いますよ。:)　
　

H/Wの性能が急速に伸びてきている影響もあるように感じますが、無駄に広範囲な検索や、無駄にビッグなデータとなっていること気にしていないのではないか？　というケースが多くなっていると感じています。
DBMSはアクセスするデータをより少なくするための工夫をしているのに...エンジニアがそれをうまく使っていない、使えていない、設計できてない...そんな"感じ"がするんです。

セッション資料を公開しました。

じゃ、like "%てない" 状況をどうすればいいか....答えは、小田さんのセッションの中にあった。。。。:)

#不慣れなDBMSもあり、こんなメトリックみたほうが分かりやすいよ〜、などのツッコミ歓迎します.

投稿日時 2012年10月21日 (日) 06時15分 Oracle, Oracle以外のデータベース, チューニング, SQL, イベント, MySQL, PostgreSQL, JPOUG, DB Tech Showcase, Index Only Scan | 固定リンク | 0 | コメント (0) | トラックバック (0)

Index Only Accessネタのおまけ

2012/10/13追記
MySQL(InnoDB)の主キーはClustered Indexなので主キーアクセスである場合はCovering indexは不要ですね。(^^;;　
PostgreSQL9.2のIndex-only scanですが、vacuumさえしっかりやっていればcovering indexを利用するようになることを確認。

前述の２点を後日追加予定です。

随分間があいてしまいました、m(_ _)m

Oracle以外でも多数の商用/OSS RDBMSでIndex Only Accessできるんですよね。
ということで、MySQL/PostgreSQLの実行計画ではどのように表示されるのか、Oracleのように索引だけ作れば勝ってにIndex Only Accessやってくれるのか？、などなど簡単に確かめてみました。(備忘録）

OracleのIndex Only Accessは以下のエントリを参考にしてください。
・いん！、イン！、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG
・JPOUG SET EVENTS 20120721 - 「(続)いん！、イン！、Index 大人の事情縛りのSQLチューニング」資料公開

今回使ったのは、MySQL 5.5.27/PostgreSQL 9.1.4そしてBeta阪ですが9.2 Beta4です。
環境はOracleが乗ってる環境と同じです。

MacBook Air late 2010 13inch 2GB (MacOS X Lion)
VirtualBox4.1.18 for MacOS X
GuestOS：CentOS5.8 x86

ちなみに、データ作りに時間を割けなかったので、クラスタリングファクターはどちらのも低めとなっています。実行計画上どのように見えるのか知りたかっただけなので. (^^;;;

MySQL 5.5.27

Oracleのデモでも使っていたスカラー副問合せで試してみました。最初は、Index Rancge Scanをグルグル。
この場合は索引、表をそれぞれアクセスします。

mysql> explain 
    -> select
    ->     t1.unique_id,
    ->     t1.item_code,
    ->     (
    ->        select
    ->            max(t3.unique_id)
    ->        from
    ->            tab31 t2 join tab311 t3
    ->            on
    ->                t2.sub_item_code = t3.sub_item_code
    ->                and t3.is_delete = 0
    ->        where
    ->            t2.item_code = t1.item_code
    ->            and t2.is_delete = 0
    ->     ) current_sub_item
    -> from
    ->     tab3 t1
    -> where
    ->     t1.unique_id between 1 and 10000
    ->     and t1.is_delete = 0
    ->     and t1.status_code = '00'
    -> ;
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
| id | select_type        | table | type   | possible_keys | key       | key_len | ref                    | rows  | Extra       |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
|  1 | PRIMARY            | t1    | range  | PRIMARY       | PRIMARY   | 5       | NULL                   | 20108 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | eq_ref | PRIMARY       | PRIMARY   | 45      | scott.t1.item_code     |     1 | Using where |
|  2 | DEPENDENT SUBQUERY | t3    | ref    | tab311_ix     | tab311_ix | 31      | scott.t2.sub_item_code |     1 | Using where |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
3 rows in set (0.00 sec)

mysql>

Index Only Accessさせた場合の実行計画です。
Extra列に Using indexと出ていればIndex Only Accessになっています。
ちなみに、Oracleとちがって勝ってにCovering Indexを使ってくれなかったのでSQLヒントを使っています。Oracleのヒントと随分書き方違うので戸惑うよ(^^;;;

mysql> explain
    -> select
    ->     t1.unique_id,
    ->     t1.item_code,
    ->     (
    ->        select
    ->            max(t3.unique_id)
    ->        from
    ->            tab31 t2 ignore index(primary) join tab311 t3 ignore index(tab311_ix)
    ->            on
    ->                t2.sub_item_code = t3.sub_item_code
    ->                and t3.is_delete = 0
    ->        where
    ->            t2.item_code = t1.item_code
    ->            and t2.is_delete = 0
    ->     ) current_sub_item
    -> from
    ->     tab3 t1
    -> where
    ->     t1.unique_id between 1 and 10000
    ->     and t1.is_delete = 0
    ->     and t1.status_code = '00'
    -> ;
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys  | key            | key_len | ref                          | rows  | Extra                    |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
|  1 | PRIMARY            | t1    | range | PRIMARY        | PRIMARY        | 5       | NULL                         | 20108 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | ref   | tab31_demo_ix  | tab31_demo_ix  | 47      | scott.t1.item_code,const     | 10000 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t3    | ref   | tab311_demo_ix | tab311_demo_ix | 33      | scott.t2.sub_item_code,const | 10000 | Using where; Using index |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
3 rows in set (0.00 sec)

mysql>

PostgreSQL 9.1

PostgreSQL9.1まではIndex Only Accessが実装されていないとのこと。（実は今年になってはじめて知ったことなのですが、その時は、「え？！　そうなの？」って感じでした）

※実行時間のバラツキがあるため実行統計情報から表や索引ブロックアクセス状況を確認しています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |             0 |            0
 16401 |      16455 | public     | tab311  | tab311_demo_ix |             0 |            0

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2050286.39 rows=10326 width=22) (actual time=45.613..6560.751 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=198.47..198.48 rows=1 width=6) (actual time=0.650..0.651 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..198.46 rows=4 width=6) (actual time=0.143..0.621 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_pk on public.tab31 t2  (cost=0.00..8.58 rows=1 width=11) (actual time=0.010..0.011 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: (t2.item_code = t1.item_code)
                       Filter: (t2.is_delete = 0::numeric)
                 ->  Index Scan using tab311_ix on public.tab311 t3  (cost=0.00..189.27 rows=49 width=17) (actual time=0.130..0.565 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: (t3.sub_item_code = t2.sub_item_code)
                       Filter: (t3.is_delete = 0::numeric)
 Total runtime: 6566.636 ms
(18 行)

時間: 6618.795 ms
scott=>

適切な索引を利用しているのでIndex Unique/Range Scanとテーブルブロックへのアクセスが確認できます。（当然といえば当然ですよね）

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |          15389 |         36920 |          1983 |        30033 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |            401 |          9599 |            64 |        30057 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |            64 |        30057
 16401 |      16455 | public     | tab311  | tab311_demo_ix |          1983 |        30033

Index Only AccessさせるためのCovering Index(FAT index)を作ってみましたが、Index Range/Unique Scanのままですね。
ちなみに、PostgreSQLってOracleやMySQLのようなSQLヒントがありません。（あったらごめんなさい。調べきれてないだけです。m(_ _)m
Covering Indexだけを残して邪魔な索引を削除することでCovering indexをアクセスさせています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |             0 |            0
 16401 |      16455 | public     | tab311  | tab311_demo_ix |             0 |            0

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2109597.62 rows=10326 width=22) (actual time=0.677..1808.056 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=204.21..204.22 rows=1 width=6) (actual time=0.176..0.176 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..204.20 rows=4 width=6) (actual time=0.024..0.137 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..8.76 rows=1 width=11) (actual time=0.007..0.009 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                 ->  Index Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..194.84 rows=49 width=17) (actual time=0.013..0.068 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
 Total runtime: 4762.819 ms
(16 行)

時間: 4855.100 ms
scott=>

実行計画上、Filterはなくなりましたが、表ブロックもアクセスしているのでIndex Only Accessにはなっていません。(ほんとにIndex Only AccessというかIndex-only Scanは9.1までは実装されていないんですね。）

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16401 | public     | tab311  |          15390 |         36919 |          1982 |        30034 |               0 |              0 |              0 |             0
 16395 | public     | tab31   |            401 |          9599 |            62 |        30059 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16401 |      16411 | public     | tab311  | tab311_pk      |             0 |            0
 16395 |      16454 | public     | tab31   | tab31_demo_ix  |            62 |        30059
 16401 |      16455 | public     | tab311  | tab311_demo_ix |          1982 |        30034

PostgreSQL 9.2 Beta4

最後に、PostgreSQL 9.2 Beta4 です。このリリースではPostgreSQLでは初めて、Index Only Access（マニュアルでは　Index-only Scanと記載されています）
PostgreSQL方面の方がIndex-only Scanと書くかたが多いのもこの影響でしょうね。日本人からするとIndex Only AccessよりIndex Only Scanの方が発音しやすい？（私だけか？）気がしますw

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..1925706.85 rows=9421 width=22) (actual time=41.205..6202.096 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=204.32..204.33 rows=1 width=6) (actual time=0.613..0.614 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..204.31 rows=4 width=6) (actual time=0.110..0.584 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Scan using tab31_pk on public.tab31 t2  (cost=0.00..11.35 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=10000)
                       Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
                       Index Cond: (t2.item_code = t1.item_code)
                       Filter: (t2.is_delete = 0::numeric)
                 ->  Index Scan using tab311_ix on public.tab311 t3  (cost=0.00..192.47 rows=49 width=17) (actual time=0.095..0.525 rows=40 loops=10000)
                       Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
                       Index Cond: (t3.sub_item_code = t2.sub_item_code)
                       Filter: (t3.is_delete = 0::numeric)
 Total runtime: 6207.935 ms
(18 行)

時間: 6313.789 ms
scott=>

9.2でもIndex Only Accessでなければ表ブロックもアクセスしますよね〜。そりゃそうだ。:)

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |          15390 |         36919 |          1649 |        29996 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |            401 |          9599 |            39 |        30037 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+--------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk    |             0 |            0
 16398 |      16432 | public     | tab31   | tab31_pk     |            39 |        30037
 16404 |      16434 | public     | tab311  | tab311_ix    |          1649 |        29996

いよいよ、PostgreSQL9.2 Beta4のIndex-only Scanの番です。:)
お〜〜〜っ、 Index Scan using xxxxという部分が、Index Only Scan using xxxxとなっています！が、 Heap Fetches 400000とある？　どゆこと？

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;

                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..2000896.05 rows=9421 width=22) (actual time=0.410..1728.699 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=212.30..212.31 rows=1 width=6) (actual time=0.168..0.168 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..212.29 rows=4 width=6) (actual time=0.023..0.131 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Only Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..13.13 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=10000)
                       Output: t2.item_code, t2.is_delete, t2.sub_item_code
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                       Heap Fetches: 10000
                 ->  Index Only Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..198.67 rows=49 width=17) (actual time=0.013..0.066 rows=40 loops=10000)
                       Output: t3.sub_item_code, t3.is_delete, t3.unique_id
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
                       Heap Fetches: 400000
 Total runtime: 5412.612 ms
(18 行)

時間: 5465.400 ms
scott=>

やはり！　Heap Fetchesとあるのでおかしいと思っていたら...orz. なんで表ブロックアクセスしてんの〜〜〜っ。実行計画は、Index Only Scan。謎。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |          15389 |         36920 |          1983 |        30033 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |            401 |          9599 |            64 |        30057 |               0 |              0 |              0 |             0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk      |             0 |            0
 16398 |      16435 | public     | tab31   | tab31_demo_ix  |            64 |        30057
 16404 |      16436 | public     | tab311  | tab311_demo_ix |          1983 |        30033

何故、Inde Only Scanなのに表ブロックをアクセスしてしまうのか、わかった〜〜〜っ、と思う。（vacuum analyze が必要らしい。ちなみに前述の結果はデータをINSERTし、analyzeコマンドだけを実施した状態だった）

気持ちを落ち着けて〜〜〜！

[oracle@leaffish ˜]$ psql -U oracle scott
タイミングは on です。
psql (9.2beta4)
"help" でヘルプを表示します.

scott=# vacuum analyze tab311;
scott=# vacuum analyze tab31;

こんどこそ！　できた〜〜〜〜っ！と思う。
Heap Fetchesも０だし、表ブロックへのアクセスもほぼない。ほぼない。大切なので２度書きました。表ブロックへのアクセスは０にはならないようです。今のところ。こまけーことはいいんだよ的で、ワイルド。 :)　数ブロックアクセスしているのはどのようなブロックなのでしょうね

scott=> explain analyze verbose
scott-> select
scott->     t1.unique_id,
scott->     t1.item_code,
scott->     (
scott(>        select
scott(>            max(t3.unique_id)
scott(>        from
scott(>            tab31 t2 join tab311 t3
scott(>            on
scott(>                t2.sub_item_code = t3.sub_item_code
scott(>                and t3.is_delete = 0
scott(>        where
scott(>            t2.item_code = t1.item_code
scott(>            and t2.is_delete = 0
scott(>     ) current_sub_item
scott-> from
scott->     tab3 t1
scott-> where
scott->     t1.unique_id between 1 and 10000
scott->     and t1.is_delete = 0
scott->     and t1.status_code = '00'
scott-> ;
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tab3_pk on public.tab3 t1  (cost=0.00..188801.90 rows=9921 width=22) (actual time=0.255..1116.525 rows=10000 loops=1)
   Output: t1.unique_id, t1.item_code, (SubPlan 1)
   Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
   Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
   SubPlan 1
     ->  Aggregate  (cost=18.95..18.96 rows=1 width=6) (actual time=0.108..0.109 rows=1 loops=10000)
           Output: max(t3.unique_id)
           ->  Nested Loop  (cost=0.00..18.94 rows=4 width=6) (actual time=0.019..0.081 rows=40 loops=10000)
                 Output: t3.unique_id
                 ->  Index Only Scan using tab31_demo_ix on public.tab31 t2  (cost=0.00..9.13 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=10000)
                       Output: t2.item_code, t2.is_delete, t2.sub_item_code
                       Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
                       Heap Fetches: 0
                 ->  Index Only Scan using tab311_demo_ix on public.tab311 t3  (cost=0.00..9.32 rows=49 width=17) (actual time=0.012..0.036 rows=40 loops=10000)
                       Output: t3.sub_item_code, t3.is_delete, t3.unique_id
                       Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
                       Heap Fetches: 0
 Total runtime: 1121.140 ms
(18 行)

時間: 1123.194 ms

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
 relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16404 | public     | tab311  |              0 |             3 |             0 |        32015 |               0 |              0 |              0 |             0
 16398 | public     | tab31   |              0 |             1 |             0 |        30120 |               0 |              0 |              0 |             0

scott=>  select * from pg_statio_user_indexes where relname in ('tab31','tab311');
 relid | indexrelid | schemaname | relname |  indexrelname  | idx_blks_read | idx_blks_hit 
-------+------------+------------+---------+----------------+---------------+--------------
 16404 |      16414 | public     | tab311  | tab311_pk      |             0 |            0
 16398 |      16421 | public     | tab31   | tab31_demo_ix  |             0 |        30120
 16404 |      16422 | public     | tab311  | tab311_demo_ix |             0 |        32015

参考：
・INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011
・Covering Index と self-join と MySQL
・How are index-only scans implemented in InnoDB
・PostgreSQL 9.2 highlight: Index-only scans
・PostgreSQLアーキテクチャ入門（INSIGHT OUT 2011）

投稿日時 2012年8月21日 (火) 07時27分 Oracle以外のデータベース, チューニング, SQL, MySQL, PostgreSQL, JPOUG, Index Only Scan | 固定リンク | 0 | コメント (0) | トラックバック (0)

オープンソースカンファレンス2011.DB に参加した

OSC.DBって随分ご無沙汰してたと思ったら、前回参加したOSC.DBは、2007年でしたか。息子ちゃんが生まれた年です：）

OSC.DBも2008年以来、久々に開催でしたし、久々にお会いできた方々もいて :) でした。

全てのセッションには参加できなかったので、午前中だけ。

参加したセッションは

PostgreSQL 9.1 and more - 日本PostgreSQLユーザ会/永安悟史
OSSDB MySQL - 日本MySQLユーザ会/とみたまさひろ・須藤功平
Windowsで使う! Firebird !! - Firebird日本ユーザー会/木村明治と愉快な仲間

とOSC.DBではおなじみのユーザ（ー）会のセッションでした。

PostgreSQL 9.1 は Insight outで聞けなかったところ？を駆け足で聴いた感じ。
(9.1からサポートされたIndes only scanはOracleでもよく使うチューニング方法ですね。あとは、カスケードレプリケーションとか、pg_basebackup関連とか）

MySQLは、MySQL5.6の話とストレージエンジンである groongaストレージエンジン関連をこれまた駆け足で。
(InnoDBオプティマイザ統計情報の永続化、デッドロックをエラーログに出力とか、groongaストレージエンジンには、Spiderエンジンの斯波さんも関わっているとか、MariaDBにバンドルされることになったとか、http://labs.mysql.com/ とか）

３０分ぐらいだとどうしても駆け足になってしまいますよね。皆さん早口ですよね。
（いままでで一番高速な語りは、大規模Web サイトでのMySQL導入方法および事例紹介」セミナーの松信さんだったように思います。速すぎて日本語聞き取れねーと思ったのは人生初。おおげさかw）

お腹が減ってきたランチ前のセッションは、Firebird日本ユーザー会。ユーザーなんですよユーザじゃなくて。

木村さんと、林さんの軽快なトークが炸裂（アドリブだったらしいw) したFirefoxじゃなくてFirebirdの話。
（ブラジルではFirebird関連イベントで800人ぐらい軽く集まるらしい。すげー）

そして、ほぼパーフェクトな記録発見。 Thx （やっぱ、iPhoneだけだとメモれないというかつぶやききれないw のだ)
http://emasaka.blog65.fc2.com/blog-entry-952.html

投稿日時 2011年11月 8日 (火) 01時20分 SQL, Open Source, 勉強会, 日本オラクル青山センター, MySQL, PostgreSQL, Firebird | 固定リンク | 0 | コメント (0) | トラックバック (0)

鬼熱かった！ :: Insight out 2011- DB tech showcase

書くのおそくなっちゃいました　m(_ _)m

１０月１９日〜２１日に開催されたInsight out 2011- DB tech showcase
に、つまみ食いながらなんとか参加し、インサイトテクノロジーさんの鬼熱い魂を感じてきた :)

無理矢理空き時間作って参加したセッションは以下の通り。

Deep Dive into Oracle Database Patch (Oracle) - 諸橋渉
Why Why is probably the right answer (Oracle) - Tom Kyte
Rac Buffer Sharingの仕組み (Oracle) - 山下正
Effective Indexing (Oracle) - Tom Kyte
New challenges Information security technologies are facing (others) - David Maman
Developer and Indexes (Oracle) - Anjo Kolk

MySQLとかPostgreSQLとかOSSなのはOSCとかでも聴けるかなーと思いきづいたらOracle中心だったw

Effective Indexing/Developer and Indexes というセッションは予想以上だった、Indexを理解してるのって重要だよなーと改めて感じたセッションだった。

Tom Kyteさんが紹介していた書籍、「Relational Database Index Design and the Optimizers」

鬼熱い語りの山下さんのセッション、前回のOOWのUnconferenceの続編か？と思わせるような諸橋さんのセッション、つい引き込まれちゃいましたよ：）

参加者やスピーカが鬼熱いエンジニアであることは間違いないが、何と言っても、世界中からデータベースに関わる凄い方々を集めてしまうインサイトテクノロジーさんが一番、鬼熱いんじゃないかと感じた３日間だった。

来年も開催して頂きたいイベントだ。

投稿日時 2011年11月 1日 (火) 22時56分 Oracle, Oracle以外のデータベース, セキュリティ, イベント, MySQL, PostgreSQL, DB Tech Showcase | 固定リンク | 0 | コメント (0) | トラックバック (0)

Oracle	READ COMMITTED
12.1.0.2.0	ID=2を取得
12.2.0.1.0	ID=2を取得
18.3.0.0.0	ID=2を取得

GaragebandとiMovieで

検索

Twitter

オンラインショッピング

最近の記事

最近のコメント

最近のトラックバック

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2021年12月25日 (土)

2020年12月26日 (土)

2020年12月25日 (金)

2020年12月24日 (木)

2020年12月23日 (水)

2020年12月22日 (火)

2020年12月21日 (月)

2020年12月20日 (日)

2020年12月19日 (土)

2020年12月18日 (金)

2020年12月17日 (木)

2020年12月16日 (水)

2020年12月15日 (火)

2020年12月14日 (月)

2020年12月13日 (日)

2020年12月12日 (土)

2020年12月11日 (金)

2020年12月10日 (木)

2020年12月 9日 (水)

2020年12月 8日 (火)

2020年12月 7日 (月)

2020年12月 6日 (日)

2020年12月 5日 (土)

2020年12月 4日 (金)

2020年12月 3日 (木)

2020年12月 2日 (水)

2020年12月 1日 (火)

2019年11月 9日 (土)

2019年2月21日 (木)

2019年2月11日 (月)

2019年1月 7日 (月)

2014年6月21日 (土)

2012年10月21日 (日)

2012年8月21日 (火)

2011年11月 8日 (火)

2011年11月 1日 (火)