# 06. DB ________________________________________ SQL 第2版 ゼロからはじめるデータベース操作 https://www.shoeisha.co.jp/book/detail/9784798144450 オラクルマスター教科書 Bronze Oracle Database 12c SQL基礎 4刷 https://www.shoeisha.co.jp/book/detail/9784798139883 Oracle® Database概要 https://docs.oracle.com/cd/E57425_01/121/CNCPT/toc.htm ________________________________________ ## 1. SQL ________________________________________ ### 1.1. Oracle Database 12c SQL基礎 DB概要 1. RDBとは、1.データは表、2.表は行と列、3.複数の表を関係付ける 2. OODBとは、1. RDBの拡張、2.属性とメソッド、3.複数オブジェクト 3. RDBやOODBは、E.F.Coddのルールをサポートする 1. 表であること。行、列、フィールド 2. 主キーがあること 3. NULLがあること 4. スキーマ自体をSQLで取得できること 5. SQLがあり、DDL、DML、DCL、トランザクション制御があること 6. 更新可能なビューは更新できること 7. SELECTができ、それを元にさらにCRUDできること 8. 物理ファイルと論理構造が完全に独立していること 9. SELECTやViewによって、DBの論理構造の変更を吸収できること 10. 主キー制約・一意制約・NOT NULL制約・外部キー制約があること 11. 分散・クラスタリングをDBMSが提供できること 12. カーソルなどの低水準機能は、上記11規則を違反せず迂回できないこと 4. Oracleの場合、''はNULL 5. ER図(Peter Chen)：表=四角、外部キー=菱形、主キー=下線、複数可=二重線、導出可=点線 6. 強いentity = ライフサイクルが独立した実体 7. 弱いentity = ライフサイクルが強いentityに依存する実体 8. コンポジット = 「複数のXを組み合わせたもの」と読み替えればOK 9. 射影=SELECT句、選択=WHERE句、結合=FROM句 SELECTと式 1. SELECT DISTINCT e.salary * 12 AS "Annual Salary" FROM employees e WHERE e.salary >= 200000 2. SELECT句は、列名、式、列別名、定数、全ての列である* 3. NULLを含む算術式は、すべてNULL 4. 文字列リテラルは '…' 5. WHERE句は、列名、比較演算子、定数、値のリスト、式 6. 日付リテラルは引用符で囲む、値の大文字小文字は区別、日付書式を区別 7. BETWEEN, IN, LIKE '_A%' ESCAPE '\', IS NULL, NOT, IS NOT NULL, AND, OR 8. ORDER BY 1 DESC NULLS LAST 9. 単一行関数=普通、グループ関数=集計関数（集約関数） 10. グループ関数のネストは、2段まで 11. COUNT関数の中には、DISTINCTが使える 12. グループ関数は、NULL値を除外する 13. GROUP BY col1, col2 ... 14. HAVING 15. table_a INNER JOIN table_b ON 16. table_a LEFT JOIN table_b ON 17. table_a RIGHT JOIN table_b ON 18. table_a FULL JOIN table_b ON 19. table_a CROSS JOIN table_b ON 20. クロス積の別名はデカルト積 21. カンマ+Whereで、内部結合ができる 22. カンマ+Whereで、結合条件になるものがWhere句にない場合、クロス積になる 23. 副問い合わせは、SELECT, FROM, WHERE, HAVING, SETで利用できる 24. 複数行副問い合わせに等価記号で比較すると、複数行抽出するとエラーになる 25. 複数行副問い合わせには、IN, ANY, ALLなどを使って比較する 26. 副問い合わせが1行も返さない時、NULL扱いになる 27. 副問い合わせ内はORDER BY、IN/ANY/ALL内での行制限句を除いてなんでもできる 28. 副問い合わせをNOT INで比較する場合、NULLを1つでも抽出するとFALSEになる 29. 副問い合わせの内容によっては、EXISTS, NOT EXISTSで十分な場合がある SELECTと式（Oracle方言） 1. Oracleでは文字列結合は、|| 2. Oracleでは代替文字列リテラルは q'[...]' 3. Oracleでは表定義の参照は、DESCRIBE <表名> 4. OracleではLIKEのデフォルトエスケープがない (他の言語はデフォルトはバックスラッシュ) 5. OracleではORDER BYのNULLはデフォルトで最後 (他の言語はデフォルトは最初) 6. Oracleでは行制限例1 : FETCH FIRST 10 ROWS WITH TIES 7. Oracleでは行制限例2 : FETCH FIRST ROW ONLY 8. Oracleでは行制限例3 : OFFSET 2 ROWS FETCH NEXT 8 ROWS WITH TIES 9. OracleではDUAL表が使える 10. Oracleでは結合列を自動で決定するNUTURAL JOINが使える 11. Oracleでは結合条件の記述に同名結合用のUSING句も使用できる SQL Plus 1. SQLPlusでは置換変数は、&foo、&&foo 2. SQLPlusでは事前セットは、DEFINE foo = ... 3. SQLPlusでは置換前後の文の非表示は、SET VERIFY OFF Oracleのデータ型とフォーマット 1. Oracleの日付形式指定は、ALTER SESSION SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS' 2. Oracleの曜日形式指定は、ALTER SESSION SET nls_date_language = 'JAPANESE'; 3. Oracleの日を進めるには、+1 4. Oracleの時を進めるには、+1/24 5. 暗黙型変換1：CHAR => NUMBER 6. 暗黙型変換2：CHAR => DATE 7. 暗黙型変換3：VARCHAR2 => NUMBER 8. 暗黙型変換4：VARCHAR2 => DATE (使用禁止) 9. 暗黙型変換5：NUMBER => VARCHAR2 10. 暗黙型変換6：DATE => VARCHAR2 (使用禁止) 集合演算 1. UNION ALL 2. UNION 3. INTERSECT 4. MINUS INSERT、UPDATE、DELETE 1. INSERT INTO table(col1, ...) VALUES(val1, ...); 2. INSERT INTO table(col1, ...) SELECT val1, ...; 3. INSERT文でVALUES句とSELECT句は併用できない 4. UPDATE table SET col1=value WHERE ...; 5. DELETE table WHERE ...; DDL、トランザクション、その他 1. TRUNCATE table; 2. COMMIT, ROLLBACK, SAVEPOINT foo, ROLLBACK TO foo 3. SELECT FOR UPDATE 4. 文字：CHAR(2000), VARCHAR2(4000), LONG 5. 数字：NUMBER(9, 0), NUMBER(18,0) 6. 日付：DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE 7. 二進：RAW(2000)、LONG RAW 8. 参照：BLOB、CLOB、BFILE 9. CREATE TABLE accounts(id NUMBER(18,0) DEFAULT seq_accounts.nextval, name VARCHAR2(255), created DATE DEFAULT SYSDATE) 1. NOT NULL 2. UNIQUE, UNIQUE(col1, col2) 3. PRIMARY KEY, PRIMARY KEY(col1, col2) 4. REFERENCES foos(col1), FOREIGN KEY(col1) REFERENCES foos(col1) 5. CHECK(col1 >= ...) 6. CHECK制約には、SYSDATE、NEXTVAL、ROWNUMなどは使えない。IN句やBETWEEN句は使える 10. CREATE TABLE(...) AS SELECT 11. ALTER TABLE table ADD(col1 type) 12. ALTER TABLE table MODIFY(col1 type) 13. ALTER TABLE table DROP(col1) 14. ALTER TABLE table UNUSED COLUMN(col1) 15. ALTER TABLE table READ ONLY 16. ALTER TABLE table READ WRITE 17. DROP TABLE table ________________________________________ ### 1.2. 常識類 1. With句は使用してよい 2. Window関数は使用してよい 3. Oracleならば、Viewは必要に応じて展開されて最適化された実行計画になる - View外のWhereが、View内のテーブルの絞り込み順序の最適化に影響してくれる 4. Oracleならば、未使用の結合テーブルは除去してくれる ________________________________________ ### 1.3. SQL簡易対応表 Oracle https://docs.oracle.com/cd/F19136_01/refrn/datatype-limits.html#GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095 Mariadb https://mariadb.com/kb/ja/data-types/ Postgres https://pgsql-jp.github.io/current/html/datatype.html SQL Server https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16 文字型仕様 項目 |oracle |mariadb |postgres|sql server|SQLite ----------------------|----------|-----------|--------|----------|------- 想定バージョン |19c |11.4.2 |16.3 |2022 |3.46.0 空文字とNULL |同じ |異なる |異なる |異なる |異なる char最大 | 2000Byte | 255文字|10M文字 | 8000Byte|- varchar最大 |32767Byte | 65532Byte|10M文字 | 8000Byte|- 既定のセマンティクス |Byte |文字 |文字 |Byte |- nvarcharあるか |ある |- |- |ある |- varchar/nvarcharの型名|2 |通常 |通常 |通常 |- サロゲートの扱い |2文字(※1)|1文字(※2) |??? |2文字 |- コードポイント数取得 |LENGTHC |CHAR_LENGTH|??? |??? |- (※1)nvarcher(2)にサロゲート文字は1文字しか入らず、LENGTH=2,LENGTHC=1となる (※2)但し組み込み関数は未対応なことが多い Oracleの文字型補足 - char/varchar2の規定のセマンティクスは、NLS_LENGTH_SEMANTICSで変更可能 - マルチバイトを入れる列はnvarchar推奨。既定のNational CharactersetはAL16UTF16 - AL16UTF16はBEである - AL32UTF8の場合、4Byte文字は2文字消費するため6Byte消費する Mariadb(InnoDB)の文字型補足 - utf8mb4なら(65535-2)/4 = 16383文字 - 上記とは別に行全体で65535Byte制限あり Types type |max |oracle |mariadb |postgres |sql server --------|-------|-------------|-------------|-------------|------------- int |10^10-1|NUMBER(9,0) |NUMERIC(9,0) |NUMERIC(9,0) |numeric(9,0) int |2^31-1 |NUMBER(10,0) |INT |INT |int long |10^19-1|NUMBER(18,0) |NUMERIC(18,0)|NUMERIC(18,0)|numeric(18,0) long |2^63-1 |NUMBER(19,0) |BIGINT |BIGINT |bigint decimal |- |NUMBER(38,0) |NUMERIC(38,0)|NUMERIC(38,0)|numeric(38,0) char[] |- |char(n) |char(n) |char(n) |char(n) string |- |NVARCHAR2(n) |VARCHAR(n) |VARCHAR(n) |nvarchar(n) DateTime|- |DATE |TIMESTAMP |TIMESTAMP |??? byte[] |- |BLOB |BLOB |bytea |??? - Functions Function object |oracle |mariadb |postgres ------------------------------|--------|-----------|-------- CAST(exp AS TYPE) |y |y |y UPPER(src) |y |y |y LOWER(src) |y |y |y INITCAP(src) |y |- |y CONCAT(a, b) |y |y |y SUBSTR(src, pos, len) *1 |y |SUBSTRING |y LENGTH(src) |y |CHAR_LENGTH|y INSTR(src, string) *1 |y |y |- LPAD(src, l, c) |y |y |- RPAD(src, l, c) |y |y |- REPLACE(src, old, new) |y |y |y TRIM(src) |y |y |y ROUND(src, m) |*2 |*3 |*4 TRUNC(src, m) |y |y |y MOD(src, n) |y |y |y SYSDATE |y |- |- ADD_MONTHS(src, n) |y |- |- MONTHS_BETWEEN(a, b) |y |- |- NEXT_DAY(src, w) |y |- |- LAST_DAY(src, w) |y |- |- ROUND(src, m) (for date) |y |- |- TRUNC(src, m) (for date) |y |- |- TO_CHAR(src, f) (from date) |y |- |- TO_NUMBER(src, f) (from str) |y |- |- TO_DATE(src, f) (from str) |y |- |- NVL(src, val) |y |- |- NVL2(src, notnull, isnull) |y |- |- NULLIF(src1, src2) |y |- |y COALESCE(src1, ...) |y |y |y DECODE(src, if1, val, ...) |y |- |- GREATEST(p1, p2, ...) |y |y |*5 LEAST(p1, p2, ...) |y |y |*5 - *1 1オリジン *2 OracleはAwayFromZero式四捨五入。ただし浮動小数点型を単引数で丸める場合のみ偶数丸め *3 MariaDBはAwayFromZero式四捨五入。ただし浮動小数点型を丸める場合はcライブラリ依存 *4 PostgresはAwayFromZero式四捨五入。ただし浮動小数点型を丸める場合のみ偶数丸め *5 OracleとmariaDBはNULLを含むとNULLが返る。PostgresはNULLは無視される Group functions Function object |oracle |mariadb |postgres --------------------------|--------|--------|-------- AVG(src) |y |y |y SUM(src) |y |y |y MIN(src) |y |y |y MAX(src) |y |y |y COUNT(src) |y |y |y LISTAGG(src, ',') |y |- |- - Window functions - over(PARTITION BY col1 ORDER BY col2) Function object |oracle |mariadb |postgres --------------------------|--------|--------|-------- RANK() |y |10.2.1 |y DENSE_RANK() |y |10.2.1 |y CUME_DIST() |y |10.2.1 |y PERCENT_RANK() |y |10.2.1 |y NTILE() |y |10.2.1 |y ROW_NUMBER() |y |10.2.1 |y 5 in Group functions |y |10.2.1 |y FIRST_VALUE(src) |y |10.2.1 |y LAST_VALUE(src) |y |10.2.1 |y ________________________________________ ### 1.4. 実行計画の読み方（Oracle） Costの目安 - ～10 ：Index一致 - ～110：Index範囲。Index列のみが必要な場合（統計状況でrangeとfast-fullスキャンに分かれる） - ～310：Indexベースの結合 その他 100×1000など10万行以上の取得してから何かする必要がある場合、果然した方がよい ________________________________________ ## 2. トランザクションと復旧 ________________________________________ ### 2.1. ACID ```text DB ACID Atomicity : 原子性。トランザクションは必ず成功か失敗する。中途半端にならない Consistency : 一貫性。DB制約に違反した操作は成功しない Isolation : 分離性。トランザクションレベルに応じた独立性が保証される Durability : 耐久性。コミット完了＝保存であることの保証。殆どのRDBMSはWAL（※1）（※2）によって保証する （※1）Oracleの場合UNDOデータやREDOログのこと。REDOログの同期的な書き込みまでがコミットに含まれる （※2）REDOログなどをSSD化するだけで劇的にDBのFOR UPDATE/INSERT/UPDATE/DELETE性能が上がる 単ビジネスロジック ACID Atomicity : 同期的なビジネスロジック全体が、DBトランザクション1つと対応することで保証する Consistency : 全てのビジネスロジックで、楽観ロックか悲観ロックを採用することで保証する Isolation : 前述2つを実現することで、強制的に待ち状態にすることで保証する Durability : 前述2つを実現しつつ、WALにSSDを採用し同期コミットモードにすることで保証する ``` ________________________________________ ### 2.2. 既定の動作、暗黙のコミットとLOCK文 既定の動作 ```text [Oracle] - 非トランザクションモードが存在しない - COMMITの直後にCOMMITを実行しても怒られない - ただし、Sqlplusでは、SET AUTOCOMMIT ONで自動コミットモードにできる - ただし、Oracle.(Managed)DataAccess.dllは、実装が隠蔽され自動コミットモードであるかのように動作する - このライブラリでトランザクションモードの開始(自動コミットされないようにする)には、BeginTransactionを呼び出す - ArrayBindCountには不具合？があり、問答無用で勝手にコミットされる挙動になるらしい（未検証） - https://stackoverflow.com/questions/42684657/oracle-transaction-rollback-doesnt-work-insert-arraybinding-returning - https://stackoverflow.com/questions/35071548/why-does-oracle-odp-net-automattically-commit-inserted-records-when-using-parame - ただし、a5sqlmk2やsi object browserなど一般的なDBツールでは、通常時は即時Commitされる [Mariadb][Postgres][SQL Server] - 非トランザクションモードが存在する。デフォルトは自動コミットモードである - SET AUTOCOMMIT=0; により、START TRANSACTIONを省略できるようになる[Mariadb][Postgres] - SET IMPLICIT_TRANSACTIONS ON; により、BEGIN TRANSACTIONを省略できるようになる[SQL Server] - いずれの製品でどちらのモードでも、COMMITの直後にCOMMITを実行すると、Transactionを開始していませんと怒られる ``` 暗黙のコミットLOCK文 statement |oracle |mariadb |postgres |sql server ------------------------|-------------|-------------|-------------|----------- DDL |y |y |n |n ALTER SESSION |n |- |- |- ALTER SYSTEM |n |- |n |- それ以外のDCL |y |y |n |- SQLPLUS終了 |y |- |- |- クラッシュ時 |! |! |! |! LOCK文の扱い |DML |DDL |n |n ________________________________________ ### 2.3. トランザクションの要点 ロックの基礎知識 - ロック：他のトランザクションによる変更やDDLを禁止する - (行)排他ロック：同時に単一のトランザクションがこの状態を要求できる。更新系DML - (行)共有ロック：同時に複数のトランザクションがこの状態を要求できる。SELECT FOR UPDATE 分離レベル oracle read committed ```text トランザクションを実現してる機構の要素 1. 行write排他ロック（行排他ロック、あるいは単に行ロック） 2. 表DDL禁止ロック（テーブルロック） 3. 更新未コミット時、更新後の最新と更新前で二重持ち 実際の挙動の要点(DML同士) 1. 行write排他ロック済みの行に対する他トランザクションUPDATEは、待ちになる 2. 行write排他ロック済みの行に対する他トランザクションSELECTは、更新前データを取得する 3. 待ちトランザクションは待ちが解除されたのちに、コミット済みを利用して再開する 4. つまり楽観ロック(LSN)更新と組み合わせる場合、トランザクションの初めにチェック更新すればよい 運用時に発生するエラー https://www.shift-the-oracle.com/oerrs/ora-00054.html 実際の挙動の要点(DML、DDLとWAIT、NOWAIT) 1. DMLはデフォルトでWAIT 2. DDLはデフォルトでNOWAIT。明示もほとんどの場合不可能 ``` 分離レベル mariadb repeatable read ```text 1. SELECTとSELECT FOR UPDATEは、実質同じ行を取得する場合でも別々にrepeatable判定が行われる ``` 分離レベル postgres read committed ```text Oracleとロックの実装が異なるが、先行トランザクションと後続トランザクションの待ち組み合わせは同一 ``` 分離レベルに対して起きる症状一覧 症状 |起きる分離レベル |内容 -------------------|-----------------|-------------------------------------------------------------------------------------------------------------------------------- Dirty Read |read uncommitted |未コミットのデータを読んでしまう Non-repeatable Read|read committed |トランザクション内で同じクエリを複数回呼び出した時、行の内容が異なる Read Skew |read committed |Non Repatable Readの変形。複数のクエリ間で世代が異なる行を読み取る Phantom Read |repeatable read |トランザクション内で同じクエリを複数回呼び出した時、別トランザクションがInsertしてCommitした値は読み取る Lost Update |repeatable read |SelectとUpdateを別々にするトランザクションが競合する時、後勝ちになる Write Skew |repeatable read |SelectとUpdateを別々にするトランザクションが競合する時、Update対象がSelectと直接関係ない場合、先勝ちでも後勝ちでもない結果になる。OracleではSerializableでも発生する - SELECT FOR UPDATE の使いどころ ```text - 楽観ロックを全面的に採用していれば、基本的に不要 - 発生頻度上、楽観ロックでは問題がある時のみ使う ``` 分散トランザクション ```text 分散トランザクション - 複数のDBに同時にトランザクションをかけること - Oracle同士の場合、SQL上は意識不要 Oracleの分散トランザクション実現の仕組み（2フェーズコミット系） 1. ノード達から、調停者と先行者を1つずつ選出する。兼任も可能 2. 各ノードは、コミットの直前まで処理を行い（リソースの確保やRedoログやロックの書込完了まで）、調停者へ準備OKと返す 3. 調停者は、全てのノードが準備OKなことを確認し、まず先行者にコミット実施を要求する（※） 4. 調停者は、先行者のコミット完了確認をもって、その他のノードにもコミット実施を要求する（※） 5. 調停者は、全てのノードからコミット完了を受け取り、2フェーズコミットを終了する （※）コミット完了＝REDOログにコミット完了のログも追記 2フェーズコミットの各タイミングで障害発生時にどうなるか - 準備フェーズ中のクラッシュ ：残ったノードは全てロールバック。クラッシュノードは未完了のREDOログを適用しないで復旧 - コミットフェーズ中のクラッシュ：クラッシュ対象のノードは手動解決が必要 ``` ________________________________________ ### 2.4. 復旧とREDOログ Point in Time Recoverry ```text - データファイル自体は、不可逆 - 古いデータファイルとそれ以降のREDOログが全てあれば、それ以降の任意の時点のDBにはできる ``` ________________________________________ ### 2.5. CAP定理 分散システムに関する定理 ```text Consistency ：常に最新データにアクセスできる保証 Availability ：単一障害点がない保証 Partition tolerance：通信分断に関わらず同期されている保証 これらは2つまでしか満たせない ``` ________________________________________ ## 3. ライブラリとSQL ________________________________________ ### 3.1. ライブラリとSQL statement |oracle |mariadb |postgres |sql server ------------------------|-------------|-------------|-------------|----------- パラメータ(.NET)(※1) |:a |@a |@a |@a パラメータ(PDO) |??? |:a |:a |??? (※1) それぞれODP.NET,