[TechNight #49] Oracle Database 21c 新機能解説 第1回 (後半パート)

Oracle Database 21c 新機能解説
第1回の後半パートでは、下記について解説しています。

1.SQL
2.分析SQL関数および集計関数
3.PL/SQL
4.JSON
5.パッチ適用/アップグレード
6.マルチテナント

Transcript

1. Oracle Technology Night #49 Oracle Database 21c 新機能解説1 (後半) 日本オラクル株式会社 データベース・ソリューション部 2021年11月4日
2. 1. SQL 2. 分析SQL関数および集計関数 3. PL/SQL 4. JSON 5. パッチ適用/アップグレード 6. マルチテナント Oracle Database 21 新機能解説1 後半のアジェンダ Copyright © 2021, Oracle and/or its affiliates 2
3. Copyright © 2021, Oracle and/or its affiliates 3 SQL の新機能 • SQL set演算子の拡張 • 式での初期化パラメータのサポート • SQL DDL文のプレースホルダ • SQLマクロ
4. ANSI SQLで定義されているすべてのキーワードがサポートされるようになった 新しい演算子として下記２つが使用可能 • EXCEPT [ALL] • INTERSECT [ALL] メリット • 完全な ANSI 準拠により、他のデータベース・ベンダーとの互換性が高くなり Oracle Database への移行が容易になった SQL set演算子の拡張 Copyright © 2021, Oracle and/or its affiliates 4
5. EXCEPT [ALL] は、２つのテーブルの行の差分を抽出する事が可能 機能としては MINUS [ALL]に相当する EXCEPT [ALL] Copyright © 2021, Oracle and/or its affiliates 5 SQL> SELECT count(*) FROM (SELECT product_id FROM inventories EXCEPT SELECT product_id FROM order_items); COUNT(*) ---------- 84 実行例： データ取得の範囲イメージ テーブルA テーブルB
6. INTERSECT [ALL]は、指定した表の重複データのみを抽出する事が可能 ALLを指定していない場合、同一値のデータは１行にまとめて表示される INTERSECT [ALL] Copyright © 2021, Oracle and/or its affiliates 6 SQL> SELECT count(*) FROM (SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items); COUNT(*) ---------- 124 実行例： データ取得の範囲イメージ テーブルA テーブルB
7. 初期化パラメータの設定値を計算式で設定できるようになった 式での初期化パラメータのサポート Copyright © 2021, Oracle and/or its affiliates 7 SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ------------ ------------------------------ sga_max_size big integer 1000M sga_target big integer 1000M … SQL> ALTER SYSTEM SET SGA_TARGET = 'sga_max_size*80/100'; システムが変更されました。 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ------------ ------------------------------ sga_target big integer 800M 実行例：SGA_TARGET パラメータを SGA_MAX_SIZE の 80% を指定して設定 ※設定対象となっているパラメータを計算式に埋め込むのはNG SQL> ALTER SYSTEM SET SGA_TARGET=‘SGA_TARGET * 80/100’; ALTER SYSTEM SET SGA_TARGET=‘SGA_TARGET * 80/100’ * ERROR at line 1: ORA-00922: missing or invalid option
8. バインド変数を指定できないSQL文(例：CREATE、DROPなど) にOCIプレースホルダを含める事が可能になった Oracle Call Interface にて、OCIStmtPlaceholderSubstitute() 関数を呼び出すことで SQL 実行前にOCI プレースホルダの置換が行われる メリット • SQLインジェクション攻撃のリスクを軽減することが可能 SQL DDL文のプレースホルダ Copyright © 2021, Oracle and/or its affiliates 8
9. 使用例：CREATE USER 文 SQL DDL文のプレースホルダ Copyright © 2021, Oracle and/or its affiliates 9 CREATE USER :!username IDENTIFIED BY :!password DEFAULT TABLESPACE example QUOTA 10M ON example PROFILE app_user PASSWORD EXPIRE; OCIプレースホルダを使用したDDL文 OCIStmtPlaceholderSubstitute(stmthp, "username", strlen("username"), "scott", strlen("scott"), OCI_DEFAULT); OCIStmtPlaceholderSubstitute()関数の例
10. SQL開発者が、複雑な処理を SQL文のどこでも使用可能なマクロにカプセル化することができるようになった SQL Macros の使い方は下記2つ • スカラー式：複雑なSQL式を簡単にカプセル化 SELECT リスト, WHERE/HAVING, GROUP BY/ORDER BY句で使用 • テーブル式：FROM句で使用されるSQLをカプセル化 メリット • SQLオプティマイザがコードを変換し、効率的な処理を実現 • 他のデータベースに存在する関数の単純な書き直しメカニズムを提供 • スカラー式のマクロが使用されているWHERE句は、Exadataのストレージ側のWHERE句評価にプッシュダウン可能 SQLマクロ Copyright © 2021, Oracle and/or its affiliates 10
11. CREATE FUNCTION strpos(str VARCHAR2, sub_str VARCHAR2) RETURN VARCHAR2 SQLMACRO(SCALAR) IS BEGIN RETURN ‘INSTR(str, sub_str)’; END; / SQLマクロは、データベースの移行プロジェクトに役立つ 例えば、PostgreSQL から Oracle Database へ移行する場合、STRPOS()関数などの書き直しが必要だったが、 SQLマクロを使う事で、STRPOSをINSTRに変換するSQLマクロを作成する事で、移行に伴うSQL自体の書き直し が不要になる SQLマクロ(スカラー式) Copyright © 2021, Oracle and/or its affiliates 11 実行例：strpos が呼び出された際に内部的に instr で実行する SQL マクロ
12. SQLマクロ(テーブル式) Copyright © 2021, Oracle and/or its affiliates 12 テーブル式マクロは、以下のことが可能になった: • Parameterized views • マクロ定義の中でクエリで使用された表は直される • 上記のような表から select された行は、引数に渡される • 返されたクエリの”形”は、(通常)直される • Polymorphic views • 返されたクエリの形はランタイムで決定される • クエリに渡されたインプットテーブルは、マクロから返される • スカラー値の引数とともに、1つ以上の引数を持つことができる
13. create or replace function total_sales (zip_code varchar2) return varchar2 SQL_MACRO(TABLE) is begin return q'{ select cust.cust_postal_code as zip_code, sum(amount_sold) as revenue from sh.customers cust, sh.sales s where cust.cust_postal_code = total_sales.zip_code and s.cust_id = cust.cust_id group by cust.cust_postal_code order by cust.cust_postal_code }'; end; / SELECT * FROM total_sales('60332'); SQLマクロ(テーブル式) ① Parameterized Views Copyright © 2021, Oracle and/or its affiliates 13
14. SQLマクロ(テーブル式) ② Polymorphic Views Copyright © 2021, Oracle and/or its affiliates 14 SELECT * FROM row_sampler(t=>sh.customer, pct=>15); create or replace function row_sampler (t dbms_tf.table_t, pct number default 5) return varchar2 SQL_MACRO(TABLE) as begin return q'{select * from t order by dbms_random.value fetch first row_sampler.pct percent rows only}'; end; /
15. Copyright © 2021, Oracle and/or its affiliates 15 分析SQL関数および集計関数の新機能 • ビット単位の集計関数 • 分析関数の拡張 • 新しい分析関数および集計関数
16. 新しい集計関数として下記が追加 • BIT_AND_AGG • BIT_OR_AGG • BIT_XOR_AGG メリット • 不要なデータの移動をなくしたり、パラレル処理など他のデータベース機能を最大限に活用することで、 問合せ全体のパフォーマンスが向上する 機能概要 • ビット単位の集計関数を使用する事でビット単位の型処理がSQLで直接可能になる ビット単位の集計関数 Copyright © 2021, Oracle and/or its affiliates 16
17. BIT_AND_AGG は、ビット単位のAND 演算の結果をそれぞれ返す BIT_AND_AGG Copyright © 2021, Oracle and/or its affiliates 17 SQL> WITH x AS 1 (SELECT 5 c1 FROM dual 2 UNION ALL 3 SELECT 6 FROM dual) 4 SELECT BIT_AND_AGG(c1) FROM x; BIT_AND_AGG(C1) --------------- 4 実行例： 上記の場合、5(=101) と 6(=110) で AND 演算を行った結果、100 となる為に “4” が結果として返される １ ０ １ １ ０ ０ １ ０ １ ５・・・ ６・・・ AND演算 ＝４
18. BIT_OR_AGGは、ビット単位のOR演算の結果をそれぞれ返す BIT_OR_AGG Copyright © 2021, Oracle and/or its affiliates 18 SQL> WITH x AS 1 (SELECT 5 c1 FROM dual 2 UNION ALL 3 SELECT 6 FROM dual) 4 SELECT BIT_OR_AGG(C1) FROM x; BIT_OR_AGG(C1) -------------- 7 実行例： 上記の場合、5(=101) と 6(=110) で OR 演算を行った結果、111 となる為に “7” が結果として返される １ ０ １ １ １ ０ １ ５・・・ ６・・・ OR演算 ＝7 １ １
19. BIT_XOR_AGG は、ビット単位の XOR演算の結果をそれぞれ返す BIT_XOR_AGG Copyright © 2021, Oracle and/or its affiliates 19 上記の場合、5(=101) と 6(=110) で XOR 演算を行った結果、010 となる為に “3” が結果として返される SQL> WITH x AS 1 (SELECT 5 c1 FROM dual 2 UNION ALL 3 SELECT 6 FROM dual) 4 SELECT BIT_XOR_AGG(c1) FROM x; BIT_XOR_AGG(C1) -------------- 3 実行例： １ ０ １ １ ０ １ ５・・・ ６・・・ XOR演算 ＝3 １ ０ ０
20. 分析関数について下記２つが拡張された 1. ウィンドウ関数にて、SQL標準ウィンドウ・フレーム句のEXCLUDEオプションをサポート EXCLUDEで指定可能なオプションは下記4種類 • CURRENT ROW • GROUP • TIES • NO OTHERS 2. SELECT 文の query_block 句で window_clause がサポートされるようになった メリット • SQL:2011規格で定義されているようにSQL標準表式のウィンドウ句が実装可能 • 他の規格準拠のデータベース・システムに対して開発されたアプリケーション移行が簡単に 分析関数の拡張 Copyright © 2021, Oracle and/or its affiliates 20
21. ウィンドウ関数の EXCLUDE オプション Copyright © 2021, Oracle and/or its affiliates 21 EXCLUDE オプションの毎の除外対象 オプション 除外対象 CURRENT ROW カレント行 GROUP カレント行＋カレント行と同値の行 TIES カレント行と同値の行 NO OTHERS 除外対象なし(デフォルト) v列 3 4 5 5 6 v列 3 4 5 5 6 指定オプション SELECT結果 CURRENT ROW 4 + 5 + 5 = 9 GROUP 4 + 5 + 5 = 4 TIES 4 + 5 + 5 = 9 NO OTHERS 4 + 5 + 5=14 SQL> select sum(v) over (order by v rows between 1 preceding and 1 following exclude <オプション>) from t; 対象範囲の絞り込み 指定した対象を除外 して集計
22. 使用例 employees表の各従業員について、以下の２パターンの平均給与を算出する • 自身を除いた部門毎の平均給与 -① • 部門毎の平均給与 -② ウィンドウ関数の EXCLUDE オプション Copyright © 2021, Oracle and/or its affiliates 22 SQL> select department_id, last_name, salary, 2 avg(salary) over (o groups between 1 preceding and 1 following exclude current row) as avg_sal, 3 avg(salary) over (o groups between 1 preceding and 1 following ) as avg_sal2 4 from employees 5 window o as (partition by department_id order by department_id); DEPARTMENT_ID LAST_NAME SALARY AVG_SAL AVG_SAL2 ------------- ------------------------- ---------- ---------- ---------- 10 Whalen 4400 4400 20 Hartstein 13000 6000 9500 20 Fay 6000 13000 9500 30 Raphaely 11000 2780 4150 30 Khoo 3100 4360 4150 30 Baida 2900 4400 4150 30 Tobias 2800 4420 4150 30 Himuro 2600 4460 4150 30 Colmenares 2500 4480 4150 40 Mavris 6500 6500 50 Weiss 8000 3372.72727 3475.55556 50 Fripp 8200 3368.18182 3475.55556 50 Kaufling 7900 3375 3475.55556
23. SELECT 文の query_block 句で window_clause Copyright © 2021, Oracle and/or its affiliates 23 SQL> SELECT trades.acno, trades.tday, SUM (agg.suma) OVER W ※ FROM trades, (SELECT acno, tday, SUM(amount) AS suma FROM trades WHERE ttype = 'buy' GROUP BY acno, tday ) agg WHERE trades.acno = agg.acno AND trades.tday = agg.tday AND trades.ttype = 'buy' WINDOW W AS (PARTITION BY trades.acno ORDER BY trades.tday ROWS BETWEEN 4 PRECEDING AND CURRENT ROW); ※"W" は、末尾の "WINDOWS W AS ..." の W を指す 実行例：
24. 新しい分析関数として下記２つが追加 • SKEWNESS_POP および SKEWNESS_SAMP • KURTOSIS_POP および KURTOSIS_SAMP 新しい集計関数として下記２つが追加 • CHECKSUM • ANY_VALUE ※ALL 、DISTINCT および UNIQUE をサポート メリット • より効率的なコードを作成可能 • ANY_VALUE 集計関数を使用する事でデータベース内の処理が高速になる 新しい分析関数および集計関数 Copyright © 2021, Oracle and/or its affiliates 24
25. SKEWNESS_POP および SKEWNESS_SAMP データの尖度を算出可能な関数 KURTOSIS_POP(母集団の尖度)とKURTOSIS_SAMP(標本の尖度)が実装された これにより特定のデータに対して尖度が簡単に算出可能になった。 ※正規分布の尖度はゼロ 分析関数 Copyright © 2021, Oracle and/or its affiliates 25 19c 以前では尖度の算出する場合、下記計算式を 元に作りこみが必要だった 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0.4 0.45 -4 -3 -2 -1 0 1 2 3 4 尖度の計算式
26. SKEWNESS_POP および SKEWNESS_SAMP 分析関数 Copyright © 2021, Oracle and/or its affiliates 26 SQL> SELECT house, SKEWNESS_POP(price_big_city), SKEWNESS_POP(price_small_city) 2 FROM houses GROUP BY house; HOUSE SKEWNESS_POP(PRICE_BIG_CITY) SKEWNESS_POP(PRICE_SMALL_CITY) ---------- ---------------------------- ------------------------------ 1 0 -.66864012 2 1.13841996 1.49637083 3 0 -.12735442 SQL> SELECT house, SKEWNESS_SAMP(price_big_city), SKEWNESS_SAMP(price_small_city) 2 FROM houses GROUP BY house; HOUSE SKEWNESS_SAMP(PRICE_BIG_CITY) SKEWNESS_SAMP(PRICE_SMALL_CITY) ---------- ----------------------------- ------------------------------- 1 0 -.81051422 2 1.69705627 2.23065793 3 0 -.18984876
27. KURTOSIS_POP および KURTOSIS_SAMP データの歪度を算出可能な関数 KURTOSIS_POP (母集団の歪度)とKURTOSIS_SAMP(標本の歪度)が実装された これにより特定のデータに対して歪度が簡単に算出可能 ※データが中心点より右側に偏っている場合は正の値、左側に偏っている負の値 分析関数 Copyright © 2021, Oracle and/or its affiliates 27 19c 以前では歪度の算出する場合、下記計算式を 元に作りこみが必要だった 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0.4 0.45 -4 -3 -2 -1 0 1 2 3 4 歪度の計算式
28. KURTOSIS_POP および KURTOSIS_SAMP 分析関数 Copyright © 2021, Oracle and/or its affiliates 28 SQL> SELECT house, kurtosis_pop(price_big_city), kurtosis_pop(price_small_city) 2 FROM houses GROUP BY house; HOUSE KURTOSIS_POP(PRICE_BIG_CITY) KURTOSIS_POP(PRICE_SMALL_CITY) ---------- ---------------------------- ------------------------------ 1 -1.23 -.7058169 2 -.212 .245200191 3 -1.3 -1.5417881 SQL> SELECT house, kurtosis_samp(price_big_city), kurtosis_samp(price_small_city) 2 FROM houses GROUP BY house; HOUSE KURTOSIS_SAMP(PRICE_BIG_CITY) KURTOSIS_SAMP(PRICE_SMALL_CITY) ---------- ----------------------------- ------------------------------- 1 -1.2 -.201556 2 3.152 4.98080076 3 -1.2 -2.1671526
29. CHECKSUM SQL Server 等で実装されている CHECKSUM 関数がオラクルでも使用可能になった。 これにより、表のデータが変更されたかの確認が可能 集計関数 Copyright © 2021, Oracle and/or its affiliates 29 SQL> select * from report.trades; ★操作前のデータ状況 ACNO TID TDAY TTYPE AMOUNT TICKER ---------- ---------- ------------------- ------------ ---------- ------------ 123 1 2021-08-26 09:12:32 buy 1000 CSCO 123 1 2021-08-26 09:12:32 buy 400 JNPR 123 3 2021-08-28 09:12:32 buy 2000 SYMC 123 4 2021-08-28 09:12:32 buy 1200 CSCO 123 5 2021-08-28 09:12:32 buy 500 JNPR 123 6 2021-08-30 09:12:32 buy 200 CSCO <省略> SQL> select checksum(amount) from report.trades; CHECKSUM(AMOUNT) ---------------- 5486 ★操作前のチェックサムの計算結果
30. CHECKSUM 集計関数 Copyright © 2021, Oracle and/or its affiliates 30 ・TID=3 の amount 列値を更新する SQL> update report.trades set amount=3000 where tid=3; 1行が更新されました。 ・チェックサムを再度実施し、結果が変わっている事を確認 SQL> select checksum(amount) from report.trades; CHECKSUM(AMOUNT) ---------------- 660143★ ・ロールバックしてみる SQL> rollback; ロールバックが完了しました。 SQL> select checksum(amount) from report.trades; CHECKSUM(AMOUNT) ---------------- 5486 ★更新前の結果に戻っている
31. ANY_VALUE ANY_VALUEを使用した場合、値を比較しないでグループ内の最初の値を返すようになる MIN または MAX 関数を使用したGROUP BY問合せと比較すると迅速に結果を返す事が可能 集計関数 Copyright © 2021, Oracle and/or its affiliates 31 SELECT c.cust_id, ANY_VALUE(cust_last_name), SUM(amount_sold) FROM customers c, sales s WHERE s.cust_id = c.cust_id GROUP BY c.cust_id Plan hash value: 3821284699 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 2026 (100)| | | | | 1 | HASH GROUP BY | | 7059 | 158K| | 2026 (4)| 00:00:01 | | | |* 2 | HASH JOIN | | 918K| 20M| 1360K| 1980 (2)| 00:00:01 | | | | 3 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| | 423 (1)| 00:00:01 | | | | 4 | PARTITION RANGE ALL| | 918K| 8973K| | 523 (3)| 00:00:01 | 1 | 28 | | 5 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 523 (3)| 00:00:01 | 1 | 28 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."CUST_ID"="C"."CUST_ID") 例： ANY_VALUE を使用した GROUP BY の実行計画
32. Copyright © 2021, Oracle and/or its affiliates 32 PL/SQL の新機能 • 新しいPL/SQLイテレータ構造体 • 新しいプラグマSUPPRESSES_WARNING_6009 • PL/SQL修飾式の機能拡張 • ユーザー定義タイプのPL/SQLタイプ属性
33. PL/SQLが拡張され、ループおよび修飾式で新しいイテレータを使用して反復制御をプログラミングできるようになった 新しいPL/SQLイテレータ構造体 Copyright © 2021, Oracle and/or its affiliates 33 メリット • ループの内容がわかりやすく効率的 機能概要 • 19cまではループ内容によって作りこみが必要だった • 21cから下記ケースでのループが可能にになった 1. ステップ範囲の繰返しコントロール 2. 単一式の繰返しコントロール 3. コレクションの繰返しコントロール 4. カーソルの繰返しコントロール
34. ループ処理において増減数をステップ指定できるようになった 例：ステップ範囲の繰返しコントロール この例では NUMBER(5,1)として宣言されたループ変数n を 0.5 ずつ増加させた結果を表示する ステップ範囲の繰返しコントロール Copyright © 2021, Oracle and/or its affiliates 34 SQL> set serveroutput on SQL> BEGIN 2 FOR n NUMBER(5,1) IN 1.0 .. 3.0 BY 0.5 LOOP 3 DBMS_OUTPUT.PUT_LINE(n); 4 END LOOP; 5 END; 6 / 1 1.5 2 2.5 3
35. 停止述語の条件が True になるまでループを繰り返す事が可能になった 例：単一式の繰返しコントロール この例ではイテランドが1で始まり、ⅰの値が 100 を越えるまで i * 2 が繰り返し評価する 単一式の繰返しコントロール Copyright © 2021, Oracle and/or its affiliates 35 SQL> set serveroutput on SQL> BEGIN 2 FOR i IN 1, REPEAT i*2 WHILE i < 100 LOOP 3 DBMS_OUTPUT.PUT_LINE(i); 4 END LOOP; 5 END; 6 / 1 2 4 8 16 32 64
36. 繰り返しコントールにより、コレクションから導出されるイテランド用の値のシーケンスを生成する 例：VALUES OF・・・指定したコレクションの要素順に値を出力 この例ではコレクション・ベクトルの[11, 10, 34]からの値を出力 コレクションの繰返しコントロール① Copyright © 2021, Oracle and/or its affiliates 36 SQL> set serveroutput on SQL> DECLARE 2 TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; 3 vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34); 4 BEGIN 5 FOR i IN VALUES OF vec LOOP 6 DBMS_OUTPUT.PUT_LINE(i); 7 END LOOP; 8 END; 9 / 11 10 34
37. 繰り返しコントールにより、コレクションから導出されるイテランド用の値のシーケンスを生成する 例：INDICES OF・・・索引の値を出力 この例では、コレクション・ベクトルの[1, 3, 100]の索引を出力している コレクションの繰返しコントロール② Copyright © 2021, Oracle and/or its affiliates 37 SQL> set serveroutput on SQL> DECLARE 2 TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; 3 vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34); 4 BEGIN 5 FOR i IN INDICES OF vec LOOP 6 DBMS_OUTPUT.PUT_LINE(i); 7 END LOOP; 8 END; 9 / 1 3 100
38. 繰り返しコントールにより、コレクションから導出されるイテランド用の値のシーケンスを生成する 例：PAIRS OF・・・指定したコレクションの索引と要素のペアを出力 この例ではⅰ に要素、j に索引値を格納し、そのペア(10 => 3、11 => 1、34 => 100)を出力 コレクションの繰返しコントロール③ Copyright © 2021, Oracle and/or its affiliates 38 SQL> set serveroutput on SQL> DECLARE 2 TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; 3 vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34); 4 result intvec_t; 5 BEGIN 6 result := intvec_t(FOR i,j IN PAIRS OF vec INDEX j => i); 7 FOR i,j IN PAIRS OF result LOOP 8 DBMS_OUTPUT.PUT_LINE(i || '=>'|| j); 9 END LOOP; 10 END; 11 / 10=>3 11=>1 34=>100
39. カーソルの繰返しコントロールは、明示カーソルまたは暗黙カーソルによって返されるレコードのシーケンスを生成する 例：この例は、繰返しコントロールによる動的SQLからのすべてのレコードの生成を示す これにより、employee_idが103未満のすべての従業員のlast_nameおよびemployee_idを出力 停止述語がTRUEの場合にループ本体が実行される カーソルの繰返しコントロール Copyright © 2021, Oracle and/or its affiliates 39 SQL> DECLARE 2 cursor_str VARCHAR2(500) := 'SELECT last_name, employee_id FROM hr.employees ORDER BY last_name'; 3 TYPE rec_t IS RECORD (last_name VARCHAR2(25), 4 employee_id NUMBER); 5 BEGIN 6 FOR r rec_t IN VALUES OF (EXECUTE IMMEDIATE cursor_str) WHEN r.employee_id < 103 LOOP 7 DBMS_OUTPUT.PUT_LINE(r.last_name || ', ' || r.employee_id); 8 END LOOP; 9 END; 10 / De Haan, 102 King, 100 Kochhar, 101
40. SUPPRESSES_WARNING_6009 プラグマを使用する事で、 PLW-06009 の発生を抑止する事が可能 新しいプラグマ SUPPRESSES_WARNING_6009 Copyright © 2021, Oracle and/or its affiliates 40 メリット • より堅牢なエラー処理が可能になり、カプセル化およびモジュール化が改善される 機能概要 • 19cまでは EXCEPTION 文中に RAISE文もしくはRAISE_APPLICATION_ERRORの指定が必須であり、 指定していない場合は PLW-06009 が発生していた • 21cから SUPPRESSES_WARNING_6009 プラグマを指定する事で、より細かなユーザ定義のエラーハンド リングが可能となった
41. 例：ユーザ定義エラー ORA-2000 ‘Unexpected error raised’ をプロシージャp1 で定義し、 プロシージャ p2 の EXCEPTION 文で p1 を呼び出す 新しいプラグマ SUPPRESSES_WARNING_6009 Copyright © 2021, Oracle and/or its affiliates 41 CREATE PROCEDURE p1 AUTHID DEFINER IS PRAGMA SUPPRESSES_WARNING_6009(p1); BEGIN RAISE_APPLICATION_ERROR(-20000, 'Unexpected error raised'); END; / CREATE PROCEDURE p2 AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE('In procedure p2'); EXCEPTION WHEN OTHERS THEN p1; END p2; /
42. 修飾式で使用するための以下の 3つの新しいイテレータ選択アソシエーションのタイプが追加された。 • 基本イテレータ選択アソシエーション • 索引イテレータ選択アソシエーション • シーケンス・イテレータ選択アソシエーション メリット • 修飾式にてイテレータの生成が簡単に実装する事が可能になり、プログラムのわかりやすさと生産性が向上する 機能概要 • 19cまではユーザ独自に作りこみが必要だった • 21cから上記イテレータ選択アソシエーションを使用する事で PL/SQL修飾式の機能拡張 Copyright © 2021, Oracle and/or its affiliates 42
43. 基本イテレータ選択アソシエーションは、イテランド値ごとに式が評価されコレクションに追加される。 このとき、イテランド値が索引として使用される。 基本イテレータ選択アソシエーション Copyright © 2021, Oracle and/or its affiliates 43 SQL> declare 2 type vec is table of pls_integer index by pls_integer; 3 result vec; 4 begin 5 result := vec(FOR i IN 1..5 => 2*i); 6 for i in result.first .. result.last loop 7 dbms_output.put_line(result(i)); 8 end loop; 9 end; 10 / 2 4 6 8 10 この例では、 1～5をそれぞれ2倍した値を コレクション “result” に格納
44. イテランド値ごとに索引式と値式が評価され、拡張された値が拡張された索引を使用してコレクションに追加される。 索引イテレータ選択アソシエーション Copyright © 2021, Oracle and/or its affiliates 44 SQL> declare 2 type vec is table of varchar2(3); 3 result vec; 4 begin 5 result := vec(for i in 10..21 by 2 index i/2 => i); 6 for i in result.first .. result.last loop 7 dbms_output.put_line(result(i)); 8 end loop; 9 end; 10 / 10 12 14 16 18 20 この例では、 10～21を2ずつカウントアップし、そのうち の偶数値のみをコレクション “result” に格納
45. 索引イテレータ選択アソシエーションは、値のシーケンスをコレクションの末尾に追加できる。 イテランド値ごとに値式が評価され、コレクションの末尾に追加される シーケンス・イテレータ選択アソシエーション Copyright © 2021, Oracle and/or its affiliates 45 SQL> declare 2 type vec is table of pls_integer index by pls_integer; 3 result vec; 4 v1 vec :=vec(for i in 1..3 => i); 5 v2 vec :=vec(for i in 4..6 => i); 6 begin 7 result := vec(for v in values of v1, reverse values of v2 sequence => v); 8 for i in result.first .. result.last loop 9 dbms_output.put_line(result(i)); 10 end loop; 11 end; 12 / 1 2 3 6 5 4 この例では、下記２つのコレクション結合結果を result に格納 ・ 1,2,3が格納されているコレクション v1 ・4,5,6が格納されているコレクション v2の逆順
46. PLS_INTGER、BOOLEAN などのユーザー定義タイプの作成が可能になった ユーザー定義タイプのPL/SQLタイプ属性 Copyright © 2021, Oracle and/or its affiliates 46 メリット • Oracleのオブジェクト指向プログラミング・モデルに従ったプログラムを開発する場合に便利 機能概要 • 永続不可オブジェクト型に BOOLEAN や PLS_INTEGER などの PL/SQL スカラー・データ型が指定可能
47. 例１：PLS_INTGER 型のユーザー定義タイプを作成する ユーザー定義タイプのPL/SQLタイプ属性 Copyright © 2021, Oracle and/or its affiliates 47 SQL> CREATE TYPE objplsint AS OBJECT (I PLS_INTEGER) NOT PERSISTABLE; 2 / Type created. 例２：BOOREAN 型のユーザー定義タイプを作成する SQL> CREATE TYPE objbool AS OBJECT (I BOOLEAN) NOT PERSISTABLE; 2 / Type created.
48. Copyright © 2021, Oracle and/or its affiliates 48 JSON の新機能 • JSON型：新しいJSONデータ型 • JSON_TRANSFORM関数 • SQL/JSON構文の改善 • JSON用複数値インデックス • ビューの作成および仮想列の追加の拡張 • JSONスカラー（RFC8259サポート） • 古いクライアントとバイナリJSONの互換性/ネイティブJSONデータ型のJDBCサポート
49. CREATE TABLE J_PURCHASEORDER ( id INTEGER PRIMARY KEY, po_document JSON ); JSONを最適化して保存する新しいSQLデータ型 バイナリJSON型 • SQL、PL/SQLで利用可能な新しいデータ型 • OCI, JDBCでネイティブ・サポート • OSONをベース – 最適化されたバイナリ表現 • 自己完結型フォーマット • 高速フィールド・ルックアップ • 部分更新可能 • スキャンはテキストより最大10倍高速 • 更新はテキストより最大10倍高速 Oracle Databaseプラットフォームと統合 • Parallel Query • RAC • GoldenGate • … • 高度なセキュリティ機能を利用可能 • VPD、暗号化など • JSONと他のデータを一度に管理可能 • Converged Databaseによる運用の簡素化 JSON型 Copyright © 2021, Oracle and/or its affiliates 49
50. 例：JSON型を利用した表の作成とデータの挿入 JSON型 Copyright © 2021, Oracle and/or its affiliates 50 -- 表を作成 SQL> create table customers(id number, created timestamp, cdata JSON); -- 構成を確認 SQL> desc customers; Name Null? Type ------- ----- ------------ ID NUMBER CREATED TIMESTAMP(6) CDATA JSON -- データを投入 SQL> insert into customers values (1,systimestamp,'{"name":"Atif","address":{"city":"San Mateo"}}'); commit;
51. 例：JSON型の検索 JSON型 Copyright © 2021, Oracle and/or its affiliates 51 -- ドット表記を使った検索 SQL> select c.cdata.address.city.string() from customers c; C.CDATA.ADDRESS.CITY.STRING() -------------------------------------------------------------------------------- San Mateo -- JSON_VALUE関数を利用 SQL> select JSON_VALUE(cdata, '$.address.city') from customers; JSON_VALUE(CDATA,'$.ADDRESS.CITY') -------------------------------------------------------------------------------- San Mateo }
52. JSONデータを格納できる他のデータ型との比較 他のデータ型（VARCHAR2/CLOB/BLOB) • 19c以前のバージョンで利用 • 指定したデータ型の形式で格納 • 整形されたJSONデータであることは保証されない。整 形されたJSONかどうかはIS_JSON関数で確認が必 要（制約を省略するとドット表記構文を使用して JSONデータをクエリできない) JSON型 • 21c以降で利用（初期化パラメータcompatible >20) • OracleでJSONを扱うために最適化されたバイナリJSON 形式（OSON）で保存 • 整形されたJSONデータであることが保証される • ドット表記構文でスカラー値へ変更するには項目メソッドを 使用 • JSON標準に含まれないSQLスカラーデータ型に対応する 拡張データ型の利用が可能 JSON型 Copyright © 2021, Oracle and/or its affiliates 52 21c以降の 推奨はJSON型
53. 拡張データ型のサポート 拡張データ型としてJSON標準にない以下のSQLスカラーデータ型をサポートする • JSON標準はオブジェクト、配列、数値、文字列、ブール、NULL JSON型 Copyright © 2021, Oracle and/or its affiliates 53 拡張データ型 SQLスカラーデータ型 Binary RAW date DATE timestamp TIMESTAMP year-month interval INTERVAL YEAR TO MONTH day-second interval INTERVAL DAY TO SECOND double BINARY-DOUBLE float BINARY_FLOAT
54. -- CUSTOMERS表からJSONオブジェクトを生成、なお、returningでJSON指定しない場合はVARCHAR2 SQL> select JSON_OBJECT(* returning JSON) from customers; JSON_OBJECT(*RETURNINGJSON) -------------------------------------------------------------------------------- {"ID":1,"CREATED":"2021-09-27T02:33:41.333275","CDATA":{"name":"Atif","address": -- 生成されたJSONオブジェクト（subQuery)から、CREATEDの型を確認 SQL> with subQuery as ( 2 select JSON_OBJECT(* returning JSON) jdata 3 from customers) 4 select s.jdata.CREATED.type() 5 from subQuery s; S.JDATA.CREATED.TYPE() -------------------------------------------------------------------------------- Timestamp 例：拡張データ型 JSON型 Copyright © 2021, Oracle and/or its affiliates 54
55. 変更のための新しい演算子 JSON_Transform • 既存の構文とセマンティクス(述語など)に従った新たな SQL/JSON演算子 • 一度の処理に複数の演算子の使用可能 (SET, APPEND, REMOVEなど) • 指定した順序で適用 • アトミック型（エラーが発生した場合はすべて無効） • 部分的な更新をサポート • JSON型ではログサイズを削減することに最適化(ディスク上で部分的なアップデート) • SELECT内で利用する場合はクライアントに送信前にデータを変更 • 例：クライアントにJSONを送信する前に個人情報のデータを削除する JSON_TRANSFORM関数 Copyright © 2021, Oracle and/or its affiliates 55
56. 使用可能な変更操作とハンドラ JSON_TRANSFORM関数 Copyright © 2021, Oracle and/or its affiliates 56 各操作の後に対象となるデータのパス式を指定する。またオプションで各操作で許可されるハンドラが存在。 キーワード 説明 操作 REMOVE 対象となる入力データを削除 KEEP 対象となる入力データ以外を削除 RENAME 対象となるフィールドの名前を変更 SET 対象となるデータに値を設定 (≒UPSERT） REPLACE 対象となるデータの値を置き換え INSERT 対象となる場所に値を挿入 APPEND 対象となる配列の末尾に値を追加（配列への挿入） ハンドラ ON EXISTING パス式がデータと一致した場合の動作を指定（ERROR/IGNORE/REPLACE/REMOVE) ON MISSING パス式がデータと一致しない場合の動作を指定（ERROR/IGNORE/CREATE) ON NULL 結果がNULLの場合に行う動作を指定（NULL/ERROR/IGNORE/REMOVE)
57. 例：SET、RENAME、APPENDを実行 JSON_TRANSFORM関数 Copyright © 2021, Oracle and/or its affiliates 57 update customers set cdata = JSON_Transform (cdata, SET '$.lastUpdated' = SYSTIMESTAMP, SET '$.address.zip' = 94402, RENAME '$.name' = 'firstName', APPEND '$.friends' = 'Beethoven' CREATE ON MISSING) 更新 -- JSON_SERIALIZEで確認 SQL> select JSON_Serialize(cdata PRETTY) from customers; JSON_SERIALIZE(CDATAPRETTY) -------------------------------------------- { "name" : "Atif", "address" : { "city" : "San Mateo" } } JSON_SERIALIZE(CDATAPRETTY) ---------------------------------------------------- { "address" : { "city" : "San Mateo", "zip" : 94402 }, "lastUpdated" : "2021-09-27T05:43:28.009609Z", "firstName" : "Atif", "friends" : [ "Beethoven" ] }
58. • JSONコンストラクタ、JSON_SCALAR関数の導入（初期化パラメータcompatibleが20以上の設定が必要） • SQL/JSONパス式の項目メソッドの拡張 SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 58
59. JSON_DATA ---------------------------------------- { "empno" : 7839, "name" : "KING", "salary" : 5000, "department" : { "name" : "ACCOUNTING", "loc" : "NEW YORK" } } ※実際には１行で表示 SELECT JSON { 'empno' : empno, 'name' : ename, 'salary' : sal, 'department' : ( SELECT JSON { 'name' : dname, 'loc' : loc } FROM dept d WHERE d.deptno = e.deptno ) } AS JSON_DATA FROM emp e WHERE e.empno = 7839; JSONコンストラクタ 入力されたテキストのJSON値（リテラルのSQL文字列、VARCAHR2/CLOB/BLOB型データ）を解析し、JSONデータ 型インスタンスとして返す テキストJSONデータの解析（= WHERE条件のIS JSONと等価)およびJSON型への変換に利用 例：JSONテキストに似た構文を使用してSQLにてJSON型の値を作成(SQLでJSON-likeな構築構文) SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 59
60. JSONコンストラクタ SQLでデータベース内の非JSONデータからJSONオブジェクトと配列を生成するときにも利用可能 • SQL/JSON関数JSON_OBJECTとJSON_ARRAYよりもJSON型指定が不要な分シンプルに記述できる • JSON(json_object( … ))またはjson_object( … RETURNING JSON) ＝ JSON { … } • JSON(json_array( … )) またはjson_array( … RETURNING JSON) ＝ JSON [ … ] SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 60 SQL> select JSON[1,2,'cat'] from dual; JSON[1,2,'CAT'] -------------------------------------------------------------------------------- [1,2,"cat"] SQL> select JSON(1,2,'cat’ returning JSON) from dual; JSON[1,2,'CAT’RETURUNINGJSON] -------------------------------------------------------------------------------- [1,2,"cat"]
61. JSON_SCALAR関数 SQLスカラー値を入力として受けいれ、対応するJSONスカラー値を返す 特長： • 拡張データ型（DATEなど）の利用可 • ソースのSQLデータ型を記憶 • テキストJSONデータの解析は行わない SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 61
62. 例：JSON_SCALAR関数 SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 62 SQL> desc customers 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- ID NUMBER CREATED TIMESTAMP(6) CDATA JSON -- CREATED列(SQLスカラー値)をJSONスカラー値に変換 SQL> select json_scalar(created) from customers; JSON_SCALAR(CREATED) -------------------------------------------------------------------------------- "2021-09-27T02:33:41.333275“ -- JSONスカラー値の型を確認 SQL> select json_value(json_scalar(created),'$.type()') from customers; JSON_VALUE(JSON_SCALAR(CREATED),'$.TYPE()') -------------------------------------------------------------------------------- Timestamp
63. SQL/JSONパス式の項目メソッドの追加 追加された項目メソッド • データ型変換：float()、double()、binary()、ymInterval()、dsInterval() • 集計：avg()、count()、minNumber()、maxNumber()、minString()、maxString()、sum() SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 63 集計の例 with jtab as (select JSON_Array(1,2,3,4,5,6) jcol from dual) select jtab.jcol[*].maxNumber() from jtab jtab; JTAB.JCOL[*].MAXNUMBER() ------------------------ 6 with jtab as (select JSON_Array(1,2,3,4,5,6) jcol from dual) select jtab.jcol[*].sum() from jtab jtab; JTAB.JCOL[*].SUM() ------------------ 21 with jtab as (select JSON_Array(1,2,3,4,5,6) jcol from dual) select jtab.jcol[*].avg() from jtab jtab; JTAB.JCOL[*].AVG() ------------------ 3.5
64. JSON型と項目メソッド JSON型のJSON値はそのまま直接比較やGROUP / ORDERBY式で使用できないため、項目メソッドを使用 SQL/JSON構文の改善 Copyright © 2021, Oracle and/or its affiliates 64 -- JSON型インスタンスの直接比較はエラー SQL> select count(1)from customers c where c.cdata.address.zip > 12345; ORA-40796: invalid comparison operation involving JSON type value -- JSON型インスタンスでGROUP/ORDER BY式はエラー SQL> select count(1)from customers c group by c.cdata.address.zip; ORA-40796: invalid comparison operation involving JSON type value -- 項目メソッドでデータ型変換すれば実行できる SQL> select count(1) from customers c where c.cdata.address.zip.number() > 12345 2 group by c.cdata.address.zip.number(); COUNT(1) ---------- 1
65. スカラー配列値の索引 JSON型に格納したJSONデータの文字列または数値の配列に対して複数値索引を作成可能 留意点 • JSON型に格納されたデータが対象 • 単一のスカラー値への索引作成も可能だが、性能面でB*Tree/bitmap索引利用を推奨 • 索引付けするデータのSQLデータ型を示すデータ変換項目メソッドを索引パス式に含めて作成 • 問い合わせではWHERE句にJSON_EXISTSを使用する • onlyを含む項目メソッドを索引作成に使用した場合は同じ項目メソッドを使用する問い合わせのみ、onlyを含まない 項目メソッドの場合は、指定した型に変換できるスカラー値を対象にできる JSON用複数値索引 Copyright © 2021, Oracle and/or its affiliates 65 SQL> create multivalue index cust_zip_idx on customers c (c.cdata.address.zip.number())
66. 例：表に配列をもつデータを追加 複数値インデックス Copyright © 2021, Oracle and/or its affiliates 66 -- データを追加 SQL> insert into customers values (2, systimestamp, '{"firstName":"Rodrigo", "address":[{"type":"home","city":"Sunnyvale","zip":94085}, {"type":"work","city":"Redwood Shores","zip":94065}]}'); 1行が作成されました。 --確認 SQL> select id,json_query(cdata,'$.address.zip' WITH CONDITIONAL WRAPPER) zip from customers; ID ZIP ---------- -------------------- 1 94402 2 [94085,94065
67. 例：複数値インデックスを作成し、検索 複数値インデックス Copyright © 2021, Oracle and/or its affiliates 67 -- 索引の作成 SQL> create multivalue index cust_zip_idx on customers c(c.cdata.address.zip.number()); 索引が作成されました -- 実行計画の取得 set autotrace trace exp -- JSON_Existsを条件にしたSELECT文を実行 ※実行計画は後に記載 SQL> select id from customers c 2 where JSON_Exists(cdata, '$.address?(@.zip.number()==94065)'); ID ---------- 2
68. 例：実行計画の確認 複数値インデックス Copyright © 2021, Oracle and/or its affiliates 68 実行計画 ---------------------------------------------------------- Plan hash value: 381260005 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 | 26 | 2 (0)| 00:00:01 | | 2 | HASH UNIQUE | | 1 | 26 | | | |* 3 | INDEX RANGE SCAN (MULTI VALUE) | CUST_ZIP_IDX | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(JSON_QUERY("CDATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.address.zip.number()' RETURNING NUMBER ASIS WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=94065) Note ----- - dynamic statistics used: dynamic sampling (level=2)
69. DBMS_JSON.CREATE_VIEWの引数追加 Autonomous Databaseでのみ提供されていた以下の引数が利用できるようになり、マテリアライズド・ビューやパスを指 定したサブセットでのビューを作成可能 ビューの作成および仮想列の追加の拡張 Copyright © 2021, Oracle and/or its affiliates 69 引数名 説明 materialize プール値で、ビューがマテリアライズドかどうかを示す mvrefreshmode materializeがtrueのとき、リフレッシュ・モードを指定する path 展開するJSONフィールドのパス。JSONパス式の構文を使用 resolvenameconflicts 仮想列名で競合が発生した場合の解決の有無。デフォルトはfalse（解決なし） colnameprefix ビュー列名に対する接頭辞の指定 mixedcasecolumns ビュー列名の大文字/小文字の区別を指定
70. 例：マテリアライズドビューの作成と定義の確認 ビューの作成および仮想列の追加の拡張 Copyright © 2021, Oracle and/or its affiliates 70 -- MVIEWの作成 declare dg clob; --データガイド用の変数 begin select json_dataguide(cdata,dbms_json.FORMAT_HIERARCHICAL) into dg from customers; dbms_json.create_view ( viewname => ‘MV_CUSTOMERS’, --MVIEW名 tablename=> ‘CUSTOMERS’, --JSON列を含む表名 jcolname => ‘CDATA’, --対象表のJSON列名 dataguide=> dg, materialize => true); end; / -- 情報の確認 SQL> select mview_name,query,refresh_mode,refresh_method from user_mviews; MVIEW_NAME QUERY REFRESH_M REFRESH_ ------------- ---------------------------- --------- -------- MV_CUSTOMERS SELECT RT."ROWID" ROW_ID, RT STATEMENT FAST SQL> select * from mv_customers; ROW_ID CREATED ID name city ------------------ ------------------------- ----- ---- ----------- AAASvnAAMAAAGw+AAA 21-10-29 17:27:28.053211 1 Atif San Mateo
71. 新しいJSON RFCのサポート（トップレベルのスカラー値を許可） RFC8259をサポートし、JSONドキュメントのトップレベルで スカラー値を利用可能 • 以前はRFC4627 • 初期化パラメータcompatible>20の設定が必要 • RFC8259サポートにした場合も、テキストJSONに対して 以前の動作（トップレベルのスカラー値の不許可）を設定 できる • IS JSON演算子のDISALLOW SCALARSを指定 RFC8259サポート Copyright © 2021, Oracle and/or its affiliates 71 -- サンプルデータを投入 create table whatsJson (val varchar2(30)); insert into whatsJson values(‘1’); insert into whatsJson values(‘true’); insert into whatsJson values(‘“true”’); insert into whatsJson values(‘null’); insert into whatsJson values(‘dog’); insert into whatsJson values(‘“cat”’); insert into whatsJson values(‘{}’); insert into whatsJson values(‘[]’); -- RFC8259(トップレベルのスカラー値OK） select val from whatsJson where val is json; 1 "true" true null "cat" {} [] select val from whatsJson where val is NOT json; dog -- 以前のRFCを強制（トップレベルのスカラー値を不許可） select val from whatsJson where val is json (DISALLOW SCALARS); {} []
72. プログラミング言語のクライアントドライバーについて 以前のバージョンのクライアントも利用可能だが21c以上のドライバ利用を強く推奨 • 21cより前のバージョンでは自動的にテキストJSONに変換され、JSON型の機能や性能のメリットを生かせないため JDBCの場合は、21cで新しいJSON型を操作するためのoracle.sql.jsonパッケージを提供 • 特長 • IS JSONのチェックが不要 • 拡張JSON型のサポート • JDBC21cのJSONパーサーによる最適化 • パッケージ詳細とサンプル • https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/sql/json/package-summary.html 以前のバージョンのクライアントと新しいJSON型の互換性 Copyright © 2021, Oracle and/or its affiliates 72
73. Copyright © 2021, Oracle and/or its affiliates 73 パッチ適用/アップグレード • パッチ適用手順の簡素化 • AutoUpgradeツール
74. • 概要 • 運用中のOracle Grid Infrastructureホームを 同じリリースの異なるRUのOracle Grid Infrastructureホームに切り替えることができるように • 例えば、以下のような切り替えが可能 • RU適用前環境から適用済み環境へ • 個別パッチ適用前環境から適用済み環境へ • RAC構成の場合ローリングアップグレードが可能 • メリット • 運用中の環境とパッチ適用済みの環境とをコマンドで切り替えることができるようになったことで よりシンプルにOracle Grid Infrastructureホームへのパッチ適用を可能に Oracle Grid Infrastructureホームへのパッチ適用と切り替え Copyright © 2021, Oracle and/or its affiliates 74 Oracle Grid Infrastructure 21.3 Oracle Grid Infrastructure 21.X $ mkdir -p /u01/app/21.X.0/grid $ chown grid:oinstall /u01/app/21.X.0/grid $ cd /u01/app/21.X.0/grid $ unzip -q download_location/grid.zip $ /u01/app/21.X.0/grid/gridSetup.sh –switchGridHome [-applyRU 適用するパッチの場所] [-applyOneOffs コンマ区切りの個別パッチの場所]
75. Oracle Database をアップグレードする唯一の推奨方法 • 1つのコマンドで、単一のデータベース、或いは多くのデータベースをまとめて アップグレードすることが可能な自動アップグレードユーティリティ • コンフィグファイルを編集し、ツールを実行して自動アップグレード • 事前チェックのみ / 実行前後にユーザ定義のスクリプト処理、など柔軟に実行可能 • RAC、Data Guard、Non-CDB構成に対応 • 既に世界で数千のお客様がご利用中 AutoUpgradeとは Copyright © 2021, Oracle and/or its affiliates 75 upg> Job Console 11.2.0.4 12.1.0.2 12.2.0.1 18.0 autoupgrade.jar MOS Note: 2485457.1 3MB config.cfg autoupgrade.jar 12.2.0.1 18.5.0 19.3
76. Data Guard環境でAutoUpgradeがより使いやすく • 概要 • Data Guard環境においてAutoUpgradeがより使いやすくなった • これまでのバージョンではData Guard Brokerに関する操作を手動で行う必要があったが、 AutoUpgradeの最新バージョンではツール側で自動的に有効/無効化が可能に • メリット • 最新のAutoUpgradeを利用することで以前のバージョンよりもユーザの介入要素が少なくなり 管理者の工数削減や作業ミスを低減 • 多くのデータベースを一括でアップグレードする場合に特に有効 AutoUpgradeによるデータベース・アップグレード時のData Guard操作の自動化 Copyright © 2021, Oracle and/or its affiliates 76
77. Data Guard環境でAutoUpgradeを利用する場合の概要手順 1. プライマリ、スタンバイに新しいバージョンのOracleホームをインストール 2. プライマリでAutoUpgradeを実行しデータベースをアップグレード このときAutoUpgradeが以下を自動設定 • REDO転送をDEFER(遅延)に設定 • Broker構成の場合アップグレード中はTRANSPORT-OFFに設定 3. スタンバイにて新しいOracleホームへの変更作業を実施 (環境変数の変更、tnsnames.ora、ブローカー構成ファイルなどを新しいOracleホームへコピー) 4. スタンバイデータベースにて構成とサービスをアップグレード srvctl upgrade database –db $ORACLE_UNQNAME -oraclehome $ORACLE_HOME 5. スタンバイデータベースを有効化し起動 6. REDO転送を再開 参考：AutoUpgradeによるData Guard環境のアップグレード Copyright © 2021, Oracle and/or its affiliates 77
78. • 実行手順動画 • 実行手順ブログ記事 https://dohdatabase.com/2021/01/05/how-to-upgrade-with-autoupgrade-and-data-guard/ 参考：AutoUpgradeによるData Guard環境のアップグレード Copyright © 2021, Oracle and/or its affiliates 78 YouTubeリンク
79. • 概要 • Oracle Real Application Clusters (Oracle RAC)環境においてAutoUpgradeがより使いやすくなった • AutoUpgrade 19.7以前はすべてのノードのインスタンスを手動でシャットダウンする必要があったが、 AutoUpgrade が自動実行してくれるようになった • Oracle Database 21cの新機能マニュアルに記載されているがAutoUpgrade 19.8以降ですでに利用可能 • メリット • 最新のAutoUpgrade を利用することで以前のバージョンよりもユーザの介入要素が少なくなり 管理者の工数削減や作業ミスを低減 • 多くのデータベースを一括でアップグレードする場合に特に有効 AutoUpgradeによる Oracle RACデータベースのアップグレードに必要なステップの自動化 Copyright © 2021, Oracle and/or its affiliates 79
80. • AutoUpgradeは自動的にOracle RAC構成を検出 • Oracle RAC、Oracle RAC One Nodeに対応 • 全ノードのインスタンスをシャットダウンするためダウンタイムが発生 • 対応OSはLinuxまたはUNIXベースのシステム Windowsは未対応 • Oracle Grid InfrastructureのアップグレードはAutoUpgradeの対象外 参考：AutoUpgradeによるOracle RAC環境のアップグレードの前提条件 Copyright © 2021, Oracle and/or its affiliates 80
81. 以下の手順をAutoUpgradeが自動実行する 1. 全データベース・インスタンスをシャットダウン 2. インスタンスの自動起動を無効化 3. インスタンスがOracle RACクラスタ・メンバーであった場合、 cluster_databaseパラメータをFALSEに設定しインスタンスをアップグレードモードで起動 4. Oracle Databaseホームバイナリをアップグレード 5. srvctl upgrade databaseの実行 6. srvctl enable databaseの実行 7. cluster_databaseパラメータをTRUEに設定 8. データベース・インスタンスを起動 9. 2node目以降のOracle Databaseホームバイナリアップグレード、データベース・インスタンスを起動 参考：Oracle RAC環境におけるAutoUpgradeの動作 Copyright © 2021, Oracle and/or its affiliates 81
82. • 概要 • AutoUpgradeを利用して非CDBからPDBへの変 換、およびアップグレードを自動化することが可能 • 前提条件 • ターゲットがOracle Database 21cの場合、 AutoUpgrade実行前にターゲットCDBの 作成が必須 • ソース非CDBのデータベース名は変更できずその ままPDB名となる • AutoUpgrade実行後に非CDB構成への ロールバックは不可 実行前のバックアップ取得を推奨 • 実行イメージ 1. ターゲットCDBの情報をコンフィグファイルに 記載しAutoUpgradeを実行 2. ターゲットデータベースでPDBへ変換 3. PDBをアップグレード AutoUpgradeによる非CDBからPDBへのアップグレードおよび変換の自動化 Copyright © 2021, Oracle and/or its affiliates 82 旧バージョン 新バージョン
83. Copyright © 2021, Oracle and/or its affiliates 83 マルチテナント • 非CDB構成はサポート対象外 • アプリケーション・コンテナの運用 • Linux namespaceとの組み合わせによるPDB単位の分離
84. 非CDB構成がサポート対象外に • 概要 • 非CDB構成はOracle Database 21cではサポート対象外 • Oracle Universal InstallerおよびDBCAを使用して非CDB Oracle Databaseインスタンスを作成不可に • マルチテナント・コンテナ・データベースは、 Oracle Database 21cで唯一サポートされているアーキテクチャとなる 詳細: 非CDBおよびCDBの使用 Copyright © 2021, Oracle and/or its affiliates 84 non-CDB構成 CDB構成 12c ～ 19c 21c ～ • 12cからnon-CDB構成は非推奨 • Non-CDB構成のサポートは19cまで • 21c以降ではCDB構成のみサポートされる
85. 12.2新機能の再確認 • R12.2 からの新機能 • 共通メタデータおよびデータ共有の仕組み • バージョニングされたセットのプロビジョニング アプリケーション・コンテナと実装例 Copyright © 2021, Oracle and/or its affiliates 85 https://docs.oracle.com/cd/F19136_01/multi/overview-of-the-multitenant-architecture.html#GUID-8C2E6389-7AFF-4071-8948-E44372E66050 名称 解説 図 アプリケーション・ ルート アプリケーション・コンテナを構成する際に必要 となる起点PDBを指し、CDB-root上で稼働するAP 固有の疑似的なCDBであると表現できる A アプリケーション・ PDB アプリケーション・コンテナ内に属するPDB B アプリケーション・ シード CDBシードと同様にアプリケーション・PDB を作成する場合の補助PDB C アプリケーション APコンテナを作成すると有効になるオブジェクト= 管理単位でバージョニングできる D Application Container の構成要素 A B C Application Container 概略図 Application D app_root CDB-ROOT app_pdb
86. 留意点 • 共有リンクオブジェクト変更は Alter … begin/end で行う alter pluggable database application MYFIRSTAPP begin install ‘1.0’; 変更処理 ～ alter pluggable database application MYFIRSTAPP end install ‘1.0’; • 変更後、アプリケーションPDBで sync する alter pluggable database xxx application MYFIRSTAPP sync; ※ sync しない AP-PDB は古い version で運用が継続される ->検証・開発などで効果的 ->version 管理は重要! アプリケーション・コンテナと実装例 Copyright © 2021, Oracle and/or its affiliates 86 Application ver. 1.0 更新した Application ver. 2.0 sync! ver. 1.0 ver. 1.0 ver. 2.0
87. • 機能概要 • ALTER PLUGGABLE DATABASE APPLICATION … SYNC文を使用することで、 アプリケーションPDBのバージョンやパッチ・レベルをアプリケーション・ルートと同期可能 このSYNC句にて複数のアプリケーションPDB名を指定することが可能に • メリット • Oracle Database 21cより前のリリースでは複数のアプリケーションPDBに対して同期を行いたい場合、 それぞれのアプリケーションPDBに対してALTER PLUGGABLE DATABASE APPLICATION … SYNC文 を発行する必要があった 21c以降では一文で複数のアプリケーションPDB名を指定して同期化可能 PDBアプリケーション同期化の拡張構文 Copyright © 2021, Oracle and/or its affiliates 87 SQL> ALTER PLUGGABLE DATABASE APPLICATION app1, app2 SYNC; Pluggable database altered.
88. データベース・ネスト • 機能概要 • データベース・ネスト(DbNest)はデータベースインスタンスを 同じホストで実行されている他のデータベース アプリケーションから分離することができる機能 • CDBとPDBはそれぞれ“ネスト”とよばれる独自のコンテナ環境に 存在しLinuxの機能を使って管理される • Linux: Namespaces, Cgroups, Capabilities, SECComp filtering • 各PDBネスト内のLinuxプロセスは独自のプロセスIDを持ち 他のネスト内のPDBにアクセスすることは不可 • メリット • Linux固有のコンテナ機能を用いて、CDBやPDBを階層化することで 従来のマルチテナント環境にない分離性を実現 • よりセキュアな構成を実現可能に データベースとのネームスペースの統合 Copyright © 2021, Oracle and/or its affiliates 88 OPERATING SYSTEM PDB 1 Nest PDB 2 Nest PDB 3 Nest CDB Nest
89. 初期化パラメータの有効化、構成ファイルの作成をすることでDbNestを有効化する • 初期化パラメータ • 構成ファイル • ファイルシステム分離を実現するためにマウントするディレクトリの ホワイトリスト、ブラックリストをテキストファイルで作成 DbNestの有効化 Copyright © 2021, Oracle and/or its affiliates 89 パラメータ 説明 DBNEST_ENABLE DbNest有効、無効化の管理を行うためにCDBルートに設定する • NONE：DbNestの無効化(デフォルト) • CDB_RESOURCE_PDB_ALL：すべてのPDBにネストを有効化しCDBはリソース のみのネストを有効化 ※use_dedicated_brokerパラメータが有効化されていることが前提 DBNEST_PDB_FS_CONF 構成ファイルの場所を指定するためにCDBルートに設定 DBNEST_NO_DEFAULT /home/oracle/MYCDB/$PDB DBNEST_NO_FS_ROOT_MODE /usr/local/bin /bin/usr/bin nest_whitelist.txt nest_blacklist.txt
90. Oracle Databaseが自動的にネストの作成、管理、および削除を行う Oracle Databaseによるネストの管理 Copyright © 2021, Oracle and/or its affiliates 90 •インスタンス起動時にCDBルートの親ネストを作成 •PDBごとに1つの子ネストを作成 •CREATE PLUGGABLE DATABASEコマンドで作成されたPDBの子ネストの作成を自動的にトリガー 作成 •PDBへの最初のログイン時にCDBがPDBの子ネストをオープン オープン •CPUカウントが変更された場合、ResourceManagerがネスト構成を自動的に更新 アップデート •PDBもしくはCDBルートからPDBをクローズするとバックグラウンドプロセスがPDB子ネストをクローズ クローズ •PDBが削除もしくはUnplugされるとPDB子ネストを削除 •データベースインスタンスがシャットダウンされるとCDBの親ネストを削除 削除
92. Our mission is to help people see data in new ways, discover insights, unlock endless possibilities. 92