TechNight#70 Oracle Database SQLの進化と開発者向け機能12.1-21c/technight70-SQL121-21c

TechNight#70 Oracle Database SQLの進化と開発者向け機能12.1-21c

YouTube動画はこちら
https://www.youtube.com/watch?v=lY2Bu2cVXzk

後半の23cのSQL新機能はこちら
https://speakerdeck.com/oracle4engineer/oracle-database-23c-sqlxin-ji-neng

[2023/10/06追記]
p36について、最新の情報を確認し、以下のように修正しました
・ パーティション操作はADD,DROP,RENAMEが可能、SPLIT、
MERGE、MOVEは内部パーティションについても不可
→
・外部パーティションに対してSPLIT、MERGE、MOVEメンテナンス操作はできない

Transcript

1. Oracle DatabaseのSQLの進化と開発者向け機能 Oracle Database Technology Night #70 2023年8月24日 日本オラクル株式会社
2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2 Copyright © 2023, Oracle and/or its affiliates 10/6/2023
3. 1. SQL実行確認環境のご紹介 2. 12.1 から21cまでのSQLおよび開発向け機能紹介 3. 23cでのSQLおよび開発向け機能紹介 アジェンダ Copyright © 2023, Oracle and/or its affiliates 3
4. https://livesql.oracle.com • ブラウザだけでSQLの実行を試すことができる（Oracleアカウント） • 現在（2023/8）、19.17 Enterprise Edition-Extreme Performance環境でSQL実行が可能 • 実行できないSQLについては、Live SQLの画面右上のHelpからご確認ください • 参照操作だけでなく、DDL文も実行し、データの挿入も可能(10MBまで)。セッションが切れると作成したオブジェク トは失われます • サンプルスキーマを含むデータセットを用意済み。こちらは参照操作を実行可能 • サンプルスキーマ：Human Resources (HR), Sales History (SH), Order Entry (OE) • 他に利用可能なスキーマ：EMP and DEPT (SCOTT), World Data (WORLD), Analytic Views (AV) , Olympic Data (OLYM) , Academic (AD) • スクリプトやチュートリアルに従ってSQL実行しながら機能確認をできる • 自分のカスタムチュートリアルやスクリプトの保存もでき、公開範囲も設定可 Oracle Live SQL Copyright © 2023, Oracle and/or its affiliates 4 ご参考：Oracle Live SQLで実機確認！ORACLE MASTER Silver SQL 2019 https://blogs.oracle.com/oraclemaster/post/silver-livesql
5. 開発者向けを先にリリース Oracle Database 23c Free – Developer Release • Oracle Database 23cの新機能に容易にアクセス可能 • 最新のデータ・ドリブン・アプリケーション開発を容易にする23cの新機能をいち早くお試 しいただき、アプリケーション構築にお役立てください • フルセットを提供するOracle Database 23cは別途リリース予定です • 以下の形式でリリース (Windows版も近日リリースを予定) • Docker image • VirtualBox VM • Linux RPM file • 以下の条件でご利用可能です Free Use Terms & Conditions license • 従来のExpress Editionの後継 • 利用可能なCPU（2Core)、ストレージ(12GB)、メモリ(2G)に制限があります • サポートや保証はありません • 利用可能な機能は下記ドキュメントをご参照ください https://docs.oracle.com/en/database/oracle/oracle-database/23/dblic/Licensing- Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87 Oracle Database 23c FREE 関連情報まとめ https://bit.ly/Qiita-23c Copyright © 2023, Oracle and/or its affiliates 5
6. Copyright © 2023, Oracle and/or its affiliates 6 12c R1 (2013) 新機能
7. NEW IN 12.1 • VARCHAR2の最大サイズが32767バイトまで拡張可能に • 11.2までは4000バイト • MAX_STRING_SIZEパラメータをEXTENDEDに設定、設定は不可逆で一度設定するとSTANDARD（従来の設定）には戻せない • カラムに4000バイトより大きいVARCHAR2を定義 VARCHAR2の最大サイズが32kまで拡張可能に Copyright © 2023, Oracle and/or its affiliates 7 SHUTDOWN IMMEDIATE //DBシャットダウン STARTUP UPGRADE //アップグレードモードで起動 ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED; $ORACLE_HOME/rdbms/admin/utl32k.sql SHUTDOWN IMMEDIATE //DB再起動 STARTUP //DB再起動 CREATE TABLE products ( id NUMBER PRIMARY KEY, name VARCHAR2(255), description VARCHAR2(32767), release_date DATE );
8. • INSERT時に採番した数値をいれなくとも、自動的にインクリメントする列 • 11.2まではインサートトリガーを用いてシーケンスから採番する実装が必要。12.1ではこのIDENTITY列を使用することでシーケンス の別途作成が不要に。また、12.1では事前にシーケンスを作成し、カラムのデフォルト値として指定する方法も可能 • IDENTITY列と内部作成されたシーケンスはALL_TAB_IDENTITY_COLSで確認。シーケンスの変更はALTER SEQUENCEでは なくALTER TABLEで実施 • ユースケース：プライマリーキー, ドキュメント番号, イベント番号, トレースID, 他一意のIDを持つカラム IDENTITY 列 (ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 8 CREATE TABLE tickets ( ticket_id NUMBER GENERATED AS IDENTITY, description VARCHAR2(255) ); CREATE TABLE tickets ( ticket_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10), description VARCHAR2(255) ); NEW IN 12.1 //事前にシーケンス作成 CREATE SEQUENCE test_seq; //デフォルト値として定義 CREATE TABLE tickets ( ticket_id NUMBER DEFAULT test_seq.nextval, description VARCHAR2(255) ); 参考）シーケンスを事前に作成してデフォルト値とする場合
9. • SELECT文の結果を並べて特定行だけ抜き出すSQL文の記述が簡略化 • 11.2まではROW_NUMBER関数を使用 • OFFSET n ROWS FETCH FIRST m ROWS ONLYと記述することで、SELECT結果からn件とばして次のm件を表示の意味 • OFFSET句を省略し、FETCH FIRST n ROWS ONLYとするとTopN件問い合わせとなる • ユースケース：Webページのページ送り画面の実装、TopN件の問い合わせ 行制限(ISO SQL 標準)OFFSET句とFETCH FIRST句 Copyright © 2023, Oracle and/or its affiliates 9 SELECT first_name, last_name, hire_date FROM employees ORDER BY hire_date OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY; SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES; パーセント指定も可能で、右の例では、PERCENTを利用し、 同順位を含む（WITH TIES）最初の５％をSELECT NEW IN 12.1 SELECT first_name, last_name, hire_date FROM ( SELECT first_name, last_name, hire_date, ROW_NUMBER() OVER (ORDER BY hire_date) ranking FROM hr.employees ) WHERE ranking BETWEEN 6 AND 10; 11.2まで 12.1から
10. •WITH句の中にPL/SQLファンクションを含めることで別途ファンクションを作成せずにSQL内で記述。 • 複雑な計算をSQLの外だしすることでSELECT文の複雑化を防ぐ • 読み取り専用DBへの一時的なファンクション使用での活用 SQLのWITH句内にPL/SQL ファンクションを記述 Copyright © 2023, Oracle and/or its affiliates 10 WITH FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS pos BINARY_INTEGER; len BINARY_INTEGER; BEGIN pos := INSTR(url, 'www.'); len := INSTR(SUBSTR(url, pos + 4), '.') - 1; RETURN SUBSTR(url, pos + 4, len); END; SELECT DISTINCT get_domain(catalog_url) FROM orders; NEW IN 12.1
11. •データの値ではなく、データの値の変化のパターンに従って該当する行を検索 • 項目/行にまたがるパターンを検出する機能 • 単一のSQL内でのパターン分析 •ユースケース: • 金融のトランザクション: 株のチャート分析、不正検知 • クリックストリームログ: Webサイトの閲覧者の訪問から離脱までのページ遷 移のログ • テレコミュニケーション: 不在着信の分析 • 医療センサ: 観測と検出の自動化 • セキュリティ:サイトのアクセスパターン分析 • … SQLパターンマッチング(ISO SQL標準) Copyright © 2023, Oracle and/or its affiliates 11 SELECT * FROM employees MATCH_RECOGNIZE ( … PATTERN(X+ Z{2}) … ); 昇順 パターンは正規表現を使っ て定義する NEW IN 12.1
12. SQL パターンマッチング Copyright © 2023, Oracle and/or its affiliates 12 PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) days X NEW IN 12.1 Stock price • ダブルボトム (“W”) パターンを検出しレ ポートする: 1. 価格下落を定義
13. SQL パターンマッチング Copyright © 2023, Oracle and/or its affiliates 13 PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) X Y Stock price NEW IN 12.1 days Stock price • ダブルボトム (“W”) パターンを検出しレ ポートする: 1. 価格下落を定義 2. 価格上昇を定義
14. SQL パターンマッチング Copyright © 2023, Oracle and/or its affiliates 14 days SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) W AS (price < PREV(price)) Z AS (price > PREV(price)) ); X Y W Z Stock price NEW IN 12.1 • ダブルボトム (“W”) パターンを検出しレ ポートする: 1. 価格下落を定義 2. 価格上昇を定義 3. 2番目の価格下落を定義 4. 2番目の価格上昇を定義 5. MEASURES句で表示するのは 最初と最後のデータポイント （time）を指定 6. 銘柄(name) 列の値で、論理グ ループ（Partition）を構成 7. time列でソート
15. SQL パターンマッチング Copyright © 2023, Oracle and/or its affiliates 15 • ダブルボトム (“W”) パターンを検出しレ ポートする: 1. 価格下落を定義 2. 価格上昇を定義 3. 2番目の価格下落を定義 4. 2番目の価格上昇を定義 5. MEASURES句で表示するのは 最初と最後のデータポイント （time）を指定 6. 銘柄(name) 列の値で、論理グ ループ（Partition）を構成 7. time列でソート SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) W AS (price < PREV(price)) Z AS (price > PREV(price)) ); 1 9 13 19 days first_x last_z 1 9 13 19 Stock price NEW IN 12.1
16. • シンプルなクエリ（ドット記法） ネイティブ JSON をサポート (ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 16 SELECT m.data FROM movies m WHERE m.data.releasedIn = 'US'; SELECT JSON_VALUE(data, '$.actors[0].name') FROM movies WHERE JSON_VALUE(data, '$.revenue' RETURNING NUMBER(12) ) > 10000000; • JSONパス式を用いた高度な問合せ • データベースからJSONドキュメントを格納と 取り出しを実施する • SQLでJSONドキュメントを検索 • リレーショナルデータとJSONデータをまたがる 検索と結合 • ACIDトランザクションのJSONへの拡張 • 全てのデータに同じアクセスルールとコンプラ イアンスルールを適用 NEW IN 12.1
17. Copyright © 2023, Oracle and/or its affiliates 17 12c R2新機能 (2016)
18. オブジェクト名の最大長が128バイトに拡張(ISO SQL標準) Copyright © 2023, Oracle and/or its affiliates 18 CREATE TABLE VERY_VERY_LONG_TABLE_NAME_IDENTIFIER ( VERY_VERY_LONG_TEXT_COLUMN_WITH_DATA_TYPE_VARCHAR2_25 VARCHAR2(25) ); Table VERY_VERY_LONG_TABLE_NAME_IDENTIFIER created. INSERT INTO VERY_VERY_LONG_TABLE_NAME_IDENTIFIER VALUES ('Hello World!'); 1 row inserted. SELECT * FROM VERY_VERY_LONG_TABLE_NAME_IDENTIFIER; VERY_VERY_LONG_TEXT_COLUM ------------------------- Hello World! NEW IN 12.2 • オブジェクト名（表、列、ビュー、ストアド・プロシージャ、ファンクションなど）の最大長が30バイトから128バイトに拡張 • ユースケース • マルチバイトが必要な日本語オブジェクト名 • 一般的でない略称の回避
19. • デフォルトでは文字列の照合をバイナリで 実施するため、大文字小文字は区別さ れる • 大文字小文字を区別しないコレーション であるBINARY_CIを列単位またはテーブ ル単位で適用可能に • 未指定の列のコレーションは親テーブルま たはスキーマのデフォルトのコレーションプロ パティから継承される • COLLATE 演算子は式の任意の場所で、 明示的なコレーションをキャストが可能 • コレーションはそろっていないと結合に失敗 する、またソート結果も変化することに注 意 大文字と小文字を区別しない文字照合（COLLATION） Copyright © 2023, Oracle and/or its affiliates 19 CREATE TABLE product ( id NUMBER, name VARCHAR2(50) COLLATE BINARY_CI, comments VARCHAR2(500) ) DEFAULT COLLATION BINARY; SELECT name, comments FROM product WHERE name LIKE '%BASE%' OR comments COLLATE BINARY_CI LIKE '%REPORT%'; NAME COMMENTS _ Oracle Database Activity-Based Management Business Intelligence Replaces Reports _CI = case-insensitive 表のBINARY設定を継承 Uses runtime collation NEW IN 12.2
20. • コンパイル時に、非推奨関数であることの警告メッセージを出すことが可能に • プラグマ：コンパイラに特定の情報を渡すために使用するコンパイラ指令 • コンパイル時の警告のため、実行時に警告は出力されない PL/SQL deprecateプラグマの追加（非推奨関数であることをメッセージで警告） Copyright © 2023, Oracle and/or its affiliates 20 CREATE PROCEDURE p AUTHID DEFINER IS PRAGMA DEPRECATE (p, 'Use p2 instead.'); BEGIN DBMS_OUTPUT.PUT_LINE('p'); END p; PLW-06019: entity P is deprecated CREATE PROCEDURE q AUTHID DEFINER IS BEGIN p(); DBMS_Output.Put_Line('q'); END q; PLW-06020: reference to a deprecated entity: Use p2 instead. NEW IN 12.2
21. PL/SQL の JSON サポート Copyright © 2023, Oracle and/or its affiliates 21 WITH FUNCTION updateTax(JSON_DOC in VARCHAR2 ) RETURN VARCHAR2 IS jo JSON_OBJECT_T; //JSONオブジェクトのタイプを宣言、インスタンスを生成し要素の追加削除などを実施可能 price NUMBER; taxRate NUMBER; BEGIN jo := JSON_OBJECT_T(JSON_DOC); //JSON_OBJECT_Tのインスタンスを生成 taxRate := jo.get_Number('taxRate'); //キーのペア値を数値で取得 price := jo.get_Number('total'); jo.put('totalIncludingTax', price * (1+taxRate)); //キー・バリューペアの追加 RETURN jo.to_string(); //キーとペアの値を文字列で取得 END; ORDERS AS ( SELECT '{"taxRate":0.175,"total":10.00}' JSON_DOCUMENT FROM dual ) SELECT JSON_DOCUMENT, updateTax(JSON_DOCUMENT) FROM ORDERS; JSON_DOCUMENT UPDATETAX(JSON_DOCUMENT) ------------------------------- --------------------------------------------------------- {"taxRate":0.175,"total":10.00} {"taxRate":0.175,"total":10.00,"totalIncludingTax":11.75} NEW IN 12.2 • JSONオブジェクトの作成および編集が可能なオブジェクトタイプやメソッドが追加
22. • VALIDATE_CONVERSION関数でデータ型が変換可能か検証が可能に • 返り値が1の場合、変換可能1。 0の場合、変換不可 データ型変換の検証 Copyright © 2023, Oracle and/or its affiliates 22 SELECT VALIDATE_CONVERSION('123' AS NUMBER) AS valid FROM dual; VALID ----- 1 SELECT VALIDATE_CONVERSION('abc' AS NUMBER) AS valid FROM dual; VALID ----- 0 -- Get all values that will not convert SELECT * FROM metrics_ext WHERE VALIDATE_CONVERSION(recorded_date AS DATE, 'YYYY-MM-DD HH24:MI:SS') = 0; ID RECORDED_DATE VALUE ------ -------------------- ------ 213441 22/23/12 04:23:15 pm 232.44 817 22/19/03 02:30:00 pm 15.76 NEW IN 12.2
23. • LISTAGG関数：集計単位ごとの値リストを作成する関数。リストは1つの列内に横並びで表示。区切り文字は指定 • LISTAGG関数を通して、オーバーフローしたデータを切り捨てて表示 • MAX_STRING_SIZE=STANDARDで4000バイト、EXTENDEDで32767バイトまで表示 • 末尾にトランケートして切り捨てた行数の表示、WITHOUT COUNT句で非表示も可 LISTAGG OVERFLOW Copyright © 2023, Oracle and/or its affiliates 23 SELECT LISTAGG(object_name, ', ') FROM all_objects; * ERROR at line 1: ORA-01489: result of string concatenation is too long SELECT LISTAGG(object_name, ', ' ON OVERFLOW TRUNCATE '... more objects') FROM all_objects; LISTAGG(OBJECT_NAME,','ONOVERFLOWTRUNCATE'...MOREOBJECTS') -------------------------------------------------------------------------------- , CON$RECYCLE, CON$RECYCLE_CON#_UNIQUE_KEY, JSONMAP_VIEW$, SYS_LOB0000000291C000 02$$, I_JSONMAP_VIEW$, OBJNUM_REUSE, I_OBJNUM_REUSE1, I_OBJNUM_REUSE2, DOMAIN$, SYS_LOB0000000298C00012$$, SYS_LOB0000000298C00010$$, SYS_LOB0000000298C00008$$, SYS_LOB0000000298C00006$$, I_DOMAIN, DOMAIN_COL$, SYS_LOB0000000308C00006$$, I_ DOMAIN_COL, DOMAIN_CDEF$, SYS_LOB0000000312C00011$$, I_DOMAIN_CDEF, DOMAIN_CCOL$ , COL_DOMAIN$, DOMAIN_CON$, ... more objects(76528) NEW IN 12.2
24. • 集計計算の近似値算出。概算だが高速に集計結果を返す。エラー率1%以内、大規模データでは10倍高速化 • 近似問合せ関数APPROX_COUNT_DISTINCT、APPROX_MEDIAN 、APPROX_PERCENTILE • セッションもしくはシステムレベルでapprox_for_aggregationパラメータをtrueにすることで、count(distinct [カラム 名])などを自動で近似値計算バージョンに変更可能 • APPROX_COUNT_DISTINCTは統計情報のNDV計算でも使用される • ESTIMATE_PERCENTパラメータがDBMS_STATS.AUTO_SAMPLE_SIZE（デフォルト）のとき 近似問合せ処理 Copyright © 2023, Oracle and/or its affiliates 24 SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers" FROM sales GROUP BY prod_id ORDER BY prod_id; PROD_ID Number of Customers ---------- ------------------- 13 2516 14 2030 15 2105 16 2367 . . . NEW IN 12.2
25. • SELECT結果をJSON形式で出力 JSON 生成 (ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 25 SELECT JSON_OBJECT('Id' IS employee_id, 'FirstName' IS first_name, 'LastName' IS last_name) AS JSON_DOC FROM hr.employees WHERE employee_id = 100; JSON_DOC ------------------------------------------------------------- { "Id" : 100 , "FirstName" : "Steven" , "LastName" : "King" } NEW IN 12.2
26. • JSON ドキュメントの構造を分析してガイド • JSONデータガイドを引数にして仮想カラムやビューを自動生成する ファンクションを提供 • DBMS_JSON.CREATE_VIEW() • DBMS_JSON.ADD_VIRTUAL_COLUMN() JSON データガイド Copyright © 2023, Oracle and/or its affiliates 26 SELECT tweet FROM tweets; TWEET ------------------------------------------------- { "created_at": "Thu Apr 06 15:24:15 +0000 2017", "id_str": "850006245121695744", "text": "Yay Twitter!", "user": { "id": 2244994945, "name": "Twitter Dev", "screen_name": "TwitterDev", "location": "Internet", "url": "https://dev.twitter.com/" } } SELECT JSON_DATAGUIDE(tweet) FROM tweets; JSON_DATAGUIDE(tweet) ---------------------------- [ { "o:path": "$", "type": "object", "o:length": 256 }, { "o:path": "$.text", "type": "string", "o:length": 16 }, { "o:path": "$.user", "type": "object", "o:length": 128 }, { "o:path": "$.user.id", "type": "number", "o:length": 16 }, { "o:path": "$.user.url", "type": "string", "o:length": 32 }, … NEW IN 12.2
27. Copyright © 2023, Oracle and/or its affiliates 27 18c新機能 (2018)
28. NEW IN 18c • SQL文中に外部表定義を記述 • 一度しか使わない外部表を作成する必要がない • CSVからのデータロードの効率化。オブジェクトが不用意に増えることを防ぐ インライン外部表 Copyright © 2023, Oracle and/or its affiliates 28 CSVからのデータロードの効率化 CREATE TABLE metrics_xt ( sensor_id NUMBER, … ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER … LOCATION ('metrics.csv') REJECT LIMIT UNLIMITED ); INSERT INTO metrics SELECT * FROM metrics_xt; DROP TABLE metrics_xt; INSERT INTO metrics SELECT metrics_xe.* FROM EXTERNAL( (sensor_id NUMBER, … ) TYPE ORACLE_LOADER … LOCATION ('metrics.csv') REJECT LIMIT UNLIMITED ); 12.2まで 18cから
29. • 異なるデータソース(table, JSON, etc.)をまたいでアルゴリ ズムをカプセル化し再利用 • テーブル名や列名を引数にとり加工・演算した結果を出力 • 入力・出力の列数を可変にできる Polymorphicテーブル関数(ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 29 SELECT state_id, …, AVG(credit_score), risk FROM CREDIT_RISK( tab => prod.customers, cols => columns(dob, zip,loan_default), outs => columns(credit_score, risk_level)) WHERE risk_level = 'High' GROUP BY state_id; CREDIT RISK ALGORITHM SQL QUERY POLYMORPHIC TABLE FUNCTION INPUTS: TABLE JSON XML ANALYTIC VIEW PROD.CREDIT_RISK H H H H STATE_ID RISK A_SCORE POP LOANS A_LOAN NEW IN 18c Tech Night 2018 10/10(水) ～ Oracle 18c の新機能や機能拡張ご紹介 ～ Development と Performance 関連の機能強化 https://www.oracle.com/jp/a/ocom/docs/jp-db-technight-content/22-1- development-dl-final.pdf Tech Night 2019 02/21(木) PL/SQL最新方法 https://www.oracle.com/jp/a/ocom/docs/jp-db-technight-content/26-1-plsql- dl-final.pdf
30. • セッションを終了させることなく、暴走したクエリを停止させる • セッションは継続するため、後続のSQLの処理は実行される • 構文：ALTER SYSTEM CANCEL SQL '[SID], [SERIAL], @[INST_ID], [SQL_ID]'; • SID（必須）セッションID • SERIAL（必須）セッションシリアル番号 • INST_ID（オプション）インタンス番号、省略時はSQL実行インスタンス • SQL_ID （オプション）SQL番号、省略時はセッションでアクティブなSQL アクティブなSQLがない場合に省略すると次のSQLがキャンセルされる SQLのキャンセル Copyright © 2023, Oracle and/or its affiliates 30 ALTER SYSTEM CANCEL SQL '181, 12538'; System altered. SELECT … FROM products p, sales s, countries c WHERE p.product_id = s.product_id AND s.country_id = c.country_id; … (長時間実行継続) ERROR: ORA-01013: User requested cancel of current operation. SELECT COUNT(*) FROM sales; COUNT(*) ------------ 7798141292 NEW IN 18c
31. • TOPN問合せに対する近似計算 • 概算だが高い精度（エラー率 < 0.5%）で高速処理、大規模データでは10倍以上の高速化 • 12.1のTopN問合せ(FETCH FIRST n ROWS ONLY)と12.2の近似問合せ処理の組み合わせ進化系 • 新しい近似関数：APPROX_COUNT()、APPROX_SUM()、APPROX_RANK() 近似上位N件問合せ処理 Copyright © 2023, Oracle and/or its affiliates 31 //近似ヒット数によるトップ 5 blogs SELECT blog_post, APPROX_COUNT(*) FROM weblog GROUP BY blog_post FETCH FIRST 5 ROWS ONLY; //各地域のトップ 50 顧客とおおよその支出 SELECT region, customer_name, APPROX_RANK(PARTITION BY region ORDER BY APPROX_SUM(sales) DESC) appr_rank, APPROX_SUM(sales) appr_sales FROM sales_transactions GROUP BY region, customer_name HAVING APPROX_RANK(..) <=50; NEW IN 18c
32. • SDO_UTIL.FROM_GEOJSON(), JSONをSpatialのジオメトリオブジェクトに変換 • SDO_UTIL.TO_GEOJSON(), SpatialジオメトリオブジェクトをGeoJSONに変換 (Geo)SpatialのJSONサポート Copyright © 2023, Oracle and/or its affiliates 32 SELECT ROUND( SDO_GEOM.SDO_DISTANCE( -- Marseille [longitude, latitude] SDO_UTIL.FROM_GEOJSON('{"type": "Point", "coordinates": [-5.380583, 43.280427]}'), -- Paris SDO_UTIL.FROM_GEOJSON('{"type": "Point", "coordinates": [-2.346941, 48.858884]}'), 0.01, 'unit=KM'), 2) AS distance_in_km FROM dual; DISTANCE_IN_KM -------------- 662.85 NEW IN 18c
33. • 値が必要とされる場所で、複雑な値をコンパクトな形式で宣言・定義可能 PL/SQL 修飾式 Copyright © 2023, Oracle and/or its affiliates 33 DECLARE arr DBMS_SQL.NUMBER_TABLE; BEGIN arr := DBMS_SQL.NUMBER_TABLE ( 1 => 1, 2 => 2, 3 => 3, 4 => 4, 5 => 5); END; DECLARE TYPE t_aa IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER; v_aa1 t_aa := t_aa(1 => 'Maria', 2 => 'Martin', 3 => 'Gerald'); BEGIN DBMS_OUTPUT.PUT_LINE(v_aa1(1)); DBMS_OUTPUT.PUT_LINE(v_aa1(2)); DBMS_OUTPUT.PUT_LINE(v_aa1(3)); END; Maria Martin Gerald NEW IN 18c
34. Copyright © 2023, Oracle and/or its affiliates 34 19c新機能 (2019)
35. • LISTAGG内にDISTINCTを追記することで、重複を除外したLISTAGGを実施 LISTAGG DISTINCT Copyright © 2023, Oracle and/or its affiliates 35 SELECT d.dname, LISTAGG (e.job,', ' on overflow truncate with count) WITHIN GROUP (order by e.job) jobs FROM scott.dept d, scott.emp e WHERE d.deptno = e.deptno GROUP BY d.dname; SELECT d.dname, LISTAGG (DISTINCT e.job,', ' on overflow truncate with count) WITHIN GROUP (order by e.job) jobs FROM scott.dept d, scott.emp e WHERE d.deptno = e.deptno GROUP BY d.dname; NEW IN 19c DISTINCT無し DISTINCT有り https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html
36. • 外部表を含めたパーティション表の作成 • 古くなったパーティションを、ストレージに外だしすることで、DB から参照できる形にして残しておける • 単一のレンジおよびリスト・パーティションのみ対応 • 外部パーティションについてはREAD ONLYになる • 外部パーティションに対してSPLIT、MERGE、MOVEメンテ ナンス操作はできない ハイブリッドパーティション表 Copyright © 2023, Oracle and/or its affiliates 36 Q4_2018 Q3_2018 Q2_2018 Q1_2018 Q4_2017 Q3_2017 Q2_2017 Q1_2017 Q4_2016 ORDERS OBJECT STORAGE TABLE PARTITIONED BY QUARTER SQL HOT DATA COLD DATA UPDATES CREATE TABLE hybrid_partition_orders ( prod_id NUMBER NOT NULL, cust_id NUMBER NOT NULL, time_id DATE NOT NULL, channel_id NUMBER NOT NULL, promo_id NUMBER NOT NULL, quantity_sold NUMBER(10,2) NOT NULL, amount_sold NUMBER(10,2) NOT NULL ) EXTERNAL PARTITION ATTRIBUTES ( TYPE ORACLE_LOADER DEFAULT DIRECTORY sales_data … ) PARTITION BY RANGE (time_id) ( PARTITION sales_2022 VALUES LESS THAN ('01-01-2023'), PARTITION sales_2021 VALUES LESS THAN ('01-01-2022') EXTERNAL LOCATION ('sales2021_data.txt'), PARTITION sales_2020 VALUES LESS THAN ('01-01-2021') EXTERNAL LOCATION ('sales2020_data.txt') NEW IN 19c
37. • SQL/JSON 文法の簡易化: • JSON_TABLEの代わりにNESTEDを利用することでシンプルなパスの表現に改善 • JSON_OBJECT の拡張: カラム名から属性名を導出する • JSON_MERGEPATCH:JSONドキュメントの書き換え、部分更新を高速に実施 • JSONとObject Typeのマッピング：オブジェクト型をJSONでハンドリング可能に • JSON_SERIALIZE: JSONドキュメントを保存するデータ型をVARCHAR2/CLOB/BLOBに変換、整形して表示 • JSON_DATAGUIDE関数: GeoJSONのサポート JSON サポートの拡張 Copyright © 2023, Oracle and/or its affiliates 37 NEW IN 19c [スライド]Agile Database Development with JSON https://speakerdeck.com/chrissaxon/agile-database-development-with-json [blog]How to Store, Query, and Create JSON Documents in Oracle Database https://blogs.oracle.com/sql/post/how-to-store-query-and-create-json-documents-in-oracle-database
38. • GROUP BY 文を簡潔にし、MINやMAXを使うのに比べて、パフォーマンスを向上させるファンクション • グループ内での最初の値を返すように最適化されており、グループ内のランダムな値が返る • 行の比較を行わないため高速 • GROUP BY 句の一部としてすべての列を指定する必要がない ANY_VALUE() Copyright © 2023, Oracle and/or its affiliates 38 SELECT c.country, ANY_VALUE(s.sales_rep_name), SUM(s.amount_sold) FROM customers c, sales s WHERE s.cust_id = c.cust_id GROUP BY c.cust_id; COUNTRY ANY_VALUE(SALES_REP_NAME) SUM(AMOUNT_SOLD) ------- -------------------------- ---------------- USA Sandburg 788 Austria Oliver 3201 ... 34 rows selected. NEW IN 19c
39. • ビット・ベクターSQL演算子を使用してCOUNT DISTINCT操作を高速化 • マテリアライズド・ビューを使用し、通常のCOUNT(DISTINCT)で記述したSQLをリライトして活用可能 • 12.2のAPPROX_COUNT_DISTINCTとは近似計算だが、こちらは正確な値をビットマップ演算を使用して高速化。 対象は数値型のみ ビットマップ・ベースのCOUNTの個別SQLファンクション Copyright © 2023, Oracle and/or its affiliates 39 //マテリアライズド・ビュー作成 CREATE MATERIALIZED VIEW mv_aaa ENABLE QUERY REWRITE AS SELECT c3, c4, BITMAP_BUCKET_NUMBER(c10) as bm_bktno , BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(c10),'RAW') as bm_details FROM aaa GROUP BY c3, c4, BITMAP_BUCKET_NUMBER(c10); //SQL実行 SELECT c4, COUNT(DISTINCT c10) FROM aaa GROUP BY c4; //内部的にリライトされて実行されるSQL SELECT c4, SUM(BITMAP_COUNT(bm_details)) FROM mv_aaa GROUP BY c4;
40. Copyright © 2023, Oracle and/or its affiliates 40 21c 新機能(2021)
41. • 複雑な処理をSQL文中のどこでも使用できる再利用可能なマクロとしてファンクションとして作成可能に • 動作するときは、ファンクション実行ではなく、SQL文に該当する文を埋め込んだ形でSQLとして実行される。ファンクショ ン実行ではないため、動作が軽くなる • SQLマクロの種類 • スカラー式, SELECT, WHERE, GROUP BY ,HAVING 句で使用 • テーブル式, FROM句で計算やビジネスロジックのカプセル化のために使用 19.7にバックポート SQL マクロ Copyright © 2023, Oracle and/or its affiliates 41 NEW IN 21c
42. • SELECT, WHERE, GROUP BY ,HAVING 句で使用 • 以下例のようにSQLマクロで定義した部分がSQL文中に埋め込まれ実行される • どのようにSQL文として展開されるかはDBMS_UTILITY.EXPAND_SQL_TEXT()プロシージャで確認可能 SQL マクロ スカラー式 Copyright © 2023, Oracle and/or its affiliates 42 NEW IN 21c CREATE FUNCTION concat_self (str VARCHAR2, cnt NUMBER) RETURN VARCHAR2 SQL_MACRO(SCALAR) IS BEGIN RETURN 'RPAD(str, cnt * LENGTH(str), str)'; END; / SELECT last_name, concat_self(last_name,2) FROM hr.employees; SELECT last_name, RPAD(last_name, 2 * LENGTH(last_name), last_name) FROM hr.employees; LAST_NAME NAME --------- ---------------- Abel AbelAbel Ande AndeAnde Atkinson AtkinsonAtkinson ... 107 rows selected.
43. テーブル式 Parameterized ViewsとPolymorphic Views Parameterized Views（テーブル引数を持たない） • マクロ定義の中でクエリで使用されるテーブルは固定されている • 上記のような表から選択される行を引数で渡して行う Polymorphic Views（1つ以上のテーブル引数を持つ） • クエリに渡された入力テーブルは、マクロから返されるクエリの中で使 用される • テーブル引数はDBMS_TF.TABLE_Tタイプ(18cで登場した Polymorphicテーブル関数)を使用 SQL マクロ テーブル式 19.7にバックポート Copyright © 2023, Oracle and/or its affiliates 43 CREATE OR REPLACE FUNCTION total_sales(zip_code varchar2) RETURN varchar2 SQL_MACRO(TABLE) IS BEGIN RETURN q'{ SELECT c.cust_postal_code zip_code, SUM(amount_sold) revenue FROM customers c, sales s WHERE s.cust_id = c.cust_id AND c.cust_postal_code = total_sales.zip_code GROUP BY zip_code ORDER BY zip_code }'; END; / SELECT * FROM total_sales('60332'); 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; / SELECT * FROM row_sampler(t=>sh.customer, pct=>15); NEW IN 21c
44. • インサートのみが許されるテーブル。不正な変更からデータを保護 • 耐改ざん性に特化したテーブルで、証跡性の特性はない。 • DROPとDELETEについては保持期間を指定、その他の変更操作に関しては不可 • DROP：保持期間を過ぎたら操作可能。無期限操作不可指定も可。 INSERT前であれば削除可 • DELETE：保持期間を過ぎたら操作可能。無期限操作不可指定も可。 DBMS_IMMUTABLE_TABLE.delete_expired_rows()で保持期限をすぎた表の一括削除も可能 不変表（Immutable Table） Copyright © 2023, Oracle and/or its affiliates 44 19.11にバックポート CREATE IMMUTABLE TABLE trade_ledger ( trade_id NUMBER, customer_name VARCHAR2(100), confirmation_date DATE, value NUMBER(8,2)) NO DROP UNTIL 3 DAYS IDLE NO DELETE UNTIL 16 DAYS AFTER INSERT; INSERT INTO trade_ledger VALUES (12341, 'Gerald', '08-SEPT-2022', 1000); DELETE trade_ledger WHERE trade_id = 1; ORA-05715: operation not allowed on the blockchain or immutable table DROP TABLE trade_ledger PURGE; ORA-05723: drop blockchain or immutable table TRADE_LEDGER not allowed NEW IN 21c
45. SQL 演算子の拡張 (ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 45 Operator Returns UNION ２つのクエリのいずれかに含まれるデータを表示。同一値のデータは1行で表示 UNION ALL ２つのクエリのいずれかに含まれるデータを表示。同一値のデータは1行でまとめない INTERSECT 2つのクエリの重複データのみを抽出。同一値のデータは1行で表示 INTERSECT ALL 2つのクエリの重複データのみを抽出。同一値のデータを1行でまとめない MINUS 最初のクエリにはあって2番目のクエリにはない結果を抽出し、同一値のデータは1行で表示 MINUS ALL 最初のクエリにはあって2番目のクエリにはない結果を抽出し、同一値のデータを1行でまとめない EXCEPT 最初のクエリにはあって2番目のクエリにはない結果を抽出し、同一値のデータは1行で表示。 MINUSと同じ EXCEPT ALL 最初のクエリにはあって2番目のクエリにはない結果を抽出し、同一値のデータを1行でまとめない。 MINUS ALLと同じ NEW IN 21c クエリ1 クエリ2 クエリ1 クエリ2 INTERSECT MINUS/EXCEPT
46. • ウィンドウ関数のウィンドウフレーム句のEXCLUDEオプションをサポート • CURRENT ROW/GROUP/TIES/NO OTHERSの4種類を指定可能 • SELECT文のクエリブロック句でWINDOW句をサポート 分析関数の拡張(ISO SQL 標準) Copyright © 2023, Oracle and/or its affiliates 46 SELECT v, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 1 1 2 1 4 3 4 5 3 6 6 9 9 5 8 3 8 13 5 10 5 15 5 11 6 11 16 6 5 5 11 11 7 rows selected. NEW IN 21c
47. • 説明のため、4行目のV=5（赤色）に注目します • まず、SUMの対象範囲の絞込み 1 PRECEDING AND 1 FOLLOWINGから前の1行と後ろの1行（水色） • EXCLUDE句によって除外されるものをオプションに従って考慮し、SUMする 分析関数の拡張(ISO SQL 標準)解説 Copyright © 2023, Oracle and/or its affiliates 47 SELECT v, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, SUM(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 1 1 2 1 4 3 4 5 3 6 6 9 9 5 8 3 8 13 5 10 5 15 5 11 6 11 16 6 5 5 11 11 7 rows selected. NEW IN 21c オプション 除外対象 CURRENT ROW カレント行 GROUP カレント行＋カレント行と同値の行 TIES カレント行と同値の行 NO OTHERS 除外対象なし(デフォルト) 3+5+5 3+5+5 3+5+5 3+5+5
48. https://blogs.oracle.com/sql/post/better-loops-and-qualified-expressions-array-constructors-in-plsql PL/SQL Iterator Constructsの機能拡張 Copyright © 2023, Oracle and/or its affiliates 48 -- Step control BEGIN FOR val IN 2..6 BY 2 LOOP DBMS_OUTPUT.PUT_LINE (val); END LOOP; END; 2 4 6 -- Skip iteration values BEGIN FOR val in 1..3 WHEN val != 2 LOOP DBMS_OUTPUT.PUT_LINE(val); END LOOP; END; Val = 1 Val = 3 -- Iteration sequences BEGIN FOR val IN 1, 2, 3 LOOP DBMS_OUTPUT.PUT_LINE(val); END LOOP; END; 1 2 3 -- Mutable iterators BEGIN FOR val MUTABLE IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('old = ' || val); val := val * 2; DBMS_OUTPUT.PUT_LINE('new = ' || val); END LOOP; END; old = 1 new = 2 old = 3 new = 6 old = 7 new = 14 -- Repeated expressions BEGIN FOR power IN 1, REPEAT power * 2 WHILE power < 16 LOOP DBMS_OUTPUT.PUT_LINE(power); END LOOP; END; 1 2 4 8 NEW IN 21c for文の変数が変更可能に インクリメントの単位を指定可能に for文の変数の全指定 条件指定でスキップ FOR文の変数の関数化と終了条件の指定
49. • JSON 型: JSON専用の新しいデータ型、OracleでJSONを使うのに最適なバイナリ形式（OSON）でデータを格納 • これまではVARCHAR2/CLOB/BLOB型を使用していたのが、JSON型が使用可能に • 整形されたJSONデータ型であることを保証 • パフォーマンスの向上 • JSONの複数値インデックス • JSON_TRANSFORM: JSONドキュメントの部分更新を含む変更操作が 効率化(19.10にバックポート) • JSONドキュメント内の値を変更するための新しい演算子を提供(SET,RENAME,APPEND,REPLACE,REMOVEなど) JSON の機能拡張 Copyright © 2023, Oracle and/or its affiliates 49 NEW IN 21c Oracle Database Technology Night#49 Oracle Database 21c 新機能解説1 https://speakerdeck.com/oracle4engineer/technight-number-49-oracle-database-21c-xin-ji-neng-jie-shuo-di-1hui-hou-ban-pato