Mac De Oracle: 2024年7月

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

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その3 Tweet

Previously on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2では、限定的なようですが、PostgreSQL 16では Join Elimination が行われることを確認しました。（全く行わないとわけではないようですね。PostgreSQL。）

今日は、Join Elimination (再び) の最後に、もう一つだけ、めちゃめちゃシンプルな Join Elimination の挙動を追ってみることにします。

前回り作成した、foo/barの2表を少々作り変えて foo2 / bar2 の2表を作成します。
Oracle Database同様の表をMySQL/PostgreSQLにも作成して検証。（今回もデータの有無は影響しないので、データは未登録です）
(なおデータ型は、MySQL/PostgreSQLに合わせて変更しています。e.g. NUMBER->INTEGER, VARCHAR2 -> VARCHAR。データは未登録では統計情報は PostgreSQL/MySQLでも取得します)

COTT@orclpdb1> l
  1  CREATE TABLE foo2 (
  2    id NUMBER
  3   , note VARCHAR2(100)
  4   , PRIMARY KEY (id)
  5* )
SCOTT@orclpdb1> /

表が作成されました。

SCOTT@orclpdb1> l
  1  CREATE TABLE bar2 (
  2    id NUMBER
  3   , sq NUMBER NOT NULL
  4   , memo VARCHAR2(100)
  5   , PRIMARY KEY (id, sq)
  6* )
SCOTT@orclpdb1> /

表が作成されました。

SCOTT@orclpdb1> @gather_tab_stats foo2

PL/SQLプロシージャが正常に完了しました。

SCOTT@orclpdb1> @gather_tab_stats bar2

PL/SQLプロシージャが正常に完了しました。

上記表を作成後、以下のSQL文を実行します。これもOracle Databaseでは Join Elimination される構文にしています。2つ目の例に類似していますが、 foo2.id = bar2.id は、 1 : 0..* の関係にあります。
したがって　foo2.id = bar2.id という結合条件では Join Elimination できませんが、代わりにインラインビュー内で小細工しています。:)
bar2 WHERE 1=0 として、インライビューの結果は、常に0行となり、そもそも結合不要なので結合自体を除外してしまえ！　というシンプルな Join Elimination の確認です。
この手の文、トリッキーでわかりにくいので個人的には好みではありませんが、ERPなどでは結構、見るような気がしますw

SELECT
  foo2.id
 , foo2.note
FROM
  foo2
  LEFT OUTER JOIN
  (
     SELECT * FROM bar2 WHERE 1=0
  ) bar2
  ON
    foo2.id = bar2.id;

Oracle Database (21c) Oracle Databaseの場合、無駄な結合を除外しているのことを確認できますよね。
この挙動を知っているからこそ、動的SQLの代替としてこのような記述をする傾向があることも理解はしていますが、元の文は読みにくくなるし、ヒント文も埋め込みにくいので、素直に動的SQLにしてくれたらいいのに。。と思ったことがなんとかありますw （Oracle DatabaseのオプティマイザーはSQL文を書き換えて最適化することも多く、この手の構文とヒントによるチューニングは相性的に悪いことが多く、チューニング難易度があがることもあり、個人的には嫌いだ！！　というのもあるのですけどもw）

SCOTT@orclpdb1> l
  1  EXPLAIN PLAN FOR
  2  SELECT
  3    foo2.id
  4   , foo2.note
  5  FROM
  6    foo2
  7    LEFT OUTER JOIN
  8    (
  9       SELECT * FROM bar2 WHERE 1=0
 10    ) bar2
 11    ON
 12*     foo2.id = bar2.id
SCOTT@orclpdb1> /

解析されました。

経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2844017661

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

8行が選択されました。

PostgreSQL (16.3) ちょっと微妙な感じですが、 Join elimination ではないですね。Nested Loop Left Joinが残ってますし。
とはいえ、 bar2にはアクセスしていないことは確認できるので似たような挙動にはなるのかも。。。
興味深いですね。。。

perftestdb=> EXPLAIN verbose
perftestdb-> SELECT
perftestdb->   foo2.id
perftestdb->  , foo2.note
perftestdb-> FROM
perftestdb->   foo2
perftestdb->   LEFT OUTER JOIN
perftestdb->   (
perftestdb(>      SELECT * FROM bar2 WHERE 1=0
perftestdb(>   ) bar2
perftestdb->   ON
perftestdb->     foo2.id = bar2.id;
                            QUERY PLAN 
-------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..0.01 rows=1 width=222)
   Output: foo2.id, foo2.note
   Join Filter: (false AND (foo2.id = id))
   ->  Seq Scan on public.foo2  (cost=0.00..0.00 rows=1 width=222)
         Output: foo2.id, foo2.note
   ->  Result  (cost=0.00..0.00 rows=0 width=4)
         Output: id
         One-Time Filter: false
(8 行)

MySQL (8.0.36) MySQLもPostgreSQLと類似しています。Left Hash Joinは残っているので、Join eliminationではないと考えて良いと思います。

mysql> EXPLAIN format=tree
    -> SELECT
    ->   foo2.id
    ->  , foo2.note
    -> FROM
    ->   foo2
    ->   LEFT OUTER JOIN
    ->   (
    ->      SELECT * FROM bar2 WHERE 1=0
    ->   ) bar2
    ->   ON
    ->     foo2.id = bar2.id;
+-----------------------------------------------------------------------------------+
| EXPLAIN                                                                           |
+-----------------------------------------------------------------------------------+
| -> Left hash join (no condition)  (cost=0.25 rows=0)
    -> Table scan on foo2  (cost=0.35 rows=1)
    -> Hash
        -> Zero rows (Impossible filter)  (cost=0..0 rows=0)
 |
+-----------------------------------------------------------------------------------+
1 row in set (0.01 sec)

今回のレントゲン。いや、実行計画の比較。たまたま閃いたので試してみたのですが想像の斜め上をいく面白さでした。

まとめると、

Oracle Database
Join eliminationによる結合の最適化を行う

PostgreSQL (16以前では未確認)

特定のケースでは、Join elimination できるようだ。（全く行っていないわけではない）

MySQL

現時点8.0までは、Join eliminationは実装されていないようだ。

では、また。

Enjoy SQL! and Execution Plan!

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1で検証に利用した表の作成ログです。
Oracle Databaseのscottユーザで作成したcustomers/orders表をcsv形式でエクスポート後、Oracle Databaseのcustomers/ordersと同じ表をPostgreSQL/MySQLそれぞれで作成、最後にcsvファイルからデータをロードしています。
試してみたい方は参考にしてみてください。

ーーーcustomers/orders表作成ログーーー

Oracle Database サンプルスキーマ OE で scottユーザへ　customers/orders表へのSELECT権限を付与後、scottユーザ側で、customers/orders表をCTAS。その後、csv形式で、それぞれのデータをexportしています。

oeユーザで。

OE@orclpdb1> grant select on customers to scott;

権限付与が成功しました。

OE@orclpdb1> grant select on orders to scott;

権限付与が成功しました。

以降、scottユーザで。

SCOTT@orclpdb1> l
  1  CREATE TABLE customers AS
  2   SELECT
  3     cust.customer_id
  4     , cust.cust_first_name AS first_name
  5     , cust.cust_last_name AS last_name
  6     , cust.address
  7     , cust.phone# AS phone_number
  8   FROM
  9   (
 10     SELECT
 11       c.customer_id
 12       , c.cust_first_name
 13       , c.cust_last_name
 14       , c.cust_address.street_address AS address
 15       , cr.COLUMN_VALUE AS phone#
 16       , ROW_NUMBER()
 17           OVER (
 18             PARTITION BY c.customer_id
 19             ORDER BY c.customer_id
 20       ) AS phone_count
 21     FROM
 22       oe.customers c
 23       , TABLE(c.phone_numbers) cr
 24     ORDER BY
 25       c.customer_id
 26       , phone_count
 27   ) cust
 28   WHERE
 29*    cust.phone_count = 1
SCOTT@orclpdb1> /

表が作成されました。

SCOTT@orclpdb1> alter table customers add constraint pk_customers primary key (customer_id) using index;

表が変更されました。

SCOTT@orclpdb1> create table orders as select * from oe.orders;

表が作成されました。

SCOTT@orclpdb1> desc orders                                  
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                           NUMBER(12)
 ORDER_DATE                                NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
 ORDER_MODE                                         VARCHAR2(8)
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 ORDER_STATUS                                       NUMBER(2)
 ORDER_TOTAL                                        NUMBER(8,2)
 SALES_REP_ID                                       NUMBER(6)
 PROMOTION_ID                                       NUMBER(6)

SCOTT@orclpdb1> alter table orders add constraint pk_orders primary key (order_id) using index;

表が変更されました。

SCOTT@orclpdb1> alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers;

表が変更されました。

SCOTT@orclpdb1> desc customers
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                                        NUMBER(6)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 ADDRESS                                            VARCHAR2(40)
 PHONE_NUMBER                                       VARCHAR2(25)

SCOTT@orclpdb1> create index fk_orders_customers on orders(customer_id);

索引が作成されました。

SCOTT@orclpdb1> l
  1  select
  2    table_name
  3    , index_name
  4    , column_name
  5  from
  6    user_ind_columns
  7  where
  8    table_name in ('CUSTOMERS', 'ORDERS')
  9  order by
 10    table_name
 11    , index_name
 12*   , column_position
SCOTT@orclpdb1> /

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
CUSTOMERS                      PK_CUSTOMERS                   CUSTOMER_ID
ORDERS                         FK_ORDERS_CUSTOMERS            CUSTOMER_ID
ORDERS                         PK_ORDERS                      ORDER_ID

SCOTT@orclpdb1> r
  1  select
  2    table_name
  3    , constraint_name
  4    , constraint_type
  5    , r_owner
  6    , r_constraint_name
  7  from
  8    user_constraints
  9  where
 10    table_name in ('CUSTOMERS','ORDERS')
 11*   and constraint_type in ('P','R')

TABLE_NAME                     CONSTRAINT_NAME      CON R_OWNER              R_CONSTRAINT_NAME
------------------------------ -------------------- --- -------------------- --------------------
ORDERS                         FK_ORDERS_CUSTOMERS  R   SCOTT                PK_CUSTOMERS
CUSTOMERS                      PK_CUSTOMERS         P
ORDERS                         PK_ORDERS            P

SCOTT@orclpdb1> select count(1) from customers;

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

SCOTT@orclpdb1> select count(1) from orders;

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

SCOTT@orclpdb1> 
SCOTT@orclpdb1> !cat gather_tab_stats.sql
set verify on
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>upper('&1'),cascade=>true,no_invalidate=>false);
set verify off
undefine 1

SCOTT@orclpdb1> @gather_tab_stats.sql orders

PL/SQLプロシージャが正常に完了しました。

SCOTT@orclpdb1> @gather_tab_stats.sql customers

PL/SQLプロシージャが正常に完了しました。

SCOTT@orclpdb1> 
SCOTT@orclpdb1> !cat makecsv.sql
-- 
-- parameter 1 : table name
-- 
set feed off
set timi off
set head off
set termout off
set veri off
set markup csv on
spool loaddata_&1..csv
select * from &1; 
spo off
set markup csv off
set termout on
set head on
set feed on
set veri on
undefine 1

SCOTT@orclpdb1> @makecsv customers
SCOTT@orclpdb1> @makecsv orders
SCOTT@orclpdb1> !ls -l loaddata*.csv
-rw-r--r--. 1 oracle oinstall 19021  7月 13 19:42 loaddata_customers.csv
-rw-r--r--. 1 oracle oinstall  6064  7月 13 19:42 loaddata_orders.csv

PostgreSQL (16.3) customers/orders表を作成（主キー制約や、参照整合性制約含む）、外部キー列に索引を作成したあと、copyコマンドでcsvファイルからデータをロードして統計情報取得という内容です。

perftestdb=> 
perftestdb=> CREATE TABLE customers
perftestdb-> (
perftestdb(>   customer_id INTEGER NOT NULL
perftestdb(>   , first_name VARCHAR(20) NOT NULL
perftestdb(>   , last_name VARCHAR(20) NOT NULL
perftestdb(>   , address VARCHAR(40)
perftestdb(>   , phone_number VARCHAR(25)
perftestdb(>   , CONSTRAINT pk_customers PRIMARY KEY (customer_id)
perftestdb(> );
CREATE TABLE
perftestdb=> CREATE TABLE orders
perftestdb-> (
perftestdb(>   order_id INTEGER NOT NULL
perftestdb(>   , order_date TIMESTAMP WITH TIME ZONE NOT NULL
perftestdb(>   , order_mode VARCHAR(8)
perftestdb(>   , customer_id INTEGER NOT NULL
perftestdb(>   , order_status SMALLINT
perftestdb(>   , order_total NUMERIC(8,2)vperftestdb(>   , sales_rep_id INTEGER
perftestdb(>   , promotion_id  INTEGER
perftestdb(>   , CONSTRAINT pk_orders PRIMARY KEY (order_id)
perftestdb(>   , CONSTRAINT fk_orders_customers foreign key (customer_id) references customers
perftestdb(> );
CREATE TABLE
perftestdb=> 
perftestdb=> CREATE INDEX fk_orders_customers ON orders(customer_id);
CREATE INDEX
perftestdb=> 
perftestdb=> \copy customers(customer_id,first_name,last_name,address,phone_number) from 'loaddata_customers.csv' csv
COPY 319
perftestdb=> \copy orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) from 'loaddata_orders.csv' csv
COPY 105
perftestdb=> vacuum analyze customers;
VACUUM
perftestdb=> vacuum analyze orders;
VACUUM
perftestdb=> 
 count 
-------
   319
(1 行)

perftestdb=> select count(1) from orders;
 count 
-------
   105
(1 行)

MySQL (8.0.36) customers/orders表を作成（主キー制約や、参照整合性制約含む）、外部キー列に索引を作成後、loadコマンドでcsvファイルからデータをロードして、統計情報取得という流れになっています
(loadコマンドでワーニングでてたりしますが、今回のテストでは影響ないので気にしないでください。。(^^;;;

mysql> CREATE TABLE customers
    -> (
    ->   customer_id INTEGER NOT NULL
    ->   , first_name VARCHAR(20) NOT NULL
    ->   , last_name VARCHAR(20) NOT NULL
    ->   , address VARCHAR(40)
    ->   , phone_number VARCHAR(25)
    ->   , CONSTRAINT pk_customers PRIMARY KEY (customer_id)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE orders
    -> (
    ->   order_id INTEGER NOT NULL
    ->   , order_date TIMESTAMP NOT NULL
    ->   , order_mode VARCHAR(8)
    ->   , customer_id INTEGER NOT NULL
    ->   , order_status SMALLINT
    ->   , order_total NUMERIC(8,2)
    ->   , sales_rep_id INTEGER
    ->   , promotion_id  INTEGER
    ->   , CONSTRAINT pk_orders PRIMARY KEY (order_id)
    ->   , CONSTRAINT fk_orders_customers foreign key (customer_id) references customers (customer_id) 
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE INDEX fk_orders_customers ON orders(customer_id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

[master@localhost ~]$ mysql -u root -D perftestdb -p --local-infile=1
Enter password: 

....中略....

mysql> 
mysql> \! ls -l load*
-rw-rw-r--. 1 master master 19021  7月 14 19:26 loaddata_customers.csv
-rw-rw-r--. 1 master master  6064  7月 14 19:27 loaddata_orders.csv

mysql> load data local infile "./loaddata_customers.csv" into table perftestdb.customers fields terminated by ',' optionally enclosed by '"';
Query OK, 319 rows affected (0.10 sec)
Records: 319  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile "./loaddata_orders.csv" into table perftestdb.orders fields terminated by ',' optionally enclosed by '"';
Query OK, 105 rows affected, 140 warnings (0.05 sec)
Records: 105  Deleted: 0  Skipped: 0  Warnings: 140

mysql> analyze table  perftestdb.customers;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| perftestdb.customers | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.05 sec)

mysql> analyze table  perftestdb.orders;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| perftestdb.orders | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> select count(1) from perftestdb.customers;
+----------+
| count(1) |
+----------+
|      319 |
+----------+
1 row in set (0.02 sec)

mysql> select count(1) from perftestdb.orders;
+----------+
| count(1) |
+----------+
|      105 |
+----------+
1 row in set (0.07 sec)

投稿日時 2024年7月24日 (水) 12時00分 Oracle, チューニング, FAQ, SQL, MySQL, PostgreSQL, パフォーマンス | 固定リンク | 0 | コメント (0)

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2 Tweet

Previously on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1では、参照整合性制約を利用した Join Elimination の挙動を確認しました。

PostgreSQL、意外にも（知ってたくせに〜w）行われませんでしたね　MySQLは事前の想定通りでしたが:)

では、次の Join Elimination のテストケースを確認してみましょう。

シンプルな例で試しています。Oracle Database / PostgreSQL / MySQL それぞれに以下の2表を作成しておきます。
どちらの表も id 列が主キーですが、前回のケースのような参照整合性制約はありません。

Oracle Database同様の表をMySQL/PostgreSQLにも作成して検証。（データはあってもなくても結合の除外には影響しないためデータは登録していません）
(なおデータ型は、MySQL/PostgreSQLに合わせて変更しています。e.g. NUMBER->INTEGER, VARCHAR2 -> VARCHAR。　また、MySQL/PostgreSQLそれぞれで統計情報も取得しておきます）

SCOTT@orclpdb1> CREATE TABLE foo (id NUMBER PRIMARY KEY, note VARCHAR2(100));

表が作成されました。

SCOTT@orclpdb1> CREATE TABLE bar (id NUMBER PRIMARY KEY, memo VARCHAR2(100));

表が作成されました。

SCOTT@orclpdb1> !cat gather_tab_stats.sql
set verify on
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>upper('&1'),cascade=>true,no_invalidate=>false);
set verify off
undefine 1

SCOTT@orclpdb1> @gather_tab_stats foo

PL/SQLプロシージャが正常に完了しました。

SCOTT@orclpdb1> @gather_tab_stats bar

PL/SQLプロシージャが正常に完了しました。

前述の表を使い、以下のSQL文を実行します！
このSQL文では、bar表を外部結合していますが、SELECTリストでは bar表を参照していません。
また、foo.id = bar.id は 1 : 0..1 であるため、bar表の対象行の結合されるかどうかは問合せ結果に影響しないようにしてあります。つまり、bar表は結合しなくてもよい問合せにしてあります。。。
（さあ、どうなるでしょうね。楽しくなってきました）

SELECT
  foo.id
  , foo.note
FROM
  foo
  LEFT OUTER JOIN bar
  ON
    foo.id = bar.id;

Oracle Database (21c)
すばらしい。無駄な結合を見つけ、Join Elimination していることを確認できます。（分かってましたけどw）

SCOTT@orclpdb1> l
  1  EXPLAIN PLAN FOR
  2  SELECT
  3    foo.id
  4    , foo.note
  5  FROM
  6    foo
  7    LEFT OUTER JOIN bar
  8    ON
  9*     foo.id = bar.id
 SCOTT@orclpdb1> /

解析されました。

経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1245013993

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

8行が選択されました。

PostgreSQL (16.3)
おおおおおおおーーーーーーーっ。このケースでは、PostgreSQLも Join Elimination しています！！！！！
bar表が結合されていません！　これまた新しい気づき。メモメモw

PostgreSQLのプランナー。 Join Elimination を全く実装していないのかと思いましたが、限定的なようですが、 Join Elimination が実装されているように見えますよね。
興味深い。発展途上というところか。。（次回のPostgreSQL アンカンファレンスで聞いてみようかな）

perftestdb=> EXPLAIN verbose
perftestdb-> SELECT
perftestdb->   foo.id
perftestdb->   , foo.note
perftestdb-> FROM
perftestdb->   foo
perftestdb->   LEFT OUTER JOIN bar
perftestdb->   ON
perftestdb->     foo.id = bar.id;

                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on public.foo  (cost=0.00..0.00 rows=1 width=222)
   Output: foo.id, foo.note
(2 行)

MySQL (8.0.36)
んーーーーっ。MySQLのオプティマイザーは、Join Elimination は考慮していないように見えますよ。軽量なオプティマイザーが売りだからだろうか。。

mysql> EXPLAIN format=tree
    -> SELECT
    ->   foo.id
    ->   , foo.note
    -> FROM
    ->   foo
    ->   LEFT OUTER JOIN bar
    ->   ON
    ->     foo.id = bar.id;
+-----------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                       |
+-----------------------------------------------------------------------------------------------+
| -> Nested loop left join  (cost=0.7 rows=1)
    -> Table scan on foo  (cost=0.35 rows=1)
    -> Single-row covering index lookup on bar using PRIMARY (id=foo.id)  (cost=0.35 rows=1)
 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

海外のブログでは、実装されてない！　と言い切られているのもありましたが、PostgreSQL 16 では限定的ですが行われるようですね。（PostgreSQL 16より前のリリースってどうなんだろう。。13のままにしてたほうがおもしろかったかな。。。むむむ

ということで、次回へつづく。

投稿日時 2024年7月23日 (火) 12時00分 Oracle, チューニング, FAQ, SQL, MySQL, PostgreSQL, パフォーマンス | 固定リンク | 0 | コメント (0)

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1 Tweet

Previously on Mac De Oracle
前回はコレクション型をアクセスした場合の実行計画がどうなるのかを確認しました。

今回は少し嗜好を変えて。。

先日、Oracle Databaseの Join Elimination が行われている実行計画を、ぼーっと眺めていたのですが、、そういえば、PostgreSQL / MySQL ってどうなんだっけ？　と。気になりまして。はい。
ちょいとぐぐると、海外のブログ等では、Join Elimination - Advanced SQL tuningなど含め、PostgreSQL / MySQL 共に実装されてない。ということが書かれているのが多かったのですが、とにかく自分の目で確かめてみるか。。。ということに。。

Oracle Databaseの実行計画の話ではないですが、本「実行計画は, SQL文のレントゲン写真だ！」シリーズの番外編的な位置付けで、今回含め3回に分けた現時点の動きを確認してみます。

まずは、Oracle Databaseでの Join elimination の復習 - 無駄に結合してないですよね？

・Join Elimination（結合の排除）と参照整合性制約　/ FAQ
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination
・join elimination（結合の排除）のバリエーション / FAQ

Oracle Databaseの主要な Join Elimination 思い出しましたか？　復讐できましたよね！？　
ということで、PostgreSQL / MySQL 含め確認していきますよ〜っ！

今回は以下のバージョンのOracle Database/PostgreSQL/MySQLを利用。（PostgreSQL、やっと16にした! w

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

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

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

この検証では、 Oracle Database のサンプルスキーマの一つである OE スキーマから、cusotomersの一部の列、および orders表を元に scottスキーマへ複製し、参照整合性制約を追加（ orders表のcustomer_idからcustomers表の主キーを参照 )します。データはあってもなくても構わないのですが、customers/orders表に関しては別ネタで検証する際に利用することも兼ねてデータもロードしています。
（表や参照整合性など利用したオブジェクト、ロード等含めたログは、最後 ( 後日公開予定 / 実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その3 ) に記載しています）

さっそく、結果から見ていきましよう（面白いですよー、そうなの！！！　いう感じではありました。Oraclerからするとw

まずは、参照整合性制約で保証されていることで、結合不要と判断される Join Elimination から。 ( db tech showcase Tokyo 2013 - A35 特濃JPOUG：潮溜まりでジャブジャブ、SQLチューニングの「参照整合性制約アレルギー」でも紹介していたので、この挙動については知っているかたは多いと思います。参照整合性制約を使ってないとお目にかかることはないタイプの Join Elimination ではあるのですけどもw )

Oracle Database / PostgreSQL / MySQL それぞれに以下のような表と主キー制約、および、参照整合性制約 (orders.customer_id -> customers.customer_id)を作成します。

Oracle Databaseでの定義内容 (なおデータ型は、MySQL/PostgreSQLに合わせて変更しています。e.g. NUMBER(n)->INTEGER or SMALLINT, VARCHAR2-> VARCHAR, TIMESTAMP WITH LOCAL TIME ZONE -> TIMESTAMP WITH TIME ZONE, TIMESTAMP)

SCOTT@orclpdb1> desc customers
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                                        NUMBER(6)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 ADDRESS                                            VARCHAR2(40)
 PHONE_NUMBER                                       VARCHAR2(25)

SCOTT@orclpdb1> desc orders                                  
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                           NUMBER(12)
 ORDER_DATE                                NOT NULL TIMESTAMP(6) WITH LOCAL TIME
                                                     ZONE
 ORDER_MODE                                         VARCHAR2(8)
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 ORDER_STATUS                                       NUMBER(2)
 ORDER_TOTAL                                        NUMBER(8,2)
 SALES_REP_ID                                       NUMBER(6)
 PROMOTION_ID                                       NUMBER(6)

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
CUSTOMERS                      PK_CUSTOMERS                   CUSTOMER_ID
ORDERS                         FK_ORDERS_CUSTOMERS            CUSTOMER_ID
ORDERS                         PK_ORDERS                      ORDER_ID

TABLE_NAME                     CONSTRAINT_NAME      CON R_OWNER              R_CONSTRAINT_NAME
------------------------------ -------------------- --- -------------------- --------------------
ORDERS                         FK_ORDERS_CUSTOMERS  R   SCOTT                PK_CUSTOMERS
CUSTOMERS                      PK_CUSTOMERS         P
ORDERS                         PK_ORDERS            P

このケースで実行するSQL文はそれぞれ共通で以下を使います。

SELECT
  DISTINCT
  order_id
FROM
  orders o 
  INNER JOIN customers c
  ON o.customer_id = c.customer_id
WHERE
  order_id < 2400;

Oracle Database (21c) customers表は結合されず、join elimination されていることがわかります。inner join で保証しようとしている orders 表に存在している order_idだcustomer表に存在している顧客の注文であるということが参照整合性制約で保証されているため、結合は不要と判断されたわけです。
参照整合性制約アレルギーのみなさんには耳の痛い話ではありますが、この制約のメリットの一つは、Join Eliminationだったりします。
話は少し脱線しますが、発症すると一生ものの参照整合性制約アレルギーなのでw うまく付き合っていきたいものですよね。使いたい！と思えなくなってしまうものなので、Pros/Consをよーーーーーく考えて上で判断したい仕組みですよね。

SCOTT@orclpdb1> l
  1  EXPLAIN PLAN FOR
  2  SELECT
  3    DISTINCT
  4    order_id
  5  FROM
  6    orders o
  7    INNER JOIN customers c
  8    ON o.customer_id = c.customer_id
  9  WHERE
 10*   order_id < 2400
SCOTT@orclpdb1> /

解析されました。

経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2834288864

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

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

   1 - access("ORDER_ID"<2400)

13行が選択されました。

念のため、参照整合性制約が無い場合はどうなるか確認しておきましょう。
Join Elimination されず、customers表が結合されている状況が確認できますよね！！

SCOTT@orclpdb1> alter table orders disable constraint fk_orders_customers;

表が変更されました。

経過: 00:00:00.31
SCOTT@orclpdb1> l
  1  EXPLAIN PLAN FOR
  2  SELECT
  3    DISTINCT
  4    order_id
  5  FROM
  6    orders o
  7    INNER JOIN customers c
  8    ON o.customer_id = c.customer_id
  9  WHERE
 10*   order_id < 2400
SCOTT@orclpdb1> /

解析されました。

経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 572428435

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    46 |   552 |     3  (34)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT           |              |    46 |   552 |     3  (34)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI           |              |    46 |   552 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ORDERS       |    46 |   368 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PK_ORDERS    |    46 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_CUSTOMERS |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   4 - access("ORDER_ID"<2400)
   5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

18行が選択されました。

PostgreSQL (16.3) なんとーーー。PostgreSQLの場合は、Join elimination しないのか。（初めて知った！！！）　脳のシワが一つ増えた！

perftestdb=> explain verbose
perftestdb-> SELECT
perftestdb->   DISTINCT
perftestdb->   order_id
perftestdb-> FROM
perftestdb->   orders o 
perftestdb->   INNER JOIN customers c
perftestdb->   on o.customer_id = c.customer_id
perftestdb-> WHERE
perftestdb->   order_id < 2400;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=7.52..7.98 rows=46 width=4)
   Output: o.order_id
   Group Key: o.order_id
   ->  Merge Join  (cost=3.73..7.40 rows=46 width=4)
         Output: o.order_id
         Merge Cond: (c.customer_id = o.customer_id)
         ->  Index Only Scan using pk_customers on public.customers c  (cost=0.15..12.93 rows=319 width=4)
               Output: c.customer_id
         ->  Sort  (cost=3.58..3.70 rows=46 width=8)
               Output: o.order_id, o.customer_id
               Sort Key: o.customer_id
               ->  Seq Scan on public.orders o  (cost=0.00..2.31 rows=46 width=8)
                     Output: o.order_id, o.customer_id
                     Filter: (o.order_id < 2400)
(14 行)

Join Eliminationされていないので参照整合性制約の有無が影響しないのは自明ですが、念の為w 参照整合性制約を削除して実行計画を確認してみます。
(PostgreSQLでは参照整合性制約を無効化／有効化することができないため、dropすることで無効化しています)

一目瞭然、影響していないことがわかります。（そうなのかーーーー。まじで知らなかったこれ）

perftestdb=> alter table orders drop constraint fk_orders_customers;
ALTER TABLE
perftestdb=*> commit;
COMMIT
perftestdb=> explain verbose
perftestdb-> SELECT
perftestdb->   DISTINCT
perftestdb->   order_id
perftestdb-> FROM
perftestdb->   orders o 
perftestdb->   INNER JOIN customers c
perftestdb->   ON o.customer_id = c.customer_id
perftestdb-> WHERE
perftestdb->   order_id < 2400;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=7.52..7.98 rows=46 width=4)
   Output: o.order_id
   Group Key: o.order_id
   ->  Merge Join  (cost=3.73..7.40 rows=46 width=4)
         Output: o.order_id
         Merge Cond: (c.customer_id = o.customer_id)
         ->  Index Only Scan using pk_customers on public.customers c  (cost=0.15..12.93 rows=319 width=4)
               Output: c.customer_id
         ->  Sort  (cost=3.58..3.70 rows=46 width=8)
               Output: o.order_id, o.customer_id
               Sort Key: o.customer_id
               ->  Seq Scan on public.orders o  (cost=0.00..2.31 rows=46 width=8)
                     Output: o.order_id, o.customer_id
                     Filter: (o.order_id < 2400)
(14 行)

MySQL (8.0.36) MySQLもPostgreSQL同様に、参照整合性制約があったとしても customers表を結合しており、Join elimination は行われていません。
（海外の記事の通り、MySQL/PostgreSQLでは Join Elimination　による結合の最適化は実装されていないように見えますね。これまでのところは。）

mysql> explain format=tree
    -> SELECT
    ->   DISTINCT
    ->   order_id
    -> FROM
    ->   orders o 
    ->   INNER JOIN customers c
    ->   ON o.customer_id = c.customer_id
    -> WHERE
    ->   order_id < 2400;
+---------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=30.2..33.3 rows=46)
    -> Temporary table with deduplication  (cost=30.2..30.2 rows=46)
        -> Nested loop inner join  (cost=25.6 rows=46)
            -> Filter: (o.order_id < 2400)  (cost=9.48 rows=46)
                -> Index range scan on o using PRIMARY over (order_id < 2400)  (cost=9.48 rows=46)
            -> Limit: 1 row(s)  (cost=0.252 rows=1)
                -> Single-row covering index lookup on c using PRIMARY (customer_id=o.customer_id)  (cost=0.252 rows=1)
 |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQLでも同様に、参照整合性制約を無効化します。
（こちらも、有効／無効だけを制御することはできず、参照整合性制約を削除して無効化する必要があります。戻すどきめんどくさいのだけどもw FOREIGN_KEY_CHECKSでチェックしないという方法はあるらしい）

こちらも参照整合性制約の有無は影響していないことは明らかですね。

mysql> alter table orders drop foreign key fk_orders_customers;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain format=tree
    -> SELECT
    ->   DISTINCT
    ->   order_id
    -> FROM
    ->   orders o 
    ->   INNER JOIN customers c
    ->   ON o.customer_id = c.customer_id
    -> WHERE
    ->   order_id < 2400;
+---------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=30.2..33.3 rows=46)
    -> Temporary table with deduplication  (cost=30.2..30.2 rows=46)
        -> Nested loop inner join  (cost=25.6 rows=46)
            -> Filter: (o.order_id < 2400)  (cost=9.48 rows=46)
                -> Index range scan on o using PRIMARY over (order_id < 2400)  (cost=9.48 rows=46)
            -> Limit: 1 row(s)  (cost=0.252 rows=1)
                -> Single-row covering index lookup on c using PRIMARY (customer_id=o.customer_id)  (cost=0.252 rows=1)
 |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

いきなり違いが見えて、楽しいーーーーぞっ。 :)

海側も無茶苦茶暑いのだろうか、海風吹いてそうでもないのだろうか。。と湘南方面を見ながらw

ということで、次回へつつく。

投稿日時 2024年7月22日 (月) 12時00分 Oracle, チューニング, FAQ, SQL, MySQL, PostgreSQL, パフォーマンス | 固定リンク | 0 | コメント (0)

実行計画は, SQL文のレントゲン写真だ！ No.62 / ORDBMS機能であるコレクション型の列をアクセスする実行計画ってどうなるの？ Tweet

前回の実行計画は, SQL文のレントゲン写真だ！は2023/1だったので、Long time no seeな感じではありますが、このシリーズもネタストックが多いのでまだまだ続けていく予定です:)

前回の実行計画は, SQL文のレントゲン写真だ！のエントリーは以下
実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 / No.61 / ANSI JOINのおまけのおまけ

さて、今日のレントゲンからはどのような状況が見えるのでしょうか。。。

一から準備するのは大変なのでサンプルスキーマである、OEの customers表を利用します。
Oracle Database Release 18 / Database Sample Schemas / 4.5 OEサンプル・スキーマの表の説明

オブジェクト型の列が複数ありますね。ニコニコ（よいサンプルだw）
（今回のエントリーでは、CUST_ADDRESS、CUST_ADDRESS列を利用します）

OE@orclpdb1> desc customers
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(40)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)

利用する列に絞ってデータを覗いてみます。
（オブジェクト型を利用している2列を含む５列に絞ってあります）

OE@orclpdb1> r
  1  SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address AS address
  6    , c.phone_numbers AS phones
  7  FROM
  8    customers c
  9  WHERE
 10*   customer_id = 348

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_P PHONES
----------- -------------------- -------------------- -------------------------------------------------- ----------------------------------------------------
        348 Kelly                Lange                CUST_ADDRESS_TYP('Piazza Del Congresso 22', '36121 PHONE_LIST_TYP('+39 49 012 4373', '+39 49 083 4373')
                                                      9', 'San Giminiano', NULL, 'IT')

ひとつめは、CUST_ADDRESS_TYP型、住所情報ですね。複数ある属性から、STREET_ADDRESS　だけをアクセスすることにします

OE@orclpdb1> set linesize 80
OE@orclpdb1> desc CUST_ADDRESS_TYP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(10)
 CITY                                               VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(10)
 COUNTRY_ID                                         CHAR(2)

ふたつめは、PHONE_LIST_TYP型、複数の電話を持つことを前提としたモデルですが、最大で5個までですね。それ以上の人はどうするのでしょう？（という余計なことはここでは気にしないでw
電話番号は２つは不要なので、最初の電話番号だけを利用することします。 1顧客1電話番号（主番号）という問い合わせにすると面白そうですね。すこし難易度を上げたSELECT文のほうが面白いですし:)

OE@orclpdb1> desc PHONE_LIST_TYP
 PHONE_LIST_TYP VARRAY(5) OF VARCHAR2(25)

ところで、
オブジェクト型に出くわすと、Oracle Databaseって色々飲み込んだというか取り込んだORDBMSでもあることを思い出させてくれますw
それと同時に、ああああ〜〜っ。構文どうだっけーーーーと。（サクッと出てこないw
(ちなみに、Oracle Database 8の頃にORDBMSの機能が取り込まれた。と言う、ちょっと曖昧な記憶がありますが、おそらくその頃なので、1997年ぐらいですよねw)

ということで、オブジェクト型にアクセスしつつ、今日の実行計画というレントゲン写真を診ながら動きを確認してみましょう。
CUST_ADDRESS_TYP型の属性は単純なので修飾してあげればよいですよね。

 OE@orclpdb1> r
  1  SELECT
  2    customer_id
  3    , cust_first_name
  4    , cust_last_name
  5    , cust_address.street_address
  6  FROM
  7    customers 
  8  WHERE
  9*   customer_id = 348
  , cust_address.street_address
    *
行5でエラーが発生しました。:
ORA-00904: "CUST_ADDRESS"."STREET_ADDRESS": 無効な識別子です。

あ”〜やっちまった。オブジェクト型を扱うときは、表エイリアスが必要だったはず！

OE@orclpdb1> r
  1  SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address.street_address
  6  FROM
  7    customers c
  8  WHERE
  9*   customer_id = 348

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CUST_ADDRESS.STREET_ADDRESS
----------- -------------------- -------------------- ------------------------------
        348 Kelly                Lange                Piazza Del Congresso 22

では、cusomters表に含まれる、CUST_ADDRESS_TYP型を含むクエリーの実行計画はどうなるか...

よく見る index unique scan + table access by index rowid、ユニークキーまたは主キーによる1行だけのアクセスですね。ふむふむ。

OE@orclpdb1> r
  1  explain plan for SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address.street_address
  6  FROM
  7    customers c
  8  WHERE
  9*   customer_id = 348

解析されました。

経過: 00:00:00.00
OE@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("CUSTOMER_ID"=348)

次に、customers表に含まれるコレクションオブジェクPHONE_LIST_TYP型をアクセスしてみます。
少しずつオブジェクト型を思い出してきましたw

コレクション型は、表として扱う必要があるので、TABLEファンクションを使う必要がありますよね（思い出してきましたw　パイプラインファンクションと同じ考え方）
PHONE_LIST_TYP型はコレクション型で属性名のないVARCHAR2のVARRAY型です。TABLEファンクションを利用した場合、単一列の仮想表として返されるので、列名には、COLUMN_VALUE疑似列を使います。XML/JSONでも応用できる知識なので覚えておくと便利です。
Oracle Database Release 19 / SQL言語リファレンス / COLUMN_VALUE疑似列
c.cust_address.street_addresとして、CUST_ADDRESS_TYP型のstreet_addres属性まで指定することで通常の列のように扱えます。それほどトリッキーな構文ではないですよね。

コレクション型のアクセス方法は独特なので、いざという時に慌てないよう、日頃からSQLパズルなどで遊んでいると良いかもしれません。
ある程度使えるようになっていないと、道に迷って時間を溶かすことになるので。。。

customers表のPHONE_LIST_TYP型コレクションには複数の電話番号が含まれています（よくありますよね。固定電話番号、携帯とか複数登録させるユーザー登録画面など）
このSELECT文では、PHONE_LIST_TYP型コレクションから最初の電話番号を主電話番号として取り出し(ROW_NUMBERウィンドウ関数を利用している箇所)、1顧客N電話番号ではなく、1顧客1電話番号（主番号のみ）でリストしています。

表エイリアスは必須（前述の通り）になりますが、もう一つ、コレクション型を仮想表にするTABLEファンクションを利用しています。ここがポイント。
結合しているイメージでOK。中に抱えているコレクションを仮想表として取り出し結合していると思えばイメージしやすいはず:)　
（実行計画では、それをそのままおこなっている部分があります。実行計画の赤字部分の操作を、よーく確認しておいてください）

OE@orclpdb1> r
  1   SELECT
  2     cust.customer_id
  3     , cust.phone# AS primary_phone#
  4   FROM
  5   (
  6     SELECT
  7       c.customer_id
  8       , cr.COLUMN_VALUE AS phone#
  9       , ROW_NUMBER()
 10           OVER (
 11             PARTITION BY c.customer_id
 12             ORDER BY c.customer_id
 13       ) AS phone_count
 14     FROM
 15       customers c
 16       , TABLE(c.phone_numbers) cr
 17     ORDER BY
 18       c.customer_id
 19       , phone_count
 20   ) cust
 21   WHERE
 22*    cust.phone_count = 1

CUSTOMER_ID PRIMARY_PHONE#
----------- ---------------------------------------------------------------------------
        101 +1 317 123 4104
        102 +1 317 123 4111

....中略....

        980 +91 80 012 3837
        981 +86 10 012 3839

このコレクション型から配列の属性を取り出すSELECT文の実行計画は以下の通り。コレクション自体は表の列として保持されているのでcustomers表以外へのオブジェクトにはアクセスしませんが、TABLEファンクションで作り出した仮想表から属性を取り出す際の操作として、COLLECTION ITERATOR PICKLER FETCH が行われています。
この　COLLECTION ITERATOR PICKLER FETCH　って以前、実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCHで紹介したことがあるので覚えている方も多いと思います。テーブルファンクション特有の操作です。

また、ウィンドウ関数で必要となるソートをCUSTOMERS表の主キーをINDEX FULL SCANすることで回避していのも面白い最適化です。
ソートするよりソート済みの主キーを使ってアクセスしたほうが効率的と判断した結果、INDEX FULL SCAN -> WINDOW NOSORT という操作が行われています。
データ量が多くなるケースだとソートを避ける最適化が多く見られるものOracle Databaseのオプティマイザの特徴ですね。わかりやすくて好きです:)　
悪くない実行計画ではないでしょうか。

OE@orclpdb1> r
  1   EXPLAIN PLAN FOR SELECT
  2     cust.customer_id
  3     , cust.phone# AS primary_phone#
  4   FROM
  5   (
  6     SELECT
  7       c.customer_id
  8       , cr.COLUMN_VALUE AS phone#
  9       , ROW_NUMBER()
 10           OVER (
 11             PARTITION BY c.customer_id
 12             ORDER BY c.customer_id
 13       ) AS phone_count
 14     FROM
 15       customers c
 16       , TABLE(c.phone_numbers) cr
 17     ORDER BY
 18       c.customer_id
 19       , phone_count
 20   ) cust
 21   WHERE
 22*    cust.phone_count = 1

解析されました。

経過: 00:00:00.17
OE@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2349769165

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |   319 | 12760 | 22981   (2)| 00:00:01 |
|*  1 |  VIEW                                |              |   319 | 12760 | 22981   (2)| 00:00:01 |
|*  2 |   WINDOW NOSORT                      |              |  2605K|   201M| 22981   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                      |              |  2605K|   201M| 22981   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID      | CUSTOMERS    |   319 | 25201 |    10   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN                 | CUSTOMERS_PK |   319 |       |     1   (0)| 00:00:01 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH|              |  8168 | 16336 |    72   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter("CUST"."PHONE_COUNT"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "C"."CUSTOMER_ID" ORDER BY  NULL )<=1)

ということで、実行計画は読めてなんぼ、という感じなので、読む練習は怠らないようにしておきたいものですね。いろいろな機能が追加されてくるので。。。RDBMSっぽくないやつがRDBMSっぽい世界でどのように最適化され、実行されていくのか。。。最近はAI というかVector？もあるし

雨ばかりの東京より。雨が降らなきゃ猛暑だし。

ではまた。

投稿日時 2024年7月12日 (金) 12時00分 Oracle, チューニング, FAQ, SQL, パフォーマンス | 固定リンク | 0 | コメント (0)

帰ってきた！標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり） Tweet

Previously on Mac De Oracle
前回の癖は、SELECTリストに記述したスカラー副問合せが実行計画上どうみせられるかにも癖がでるというお話でした。

今日は副問合せは副問合せでもインラインビューの話題です。
FROM句でインラインビューを使う場合、表エイリアスを記述しなくてもエラーにはならいってのは、Oraclerを長年やってると不思議なことではないというか、そういうものだと思ってたりします。
とはいえ、可読性含や不思議な結果になったりする不具合などに遭遇するリスクを回避する意味でも表エイリアスを記述するのが手癖になっているかたも多いのではないでしょうか。私もそうですw（コーディング規約にも記載されていることは多いと思いますし）

ということで、今日の癖を見てみることにしますw

Oracle Database (23ai) インラインビューで表エイリアスを記述しなくても文法エラーにはなりません。
Oracle Database / Release 19 / SQL言語リファレンス / SELECT / table_reference::=のダイアグラムにあるように表エイリアスを省略可能です。（前述したように省略しないことの方が多いですけども、可読性向上のためにも）

SCOTT@localhost:1521/freepdb1> @inlineview_alias_name.sql
  1  SELECT
  2    empno
  3   ,ename
  4  FROM
  5  (
  6    SELECT
  7      empno
  8     ,ename
  9     ,mgr
 10    FROM
 11      emp
 12  )
 13  WHERE
 14*   mgr IS NULL

     EMPNO ENAME
---------- ----------
      7839 KING


  1  SELECT
  2    empno
  3   ,ename
  4  FROM
  5  (
  6    SELECT
  7      /*+ NO_MERGE */
  8      empno
  9     ,ename
 10     ,mgr
 11    FROM
 12      emp
 13  )
 14  WHERE
 15*   mgr IS NULL

     EMPNO ENAME
---------- ----------
      7839 KING


  1  SELECT
  2    empno
  3   ,ename
  4  FROM
  5  (
  6    SELECT
  7      empno
  8     ,ename
  9     ,mgr
 10    FROM
 11      emp
 12  ) iv_emp
 13  WHERE
 14*   mgr IS NULL

     EMPNO ENAME
---------- ----------
      7839 KING

MySQL (8.0.36) インラインビューに表エイリアスを記述しないと見事にエラーになります。コーディング規約で書くこと！なんて明記しなくても、書かないとシンタックスエラーなので書き忘れて、あ”〜っということはないわけすw
MySQL 8.0 リファレンスマニュアル / SQL ステートメント / データ操作ステートメント / SELECT ステートメント / 13.2.10.2 JOIN 句 / table_subqueryに、”table_subquery は、FROM 句では導出テーブルまたはサブクエリーとも呼ばれます。セクション13.2.11.8「導出テーブル」を参照してください。このようなサブクエリーには、サブクエリーの結果にテーブル名を指定するエイリアスを含める必要があります。”と記載されています。

mysql> SELECT
    ->   empno
    ->  ,ename
    -> FROM
    -> (
    ->   SELECT 
    ->     empno
    ->    ,ename
    ->    ,mgr
    ->   FROM
    ->     emp
    -> )
    -> WHERE
    ->   mgr IS NULL
    -> ;
ERROR 1248 (42000): Every derived table must have its own alias


mysql> SELECT
    ->   empno
    ->  ,ename
    -> FROM
    -> (
    ->   SELECT
    ->     empno
    ->    ,ename
    ->    ,mgr
    ->   FROM
    ->     emp
    -> ) iv_emp
    -> WHERE
    ->   mgr IS NULL
    -> ;
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.00 sec)


mysql> SELECT
    ->   /*+ NO_MERGE(iv_emp) */
    ->   empno
    ->  ,ename
    -> FROM
    -> (
    ->   SELECT
    ->     empno
    ->    ,ename
    ->    ,mgr
    ->   FROM
    ->     emp
    -> ) iv_emp
    -> WHERE
    ->   mgr IS NULL
    -> ;
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.02 sec)

PostgreSQL (13.14) PostgreSQLもMySQLと同じ挙動ですね。
PostgreSQL 13.1文書 / 第7章問い合わせ / 7.2. テーブル式 / 7.2.1. FROM句 / 7.2.1.3. 副問い合わせに、"7.2.1.3. 副問い合わせ派生テーブルを指定する副問い合わせは括弧で囲む必要があります。また、（7.2.1.2にあるように）必ずテーブル別名が割り当てられている必要があります。例を示します。"とあります。

perftestdb=> SELECT
perftestdb->   empno
perftestdb->  ,ename
perftestdb-> FROM
perftestdb-> (
perftestdb(>   SELECT 
perftestdb(>     empno
perftestdb(>    ,ename
perftestdb(>    ,mgr
perftestdb(>   FROM
perftestdb(>     emp
perftestdb(> )
perftestdb-> WHERE
perftestdb->   mgr IS NULL
perftestdb-> ;
ERROR:  subquery in FROM must have an alias
行 5: (
      ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

perftestdb=> SELECT
perftestdb->   empno
perftestdb->  ,ename
perftestdb-> FROM
perftestdb-> (
perftestdb(>   SELECT
perftestdb(>     empno
perftestdb(>    ,ename
perftestdb(>    ,mgr
perftestdb(>   FROM
perftestdb(>     emp
perftestdb(> ) iv_emp
perftestdb-> WHERE
perftestdb->   mgr IS NULL
perftestdb-> ;
 empno | ename 
-------+-------
  7839 | KING
(1 行)

実際、この手の癖に遭遇したOracle Databaseなど表エイリアス不要の世界の方々は、「おまえは、なにをいっているんだぁ〜」という感じで一瞬固まったあとに、あ〜〜〜〜っ！　、なるとは思いますけどw

ところで、最近のdown pour半端ない感じがします。私の子供の頃でも台風以外でみたことのないような集中的な豪雨が頻発しています。用心しても来るものはきてしまうわけですが、正しい情報を入手して早めに避難するなりしましょうね。と。実家近くの川の水位が落ち着いたのをみて安堵する。。。

Enjoy SQL! and 癖。

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

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

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

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

この癖を把握していれば、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 癖

ではまた。

投稿日時 2024年7月10日 (水) 17時00分 Oracle, チューニング, FAQ, SQL, MySQL, PostgreSQL, Oracle Database 12c release 1, パフォーマンス, Oracle Database 12c Release 2, Oracle Database 18c, Oracle Database 19c, Oracle Database 21c, Oracle Database 23c Free Developer Release | 固定リンク | 0 | コメント (0)

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

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

恒例の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の起動、停止時間の記録

投稿日時 2024年7月 8日 (月) 12時34分 Oracle, Mac, VirtualBox, MySQL, PostgreSQL, Oracle Database 21c, Oracle Database 23c Free Developer Release, Apple Silicon ARM64 | 固定リンク | 0 | コメント (0)

DTM / 6月に公開した曲 Tweet

ちょいと、遅れましたが、DTM/GarageBandにある既存のAppleLoopsだけでがんばって作る曲シリーズw

はじめてから3年目ですがw たぶん70曲ぐらい作ってるはず。だなーと。ありもののループを利用しているだけなので、お気に入りのループが固定化してワンパターンな感じになるのをなんとか避けつつw　ネタが浮ぶ限りというか飽きなければやっていこうかなとw
（普段とは使う脳の部位が違うので、頭のリフレッシュに最適w）

ということで、先月の１曲目は、5月に作った曲のリズムトラック版。
このリズムトラックを元にDub Versionを作ったり、作った曲のマイナーアップデートをしています。（ようするに意外に気に入ってるということでw）

Nu Funky Loops in Clouds ver. 1.1 Rhythm Tracks / N + 1 Loops

次は、Nu Funky Loops in Clouds ver. 1.1 をLoops in Cloudsでも使ったギターリフをやめて、ちょっと別な方向に振ったメジャーアップデート.

Nu Funky Loops in Clouds ver. 2 / N + 1 Loops

3曲目は偶に作りたくなるテクノ。YMOっぽいというかProphet-5っぽいうというか、坂本教授が好んで使っていた感じの音に近いループってAppleLoopsにほぼないので、中かな辛かったり、ピコピコ矩形波系の音も使いにくのはあるけどもで、そのなかから喫茶店にあったTVゲームっぽい音ループがあったので、Firecrackerっぽい感じと、YMOも後半のスネアの音。坂本教授の電気的音楽講座、大好きだったのでどんなエフェクター使ったとか云々が記憶に残っててw　それっぽい感じにしつつ。w

YMOPPOI LOOPS / N + 1 Loops

ではまた。

Enjoy DTM / GarageBand !

投稿日時 2024年7月 6日 (土) 05時56分 Mac, GarageBand, iMovie, DTM, AppleLoops, Apple Silicon ARM64 | 固定リンク | 0 | コメント (0)

帰ってきた！標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある) Tweet

Previously on Mac De Oracle
前回は、帰ってきた！標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖があるでした。
今回は、続編w とは言ってもMySQLが主役ではありませんし、DBMSのエンジンの癖でもなく、SQL Clentの癖のお話です。

前回利用したSQL文（SQLスクリプト化してありますが、スクリプト化せずコピペしても同じ）に以下のような位置に一行コメントを書いてみました。
MySQLの制限も考慮して、(--)の後には半角スペースを忘れずに！

MySQL (8.0.26) ふむふむ、という感じですよね。

[master@localhost ~]$ mysql -u scott -D perftestdb -p 
Enter password: 

....略...

mysql> \! cat one_more_thing.sql
SELECT 
  empno
 ,ename
FROM
  emp
WHERE
  mgr IS NULL; -- It means president of the company.
mysql> 
mysql> 
mysql> \. one_more_thing.sql
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.01 sec)

mysql>

PostgreSQL (13.14) PostgreSQLもふむふむという感じ。

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

....略...

perftestdb=> \! cat one_more_thing.sql
SELECT 
  empno
 ,ename
FROM
  emp
WHERE
  mgr IS NULL; -- It means president of the company.
perftestdb=> 
perftestdb=> 
perftestdb=> \i one_more_thing.sql
 empno | ename 
-------+-------
  7839 | KING
(1 行)

perftestdb=>

Oracle Database (23ai)

さて、お待ちかね、今回の癖の持ち主も実はOracle。といっても、SQL clientのSQL*Plusです。挙動を見てみましょう。
おやおや〜〜〜〜。結果が返らずスクリプトが終了しちゃいました。どういうこと？

[oracle@localhost ~]$ sqlplus -version

SQL*Plus: Release 23.0.0.0.0 - Production
Version 23.4.0.24.05

[oracle@localhost ~]$ sqlplus scott/tiger@localhost:1521/freepdb1

....略...

SCOTT@localhost:1521/freepdb1> !cat one_more_thing.sql
SELECT 
  empno
 ,ename
FROM
  emp
WHERE
  mgr IS NULL; -- It means president of the company.


SCOTT@localhost:1521/freepdb1> @one_more_thing.sql

SQL文はしっかりSQL*Plusのバッファにあるに、どういうことでしょう。 r または / で再実行させると、、

え、え、エラ〜だぁ！　。。。。
いったい、おまえは、何をいっているんだぁ（ジョジョ風）

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    empno
  3   ,ename
  4  FROM
  5    emp
  6  WHERE
  7*   mgr IS NULL; -- It means president of the company.
SCOTT@localhost:1521/freepdb1> /
  mgr IS NULL; -- It means president of the company.
             *
ERROR at line 7:
ORA-03048: SQL reserved word ';' is not syntactically valid following '...FROM
emp
WHERE
mgr IS NULL'
Help: https://docs.oracle.com/error-help/db/ora-03048/

これ、一行コメントの位置がまずいんです。 SQL*PLusの癖せいです。（昔からの癖ですので。。。
(;)セミコロンの後に記述されている一行コメントを該当行の上において、セミコロンより前になるように修正してみましょう！

こんどは正しい結果が返ってきました！ wwww SQL*Plusの癖もわかりにくいですね。癖の存在をしらないと。。。（マニュアルにも記載されているので参考にしてください）

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

SCOTT@localhost:1521/freepdb1> !cat one_more_thing.sql
SELECT 
  empno
 ,ename
FROM
  emp
WHERE
  -- It means president of the company.
  mgr IS NULL;

SCOTT@localhost:1521/freepdb1> @one_more_thing.sql

     EMPNO ENAME
---------- ----------
      7839 KING

参考）

Oracle Database / Release 19 / ユーザーズ・ガイドおよびリファレンス / 5 SQL*Plusでのスクリプトの使用 / 文の終了記号(ピリオド、セミコロンまたはスラッシュ)の後に、コメントを挿入しないでください。

では、最後に、Oracle純正のSQL client、実は、最近はもう一つあるんです。 Oraclerならみなさんご存知の、SQLcl です。
こちら、SQL*Plusと異なり、前述した癖がありません。MySQL/PostgreSQL純正のSQL clientと同じ位置に一行コメントを書いても怒られることはありません。なかなか難しいですね。Oracle純正でも挙動が異なるのでご注意くださいね。。。（苦笑いw

この挙動の違い、私もついさっき気づいたんですけどね。wwwww このエントリー書きながらwwwww (本当ですw)

[oracle@localhost ~]$ sql -version

SQLcl: リリース24.1.0.0 Production ビルド: 24.1.0.087.0929

[oracle@localhost ~]$ sql scott/tiger@localhost:1521/freepdb1

....略...

SCOTT@localhost:1521/freepdb1> !cat one_more_thing.sql
SELECT 
  empno
 ,ename
FROM
  emp
WHERE
  mgr IS NULL;  -- It means president of the company.

SCOTT@localhost:1521/freepdb1> 
SCOTT@localhost:1521/freepdb1> @one_more_thing.sql

   EMPNO ENAME    
________ ________ 
    7839 KING

SQL Client特有の癖もあるので本体以外でも癖には注意しましょうね。

Enjoy SQL! and 癖。

ではまた。

投稿日時 2024年7月 1日 (月) 20時25分 Oracle, FAQ, SQL, MySQL, PostgreSQL, SQL Client | 固定リンク | 0 | コメント (0)

帰ってきた！標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある Tweet

人間、なくて七癖。とか申しまして、わしゃ癖がない。といぅ人でも、七つぐらいは癖がある。と言ぅんですなぁ。
SQLも同じで、標準に対応しています。と言う割には、びっくりするぐらい癖があります。
（なぜか落語風w）

ということで、本日の癖、SQLにコメントを書くときは、どのタイプがお好きですかね？　みなさん。
複数行コメントと単一行コメント併用していますが、知ってる人しかしらない（そりゃそうだ）w
コメントを書くにも、ちょっとした癖の持ち主もいたりします。

Oracle Database (23ai)

SCOTT@localhost:1521/freepdb1> !cat sqlcomment.sql
SELECT
  /*
    foo
    bar
  */
  empno
 ,ename  --foobar
FROM
  emp
WHERE
  empno = 7900;

SCOTT@localhost:1521/freepdb1> @sqlcomment.sql

     EMPNO ENAME
---------- ----------
      7900 JAMES

PostgreSQL (13.14) Oracle Databaseと同じルールなのでそのまま使えます

perftestdb=> \! cat sqlcomment.sql
SELECT
  /*
    foo
    bar
  */
  empno
 ,ename  --foobar
FROM
  emp
WHERE
  empno = 7900;
perftestdb=> \i sqlcomment.sql
 empno | ename 
-------+-------
  7900 | JAMES
(1 行)

MySQL (8.0.36) MySQLでは一行コメント(--)の後に半角スペースが必須となっているので注意なのね。

mysql> \! cat sqlcomment.sql
SELECT
  /*
    foo
    bar
  */
  empno
 ,ename  --foobar
FROM
  emp
WHERE
  empno = 7900;
mysql> 
mysql> \. sqlcomment.sql
ERROR 1054 (42S22): Unknown column 'foobar' in 'field list'
mysql>

なかなか、いい癖もってますよね。MySQL

MySQLの場合、一行コメントとして、(#)もサポートされていて、こちらは、(#)の半角スペースは必須ではないと！！

mysql> \! cat sqlcomment.sql
SELECT
  /*
    foo
    bar
  */
  empno
 ,ename  #foobar
FROM
  emp
WHERE
  empno = 7900;
mysql> \. sqlcomment.sql
+-------+-------+
| empno | ename |
+-------+-------+
|  7900 | JAMES |
+-------+-------+
1 row in set (0.04 sec)

最後に、MySQLで有効なコメント記述を確認しておこう。(/* */)や(-- )という記述にしておけば、他のRDBMSで使う時にも楽そうではありますよね。(--)のスペースが必須なのは注意するとして。。

mysql> \! cat sqlcomment.sql
SELECT
  /*
    foo
    bar
  */
  empno  -- Do not forget "white space" following "--"
 ,ename  #foobar
FROM
  emp
WHERE
  empno = 7900;
mysql> 
mysql> 
mysql> \. sqlcomment.sql
+-------+-------+
| empno | ename |
+-------+-------+
|  7900 | JAMES |
+-------+-------+
1 row in set (0.01 sec)

おあとがよろしいようで。。。。

一行コメント(--)の後に空白を置くか置かないかってとこまで気を使う必要があるみたいなので、MySQLへ乗り換えたり、MySQLでも実行できるSQL文やスクリプトを用意する場合には注意したい部分ですすねー。ハマりそうなきもするけどw（一行コメントの部分もケアされているSQLコーディング標準がああって厳格に管理されていれば問題ないと思いますけども）

参考）

・Oracle Database / Release 19 / SQL言語リファレンス / コメント
・PostgreSQL 13.1文書 / 第4章 SQLの構文 / 4.1.5. コメント
・MySQL 8.0 リファレンスマニュアル / 言語構造 / コメント

梅雨らしい雨の降る、ちがさき、、いや、東京よりw。
ではまた。

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

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2024年7月24日 (水)

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その3 Tweet

2024年7月23日 (火)

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2 Tweet

2024年7月22日 (月)

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1 Tweet

2024年7月12日 (金)

実行計画は, SQL文のレントゲン写真だ！ No.62 / ORDBMS機能であるコレクション型の列をアクセスする実行計画ってどうなるの？ Tweet

2024年7月11日 (木)

帰ってきた！標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり） Tweet

2024年7月10日 (水)

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

2024年7月 8日 (月)

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

2024年7月 6日 (土)

DTM / 6月に公開した曲 Tweet

2024年7月 1日 (月)

帰ってきた！標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある) Tweet

帰ってきた！標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある Tweet