2024年7月10日 (水)

帰ってきた! 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる

さて、今日はまた、癖の話をしたいと思います!
今回のネタには標準があるわけではないですが、SELECTリストに記述するスカラー副問合せの実行計画上の見せ方の癖というか違いw

実は、このネタ、2020年ぐらいに、目黒方面(ご存知の方だけwww)にある某所で定期開催される内部勉強会的なLT大会で使ったネタだったのですが、そのあとゴタゴタしていて、ブログで書き漏らしていたことを、昨日ネタリストを纏めていた時に思い出した次いでに小ネタとして書いておきます。 (その時のKeynoteのタイトルページだけ載せておきますw)

20240710-141853


この癖を把握していれば、SELECTリストに記述されたスカラー副問合せチューニングするような案件に遭遇してしまったときでも何かの役に立つかもしれません。
(少なくとも実行計画を見ただけで、これはSELECTリストにスカラー副問合せがある! ということは一瞬で理解できるようになるはず。。。)

では早速見てみましょう。(Oracle Databaseではお馴染みの表とデータをMySQL/PostgreSQLでも事前に作成してあります)

SCOTT@orclpdb1> select * from dept;

DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@orclpdb1> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10

実行計画で見えるSELECT中のスカラー副問合せの位置に注目してください。(赤字にしてあります)

Oracle Databaseでは本体のクエリーより上に表示されますが、PostgreSQL/MySQLでは逆で、下に表示されます。

このような見せ方の違いが逆になるのって以前もご紹介したの覚えているでしょうか?
そう、帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画の見せ方にも癖がでるで紹介した癖ですね。
HASH JOINのBUILD/PROBEは実行計画上、Oracle DatabaseとPostgreSQL/MySQLでは順序が逆に表現されていましたよね!

これに気づけば、あなたも、道にまようこともなく実行計画を追っていけるはず!! :)

Oracle Database (21c)
(このようにスカラー副問合せ部分が性能上ネックになりそうな場合、Oracle Databaseのオプティマイザは、スカラー副問合せを結合に書き換えて最適化することがあるため、この例ではそれを無効化するNO_UNNESTヒントを利用しています。)

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> !cat scalar_subquery_plan.sql
SELECT
deptno
,dname
,(
SELECT
/*+ NO_UNNEST */
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;

SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> @scalar_subquery_plan.sql

経過: 00:00:00.18

実行計画
----------------------------------------------------------
Plan hash value: 1445953226

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 28 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_DEPT | 4 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 5 | 65 | 3 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

3 - access("EMP"."DEPTNO"=:B1)


見ての通り、PostgreSQL/MySQLはスカラー副問合せ部分の実行計画の位置がOracle Databaseのそれとは異なることがわかると思います。:)
PostgreSQL(13.14)

perftestdb=> \! cat scalar_subquery_plan.sql
EXPLAIN ANALYZE
SELECT
deptno
,dname
,(
SELECT
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;
perftestdb=> \i scalar_subquery_plan.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=5.87..5.88 rows=4 width=46) (actual time=0.246..0.247 rows=4 loops=1)
Sort Key: dept.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on dept (cost=0.00..5.83 rows=4 width=46) (actual time=0.065..0.095 rows=4 loops=1)
SubPlan 1
-> Aggregate (cost=1.19..1.20 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=4)
-> Seq Scan on emp (cost=0.00..1.18 rows=5 width=5) (actual time=0.003..0.006 rows=4 loops=4)
Filter: (deptno = dept.deptno)
Rows Removed by Filter: 10
Planning Time: 1.916 ms
Execution Time: 0.843 ms
(11 行)


MySQL(8.0.36)

mysql> \! cat scalar_subquery_plan.sql
EXPLAIN FORMAT=tree
SELECT
deptno
,dname
,(
SELECT
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;

mysql> \. scalar_subquery_plan.sql
+----------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------+
| -> Index scan on dept using PRIMARY (cost=0.65 rows=4)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: max(emp.sal) (cost=1.28 rows=1)
-> Filter: (emp.deptno = dept.deptno) (cost=1.14 rows=1.4)
-> Table scan on emp (cost=1.14 rows=14)
|
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'perftestdb.dept.deptno' of SELECT #2 was resolved in SELECT #1 |
+-------+------+------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


ただし、Oracle DatabaseだけはSELECTリストのスカラー副問合せをUNNESTして結合に書き換える最適化を行うこともあるので、実行計画だけだと元のSQL文に記述されているSELECTリスト中のスカラー副問合せに気付けないこともあります.
とはいえ、一般的には、そこに至るまでの間に、SQL文は抜き出せているでしょうから困ることはないでしょうね。(現場がリモートで、実行計画だけ送られてきた!なんてことでもなければw)

SELECTリスト中に記載したスカラー副問合せがUNNESTされてMERGE JOINに書き換えられた例(UNNESTヒント利用)
2013年、Oracle Database 12cR1で実装された最適化機能で、Scalar Subquery Unnesting Transformation (Oracle Database 12c R1 New Feature)でも説明していますので、詳しく知りたい方は参考にしてみてください。

SCOTT@orclpdb1> !cat scalar_subquery_unnest.sql
SELECT
deptno
,dname
,(
SELECT
/*+ UNNEST */
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;

SCOTT@orclpdb1> @scalar_subquery_unnest.sql

経過: 00:00:00.17

実行計画
----------------------------------------------------------
Plan hash value: 2834279049

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 145 | 11 (19)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 5 | 145 | 11 (19)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 64 | 8 (25)| 00:00:01 |
| 5 | VIEW | VW_SSQ_1 | 4 | 64 | 7 (15)| 00:00:01 |
| 6 | HASH GROUP BY | | 4 | 28 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 98 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
filter("ITEM_1"(+)="DEPT"."DEPTNO")


Enjoy SQL! and 癖

ではまた。






関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される! (23ai〜)
帰ってきた! 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(前編)
帰ってきた! 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(後編)ー 列エイリアスの扱いにも癖がある!
帰ってきた! 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
帰ってきた! 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)

| | | コメント (0)

2024年7月 8日 (月)

VirtualBox TestBuild 7.0.97r163779 (2024-07-04T18:53:02Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

2024-07-04T18:53:02Z に最新のTestBuild ( development revision 163779 ) が公開されていました。


20240708-101041

恒例のOracle Database 21c on VirtualBox TestBuild for macOS/ARM64 の起動時間の記録です。

今回は、M1/M2とも前回とほぼ同じぐらい。改善は次回に期待 :) :) :) :) :)
とはいえ、そろそろ大詰め? な感じもしなくもない。。。。

M1

oracle@Mac-Studio ~ % ./print_env.sh 

*** mac info. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r163779

起動1回目 : 168 sec
停止1回目 : 77 sec
起動2回目 : 156 sec
停止2回目 : 48 sec

M2

oracle@angelfish ~ % ./print_env.sh

*** mac info. ***
Model Name: MacBook Air
Chip: Apple M2
Total Number of Cores: 8 (4 performance and 4 efficiency)
Memory: 24 GB

*** macOS ver. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** VirtualBox ver. ***
7.0.97r163779

起動1回目 :  93 sec
停止1回目 : 33 sec
起動2回目 : 95 sec
停止2回目 : 55 sec


VMのOSバージョンなどは過去のエントリーを見ていただくとして、M1/M2 それぞれ以下のPostgreSQL/MySQL/Oracle Databaseが起動することを確認。ルーティーン :)

MySQL

[master@localhost ~]$ mysql -u scott -D perftestdb -p -h localhost -e 'select version();'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+

PostgreSQL

[master@localhost ~]$ sudo su - postgres -c 'psql -d perftestdb -U discus -p 5432 -W -h localhost -c "select version()"'
パスワード:
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

Oracle Database (21c)

[oracle@localhost ~]$ sqlplus / as sysdba @version

....中略....

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Oracle Database (23ai)

[oracle@localhost ~]$ sqlplus hr/oracle@localhost:1521/freepdb1 @version

....中略....

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05


では、次回の起動停止時間ログをお楽しみに:)
最近のペースだと、7月の後半にありそうですよね.





MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702
ySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161342
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161709
MySQL 8.0.36 , PostgreSQL 13.14, Oracle Database 21c, Oracle Database 23ai on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r162957
VirtualBox TestBuild for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 / 7.0.97r162957(2024/4/26) / 7.0.97r163029(2024/5/3)
VirtualBox TestBuild 7.0.97r163376 (2024-05-28T15:08:56Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録
VirtualBox TestBuild 7.0.97r163425 (2024-06-05T13:13:46Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録
VirtualBox TestBuild 7.0.97r163606 (2024-06-21T11:55:16Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

| | | コメント (0)

2024年6月29日 (土)

帰ってきた! 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(後編)ー 列エイリアスの扱いにも癖がある!

Previously on Mac De Oracle
前回は、帰ってきた! 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(前編)でした、

引用符にも多少の方言が存在すること、NULLのソート方法にも同様に方言が存在することを確認しました。
今回はその後編です。

引用符で囲んだ識別子(列名、表名、列エイリアス、表エイリアス)を英語だと、Quoted Identifier と記載されシノニムはあまり見かけません。(SQL-1992などでは、delimited identifier と記されている程度ですかね)日本語のマニュアル等では翻訳影響だと思いますが、多少揺れていたりします。

Oraclerのみなさんだと、引用識別子 のほうが馴染み深い日本語訳だと思いますが、他のRDBMSの日本語マニュアルでは、引用符付き識別子 と記載されていたりします。とうのは前回にも書いてますが、Oracle Database/PostgreSQL/MySQLの間でも多少表現は違ったりします。

参考)
Database Oracle / Release 19 / SQL言語リファレンス / データベース・オブジェクト名および修飾子 / データベース・オブジェクトのネーミング規則
MySQL 8.0 リファレンスマニュアル / 言語構造 / スキーマオブジェクト名
PostgreSQL 13.1文書 / 第4章 SQLの構文 / 4.1. 字句の構造


という前置きはこれぐらいで。本日のお題。列エイリアスの扱いの癖!

今日のお題は、列エイリアスの扱いの癖 を見てみます(気づかないと意外にハマりますよーーw)

今回も前回同様、お馴染みの、emp表を、Oracle Database 23ai/PostgreSQL 13.14/MySQL 8.0.36 それぞれに作成してあります。(以下はOracle Database)

SCOTT@localhost:1521/freepdb1> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SCOTT@localhost:1521/freepdb1> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10

14 rows selected.


では、前回のおさらいから、MySQLだけ癖が強い結果となりましたが、問い合わせたい結果はどれも正しいですよね!(標準はあるにはあるが、癖の多いSQLらしい。素晴らしい結果ですよねw)

Oracle Database 23ai

SCOTT@localhost:1521/freepdb1> set null [null]
SCOTT@localhost:1521/freepdb1> @quoted_identification.sql
1 SELECT
2 mgr AS "Boss's emp no."
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 "Boss's emp no."
8 ORDER BY
9* "Boss's emp no." NULLS FIRST

Boss's emp no. HEAD_COUNTS
-------------- -----------
[null] 1
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1

7 rows selected.

PostgreSQL 13.14

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \! cat quoted_identification.sql
SELECT
mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"Boss's emp no."
ORDER BY
"Boss's emp no." NULLS FIRST
;
perftestdb=> \i quoted_identification.sql
Boss's emp no. | head_counts
----------------+-------------
[null] | 1
7566 | 2
7698 | 5
7782 | 1
7788 | 1
7839 | 3
7902 | 1
(7 行)


MySQL 8.0.36

mysql> \! cat quoted_identification.sql
SELECT
mgr AS `Boss's emp no.`
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
`Boss's emp no.`
ORDER BY
`Boss's emp no.` IS NULL DESC
,`Boss's emp no.` ASC
;
mysql>
mysql> \. quoted_identification.sql
+----------------+-------------+
| Boss's emp no. | head_counts |
+----------------+-------------+
| NULL | 1 |
| 7566 | 2 |
| 7698 | 5 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 3 |
| 7902 | 1 |
+----------------+-------------+
7 rows in set (0.01 sec)


今日は、上記のクエリー列エイリアスにちょっと意地の悪い変更を行なって、その挙動の違いを見てみたいと思います。(おもしろいよ、それぞれの個性が出てて)
オリジナルでは、mgr AS "Boss's emp no." としていた列エイリアスですが、まずは、 mgr AS empno と、非識別引用子にして、かつ、emp表の列名である empno と同じ名称にしてあります。。。 AS 列エイリアス にはなっているので文法的には正しいです。。。よね。ちょっと嫌な予感はしますがw

(MySQLの場合、ORDER BY句の構文が異なります。以下、Oracle Database 23ai/PostgreSQL向け)

Before

SELECT
mgr AS Boss's emp no."
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"Boss's emp no."
ORDER BY
"Boss's emp no." NULLS FIRST;


After

SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno NULLS FIRST;

では、早速、MySQLから実験してみましょう!

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | smallint | NO | PRI | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(10) | YES | | NULL | |
| mgr | smallint | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | smallint | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.05 sec)

mysql> \! cat quoted_identification2.sql
SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno IS NULL DESC
,empno ASC
;


おおおおおおおおおーーーーーーー。想定外の結果がががががー(いや、予想してましたよw)。

mysql> \. quoted_identification2.sql
+-------+-------------+
| empno | head_counts |
+-------+-------------+
| NULL | 1 |
| 7566 | 1 |
| 7566 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7902 | 1 |
+-------+-------------+
14 rows in set, 1 warning (0.02 sec)


ワーニングがでてますね。覗いてみると、どうやら、empno が曖昧だけど、実行しておいたから。。。。と。emp表のempnoと mgr列に対する列エイリアス、どちらか曖昧だけど、とりあえず、emp表のempno列の方でやっといたでーーー。ということみたいですね。まじか。。。 AS 列エイリアス とSQLで書いてるから列エイリアスとしてみてくれんの???(ちょっとわざとらしいセリフを入れてみましたw)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1052 | Column 'empno' in group statement is ambiguous |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)


そうか、列エイリアスと表の列名が被ってて、曖昧だと。 では、 引用符を使って、これは、列エイリアスだーーーーーーとわかるように書けば良いのでは??  
ということで試してみる。

MySQLのデフォルトの引用符(`)バッククォートで囲った結果。。。だめだ。。。。引用識別子にしても、表の列側としてハンドリングされている。。。。。

mysql> \! cat quoted_identification2.sql
SELECT
mgr AS `empno`
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
`empno`
ORDER BY
`empno` IS NULL DESC
,`empno` ASC
;
mysql> \. quoted_identification2.sql
+-------+-------------+
| empno | head_counts |
+-------+-------------+
| NULL | 1 |
| 7566 | 1 |
| 7566 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7902 | 1 |
+-------+-------------+
14 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1052 | Column 'empno' in group statement is ambiguous |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>


気を取り直して、PostgreSQL ではどうなのでしょう?

perftestdb=> \d+ emp
テーブル"public.emp"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計目標 | 説明
----------+-----------------------+----------+---------------+------------+------------+----------+------
empno | numeric(4,0) | | not null | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
インデックス:
"pk_emp" PRIMARY KEY, btree (empno)
"ix_deptno" btree (deptno)
外部キー制約:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
アクセスメソッド: heap

perftestdb=> \! cat quoted_identification.sql
SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno NULLS FIRST
;

ギョギョ! サカナくんみたいな声がでますね!w MySQLと同じ挙動です!!!! なーーーーーーーんーーーーーーだってーーーーーーーっ。 MySQLとは異なりワーニングもなく、結果が想定結果を違うんだーー。というところから列エイリアスが列エイリアスと認識されていないことに気づいてあげないとならないですね。。。これだと。 難易度が上がったw
MySQL、意外と親切かも。ワーニングで教えてくれるなんて。。。

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \i quoted_identification.sql
empno | head_counts
--------+-------------
[null] | 1
7566 | 1
7566 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7782 | 1
7788 | 1
7839 | 1
7839 | 1
7839 | 1
7902 | 1
(14 行)

では、引用識別子に書き換えてみましょう。。。。。あ、まじで、PostgreSQLもMySQLと同じ挙動ですね。 列エイリアスだよーーーーーと明示しても、表の列としてのハンドリングを優先しています。。。むむむ。

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \! cat quoted_identification2.sql
SELECT
mgr AS "empno"
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"empno"
ORDER BY
"empno" NULLS FIRST
;
perftestdb=> \i quoted_identification2.sql
empno | head_counts
--------+-------------
[null] | 1
7566 | 1
7566 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7782 | 1
7788 | 1
7839 | 1
7839 | 1
7839 | 1
7902 | 1
(14 行)

perftestdb=>


では、ここまでくると、わかりますよね。 前回のエントリーで、MySQL の立ち位置にいるのは、Oracle Database 23ai でーーーす。
なお、23aiからGROUP BYに列エイリアスが書けるようになってます。

結果を見てみましょうw

おおおおおーーーーーー。まじでーーーー。
MySQL/PostgreSQLと異なる挙動。列エイリアス(非引用識別子)と表の列名が被ってる影響で列エイリアスがエイリアスとして認識されていないためエラーとして扱っていますね。興味深い。いや、実はミスに気づきやすいのかも。。。

SCOTT@localhost:1521/freepdb1> set null [null]
SCOTT@localhost:1521/freepdb1> edit quoted_identification.sql

SCOTT@localhost:1521/freepdb1> @quoted_identification.sql
1 SELECT
2 mgr AS empno
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 empno
8 ORDER BY
9* empno NULLS FIRST
mgr AS empno
*
ERROR at line 2:
ORA-00979: "MGR": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/


非識別引用子のままだと、表の列名である、empno と区別できない(AS empno と記載しても)ので、引用識別子にしてみます。PostgreSQL/Oracle Databaseの引用符は(")ダブルクォートですよね。

実行してみると。。。。。。あーーーーら不思議、MySQL/PostgreSQLとは異なり、引用識別子にしてあげることで、emp表のempno列とはことなる、列エイリアスと認識して、正しく処理しています。。。なんと。。。というか、この実装のほうが引用識別子の意味としては理解しやすくないですかね。。どうなんだろう。

SCOTT@localhost:1521/freepdb1> !cp quoted_identification.sql quoted_identification2.sql

SCOTT@localhost:1521/freepdb1> edit quoted_identification2.sql

SCOTT@localhost:1521/freepdb1> @quoted_identification2.sql
1 SELECT
2 mgr AS "empno"
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 "empno"
8 ORDER BY
9* "empno" NULLS FIRST

empno HEAD_COUNTS
---------- -----------
[null] 1
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1

7 rows selected.

Elapsed: 00:00:00.01
SCOTT@localhost:1521/freepdb1> set linesize 80
SCOTT@localhost:1521/freepdb1> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SCOTT@localhost:1521/freepdb1>


引用識別子/非引用識別子が既存の列名と同じ場合の評価の仕方は皆バラバラですねw。思いっきり実装依存の部分なので、列、表名に対するエイリアスを指定する場合には注意しましょう。
(個人的な感覚でしかないですがw

Oracle Databaseの実装のほうが直感的には理解しやすいきがします)

MySQLの場合、PostgreSQL同様に実行されますが、面白い特徴は、ワーニングという形で、"Warning 1052 | Column 'empno' in group statement is ambiguous" を示してくれることですね。MySQLでは warningのカウントに注意!!! 忘れないようにしたいですね。重要なコメントが書かれてたりしますw

この3者の中で、もっとも問題に気づきにくいのは、PostgreSQLでしょうか。。。
挙動がMySQL側に似ているので、MySQLのようにちょっと曖昧だけど、実行だけしておいたよ! 的なワーニングでも返してくれると、
MySQLのように気づきやすくなるかもしれませんね。> 各位


これだから、癖のあるSQLとの付き合いはやめられないwww


Enjoy SQL! そして、癖! もw

ではまた。





関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される! (23ai〜)
帰ってきた! 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(前編)

| | | コメント (0)

2024年6月23日 (日)

VirtualBox TestBuild 7.0.97r163606 (2024-06-21T11:55:16Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

2024-06-21に最新のTestBuildが公開されていました。


20240623-112941

恒例のOracle Database 21c on VirtualBox TestBuild for macOS/ARM64 の起動時間の記録です。

 

oracle@Mac-Studio ~ % ./print_env.sh

*** mac info. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r163606

 

M1で停止時間が2桁秒台になったほか、起動時間も改善してますね!

起動1回目 : 130 sec
停止1回目 : 66 sec
起動2回目 : 145 sec
停止2回目 : 45 sec

 

 

oracle@angelfish ~ % ./print_env.sh

*** mac info. ***
Model Name: MacBook Air
Chip: Apple M2
Total Number of Cores: 8 (4 performance and 4 efficiency)
Memory: 24 GB

*** macOS ver. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** VirtualBox ver. ***
7.0.97r163606

 

 

起動停止時間の記録をとり始めてから、M2でも最短記録ですね!!!! 

起動1回目 : 108 sec
停止1回目 : 34 sec
起動2回目 : 91 sec
停止2回目 : 49 sec

 

今回は、起動停止時間ともこれまでの最速値です。(^^) (^^) (^^)

次回のリリースが楽しみですね。。。

 

VMのOSバージョンなどは過去のエントリーを見ていただくとして、M1/M2 それぞれ以下のPostgreSQL/MySQL/Oracle Databaseが起動することを確認。

 

PostgreSQL

[master@localhost ~]$ sudo su - postgres -c 'psql -d perftestdb -U discus -p 5432 -W -h localhost -c "select version()"'

version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

 

MySQL

[master@localhost ~]$ mysql -u scott -D perftestdb -p -h localhost -e 'select version();'

+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+

 

Oracle Database 21c

[oracle@localhost ~]$ sqlplus / as sysdba @version

...略...

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

 

Oracle Database 23ai

[oracle@localhost ~]$ sql hr/oracle@localhost:1521/freepdb1 @version

...略...

BANNER_FULL
________________________________________________________________________________
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

 

VirtualBox Team がんばれ〜〜〜〜〜 :)

では、次回の起動停止時間ログをお楽しみに:)

 

 



MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702
ySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161342
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161709
MySQL 8.0.36 , PostgreSQL 13.14, Oracle Database 21c, Oracle Database 23ai on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r162957
VirtualBox TestBuild for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 / 7.0.97r162957(2024/4/26) / 7.0.97r163029(2024/5/3)
VirtualBox TestBuild 7.0.97r163376 (2024-05-28T15:08:56Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録
VirtualBox TestBuild 7.0.97r163425 (2024-06-05T13:13:46Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

 

 

| | | コメント (0)

2024年6月22日 (土)

帰ってきた! 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある!(前編)

Oracle ACE Program的に新年度に切り替わり。今期もOracle ACE Proに認定されました。:)

前置きはそれぐらいにして、今日の本題。

column expressionのaliasや、 table, view, subqueryなどのaliasを指定する際に利用することがある引用符、通常は (")ダブルクォートで囲むわけですが、そんな引用符にも癖があるというお話。
SQL-1992のドラフトではありますが以下のドキュメントを delimited identifier で検索すると見つけることができます。
( (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 )

ついでに、世間ではいろいろ言われて肩身の狭い?想いをしているかもしれない NULL. そのNULLのソートが必要になってしまった場合にも、ソートの構文に癖がある!!

ほんと、みんな、癖多すぎますよね!(w
いい感じに差し支えない単語にすると、個性 があるというか... これだからSQLは楽しいって話もありますけども。人によるかなw


Oracle Database 23ai / PostgreSQL 13.14 / MySQL 8.0.36 のそれぞれで、どうなるか見てみましょう。

それぞれのデータベースに以下のような emp表を事前に作成しておきます。Oraclerにはお馴染みの表です:)

SCOTT@localhost:1521/freepdb1> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10

14 rows selected.

まず、Oracle Database 23ai
なぜ最新にしたかと言うと、GROUP BYで alias が利用可能になった最初のリリースだからなのですw (例で利用するクエリで利用する必要があるので)
GROUP BY列の別名または位置の指定が可能に! / 23ai〜 / SQL / FAQ

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05


この例では、別名に空白などを含めているため引用符が必要になります。
Oracle Databaseの場合は昔から (") ダブルクォートですね。
一般的には引用符を必要としない記述にすることが多いのではないでしょうか。プログラムで扱うには面倒ですからね。(印字するだけの目的なら別ですが)
とはいえ、引用符の利用が必須のケースや、コーディング規約次第というところはあります。
SQL言語リファレンス/ データベース・オブジェクト名および修飾子/ データベース・オブジェクトのネーミング規則

NULLの位置が最初に来るようにソートするには、NULLS FIRSTですよね。みなさんもご存知のはず。

Oraclerのみなさんには GROUP BY でいきなりaliasを使う構文で目新しいですよね。すっきり書けるようになって嬉しい:)

SCOTT@localhost:1521/freepdb1> set null [null]
SCOTT@localhost:1521/freepdb1> @quoted_identification.sql
1 SELECT
2 mgr AS "Boss's emp no."
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 "Boss's emp no."
8 ORDER BY
9* "Boss's emp no." NULLS FIRST

Boss's emp no. HEAD_COUNTS
-------------- -----------
[null] 1
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1

7 rows selected.


次は、PostgreSQL 
Oracle Database同様、引用符が必要な別名は、ダブルクォートを利用します。( PostgreSQL 16.0 / 4.1.1. 識別子とキーワード )

NULLS FIRSTでNULLをいい感じにソートする方法も同じですね。

perftestdb=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

perftestdb=>
perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \! cat quoted_identification.sql
SELECT
mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"Boss's emp no."
ORDER BY
"Boss's emp no." NULLS FIRST;
;
perftestdb=> \i quoted_identification.sql
Boss's emp no. | head_counts
----------------+-------------
[null] | 1
7566 | 2
7698 | 5
7782 | 1
7788 | 1
7839 | 3
7902 | 1
(7 行)

さて、最後は、MySQLです。

気付いたかと思いますが、本日の癖の主役ですw

MySQLのデフォルトの引用符は、なんと、(`) バッククォートです。手癖で、ダブルクォートをタイプして、え!と一瞬固まる、Oraclerが。。> 俺だよw
( MySQL 8.0 リファレンスマニュアル / 言語構造 / スキーマオブジェクト名 )

また、NULLのソートも可能ですが、見たこともない構文でソートします。私まだよくわかってないですが、これで良いらしい。この癖に慣れる必要もありそう。。

+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
mgr AS `Boss's emp no.`
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
`Boss's emp no.`
ORDER BY
`Boss's emp no.` IS NULL DESC
,`Boss's emp no.` ASC
;
mysql>
mysql> \. quoted_identification.sql
+----------------+-------------+
| Boss's emp no. | head_counts |
+----------------+-------------+
| NULL | 1 |
| 7566 | 2 |
| 7698 | 5 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 3 |
| 7902 | 1 |
+----------------+-------------+
7 rows in set (0.01 sec)

ところで、MySQLでも、(")ダブルクォートを引用符にすることができます。
( MySQL 8.0 リファレンスマニュアル / 5.1.11 サーバー SQL モード / ANSI_QUOTES 参照のこと )

sql_modeに ANSI_QUOTESを設定することで使えるようになります。。。。あ〜っ、スッキリ。NULLのソート構文以外はw

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode = concat(@@sql_mode,',ANSI_QUOTES');
Query OK, 0 rows affected (0.01 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"Boss's emp no."
ORDER BY
"Boss's emp no." IS NULL DESC
,"Boss's emp no." ASC
;
mysql> \. quoted_identification.sql
+----------------+-------------+
| Boss's emp no. | head_counts |
+----------------+-------------+
| NULL | 1 |
| 7566 | 2 |
| 7698 | 5 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 3 |
| 7902 | 1 |
+----------------+-------------+
7 rows in set (0.00 sec)

mysql>


ANSI_QUOTESを無効にすると、GROUP BY で指定した alias 無効となり、デフォルトで有効化されているONLY_FULL_GROUP_BYのため、ERROR 1055 となります。なんとなく理解した!

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> set session sql_mode = replace(@@sql_mode,'ANSI_QUOTES,','');
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"Boss's emp no."
ORDER BY
"Boss's emp no." IS NULL DESC
,"Boss's emp no." ASC
;
mysql> \. quoted_identification.sql
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'perftestdb.emp.mgr' which is not functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by


そういえば、
英語だと、大抵は、quoted identifier と書かれていますが、日本語表記だと 引用符付き識別子とか、引用識別子、 各社のドキュメントで微妙に違いがあったりして難しいなぁ。と思ったり。
quoted identifier ってカタカタにしたら長くてタイプするの面倒、結局、Oraclerなので、引用識別子/非引用識別子 で通りしゃったりしますけど。


ではでは。
次回へつづく。






関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される! (23ai〜)

| | | コメント (0)

2024年6月15日 (土)

VirtualBox TestBuild 7.0.97r163425 (2024-06-05T13:13:46Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

さて、恒例のOracle Database 21c on VirtualBox TestBuild for macOS/ARM64 の起動時間の記録です。
2024-06-05T13:13:46Zに公開されていた。起動自体は非常に安定してきましたね。細かいところは色々あるようですが、正式リリースが待ち遠しい。

oracle@Mac-Studio ~ % ./print_env.sh 

*** mac info. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r163425


Oracle Database 21c / Mac Studio M1
微妙な差なので変化なしという感じですね。

起動1回目 : 202 sec
停止1回目 : 86 sec
起動2回目 : 163 sec
停止2回目 : 143 sec


oracle@angelfish ~ % ./print_env.sh

*** mac info. ***
Model Name: MacBook Air
Chip: Apple M2
Total Number of Cores: 8 (4 performance and 4 efficiency)
Memory: 24 GB

*** macOS ver. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** VirtualBox ver. ***
7.0.97r163425


Oracle Database 21c / MacBook Air M2
若干、前回公開されたTestBuildより遅くなってる気がしますよね。(たった二回しか起動停止させてないので、参考記録程度なわけですけども)

起動1回目 : 244 sec
停止1回目 : 136 sec
起動2回目 : 201 sec
停止2回目 : 101 sec


Virtualbox-20240605-7097r163425

その他、いつもの起動確認。
PostgreSQL 13.14 / MySQL 8.0.36 / Oracle Database 21c 及び、 23ai (M1/M2それぞれ起動)
GuestOSのバージョンなどはこれまでと同じなので省略して、それぞれのバージョン確認ログで起動確認。

[master@localhost ~]$ mysql -u scott -D perftestdb -p -h localhost -e 'select version();'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+

[master@localhost ~]$ sudo su - postgres -c 'psql -d perftestdb -U discus -p 5432 -W -h localhost -c "select version()"'
パスワード:
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

[oracle@localhost ~]$ sqlplus scott@orclpdb1 @version

...略...

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


[oracle@localhost ~]$ sql hr@192.168.1.138:1521/freepdb1 @version

...略...

BANNER_FULL
________________________________________________________________________________
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@localhost ~]$ cat version.sql
select banner_full from v$version;
exit


これ、ルーティーンになりそうw

ではまた。




MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702
ySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161342
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161709
MySQL 8.0.36 , PostgreSQL 13.14, Oracle Database 21c, Oracle Database 23ai on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r162957
VirtualBox TestBuild for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 / 7.0.97r162957(2024/4/26) / 7.0.97r163029(2024/5/3)
VirtualBox TestBuild 7.0.97r163376 (2024-05-28T15:08:56Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録


| | | コメント (0)

2024年5月31日 (金)

VirtualBox TestBuild 7.0.97r163376 (2024-05-28T15:08:56Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録

VirtualBox TestBuild 7.0.97r163376 (2024-05-28T15:08:56Z) / macOS/ARM64 Dev Previewがリリースされていたので、いつもの、起動確認と、Oracle Database 21cの起動時間比較を。
前回の記録は、VirtualBox TestBuild for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 / 7.0.97r162957(2024/4/26) / 7.0.97r163029(2024/5/3)を見てもらうとして、結果的には大きくな変化なし!。

頑張って! :) 

oracle@Mac-Studio ~ % ./print_env.sh 

*** mac info. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r163376

Oracle Database 21c Mac Studio M1

起動1回目 : 217sec
停止1回目 : 153sec
起動2回目 : 186sec
停止2回目 : 119sec


oracle@angelfish ~ % ./print_env.sh 

*** mac info. ***
Model Name: MacBook Air
Chip: Apple M2
Total Number of Cores: 8 (4 performance and 4 efficiency)
Memory: 24 GB

*** macOS ver. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** VirtualBox ver. ***
7.0.97r163376

Oracle Database 21c MacBook Air M2

起動1回目 : 138sec
停止1回目 : 108sec
起動2回目 : 128sec
停止2回目 : 83sec



20240531-81713

その他、いつもの起動確認。
PostgreSQL 13.14 / MySQL 8.0.36 / Oracle Database 21c 及び、 23ai 、それぞれ、VirtualBox TestBuild 7.0.97r163376 (024-05-28T15:08:56Z)でも起動した! (このあたりはマジで安定してきた)

oracle@Mac-Studio ~ % ./print_env.sh 

*** mac info. ***
ProductName: macOS
ProductVersion: 14.5
BuildVersion: 23F79

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r163376

[master@localhost ~]$ cat /etc/*release*
Oracle Linux Server release 8.5
NAME="Oracle Linux Server"
VERSION="8.5"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.5"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.5"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:5:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.5
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.5
Red Hat Enterprise Linux release 8.5 (Ootpa)
Oracle Linux Server release 8.5
cpe:/o:oracle:linux:8:5:server
[master@localhost ~]$


PostgreSQL 13.14 - 起動した!

[master@localhost ~]$ sudo su - postgres
[postgres@localhost ~]$ psql -d perftestdb -U discus -p 5432 -W -h localhost
パスワード:
psql (13.14)
"help"でヘルプを表示します。

perftestdb=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

perftestdb=> exit


MySQL 8.0.36 - 起動した!

[master@localhost ~]$ mysql -u scott -D perftestdb -p -h 192.168.1.125
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.01 sec)

mysql> exit
Bye


Oracle Database 21c - 起動した!

[oracle@localhost ~]$ cat /etc/*release*
Oracle Linux Server release 8.4
NAME="Oracle Linux Server"
VERSION="8.4"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.4"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.4"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:4:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.4
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.4
Red Hat Enterprise Linux release 8.4 (Ootpa)
Oracle Linux Server release 8.4
cpe:/o:oracle:linux:8:4:server
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SYS@ORCLCDB> exit


Oracle Database 23ai - 起動した!

[oracle@localhost ~]$ cat /etc/*release*
Oracle Linux Server release 8.9
NAME="Oracle Linux Server"
VERSION="8.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.9"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.9
Red Hat Enterprise Linux release 8.9 (Ootpa)
Oracle Linux Server release 8.9
cpe:/o:oracle:linux:8:9:server
[oracle@localhost ~]$ sql scott/@192.168.1.138:1521/freepdb1


SQL> select banner_full from v$version;

BANNER_FULL
_______________________________________________________________________________
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL>


これが、本当の5月最後の投稿ということでw

では、また。





MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r160702
ySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161342
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA5r161709
MySQL 8.0.36 , PostgreSQL 13.14, Oracle Database 21c, Oracle Database 23ai on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r162957
VirtualBox TestBuild for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 / 7.0.97r162957(2024/4/26) / 7.0.97r163029(2024/5/3)

| | | コメント (0)

2024年5月29日 (水)

帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される! (23ai〜)

ちょっと意地悪してみました。

 

前回のエントリで、以下のようにindex only scanにできるBOOLEAN型の単列索引を作成したのを覚えていますか?

 

SCOTT@freepdb1> create index ix_bool_example2 on example2(b1);

Index created.

SCOTT@freepdb1> set autot trace exp stat
SCOTT@freepdb1> select count(1) from example2 where b1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1159143718

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
586 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

この状態で、述語のBOOLEAN列(索引列)に暗黙型変換が発生する意地悪をしてみました。
BOOLEAN列を数値として計算した後に、falseと比較しているので、TO_NUMBER() の後に、TO_BOOLEAN()されています。当然、索引は使えなくなるので index only scan から table access fullに変わっています! (狙い通りですね。こんなことしないと思いますけどもw

 

で、みなさん、SQL*Plusのautoraceに見慣れない情報が出力されているのに気づきませんか!?

 

そう、Oracle Database 23ai free developerでは、SQL*PlusのautotraceでSQL Analysis reportが表示され、index range scan できるよう、述語の書き換えをご検討くさい! とレコメンドされています!(赤字部分)

 

このメッセージカスタマイズできたりしたらw 「チッ、少しは考えたSQL書けよな〜。」と上から目線のメッセージに変えてみたいw(無理でしょうけどもw) 

ということで、おまけでした!

 

SCOTT@freepdb1> select b1 from example2 where (b1 - 1) = false;

18 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1894430233

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXAMPLE2 | 1 | 1 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

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

1 - filter(TO_BOOLEAN(TO_NUMBER("B1")-1)=FALSE)

SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------

1 - SEL$1 / "EXAMPLE2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"B1"

Statistics
----------------------------------------------------------
9 recursive calls
6 db block gets
11 consistent gets
0 physical reads
1012 redo size
914 bytes sent via SQL*Net to client
133 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed

 

この例だと的確なレコメンドしてるんだけど、もっとムズイやつだとどうなるんだろうね。誰か本番で使って結果公開して欲しい :)

 

Enjoy SQL!

 

 



関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)

 

 

| | | コメント (0)

帰ってきた! 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る(後編)

Previously on Mac De Oracle
前回は、BOOLEAN型に使える値の差異について、Oracle Database 23ai/PostgreSQL/MySQLで比較してみました。 なかなか癖がありますよね。それぞれw Oracle Database 23aiは、Db2やSnowflakeの仕様に類似していそうでしたよね。(未検証ですが)
ということで、5月もラストスパートwで、BOOLEANネタ後編で締めたいと思いますw

 

BOOLEAN/BOOL型どちらでもOKということなので、一応確認しておきます。

 

Oracle Database


SCOTT@freepdb1> create table sample3 (b1 boolean, b2 bool);

Table created.

SCOTT@freepdb1> desc sample3
Name Null? Type
----------------------------------------- -------- ----------------------------
B1 BOOLEAN
B2 BOOLEAN

SCOTT@freepdb1> drop table sample3 purge;Table dropped.

 

 

PostgreSQL


perftestdb=> create table sample3 (b1 boolean, b2 bool);
CREATE TABLE
perftestdb=> \d sample3
テーブル"public.sample3"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
----+---------+----------+---------------+------------
b1 | boolean | | |
b2 | boolean | | |

perftestdb=> drop table sample3;
DROP TABLE
perftestdb=>

 

MySQL


mysql> create table sample3 (b1 boolean, b2 bool);
Query OK, 0 rows affected (0.27 sec)

mysql> desc sample3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b1 | tinyint(1) | YES | | NULL | |
| b2 | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> drop table sample3;
Query OK, 0 rows affected (0.10 sec)

 

 

次は、データサイズ。
MySQL/PostgreSQLそれぞれマニュアルにて、1バイトと記載されていますが、Oracle Databaseでは発見できす(仕方ないので直接確認してみますw)
PostgreSQL - https://www.postgresql.jp/document/13/html/datatype-boolean.html MySQL BOOLEAN = TINYINT - https://dev.mysql.com/doc/refman/8.0/ja/other-vendor-data-types.html MySQL TINYINTのサイズ - https://dev.mysql.com/doc/refman/8.0/ja/integer-types.html

 

Oracleも1バイトのようですね!

 


SCOTT@freepdb1> set null [null]
SCOTT@freepdb1> col dump(b1) for a30
SCOTT@freepdb1> col vsize(b1) for 9990
SCOTT@freepdb1> select dump(b1),vsize(b1) from example2

DUMP(B1) VSIZE(B1)
------------------------------ ---------
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1

...中略...

Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
NULL [null]

33 rows selected.

 

 

BOOLEAN型を文字、数値へキャストするとどうなるでしょう?

 

Oracle Database BOOLEAN型の数値へのキャストは、1 (true) / 0 (false)、文字列へのキャストは、'TRUE' / 'FALSE' になるようですね。CHAR型へのキャストの場合、空白埋めされるようですね(マニュアルより)。

 


SCOTT@freepdb1> select id,b1,to_number(b1),memo from example2 order by id;

ID B1 TO_NUMBER(B1) MEMO
---------- ----------- ------------- ----------
1 TRUE 1 TRUE
2 TRUE 1 true
3 TRUE 1 True
4 TRUE 1 ON
5 TRUE 1 on
6 TRUE 1 On
7 TRUE 1 YES
8 TRUE 1 yes
9 TRUE 1 Yes
10 TRUE 1 T
11 TRUE 1 t
12 TRUE 1 Y
13 TRUE 1 y
14 TRUE 1 1
15 TRUE 1 0.01
16 TRUE 1 -0.01
17 TRUE 1 10
18 TRUE 1 -12
19 FALSE 0 FALSE
20 FALSE 0 false
21 FALSE 0 False
22 FALSE 0 OFF
23 FALSE 0 off
24 FALSE 0 Off
25 FALSE 0 NO
26 FALSE 0 no
27 FALSE 0 No
28 FALSE 0 F
29 FALSE 0 f
30 FALSE 0 N
31 FALSE 0 n
32 FALSE 0 0
33 [null] [null] null

33 rows selected.

SCOTT@freepdb1> select id,b1,to_char(b1),memo from example2 order by id;

ID B1 TO_CHA MEMO
---------- ----------- ------ ----------
1 TRUE TRUE TRUE
2 TRUE TRUE true
3 TRUE TRUE True
4 TRUE TRUE ON
5 TRUE TRUE on
6 TRUE TRUE On
7 TRUE TRUE YES
8 TRUE TRUE yes
9 TRUE TRUE Yes
10 TRUE TRUE T
11 TRUE TRUE t
12 TRUE TRUE Y
13 TRUE TRUE y
14 TRUE TRUE 1
15 TRUE TRUE 0.01
16 TRUE TRUE -0.01
17 TRUE TRUE 10
18 TRUE TRUE -12
19 FALSE FALSE FALSE
20 FALSE FALSE false
21 FALSE FALSE False
22 FALSE FALSE OFF
23 FALSE FALSE off
24 FALSE FALSE Off
25 FALSE FALSE NO
26 FALSE FALSE no
27 FALSE FALSE No
28 FALSE FALSE F
29 FALSE FALSE f
30 FALSE FALSE N
31 FALSE FALSE n
32 FALSE FALSE 0
33 [null] [null] null

33 rows selected.

 

PostgreSQL PostgreSQLは想定の範囲内ですね。'true'/'false', 1/0 になるようですね


perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=>
perftestdb=> select id,b1,b1::varchar,memo from example2 order by id;
id | b1 | b1 | memo
----+--------+--------+-------
1 | t | true | TRUE
2 | t | true | true
3 | t | true | True
4 | t | true | ON
5 | t | true | on
6 | t | true | On
7 | t | true | YES
8 | t | true | yes
9 | t | true | Yes
10 | t | true | T
11 | t | true | t
12 | t | true | Y
13 | t | true | y
14 | t | true | 1
17 | t | true | 10
19 | f | false | FALSE
20 | f | false | false
21 | f | false | False
22 | f | false | OFF
23 | f | false | off
24 | f | false | Off
25 | f | false | NO
26 | f | false | no
27 | f | false | No
28 | f | false | F
29 | f | false | f
30 | f | false | N
31 | f | false | n
32 | f | false | 0
33 | [null] | [null] | null
(30 行)

perftestdb=> select id,b1,b1::integer,memo from example2 order by id;
id | b1 | b1 | memo
----+--------+--------+-------
1 | t | 1 | TRUE
2 | t | 1 | true
3 | t | 1 | True
4 | t | 1 | ON
5 | t | 1 | on
6 | t | 1 | On
7 | t | 1 | YES
8 | t | 1 | yes
9 | t | 1 | Yes
10 | t | 1 | T
11 | t | 1 | t
12 | t | 1 | Y
13 | t | 1 | y
14 | t | 1 | 1
17 | t | 1 | 10
19 | f | 0 | FALSE
20 | f | 0 | false
21 | f | 0 | False
22 | f | 0 | OFF
23 | f | 0 | off
24 | f | 0 | Off
25 | f | 0 | NO
26 | f | 0 | no
27 | f | 0 | No
28 | f | 0 | F
29 | f | 0 | f
30 | f | 0 | N
31 | f | 0 | n
32 | f | 0 | 0
33 | [null] | [null] | null
(30 行)

 

MySQL TINYINTのまま文字列に変換されちゃいますね。これはこれで癖が強いといえば強い

 


mysql> select id,b1,cast(b1 as char),memo from example2 order by id;
+----+------+------------------+-------+
| id | b1 | cast(b1 as char) | memo |
+----+------+------------------+-------+
| 1 | 1 | 1 | TRUE |
| 2 | 1 | 1 | true |
| 3 | 1 | 1 | True |
| 14 | 1 | 1 | 1 |
| 15 | 0 | 0 | 0.01 |
| 16 | 0 | 0 | -0.01 |
| 19 | 0 | 0 | FALSE |
| 20 | 0 | 0 | false |
| 21 | 0 | 0 | False |
| 32 | 0 | 0 | 0 |
| 33 | NULL | NULL | null |
+----+------+------------------+-------+

 

 

 

Boolean型は索引にも利用できるので、念の為、Boolean型の検索でリテラルとして使える記述がどう評価されるか確認しておきましょうね。
暗黙のキャストやらなんやらあるのかないのか。。。索引に含めた場合、キャストされて索引として利用できないないケースなど把握しておくと便利ですよー。:)

 

Oracle Database 23ai Oracleの場合、簡易な方法として、SQL*Plusのオートトレースを利用し、 Predicate Information(述語情報)を見て filterの際に、関数が適用され内部的にキャストされてしまうかどうかで判断していくことにします。
ついでに、boolean列に非ユニーク索引(単一列索引)を作成しておき、索引が利用されることも見ておきます。

 

全てのケースは実施していませんが、大文字小文字は無視される前提で、小文字で検証しています。
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0以外の数値は全て、TRUEと解釈される。e.g. -1,10,0.1,-1.5 など) TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の数値

 

FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される) FALSE, 'OFF', 'NO', 'F', 'N', 0

 

以上が、OracleのBOOELAN型で利用できるリテラル値なので、

 

true, 'on', 'yes', 't', 'y', 1, 0以外の数値(10, 0.1, -2, -0.1)、全てにおいて、内部的にTRUEとしてハンドリングされていることがわかります。
(キャストされているわけではなく、TRUEとして評価されていることが、predicate information及び、index only scanになっているところでも判断できます)

 

ますます、 true/false, nullだけ使えばいいじゃんって感じがしますね。

 

以下、検証ログ


SCOTT@freepdb1> create index ix_bool_example2 on example2(b1);

Index created.

SCOTT@freepdb1> select b1 from example2 where b1 = true;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 't';

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 'y';

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 'yes';

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 'on';

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 1;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 10;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = 0.1;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

05:53:05 SCOTT@192.168.1.23:1521/freepdb1> select b1 from example2 where b1 = -2;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

SCOTT@freepdb1> select b1 from example2 where b1 = -0.1;

18 rows selected.

...中略...

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("B1"=TRUE)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets

...中略...

18 rows processed

 

 

PostgreSQL

 

TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、正の整数のみTRUEと解釈され、負の整数や少数はエラーとなる。 -1, 0.1, -1.5などはエラー) TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の正の整数のみ(ただし、booleanへのキャストが必要)

 

FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される) FALSE, 'OFF', 'NO', 'F', 'N', 0

 

true, 'on', 'yes', 't', 'y', 1, 0以外の正の整数のみ(ただし、booleanへのキャスト必須)(10) 全てにおいて、内部的にTRUEとしてハンドリングされていることがわかります。
(キャストされているわけではなく、Index Cond:及び、、hintで矯正していますが、Index Scan uging..になっているところでも判断できます)

 

0以外の正の整数以外(0.1, -2, -0.1)は、エラーになっていることが確認できます。

 


perftestdb=> create index ix_bool_example2 on example2(b1);
CREATE INDEX
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = true;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.093..0.098 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 13.020 ms
Execution Time: 8.653 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'on';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.038..0.046 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.259 ms
Execution Time: 0.142 ms
(5 行)

perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'yes';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.021..0.026 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.168 ms
Execution Time: 0.087 ms
(5 行)

perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 't';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.022..0.030 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.173 ms
Execution Time: 0.117 ms
(5 行)

perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'y';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.017..0.021 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.119 ms
Execution Time: 0.066 ms
(5 行)

perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 1;
ERROR: operator does not exist: boolean = integer
行 1: ...example2 ix_bool_example2) */ b1 from example2 where b1 = 1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 1::boolean;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.010..0.015 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.125 ms
Execution Time: 0.067 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 0.1::boolean;
ERROR: cannot cast type numeric to boolean
行 1: ..._bool_example2) */ b1 from example2 where b1 = 0.1::boolean;
^
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = -2::boolean;
ERROR: operator does not exist: - boolean
行 1: ... ix_bool_example2) */ b1 from example2 where b1 = -2::boolea...
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = -0.1::boolean;
ERROR: cannot cast type numeric to boolean
行 1: ...bool_example2) */ b1 from example2 where b1 = -0.1::boolean;


暗黙型変換ではないですが、暗黙変換同様に関数が利用された場合は、索引が利用できなくなることが確認できますよね。

 


perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where (b1::integer - 1)::boolean = false;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.example2 (cost=10000000000.00..10000000001.52 rows=20 width=1) (actual time=0.027..0.041 rows=15 loops=1)
Output: b1
Filter: (NOT (((example2.b1)::integer - 1))::boolean)
Rows Removed by Filter: 15
Planning Time: 0.171 ms
Execution Time: 2.737 ms
(6 行)

 

 

 

MySQL

 

TRUEとして解釈されるリテラル値
TRUE, 1のみ。(エラーにはならないが、0以外の数字は数字として整数として登録さえるので要注意)

 

FALSEとして解釈されるリテラル値
FALSE, 0のみ

 

なので、true,1 は 内部的に  true と扱われていることがわかります。
'yes'はやなりエラーですね。

 


mysql> create index ix_bool_example2 on example2(b1);
Query OK, 0 rows affected (1.28 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain analyze select b1 from example2 where b1 = true;
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=true) (cost=0.651 rows=4) (actual time=0.04..0.0441 rows=4 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> explain analyze select b1 from example2 where b1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=1) (cost=0.651 rows=4) (actual time=0.0165..0.0203 rows=4 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

 

文字列だとエラーになるかと思いきや。ワーニング扱いですね。文字列がトランケートされた結果、述語が b1 = 0 に変更されてしまっています。これはダメですね。


mysql> explain analyze select b1 from example2 where b1 = 'yes';
+--------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=0) (cost=0.851 rows=6) (actual time=0.0179..0.0222 rows=6 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'yes' |
+---------+------+-----------------------------------------+
1 row in set (0.01 sec)

 

整数の正の値、負の値。予想通りそのまま、数値として比較されています。エラーにならないのでこれは避けた方が良さそう


mysql> explain analyze select b1 from example2 where b1 = 100;
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=100) (cost=0.35 rows=1) (actual time=0.0214..0.0214 rows=0 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain analyze select b1 from example2 where b1 = -100;
+------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=-(100)) (cost=0.35 rows=1) (actual time=0.0147..0.0147 rows=0 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

 

少数の場合もエラーにはなりません。述語は (Impossible WHERE) なことになってますね。これらも避けた方が良さそう。


mysql> explain analyze select b1 from example2 where b1 = 0.1;
+--------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------+
| -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=281e-6..281e-6 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain analyze select b1 from example2 where b1 = -0.1;
+--------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------+
| -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=176e-6..176e-6 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

最後に、意地悪な検証を。

 

こんなことやらないと思いますけども、MySQL独特な挙動ですね。index only scanのまま、filterしてますね。TINYINTである影響なのでしょうね。面白いですが真似しないようにw。 この手の作り込んだら探すの大変そうだし。(データ積んでしっかり検証すると炙り出せるとは思うけどw)


mysql> explain analyze select b1 from example2 where (b1 - 1) = false;
+-------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((example2.b1 - 1) = false) (cost=1.35 rows=11) (actual time=1.78..1.8 rows=4 loops=1)
-> Covering index scan on example2 using ix_bool_example2 (cost=1.35 rows=11) (actual time=0.234..0.251 rows=11 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

 

 


BOOLEAN型の癖、総まとめ

 

BOOLEAN/BOOLどちらでも定義可能 (Oracle/PostgreSQL/MySQL)
(MySQLは内部的には、TINYINT型なのでそれ由来の癖がある)

 

データサイズは、1バイト (Oracle/PostgreSQL/MySQL)

 

利用可能なリテラル値(INSERT/UPDATEでセットする場合) TRUEとして扱われる値 TRUE 
(Oracle/PostgreSQL/MySQL, No case sensitive)

 

'ON'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'YES'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'T'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'Y'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

1
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

 

0以外の数値
(Oracle 少数及び負の整数も含む)
(PostgreSQL 正の整数のみ)
(MySQL 少数以下を切り捨てた整数として扱われるのでCHECK制約などで保護した方が安全なのではないだろうか。内部的にTINYINTである影響だろう)

 

 

FALSEとして扱われる値 FALSE
(Oracle/PostgreSQL/MySQL, No case sensitive)

 

'OFF'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'NO'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'F'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

'N'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

 

0
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

 


利用可能なリテラル値(述語で利用する場合) TRUEとして扱われる値 TRUE 
(Oracle/PostgreSQL/MySQL, No case sensitive)

 

'ON'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'ON' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

'YES'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'YES' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

'T'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'T' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い 。 sql_mode挙動が変わったりするのかは未確認)

 

'Y'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'Y' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

1
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

 

0以外の数値
(Oracle 少数及び負の整数も含む)
(PostgreSQL 正の整数のみ)
(MySQL 内部的にTINYINTである影響だろうか、エラーとはならず、数値として扱われたり、切り捨てられたりする。避けた方が良い。 e.g. WHERE boolean列 = 100や、WHERE boolean列 = 0.1 など。 sql_mode挙動が変わったりするのかは未確認)

 

FALSEとして扱われる値 FALSE
(Oracle/PostgreSQL/MySQL, No case sensitive)

 

'OFF'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'OFF' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

'NO'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'NO' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

'F'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'F' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

'N'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'N' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)

 

0
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

 

全体的に、true/false (null) を使うように規約で制限したり、CHECK制約で保護しておいた方が混乱を避けられるのではないだろうか。。

 

 


癖ありすぎ! w w

 

では、また。

 



関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る

 

 

 

| | | コメント (0)

帰ってきた! 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る

PostgreSQL/MySQLには実装済みだった Boolean型がやっとOracle Database 23aiで追加されました。長ーい間実装されなかったデータ型なので、number型を使ったりして代替されていたわけですが、23ai以降は普通に使えるってことですね。
ただ、タイトルの通り、標準はあるものの、それぞれの実装には癖があります!!!!w


どのような癖があるのか、知っておきましょう。 (兼、自分用メモ)
Oracle/PostgreSQL/MySQLで違いを見てみます。

Oracle Database 23ai

SCOTT@freepdb1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

PostgreSQL
例によってw こちらの都合により、13.14を使っています。

perftestdb=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

MySQL

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



データ登録時、どの値がBOOLEANとして扱われるのかマニュアルから拾った値を元に確認しておきたいと思います。キャストが必要だったりする場合には、暗黙型変換とか気にしておいた方が良いですからね。索引にも使えるデータ型ですし。。。

Oracle Database 23ai
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0以外の数値は全て、TRUEと解釈される。e.g. -1,10,0.1,-1.5 など)
TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の数値

FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される)
FALSE, 'OFF', 'NO', 'F', 'N', 0

この仕様、Db2SnowflakeのBOOLEAN型と同じように見えますね。(試してないですけどw)

マニュアルに記載されている通りの挙動。。。数値を指定した時の解釈もマニュアル通りなのですが、こんな使い方したくないですよねw 挙動確認なので試してはいますが。。。。

SCOTT@freepdb1> @boolean_literals.sql

Table dropped.
Table created.
1 row created.
1 row created.

...中略...

1 row created.
1 row created.
Commit complete.

ID B1 MEMO
---------- ----------- ----------
1 TRUE TRUE
2 TRUE true
3 TRUE True
4 TRUE ON
5 TRUE on
6 TRUE On
7 TRUE YES
8 TRUE yes
9 TRUE Yes
10 TRUE T
11 TRUE t
12 TRUE Y
13 TRUE y
14 TRUE 1
15 TRUE 0.01
16 TRUE -0.01
17 TRUE 10
18 TRUE -12
19 FALSE FALSE
20 FALSE false
21 FALSE False
22 FALSE OFF
23 FALSE off
24 FALSE Off
25 FALSE NO
26 FALSE no
27 FALSE No
28 FALSE F
29 FALSE f
30 FALSE N
31 FALSE n
32 FALSE 0
33 [null] null

33 rows selected.




PostgreSQL 13.14
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、正の整数のみTRUEと解釈され、負の整数や少数はエラーとなる。 -1, 0.1, -1.5などはエラー)
TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の正の整数のみ(ただし、booleanへのキャストが必要)

FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される)
FALSE, 'OFF', 'NO', 'F', 'N', 0

Oracleに類似していますが、数値の扱いが微妙に違いますね。

perftestdb=> \i boolean_literals.sql
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1

...中略...

INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:boolean_literals.sql:19: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(14, 1, '1');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
psql:boolean_literals.sql:21: ERROR: column "b1" is of type boolean but expression is of type numeric
行 1: insert into example2 values(15, 0.01, '0.01');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:22: ERROR: cannot cast type numeric to boolean
行 1: insert into example2 values(15, 0.01::boolean, '0.01');v ^
psql:boolean_literals.sql:23: ERROR: column "b1" is of type boolean but expression is of type numeric
行 1: insert into example2 values(16, -0.1, '-0.01');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:24: ERROR: cannot cast type numeric to boolean
行 1: insert into example2 values(16, -0.1::boolean, '-0.01');
^
psql:boolean_literals.sql:25: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(17, 10, '10');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
psql:boolean_literals.sql:27: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(18, -12, '-12');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:28: ERROR: operator does not exist: - boolean
行 1: insert into example2 values(18, -12::boolean, '-12');
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
INSERT 0 1
INSERT 0 1

...中略...

INSERT 0 1
INSERT 0 1
psql:boolean_literals.sql:44: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(32, 0, '0');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
INSERT 0 1
Null表示は"[null]"です。
id | b1 | memo
----+--------+-------
1 | t | TRUE
2 | t | true
3 | t | True
4 | t | ON
5 | t | on
6 | t | On
7 | t | YES
8 | t | yes
9 | t | Yes
10 | t | T
11 | t | t
12 | t | Y
13 | t | y
14 | t | 1
17 | t | 10
19 | f | FALSE
20 | f | false
21 | f | False
22 | f | OFF
23 | f | off
24 | f | Off
25 | f | NO
26 | f | no
27 | f | No
28 | f | F
29 | f | f
30 | f | N
31 | f | n
32 | f | 0
33 | [null] | null
(30 行)

perftestdb=>



MySQL 8.0.32
実データ型がTINYINTであるためですが、
数値の場合0,1以外ではエラーは出ないようで注意が必要ですよね。CHECK制約で保護するとかだろうか。。。

TRUEとして解釈されるリテラル値
TRUE, 1のみ。(エラーにはならないが、0以外の数字は数字として整数として登録さえるので要注意)

FALSEとして解釈されるリテラル値
FALSE, 0のみ

Oracle/PostgreSQLとも違い、最低限に絞ってるって感じですが、数値を指定した時の挙動には要注意ですね。内部の型はTINYINTなので。。。文字列を全て受け付けないのは、これもTINYINTである影響ですかね?

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> \. boolean_literals.sql
Query OK, 0 rows affected (0.05 sec)

...中略...

Query OK, 1 row affected (0.01 sec)
ERROR 1366 (HY000): Incorrect integer value: 'ON' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'on' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'On' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'YES' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'yes' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Yes' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'T' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 't' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Y' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'y' for column 'b1' at row 1
Query OK, 1 row affected (0.01 sec)

...中略...

Query OK, 1 row affected (0.01 sec)
ERROR 1366 (HY000): Incorrect integer value: 'OFF' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'off' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Off' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'NO' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'no' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'No' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'F' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'f' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'N' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'n' for column 'b1' at row 1
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)

+----+------+-------+
| id | b1 | memo |
+----+------+-------+
| 1 | 1 | TRUE |
| 2 | 1 | true |
| 3 | 1 | True |
| 14 | 1 | 1 |
| 15 | 0 | 0.01 |
| 16 | 0 | -0.01 |
| 17 | 10 | 10 |
| 18 | -12 | -12 |
| 19 | 0 | FALSE |
| 20 | 0 | false |
| 21 | 0 | False |
| 32 | 0 | 0 |
| 33 | NULL | null |
+----+------+-------+
13 rows in set (0.00 sec)



長くなりそうなので、次回へ続く!w 

今回のまとめ

Oracle > PostgreSQL > MySQLのような感じで使える値の種類はサブセットになってる感じですね。 なので、true/false , nullだけ使ってればどこに行っても問題はなさそう。。ではあります。

Oracle Database 23ai/MySQL/PostgreSQL共通
BOOLEAN/BOOL型として定義可能。
(ただし、MySQLでは実態はTINYINTなので、TINYINT由来の癖があるので注意

TRUEとして扱われる値
TRUE 
(Oracle/PostgreSQL/MySQL, No case sensitive)

'ON'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'YES'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'T'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'Y'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

1
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

0以外の数値
(Oracle 少数及び負の整数も含む)
(PostgreSQL 正の整数のみ)
(MySQL 少数以下を切り捨てた整数として扱われるのでCHECK制約などで保護した方が安全なのではないだろうか。内部的にTINYINTである影響だろう)


FALSEとして扱われる値
FALSE
(Oracle/PostgreSQL/MySQL, No case sensitive)

'OFF'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'NO'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'F'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

'N'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)

0
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)

全体的に、true/false (null) を使うように規約等、場合によってはCHECK制約で保護しておいた方が、何かと混乱を避けられるのではないだろうか。。



補足)

この検証に利用したスクリプト
Oracle Database 23ai

SCOTT/freepdb1> !cat boolean_literals.sql
drop table if exists example2;
create table example2 (id number primary key, b1 boolean, memo varchar2(10));

-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(18, -12, '-12');

-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');

-- NULL
insert into example2 values(33, null, 'null');

commit;

-- check
set NULL [null]
select * from example2 order by id;


PostgreSQL 13.14

erftestdb=> \! cat boolean_literals.sql
drop table if exists example2;
create table example2 (id integer primary key, b1 boolean, memo varchar(10));

-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(14, 1::boolean, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(15, 0.01::boolean, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(16, -0.1::boolean, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(17, 10::boolean, '10');
insert into example2 values(18, -12, '-12');
insert into example2 values(18, -12::boolean, '-12');

-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');
insert into example2 values(32, 0::boolean, '0');

-- NULL
insert into example2 values(33, null, 'null');

-- check
\pset null [null]
select * from example2 order by id;


MySQL 8.0.32

mysql> \! cat boolean_literals.sql
drop table if exists example2;
create table example2 (id integer primary key, b1 boolean, memo varchar(10));

-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(18, -12, '-12');

-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');

-- NULL
insert into example2 values(33, null, 'null');

-- check
select * from example2 order by id;


ここまで見た感じ、true/false, 必要があれば null を使う側のルールとしておくと、みんな幸せになれる気がするよね。BOOLEAN型って。


参考資料)
Oracle Database 23 / 開発者ガイド / 外部データ型
"https://docs.oracle.com/cd/F82042_01/lnoci/data-types.html#GUID-D69455D9-CE01-44CC-B5A9-E541C7774805

Oracle Database 23 / SQL言語リファレンス / ブールデータ型
https://docs.oracle.com/cd/F82042_01/sqlrf/Data-Types.html#GUID-285FFCA8-390D-4FA9-9A51-47B84EF5F83A

Oracle Database 23 / SQL言語リファレンス / Oracleの組込みデータ型
https://docs.oracle.com/cd/F82042_01/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

PostgreSQL / データ型 / 論理値データ型
https://www.postgresql.jp/document/13/html/datatype-boolean.html

マニュアルによると、PostgreSQLのBOOLEAN型もサイズは1バイト
PostgreSQL 13 / 8.6. 論理値データ型
https://www.postgresql.jp/document/13/html/datatype-boolean.html

MySQL その他のデータベースエンジンのデータ型の使用  - BOOLEAN/BOOL
https://dev.mysql.com/doc/refman/8.0/ja/other-vendor-data-types.html

MySQL 数値型の記憶域要件 - TINYINT
https://dev.mysql.com/doc/refman/8.0/ja/storage-requirements.html#data-types-storage-reqs-numeric

MySQLのBOOLEANはTINYINT型で1バイトということですね。Boolean型のサイズはMySQL/Oracle/PostgreSQLどれも1バイト。
MySQL 8.0 / 11.1.6 Boolean Literals
https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html



長くなりそうなので、次回へ続くw

こういうことで、この手の SQLの癖w なかなか厳しいなw  Enjoy SQL!






関連エントリー
標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守!
標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル!
標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日?
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る><
標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠(有名なやつ)
標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか?
標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
帰ってきた! 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
帰ってきた! 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
帰ってきた! 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
帰ってきた! 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る

| | | コメント (0)

2024年5月19日 (日)

構文図で見る、SELECT文の構文拡張の歴史 w

2015年にイベント開催のコメントおまけで、Oracle DatabaseのSQLからSELECT文の構文拡張の歴史をOracle Database 7.3と12.1版のマニュアルに記載されている構文図の長さを使って可視化したことがあったのですが、覚えているでしょうか?w (多分、忘れてますよねw)

 

JPOUG> SET EVENTS 20151017 を開催します!

 

Oracle Database 23aiがリリースされてSQLシンタックスの拡張をマニュアルをつらつら読んでて思ったのですが、色々拡張されてますよね!
ということで、Oracle Database 7.3 / 8i 8.1.6 / 12cR1 12.1 / 23ai それぞれのSELECT文の拡張の歴史を構文図の長さを使って、今一度、可視化して残しておこうと思います。

 

みなさん、SQLの進化というか拡張に、追いつけていますよね。。。ね。。。。ね!? (大変ですけどもw)

 

各バージョンのSELECT文の構文図のソースは以下です。みなさんもマニュアルのページ数の増加や構文図の拡張に着目しつつ追ってみるのも楽しいかもしれません。  

 

Oracle7 Server SQL Reference Manual - SELECT
Oracle8i SQL Reference Release 2 (8.1.6) - SELECT and Subqueries
Database SQL Language Reference 12c 12.1 - SELECT
SQL Language Reference - Oracle Database 23ai - SELECT

 

 

 

Oracle Database 9i, 10g, 11gのダイアグラムは端折ってますが、これぐらい差分があったほうが、インパクトがあっていいかなぁ。と思いあえて載せていません :)

 

12cR1以降長すぎてこれぐらい小さくしないと収まりませんw 

 

オチも何もないですが、現場からは以上です! (なお、実際のリリース年は多少前後しているかもしれません)
Select78i12cr123ai

 

Enjoy SQL!

 

ではまた。

| | | コメント (0)

2024年5月16日 (木)

GROUP BY列の別名または位置の指定が可能に! / 23ai〜 / SQL / FAQ

23c 改め、23ai になった Oracle Database 23aiですが、SQLの使い勝手の改善がいくつか。
有名なのは、from dual を書かなくても良くなったこと。ですが、有名すぎるのであえて書きません!w

ということで、ちょっとマイナーだけど便利ですよね! という 「GROUP BY列の別名または位置の指定が可能に! / 23ai〜」 というお話。

参考 ー Oracle Databaseリリース23cの変更点
https://docs.oracle.com/cd/F82042_01/sqlrf/Changes-in-This-Release-for-Oracle-Database-SQL-Language-Reference.html

自分用アップデートメモでもありますw

まずこれまでのおさらいということで、Oracle Database 21c EEで挙動を確認しておきます。

SCOTT@orclpdb1> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


SCOTT@orclpdb1> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

group by 別名指定。。見事にエラーになります!

SCOTT@orclpdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 year
8 ORDER BY
9* year
SCOTT@orclpdb1> /
year
*
行7でエラーが発生しました。:
ORA-00904: "YEAR": 無効な識別子です。


group by 位置指定。。これも間違いなくエラーです!。。。

SCOTT@orclpdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 1
8 ORDER BY
9* year
SCOTT@orclpdb1> /
TO_CHAR(hiredate,'YYYY') AS year
*
行2でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。


ということで、これまではこんな面倒が書き方してたわけです。はい。。。

SCOTT@orclpdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 TO_CHAR(hiredate,'YYYY')
8 ORDER BY
9* year
SCOTT@orclpdb1> /

YEAR HIRED
------------ ----------
1980 1
1981 10
1982 1
1987 2


これまでは、こんな感じ。まあ面倒臭いですよね。


しかーーーーし、23ai以降では、そんな面倒は忘れてください。

SCOTT/freepdb1> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05


SCOTT/freepdb1> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.


group by 別名指定。おおおおおおーーぅ!  できた。

SCOTT/freepdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 year
8 ORDER BY
9* year
SCOTT/freepdb1> /

YEAR HIRED
---- ----------
1980 1
1981 10
1982 1
1987 2


group by 位置指定....? 

SCOTT/freepdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 1
8 ORDER BY
9 year
10*
SCOTT/freepdb1> /
TO_CHAR(hiredate,'YYYY') AS year
*
ERROR at line 2:
ORA-03162: "HIREDATE": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE
Help: https://docs.oracle.com/error-help/db/ora-03162/


なんと、23ai free developerではデフォルトで無効化されてる!?。。。とは言っても、位置指定は、order by でも使わない場合が多いので、デフォルトオフでも影響はないですかね。一般的には。

SCOTT/freepdb1> show parameter group_by_position_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
group_by_position_enabled boolean FALSE

SCOTT/freepdb1> alter session set group_by_position_enabled = true;

Session altered.

SCOTT/freepdb1> show parameter group_by_position_enabled

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
group_by_position_enabled boolean TRUE

SCOTT/freepdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 1
8 ORDER BY
9 year
10*
SCOTT/freepdb1> /

YEAR HIRED
---- ----------
1980 1
1981 10
1982 1
1987 2


strong>最後に、従来の面倒臭い構文の確認。

SCOTT/freepdb1> l
1 SELECT
2 TO_CHAR(hiredate,'YYYY') AS year
3 , COUNT(1) AS hired
4 FROM
5 emp
6 GROUP BY
7 TO_CHAR(hiredate,'YYYY')
8 ORDER BY
9* year
SCOTT/freepdb1> /

YEAR HIRED
---- ----------
1980 1
1981 10
1982 1
1987 2

Enjoy SQL!

では、また

| | | コメント (0)

2024年5月 4日 (土)

MySQL 8.0.36 , PostgreSQL 13.14, Oracle Database 21c, Oracle Database 23ai on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r162957

Oracle Database 23c 改め、23ai となったのと、4/26日版 VirtualBox Test builds Development snapshotsがリリースされていた(だが、5/3版がすでに公開されていたw)
https://www.virtualbox.org/wiki/Testbuilds


20240503-95842


20240503-85117

M2 MBAでの確認情報だけで、M1はどうよ?とこともあるので、今回は、M1側で検証してみた。(本当はこちらでバシバシ使いたいわけですけども、まだまだ遅いので確認だけw)

*** mac info. ***
ProductName: macOS
ProductVersion: 14.4.1
BuildVersion: 23E224

*** maxOS ver. ***
Model Name: Mac Studio
Chip: Apple M1 Ultra
Total Number of Cores: 20 (16 performance and 4 efficiency)
Memory: 64 GB

*** VirtualBox ver. ***
7.0.97r162957
(VirtualBox 4/26 Test-Builds Developer Snapshot)


公開されたばかりの、Oracle Database 23ai Free DeveloperのVirtualBox pre-Build VMでの確認。
https://www.oracle.com/database/technologies/databaseappdev-vm.html

Oracle Database 23ai Free Developer (VirtualBox pre-build VM) / Oracle Linux 8.9

相変わらずもっさり感は強いもののなんとか起動します! 

[oracle@localhost ~]$ cat /etc/*release
Oracle Linux Server release 8.9
NAME="Oracle Linux Server"
VERSION="8.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.9"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.9
Red Hat Enterprise Linux release 8.9 (Ootpa)
Oracle Linux Server release 8.9

[oracle@localhost ~]$ sql hr/oracle@192.168.1.138:1521/freepdb1

SQLcl: 土 5月 04 01:14:44 2024のリリース24.1 Production

Copyright (c) 1982, 2024, Oracle. All rights reserved.

接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> select banner_full from V$version;

BANNER_FULL 
_______________________________________________________________________________
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05から切断されました
[oracle@localhost ~]$ sudo service oracle status
[sudo] oracle のパスワード:

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 04-MAY-2024 01:26:55

Copyright (c) 1991, 2024, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date 04-MAY-2024 00:49:21
Uptime 0 days 0 hr. 37 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "176858bcadf91ba6e0630100007f7de0" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "FREE" has 2 instance(s).
Instance "FREE", status UNKNOWN, has 1 handler(s) for this service...
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "FREEXDB" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
Service "freepdb1" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$ sudo service oracle stop
Stopping oracle (via systemctl): [ OK ]


続いて、いつもの 21c。 こちらは、 Oracle Linux 8.4に載せています

Oracle Database 21c EE / Oracle Linux 8.4

Oracle Database 21c EE / Oracle Linux 8.4
[master@localhost ~]$ cat /etc/*release
Oracle Linux Server release 8.4
NAME="Oracle Linux Server"
VERSION="8.4"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.4"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.4"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:4:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.4
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.4
Red Hat Enterprise Linux release 8.4 (Ootpa)
Oracle Linux Server release 8.4

[master@localhost ~]$ sudo su - oracle
[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 04-5月 -2024 08:54:58

Copyright (c) 1991, 2021, Oracle. All rights reserved.

/opt/oracle/product/21c/dbhome_1/bin/tnslsnrを起動しています。お待ちください...

TNSLSNR for Linux: Version 21.0.0.0.0 - Production
システム・パラメータ・ファイルは/opt/oracle/homes/OraDBHome21cEE/network/admin/listener.oraです。
ログ・メッセージを/opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名 LISTENER
バージョン TNSLSNR for Linux: Version 21.0.0.0.0 - Production
開始日 04-5月 -2024 08:55:00
稼働時間 0 日 0 時間 0 分 0 秒
トレース・レベル off
セキュリティ ON: Local OS Authentication
SNMP OFF
パラメータ・ファイル /opt/oracle/homes/OraDBHome21cEE/network/admin/listener.ora
ログ・ファイル /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 土 5月 4 08:55:08 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

アイドル・インスタンスに接続しました。

08:55:10 SYS@ORCLCDB> startup
ORACLEインスタンスが起動しました。

Total System Global Area 1073740720 bytes
Fixed Size 9694128 bytes
Variable Size 910163968 bytes
Database Buffers 41943040 bytes
Redo Buffers 7081984 bytes
In-Memory Area 104857600 bytes
データベースがマウントされました。
データベースがオープンされました。
08:58:23 SYS@ORCLCDB> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


経過: 00:00:00.32
08:58:36 SYS@ORCLCDB> exit
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 土 5月 4 08:58:46 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

08:58:55 SYS@ORCLCDB> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
09:00:51 SYS@ORCLCDB>


最後に、安定して起動しているMySQL/PostgreSQL
MySQL 8.0.36 and PostgreSQL 13.14 / Oracle Linux 8.5

[master@localhost ~]$ cat /etc/*release
Oracle Linux Server release 8.5
NAME="Oracle Linux Server"
VERSION="8.5"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.5"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.5"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:5:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.5
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.5
Red Hat Enterprise Linux release 8.5 (Ootpa)
Oracle Linux Server release 8.5

MySQL 8.0.36

Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2024-05-03 05:34:51 EDT; 53min ago
Main PID: 1119 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 22947)
Memory: 34.5M
CGroup: /system.slice/mysqld.service
└─1119 /usr/libexec/mysqld --basedir=/usr

5月 03 05:34:04 localhost.localdomain systemd[1]: Starting MySQL 8.0 database server...
5月 03 05:34:07 localhost.localdomain mysql-check-socket[1034]: Socket file /var/lib/mysql/mysql.sock exists.
5月 03 05:34:08 localhost.localdomain mysql-check-socket[1034]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically.
5月 03 05:34:51 localhost.localdomain systemd[1]: Started MySQL 8.0 database server.
[master@localhost ~]$
[master@localhost ~]$
5月 03 05:34:47 localhost.localdomain systemd[1]: Started PostgreSQL 13 database server.

[master@localhost ~]$ mysql -u scott -D perftestdb -p -h 192.168.1.125
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> exit
Bye
[master@localhost ~]$ sudo service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[master@localhost ~]$ sudo service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Fri 2024-05-03 06:30:03 EDT; 8s ago
Process: 7704 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
Process: 1119 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=0/SUCCESS)
Main PID: 1119 (code=exited, status=0/SUCCESS)
Status: "Server shutdown complete"

5月 03 05:34:04 localhost.localdomain systemd[1]: Starting MySQL 8.0 database server...
5月 03 05:34:07 localhost.localdomain mysql-check-socket[1034]: Socket file /var/lib/mysql/mysql.sock exists.
5月 03 05:34:08 localhost.localdomain mysql-check-socket[1034]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically.
5月 03 05:34:51 localhost.localdomain systemd[1]: Started MySQL 8.0 database server.
5月 03 06:29:56 localhost.localdomain systemd[1]: Stopping MySQL 8.0 database server...
5月 03 06:30:03 localhost.localdomain systemd[1]: mysqld.service: Succeeded.
5月 03 06:30:03 localhost.localdomain systemd[1]: Stopped MySQL 8.0 database server.

PostgreSQL 13.14

[master@localhost ~]$ sudo service postgresql-13 status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/postgresql-13.service.d
└─local.conf
Active: active (running) since Fri 2024-05-03 05:34:47 EDT; 54min ago
Docs: https://www.postgresql.org/docs/13/static/
Main PID: 1343 (postmaster)
Tasks: 9 (limit: 22947)
Memory: 19.7M
CGroup: /system.slice/postgresql-13.service
├─1343 /usr/pgsql-13/bin/postmaster -D /pg/pgdata/data
├─1411 postgres: logger
├─1591 postgres: checkpointer
├─1592 postgres: background writer
├─1593 postgres: walwriter
├─1594 postgres: autovacuum launcher
├─1596 postgres: archiver
├─1597 postgres: stats collector
└─1598 postgres: logical replication launcher

5月 03 05:34:25 localhost.localdomain systemd[1]: Starting PostgreSQL 13 database server...
5月 03 05:34:33 localhost.localdomain postmaster[1343]: 2024-05-03 05:34:33.304 EDT [1343] LOG: redirecting log output to logging collector process
5月 03 05:34:33 localhost.localdomain postmaster[1343]: 2024-05-03 05:34:33.304 EDT [1343] HINT: Future log output will appear in directory "log".

[postgres@localhost ~]$ psql -d perftestdb -U discus -p 5432 -W -h localhost
パスワード:
psql (13.14)
"help"でヘルプを表示します。

perftestdb=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

perftestdb=> exit
[postgres@localhost ~]$ exit
ログアウト
[master@localhost ~]$ sudo service postgresql-13 stop
Redirecting to /bin/systemctl stop postgresql-13.service
[master@localhost ~]$ sudo service postgresql-13 status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/postgresql-13.service.d
└─local.conf
Active: inactive (dead) since Fri 2024-05-03 06:31:04 EDT; 6s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 1343 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1343 (code=exited, status=0/SUCCESS)

5月 03 05:34:25 localhost.localdomain systemd[1]: Starting PostgreSQL 13 database server...
5月 03 05:34:33 localhost.localdomain postmaster[1343]: 2024-05-03 05:34:33.304 EDT [1343] LOG: redirecting log output to logging collector process
5月 03 05:34:33 localhost.localdomain postmaster[1343]: 2024-05-03 05:34:33.304 EDT [1343] HINT: Future log output will appear in directory "log".
5月 03 05:34:47 localhost.localdomain systemd[1]: Started PostgreSQL 13 database server.
5月 03 06:31:03 localhost.localdomain systemd[1]: Stopping PostgreSQL 13 database server...
5月 03 06:31:04 localhost.localdomain systemd[1]: postgresql-13.service: Killing process 1411 (postmaster) with signal SIGKILL.
5月 03 06:31:04 localhost.localdomain systemd[1]: postgresql-13.service: Succeeded.
5月 03 06:31:04 localhost.localdomain systemd[1]: Stopped PostgreSQL 13 database server.

残る課題は処理速度改善だろうとは思うのですが、頑張って欲しいですね:)

そして、ゴールデンウィークなのに、真夏みたいな気温とか、異常すぎる。。。
体調管理に気を遣いつつも、こんな気候だと、まじで今年の夏はどうなることやら。。。。

ではまた。



Oracle Linux 8 and MySQL 8.0.32 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r160167
MySQL 8.0.32 , PostgreSQL 13.4 and Oracle Database 21c on Oracle Linux 8 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r160702
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r161342
MySQL 8.0.32 , PostgreSQL 13.6 and Oracle Database 21c on Oracle Linux 8.5 on VirtualBox for Apple Silicon Test Build 7.0.97_BETA r161709

| | | コメント (0)

2023年9月 1日 (金)

Oracle Database 23c Free Developer Releaseの”_optimizer_max_permutations” parameterの設定値について

Previously on Mac De Oracle..
前回のお話は、帰ってきた! 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出るでした。Oracle/PostgreSQL/MySQLそれぞれ、再帰問合せで試してみましたが、興味深い違いが出ました。思わず、PostgreSQLのソースコード読み始めてしまいましたwwww

ということで、本日は、その流れで気づいてOracle 23c Freeのひみつ!


たまたま気づいたのですけど、 Oracle Database 23c Free Developer Release で、
under scored parameterの "_optimizer_max_permutations" って、 300なんですね。随分少ない設定にされていました。

これ

5! (120) < 6! (720) ということを意味するので、6表以上の結合では、通常のOracleより実行計画をミスりやすいということを意味しています。
(なぜなのでしょう、Free Developer Releaseだからではないか? というコメントももらいましたが、そうなのですかねぇ。でもそうかもしれないw。理由はなぜなのかわからんので何とも言えないですね)
なので、多数の結合を伴うSQLの検証には注意した方が良いですね。もしくは、通常の 2000 ぐらいまであげて試すとかしておいた方が良いかもね。
FreeのDeveloper Releaseだから 300 になっているのか確認できる資料は見つからなかったけど。


念の為、調べてみるとやはり、23c Free Developer Releaseのみ少なく設定されていました。

SYS@free> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

parameter name parameter value
-------------------------------- ------------------------------
_optimizer_max_permutations 300
_optimizer_search_limit 5



BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

parameter name parameter value
------------------------------ ------------------------------
_optimizer_max_permutations 2000
_optimizer_search_limit 5


Oracle 23c の正式版がリリースされたら皆さんも、2000になっているか確認しましょうね!(多分、2000になっていると思うけどw)

東京の8月が毎日真夏日だったなんで、話題。来年は毎日、猛暑日じゃなければ良いのですけども。。。
残暑厳しい東京からお送りしました。

では、また。

| | | コメント (0)