DB

空間インデックス

DB

MySQL (8.0.29) https://dev.mysql.com/doc/refman/8.0/ja/creating-spatial-indexes.htmlhttps://dev.mysql.com/doc/refman/8.0/ja/spatial-function-reference.html drop table tab1;create table tab1 ( col1 int primary key, col2 varchar(255), col3 g…

count文の実行計画

DB

MySQL、Oracle、SQL Server ->インデックスがあればフルスキャンされるPostgreSQL -> 常にテーブルのフルスキャン MySQL (8.0.29) drop table tab1 ;create table tab1(col1 int not null,col2 int, col3 int); drop procedure proc1; delimiter //create pr…

セミジョイン/アンチジョイン

DB

作業概要:「in」「exists」「not in」「not exists」それぞれについて結合キーカラムが nullable の場合とnot nullの場合の実行計画を確認した 結果:「in」「exists」「not exists」については、結合キーカラムのnull属性に関係なくサブクエリのテーブルで…

ヒント句を複数指定した場合の挙動

DB

MySQL,Oracle,PostgreSQLともに最初に指定したヒント句が有効 MySQL (8.0.29) パーサーは、SELECT, UPDATE, INSERT, REPLACE ステートメントおよび DELETE ステートメントの最初のキーワードの後にオプティマイザヒントコメントを認識します。 drop table ta…

select句別名をgroup byやorder byで使用できるか確認

DB

OracleとSQL Serverはgroup byで別名を使用できない MySQL (8.0.29) drop table tab1 ;create table tab1(col1 int,col2 int);insert into tab1 values(1,1);insert into tab1 values(1,2);insert into tab1 values(2,1);select * from tab1; select col1,co…

プロシージャのオーバーロード

DB

PostgreSQLはオーバーロード可能Oracleはパッケージ内のプロシージャならオーバーロード可能MySQLとSQL Serverはオーバーロード不可 MySQL (8.0.29) サポートされていない模様念のため確認 drop procedure proc1; delimiter //create procedure proc1( in pa…

プロシージャの再帰呼び出し

DB

4DBともにプロシージャの再帰実行は可能ただし、MySQLはサーバパラメータの変更が必要 MySQL (8.0.29) select @@max_sp_recursion_depth;set session max_sp_recursion_depth=255;select @@max_sp_recursion_depth; drop procedure proc1; delimiter //creat…

SQL文作成プロシージャ

DB

MySQL (8.0.29) drop procedure proc_makesql;delimiter //create procedure proc_makesql( in p_tab_num int, in p_outer_prb numeric, in p_joincond_num numeric, in p_whereind_num numeric, in p_wherenoind_num numeric, in p_select_num numeric, in …

無償利用可能エディション

DB

MySQL (8.0.29) オープンソースのため、無償利用可能エディションの概念なし Oracle (21c) https://blogs.oracle.com/oracle4engineer/post/oracle-database-21c-xe-gahttps://o-labo.info/tried-oracle-database-xe-21c/https://qiita.com/nakaie/items/5c7…

DB間csvインポート

DB

MySQL (8.0.29)show variables like 'secure_file_priv'; drop table tab1;create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) ); insert into tab1 values(1.01, 'あ', '1234567890', now(3) );insert into tab1 …

競合/無効ヒント句の挙動

DB

結果まとめ MySQL競合ヒント句→ 競合部分は最初のヒントが使用される。その他の部分は有効。無効ヒント句→ 無効ヒント句よりも前に記載のヒント句は有効、後に記載のヒント句は無効。 Oracle競合ヒント句→ 競合部分は無視される。その他の部分は有効。無効ヒ…

シーケンスのキャッシュ

DB

MySQL (8.0.29)https://dev.mysql.com/doc/refman/8.0/ja/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes select @@innodb_autoinc_lock_mode; 0 -> 従来型1 -> 連続2 -> インターリーブ デフォルトは2で、複数のステートメントを同…

シーケンス値変更

DB

MySQL (8.0.29) -- 1. シーケンス作成drop table tab1; create table tab1(col1 int primary key auto_increment)auto_increment=50; insert into tab1 values();select * from tab1; -- 2. シーケンス値設定 alter table tab1 auto_increment = 100; ※次に…

filter条件の処理時間影響

DB

filter条件数30、1000万件の場合 MySQL 12 --> 30秒Oracle 0.2 --> 18秒PostgreSQL 0.8 --> 14秒SQL Server 22 --> 22秒 MySQL (8.0.29) drop table tab1; create table tab1( col1 int primary key ,col2 int ,col3 int ,col4 int ,col5 int ,col6 int ,col…

時刻の引き算

DB

MySQL (8.0.29) drop table tab1;create table tab1(col1 bigint auto_increment primary key,col2 datetime(6) ); insert into tab1(col2) values(current_timestamp(6) ); select * from tab1; select col1, col2, lag(col2) over(order by col1) col2_lag…

NULLのソート順

DB

MySQL (8.0.29)https://www.yoheim.net/blog.php?q=20190103 drop table tab1;create table tab1(col1 int primary key,col2 int);insert into tab1 values(1,1);insert into tab1 values(2,2);insert into tab1 values(3,NULL); select * from tab1 order b…

オンライン再定義(カラム追加)

DB

更新待ち時間結果 MySQLpt-online-schema-change -> 8秒ALGORITHM=INSTANT -> ほぼ無しALGORITHM=INPLACE -> 1秒ALGORITHM=COPY -> 5秒(カラム追加時間) Oracledbms_redefinition -> 0.5秒alter table -> ほぼ無し PostgreSQLpg-online-schema-change -> 8秒…

オンライン再定義(インデックス追加)

DB

更新待ち時間結果 MySQLpt-online-schema-change -> 8秒ALGORITHM=INPLACE -> ほぼ無しALGORITHM=COPY -> 5秒(インデックス作成時間) Oracledbms_redefinition -> 0.5秒onlineあり -> ほぼ無しonlineなし -> 8秒(インデックス作成時間) PostgreSQLconcurrent…

not nullカラムにnvl使用時の実行計画

DB

MySQL (8.0.29) drop table tab1;create table tab1(col1 int not null, col2 int);insert into tab1 values(1,1); select * from tab1; analyze table tab1; explain analyzeselect * from tab1 where ifnull(col1,0) != 0 and ifnull(col2,0) != 0; →NOT N…

レプリケーションのマルチスレッドREDO適用

DB

MySQL (8.0.29)https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0079https://blog.s-style.co.jp/2022/01/8498/ マルチスレッドスレーブ (データベース間: 5.6~、データベース内: 5.7~) 8.0.27からデフォルトで有効 replica_parallel_worker…

サイレント接続

DB

MySQL (8.0.29) drop table tab1;create table tab1(col1 int,col2 varchar(30) );insert into tab1 values(1234567890,'1234567890');insert into tab1 values(1234567890,'1234567890'); select * from tab1; mysql --silent --skip-column-names test -e …

ヌルのインデックス

DB

Oracleはヌルデータのインデックスは作成されないMySQL、PostgreSQL、SQL Serverはヌルデータもインデックスは作成され、使用される MySQL (8.0.29)https://qiita.com/omukaik/items/6c9d3018c6ab8fbef7bbhttps://dev.mysql.com/doc/refman/8.0/ja/is-null-o…

パーティションインデックスのパーティションレベル削除

DB

MySQL (8.0.29) パーティションレベル削除不可(構文がない模様) Oracle (19c) drop table tab1 purge; create table tab1( col1 int primary key, col2 int, col3 int, col4 int) partition by range (col2) ( partition p1 values less than (0) , partitio…

プロシージャのIN/OUTパラメータ

DB

MySQL (8.0.29) drop procedure proc1; delimiter //create procedure proc1( in param1 numeric,inout param2 numeric,out param3 numeric)begin set param2 = param2 * param2; set param3 = param1 * 100;end//delimiter ; set @i = 10;set @j = 20;call …

code obfuscation

DB

MySQL (8.0.29)調べた限りなし Oracle (19c) https://docs.oracle.com/cd/F19136_01/lnpls/plsql-source-text-wrapping.html#GUID-AB6FFBAD-DE20-4197-A885-AF81F3766FA2https://www.shift-the-oracle.com/utility/wrap.html WRAP ユーティリティ vi proc1.s…

大量更新中のダンプ時間

DB

大量データ更新後、コミット前の状態でテーブルダンプを実行処理時間を通常時比較 1.5~3倍の時間となる MySQL (8.0.29) -- 1. テストデータ作成 drop table tab1;create table tab1( col1 int primary key ,col2 bigint ,col3 datetime ,col4 varchar(100) …

プルーニング条件の推移律

DB

内部結合した別テーブルに指定された条件を推移的に使用してインデックスアクセス時にパーティションプルーニングできるか確認 → いずれのDBも有効 MySQL (8.0.29) drop table tab1;create table tab1( col1 bigint ,col2 bigint ,col3 bigint) partition by…

外部結合→内部結合の自動変換

DB

いずれのDBも内部結合への自動変換をサポートしている模様 MySQL (8.0.29) drop table tab1;create table tab1( col1 bigint primary key ,col2 bigint ); drop table tab2;create table tab2( col1 bigint primary key ,col2 bigint ); drop procedure proc…

IN句上限

DB

MySQL (8.0.29)調べた限り上限はない模様実際には100万件程度が上限の模様 前提:CPU2コア、メモリ4Gtab1の件数=1 vim /etc/my.cnf max_allowed_packet = 1Ginnodb_buffer_pool_size = 2G : > a.sql echo "select 1 from tab1" >> a.sqlecho "where col1 in …

ブロック破損再現

DB

MySQL (8.0.29) drop table tab1; create table tab1( col1 numeric not null primary key, col2 char(255)); drop procedure proc1; delimiter //create procedure proc1(in x int)begin declare i int; set i = 1; start transaction; while i <= x do ins…