1. Oracle Shardingの使用
  2. 問合せおよびDMLの実行

8 問合せおよびDMLの実行

シャード・データベースでは、問合せおよびDMLをシャードにルーティングして、シャーディング・キーの有無に関係なく実行できます。アプリケーションによってキーが提供される場合、データベース・リクエストはシャードに直接ルーティングされますが、キーが指定されない場合、リクエストはシャード・カタログによって処理され、実行に必要なシャードに送信されます。

データベース・リクエストのシャードへのルーティング方法

Oracle Shardingでは、シャーディング・キーがリクエストに指定されているかどうかに応じて、データベース問合せおよびDMLリクエストは2種類の主な方法でシャードにルーティングされます。

この2つのルーティング方法は、直接ルーティングおよびプロキシ・ルーティングと呼ばれます。

直接ルーティング

データベース・リクエストでシャーディング・キーを指定することで、シャードに直接接続して問合せおよびDMLを実行できます。直接ルーティングは、パフォーマンスを向上させるためにシャードにアクセスする場合に推奨される方法で、特に利点があります。

プロキシ・ルーティング

アプリケーションでは、複数のシャードからのデータを必要とする問合せや、シャーディング・キーを指定していない問合せを直接ルーティングできません。こうした問合せには、アプリケーションとシャードの間でリクエストをルーティングするためのプロキシが必要になります。プロキシ・ルーティングは、シャード・カタログ問合せコーディネータによって処理されます。

問合せおよびDMLのシャードへの直接ルーティング

シャーディング・キーを提供する場合、アプリケーションはリクエストをシャードに直接ルーティングできます。直接ルーティング・メカニズムでは、リクエストはルーティングされたシャードに属するデータの問合せおよび操作のみが可能です。

シャード上のデータへの直接アクセスには、いくつかの利点があります。

  • パフォーマンスの向上: 全体的に、アプリケーションでは、シャード・カタログを介してリクエストをシャードに間接的にルーティングするよりもパフォーマンスが向上します(プロキシによる)。直接ルーティングでは、リクエストおよび結果がコーディネータ・データベースを通過する必要はありません。

  • シャードの地理的分散に対応します。アプリケーションは、リージョン内でローカライズされたシャード内のデータにアクセスできます。
  • ロード・バランシングが容易: チャンク移動を使用してシャード間でデータを移動することで、シャード間でのアプリケーション・リクエストのロード・バランシングを簡単に実行できます。
  • すべてのタイプの問合せをサポートします。
    • シャード表に対するSELECT、INSERTおよびUPDATE: これらの問合せの有効範囲は、アクセスされるシャードに属するデータです。
    • 重複表に対するSELECT、INSERTおよびUPDATE: これらの問合せの有効範囲は、重複表内のすべてのデータです。重複表のマスター・コピーはコーディネータ・データベースに存在するため、重複表のDMLはコーディネータ・データベースに再ルーティングされます。

次の図は、シャードへの直接ルーティングを使用した重複表に対するDMLを示しています。

  1. アプリケーションは、シャードの1つ(シャードDB1)にDMLリクエストを直接送信します。
  2. DMLは、シャードDB1からコーディネータ・データベースに転送され、マスター重複表で実行されます。
  3. コーディネータ・データベースのリフレッシュ・メカニズムが定期的に実行され、すべてのシャードで重複表のインスタンスが更新されます。

図8-1 直接ルーティングを使用した重複表のDML


図8-1の説明が続きます
「図8-1 直接ルーティングを使用した重複表のDML」の説明

直接ルーティングの詳細は、クライアント・アプリケーション・リクエストのルーティングを参照してください。

直接ルーティング用アプリケーションの開発の詳細は、シャード・データベースのアプリケーションの開発を参照してください。

プロキシによる問合せおよびDMLのルーティング

シャード・カタログ問合せコーディネータをプロキシとして使用すると、Oracle Shardingでは、シャーディング・キーを指定しない問合せおよびDMLのリクエスト・ルーティングを処理できます。

コーディネータをプロキシとして使用することで、Oracle Shardingでは、問合せを実行するシャードを指定しなくても、任意のデータベース・アプリケーションがSQL文を実行できる柔軟性が提供されます。

次の図は、プロキシ・ルーティングを使用した重複表に対するDMLを示しています。

  1. DMLリクエストがアプリケーションからコーディネータ・データベースに送信され、マスター重複表で実行されます。
  2. コーディネータ・データベースのリフレッシュ・メカニズムが定期的に実行され、すべてのシャードで重複表のインスタンスが更新されます。

図8-2 プロキシ・ルーティングを使用した重複表のDML


図8-2の説明が続きます
「図8-2 プロキシ・ルーティングを使用した重複表のDML」の説明

コーディネータの詳細は、問合せ処理と問合せコーディネータを参照してください。

この章の残りのトピックでは、プロキシによるデータベース・リクエストのルーティングおよび処理について説明します。

問合せコーディネータへの接続

Oracle Sharding問合せコーディネータ(シャード・カタログのコンポーネント)には、シャード・トポロジのメタデータが含まれており、シャード・データベースの問合せ処理をサポートします。

マルチシャード問合せを実行するには、シャード・カタログ・データベースでGDS$CATALOGサービスを使用してコーディネータに接続します。 

sqlplus app_schema/app_schema@shardcatvm:1521/GDS\$CATALOG.oradbcloud

コーディネータの詳細は、問合せ処理と問合せコーディネータを参照してください

問合せコーディネータ操作

シャード・カタログのSQLコンパイラは、関連するシャードを自動的に識別し、関係するすべてのシャード間で問合せの実行を調整します。コーディネータとシャードの間の通信のために、データベース・リンクが使用されます。

次の図に示すように、おおまかに言うと、コーディネータは各受信問合せQをコーディネータ問合せ(CQ)とシャード問合せ(SQ)の2つの問合せにリライトします。SQ (シャード問合せ)は各参加シャードで実行されるキューの一部、CQ (コーディネータ問合せ)はコーディネータ・シャードで実行されるキューの一部です。

問合せQはCQ (Shard_Iterator (SQ))にリライトされます。ここで、Shard_Iteratorはシャードに接続してSQを実行する演算子です。パラレルまたはシリアルで実行できます。

図8-3問合せコーディネータ操作


図8-3の説明が続きます
「図8-3問合せコーディネータの操作」の説明

次の例では、集計問合せQ1をQ1'にリライトしています。

Q1 : SELECT COUNT(*) FROM customers

Q1’: SELECT SUM(sc) FROM (Shard_Iterator(SELECT COUNT(*) sc FROM s1 (i) ))

この処理には2つの主要な要素があります。

  1. 関連するシャードが識別されます。

  2. 問合せが分散形式にリライトされ、関連するシャードで繰り返されます。

コーディネータ・データベースでの問合せのコンパイル中に、問合せコンパイラはシャーディング・キーに対する述語を分析し、関与するシャード(つまり、問合せで参照されるシャード表の行があるシャード)を識別するために使用できる述語を抽出します。残りのシャードは除外されたシャードと呼ばれます。

関与するシャードが1つのみ識別された場合は、問合せ全体がそのシャードにルーティングされて実行されます。これは単一シャード問合せと呼ばれます。

関与する複数のシャードがある場合、その問合せはマルチシャード問合せと呼ばれ、リライトされます。リライト処理では、問合せによって計算される式および問合せの形態が考慮されます。

単一シャード問合せのための問合せ処理

単一シャード問合せは、1つのシャードでのみデータをスキャンする必要があり、他のシャードでデータを検索する必要がない問合せです。

単一シャード問合せは、特定のシャードに接続してそのシャードに問合せを発行するクライアントに似ています。このシナリオでは、問合せ全体が関与する単一のシャードで実行され、コーディネータは処理された行をクライアントに返すだけです。コーディネータの計画は、リモート・マップ・カーソルに似ています。

たとえば、次の問合せは、顧客123のデータが1つのシャードのみにあるため、そのシャードにすべてマップされます。

SELECT count(*) FROM customers c, orders o WHERE c.custno = o.custno and c.custno = 123;

この問合せには、問合せのコンパイル時(リテラル)または問合せ開始時(バインド)に判明する1つのシャードにのみマップするシャード・キーの条件が含まれています。問合せは該当するシャードですべて実行されます。

単一シャード問合せでは次のものがサポートされます。

  • 等価およびINリスト(Area = 'West'など)

  • リテラル、バインド、またはリテラルとバインドの式を含む次のような条件 

    Area = :bind

Area = CASE :bind <10 THEN ‘West’ ELSE ‘East’ END

  • SELECT、UPDATE、DELETE、INSERT、FOR UPDATEおよびMERGE。UPSERTはサポートされていません。

マルチシャード問合せのための問合せ処理

マルチシャード問合せは、複数のシャードでデータをスキャンする必要がある問合せであり、各シャードでの処理は他のシャードから独立しています。

マルチシャード問合せは、複数のシャードにマップされ、結果をクライアントに送信する前に、コーディネータが処理する必要がある場合があります。たとえば、次の問合せは各顧客によって発注されたオーダー数を取得します。 

SELECT count(*), c.custno FROM customers c, orders o WHERE c.custno = o.custno
 GROUP BY c.custno;

この問合せは、コーディネータによって次のように変換されます。

SELECT sum(count_col), custno FROM (SELECT count(*) count_col, c.custno
 FROM customers c, orders o 
 WHERE c.custno = o.custno GROUP BY c.custno) GROUP BY custno;

インライン問合せブロックは、リモート・マップ問合せブロックのように各シャードにマップされます。コーディネータは、すべてのシャードからの結果セットに対して、さらなる集計およびGROUP BYを実行します。各シャードでの実行の単位はインライン問合せブロックです。

マルチシャード問合せとグローバルな読取り一貫性

マルチシャード問合せでは、すべてのシャードで最も大きい共通SCNで問合せを発行することによって、グローバルな読込み一貫性(CR)を維持する必要があります。一貫性レベルを設定する方法の詳細は、マルチシャード問合せでの一貫性レベルの指定を参照してください。

マルチシャード問合せでのヒントの受け渡し

コーディネータに対する元の問合せで指定したヒントは、シャードに伝播されます。

マルチシャード問合せでの実行速度低下のトレースとトラブルシューティング

クエリー・リライトおよびシャード・プルーニングをトレースするには、コーディネータに対してトレース・イベントshard_sqlを設定します。観測される一般的なパフォーマンス問題の1つは、シャーディングの一定の制限により、GROUP BYがシャードにプッシュされない場合です。考えられるすべての操作がシャードにプッシュされ、シャードからの結果を統合するためのコーディネータでの処理が最低限であるかどうかを確認します。

マルチシャード問合せでの一貫性レベルの指定

シャード間でマルチシャード問合せを実行するときに、初期化パラメータMULTISHARD_QUERY_DATA_CONSISTENCYを使用して様々な一貫性レベルを設定できます。

マルチシャード問合せでは、様々な一貫性レベルを指定できます。たとえば、一部の問合せでシャード間のSCN同期のコストを回避する必要がある場合は、それらのシャードをグローバルに分散できます。別のユース・ケースとして、レプリケーション用のスタンバイを使用している場合は、プライマリとそのスタンバイから結果がフェッチされる可能性があるため、マルチシャード問合せで少し古いデータが許容されます。

デフォルトのモードは厳密な一貫性であり、すべてのシャード間でSCN同期が実行されます。他のモードでは、SCN同期はスキップされます。delayed_standby_allowedレベルでは、ロード・バランシングなどの要因に応じてスタンバイからもデータをフェッチでき、古いデータを含めることができます。

このパラメータは、システム・レベルまたはセッション・レベルで設定できます。

関連項目:

MULTISHARD_QUERY_DATA_CONSISTENCYの使用方法の詳細は、Oracle Databaseリファレンスを参照してください。

サポートされる問合せ構成と問合せ形態の例

Oracle Shardingでは、単一シャード問合せとマルチシャード問合せの形態がサポートされていますが、いくつかの制限事項があります。

Oracle Shardingでの問合せコンストラクトに関する制限事項は、次のとおりです。

  • CONNECT BYを使用する問合せ CONNECT BYを使用する問合せはサポートされません。

  • MODEL句 MODEL句はサポートされていません。

  • WHERE句のユーザー定義のPL/SQL マルチシャード問合せでは、ユーザー定義のPL/SQLはSELECT句でのみ使用できます。WHERE句で指定された場合は、エラーがスローされます。

  • XLATEおよびXML問合せタイプ XLATEおよびXML問合せタイプの列はサポートされません。

  • オブジェクト型 オブジェクト型をSELECTリスト、WHERE句などに含めることはできますが、オブジェクト型のカスタム・コンストラクタおよびメンバー関数をWHERE句に含めることはできません。

    さらに、重複表の場合、NOT FINAL型(つまりNOT FINALキーワードを指定して作成されたオブジェクト型)は、列のデータ型として使用できません。シャード表の場合、NOT FINAL型を列のデータ型として使用できますが、キーワードNOT SUBSTITUTABLE AT ALL LEVELSを指定して列を作成する必要があります。

ノート:

重複表のみに関連する問合せは、コーディネータで実行されます。

次のトピックでは、Oracle Shardingでサポートされる問合せ形態の例をいくつか示します。

シャード表のみに対する問合せ

単一表の問合せの場合、問合せにはシャードを限定するシャーディング・キーに対する等価フィルタを指定できます。結合問合せの場合は、すべての表がシャーディング・キーに対する等価フィルタを使用して結合される必要があります。

次の例は、シャード表のみが関与する問合せを示しています。

例8-1 内部結合

SELECT … FROM s1 INNER JOIN s2 ON s1.sk=s2.sk 
WHERE any_filter(s1) AND any_filter(s2)

例8-2 左外部結合

SELECT … FROM s1 LEFT OUTER JOIN s2 ON s1.sk=s2.sk

例8-3 右側外部結合

SELECT … FROM s1 RIGHT OUTER JOIN s2 ON s1.sk=s2.sk

例8-4 完全外部結合

SELECT … FROM s1 FULL OUTER JOIN s2 ON s1.sk=s2.sk
WHERE any_filter(s1) AND any_filter(s2)

シャード表および重複表の両方が関係する問合せ

シャード表と重複表が関係する問合せは、シャーディング・キーに対する述語に応じて、単一シャード問合せまたはマルチシャード問合せになります。唯一の違いは、問合せに非シャード表が含まれていることです。

ノート:

シャード表と重複表の結合では、任意の列で、任意の比較演算子(= < > <= >=)または任意の結合式を使用できます。

例8-5 内部結合

SELECT … FROM s1 INNER JOIN r1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

例8-6 左外部結合または右外部結合

この場合、シャード表はLEFT OUTER JOINの最初の表です。 

SELECT … FROM s1 LEFT OUTER JOIN r1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

SELECT … FROM r1 LEFT OUTER JOIN s1 ON any_join_condition(s1,s2) 
AND any_filter(r1) AND filter_one_shard(s1)

この場合、シャード表はRIGHT OUTER JOINの2番目の表です。 

SELECT … FROM r1 RIGHT OUTER JOIN s1 ON any_join_condition(s1,r1) 
WHERE any_filter(s1) AND any_filter(r1)

SELECT … FROM s1 RIGHT OUTER JOIN r1 ON any_join_condition(s1,s2) 
AND filter_one_shard(s1) AND any_filter(r1)

場合によっては、重複表がLEFT OUTER JOINの最初の表であるか、シャード表が最初の表でシャーディング・キーに対するフィルタ述語に基づいて単一のシャードにマップされます。 

SELECT … FROM r1 LEFT OUTER JOIN s1 ON any_join_condition(s1,s2) 
AND any_filter(r1) AND any_filter(s1)

場合によっては、重複表がRIGHT OUTER JOINの2番目の表であるか、シャード表が2番目の表でシャーディング・キーに対するフィルタ述語に基づいて単一のシャードにマップされます。 

SELECT … FROM s1 RIGHT OUTER JOIN r1 ON any_join_condition(s1,s2) 
AND any_filter (s1) AND any_filter(r1)

例8-7 完全外部結合

SELECT … FROM s1 FULL OUTER JOIN r1 ON s1.sk=s2.sk
WHERE any_filter(s1) AND any_filter(s2)

この場合、シャード表で複数のシャードへのアクセスが必要となります。

SELECT … FROM s1 FULL OUTER JOIN r1 ON s1.non_sk=s2.non_sk
WHERE any_filter(s1) AND any_filter(s2)

例8-8 セミ結合(EXISTS) 

SELECT … FROM s1 EXISTS 
(SELECT 1 FROM r1 WHERE r1.anykey=s1.anykey)

SELECT … FROM r1 EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey and filter_one_shard(s1))

この場合、シャード表は複数シャードの関与を必要とする副問合せにあります。

SELECT … FROM r1 EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey)

例8-9 アンチ結合(NOT EXISTS) 

SELECT … FROM s1 NOT EXISTS 
(SELECT 1 FROM r1 WHERE r1.anykey=s1.anykey)

この場合、シャード表は副問合せにあります。

SELECT … FROM r1 NOT EXISTS 
(SELECT 1 FROM s1 WHERE r1.anykey=s1.anykey

Oracle Shardingでサポートされる集計関数

Oracle Shardingでは、次の集計はプロキシ・ルーティングによってサポートされます。

  • COUNT

  • SUM

  • MIN

  • MAX

  • AVG

ユーザー定義型を使用した問合せ

ユーザー定義のSQLオブジェクト型とSQLコレクション型は、ユーザー定義型と呼ばれます。Oracle Shardingでは、ユーザー定義型を使用した問合せがサポートされます。

例8-10 ユーザー定義型を持つ表の作成

次の例では、全シャード・タイプおよびタイプ本体を作成し、そのタイプを参照するシャード表を作成します。

ALTER SESSION ENABLE SHARD DDL;

CREATE OR REPLACE TYPE person_typ AS OBJECT (
    first_name   VARCHAR2(20),
    last_name    VARCHAR2(25),
    email        VARCHAR2(25),
    phone        VARCHAR2(20),
    MEMBER FUNCTION details (
    self IN person_typ
    ) RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY person_typ AS
    MEMBER FUNCTION details (
    self IN person_typ
    ) RETURN VARCHAR2 IS
        result VARCHAR2(100);
    BEGIN
        result := first_name || ' ' || last_name || ' ' || email || ' ' || phone;
        RETURN result;
    END;
END;
/

CREATE SHARDED TABLE Employees
( Employee_id      NUMBER NOT NULL
, person      person_typ
, signup_date DATE NOT NULL
, CONSTRAINT RootPK PRIMARY KEY(CustNo)
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

例8-11 型コンストラクタを使用したデータの挿入 

INSERT INTO Employees values ( 1, person_typ('John', 'Doe', 'jdoe@example.com', '123-456-7890'), to_date('24 Jun 2020', 'dd Mon YYYY'));

例8-12 ユーザー定義型の列のマルチシャード問合せ

SELECT e.person FROM Employees e;


SELECT e.person.first_name, e.person.last_name FROM Employees e;


SELECT e.person.details() FROM Employee e where e.person.first_name = 'John’;

 
SELECT signup_date from Employees e where e.person = person_typ('John', 'Doe', 'jdoe@example.com', '123-456-7890’);

プロキシ・ルーティング用の実行計画

マルチシャード問合せでは、各シャードで別個の実行計画が生成されます。この実行計画はデータのサイズに応じて最適化され、シャードで使用可能なリソースが計算されます。

SQLフラグメントの実行計画を参照するために、個々のシャードに接続する必要はありません。dbms_xplan.display_cursor()で提供されるインタフェースは、シャードで実行されたSQLセグメントの計画をコーディネータに表示します。また、[V/X]$SHARD_SQLは、マルチシャード問合せのシャードSQLフラグメントをターゲットのシャード・データベースに一意にマップします。

dbms_xplan.display_cursor()のSQLセグメント・インタフェース

2つのインタフェースを使用して、シャードで実行されたSQLセグメントの計画を表示できます。これらのインタフェースは、引数としてシャードIDを取り、指定されたシャードの計画を表示します。ALL_SHARDS形式では、すべてのシャードの計画が表示されます。

シャードのすべての計画を出力するには、次に示すようにformat値ALL_SHARDSを使用します。 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid,
                                              cursor_child_no=>:childno,
                                              format=>'BASIC +ALL_SHARDS‘,
                                              shard_ids=>shard_ids))

シャードの計画を選択して出力するには、display_cursor()関数内でシャードIDを渡します。複数のシャードの計画を出力する場合は、次に示すようにshard_idsパラメータでシャードIDを含む数値の配列を渡します。 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid, 
                                               cursor_child_no=>:childno,
                                               format=>'BASIC',
                                               shard_ids=>ids))

1つのシャードの計画を返すには、次に示すようにshard_idパラメータにシャードIDを直接渡します。 

select * from table(dbms_xplan.display_cursor(sql_id=>:sqlid,
                                              cursor_child_no=>:childno,
                                              format=>'BASIC',
                                              shard_id=>1))

V$SQL_SHARD

V$SQL_SHARDは、マルチシャード問合せのシャードSQLフラグメントをターゲットのシャード・データベースに一意にマップします。このビューは、特定のマルチシャード問合せのシャードSQLフラグメントごとにアクセスされたシャードのリストがシャード・コーディネータ・データベースに格納されている場合にのみ関連します。マルチシャード問合せを実行するたびに、異なるシャードのセットに対してシャードSQLフラグメントを実行できるため、実行のたびにシャードIDが更新されます。このビューには、各リモート・ノードのシャードSQLフラグメントのSQL IDと、シャードSQLフラグメントが実行されたシャードIDが保持されています。 

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
 SQL_ID                                            VARCHAR2(13)
 CHILD_NUMBER                                      NUMBER
 NODE_ID                                           NUMBER
 SHARD_SQL_ID                                      VARCHAR2(13)
 SHARD_ID                                          NUMBER
 SHARD_CHILD_NUMBER                                NUMBER

  • SQL ID - コーディネータ上のマルチシャード問合せのSQL ID

  • CHILD_NUMBER - コーディネータ上のマルチシャード問合せのカーソル子番号

  • NODE - マルチシャード問合せのシャードSQLフラグメントのリモート・ノードのID

  • SHARD_SQL_ID - 指定されたリモート・ノードIDに対するシャードSQLフラグメントのSQL ID

  • SHARD_ID - シャードSQLフラグメントが実行されたシャードのID

  • SHARD_CHILD_NUMBER - シャードに対するシャードSQLフラグメントのカーソル子番号(デフォルトは0)

シャード・データベースに対するマルチシャード問合せと実行計画の例を次に示します。

SQL> select count(*) from departments a where exists (select distinct department_id
 from departments b where b.department_id=60);
------------------------------------------------
| Id  | Operation          | Name              |
------------------------------------------------
|   0 | SELECT STATEMENT   |                   |
|   1 |  SORT AGGREGATE    |                   |
|   2 |   FILTER           |                   |
|   3 |    VIEW            | VW_SHARD_377C5901 |
|   4 |     SHARD ITERATOR |                   |
|   5 |      REMOTE        |                   |
|   6 |    VIEW            | VW_SHARD_EEC581E4 |
|   7 |     SHARD ITERATOR |                   |
|   8 |      REMOTE        |                   |
------------------------------------------------

V$SQL_SHARDビューに対するSQL_IDの問合せ。 

SQL> Select * from v$sql_shard where SQL_ID = ‘1m024z033271u’;
SQL_ID        NODE_ID   SHARD_SQL_ID  SHARD_ID
------------- -------  -------------- --------
1m024z033271u       5   5z386yz9suujt        1
1m024z033271u       5   5z386yz9suujt       11 
1m024z033271u       5   5z386yz9suujt       21 
1m024z033271u       8   8f50ctj1a2tbs 	    11

関連項目:

Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス

Oracle Databaseデータベース・リファレンス

サポートされているDMLと例

OracleシャーディングのDMLは、重複表またはシャード表のいずれかをターゲットにできます。ターゲットが重複表の場合、DMLに制限はありません。

シャード表をターゲットとするDML (主に挿入、更新および削除)は、次のようにできます。

  • ターゲット表のみが参照される単純なDML
  • 他の表を参照するDML
  • MERGE文

ターゲット表のみが参照される単純なDML

次に、サポートされているDMLの例をいくつか示します。

例8-13 すべての行の更新

UPDATE employees SET salary = salary *1.1;

例8-14 単一行の挿入

INSERT INTO employees VALUES (102494, 'Jane Doe, ...
    );

例8-15 単一行の削除

DELETE employees WHERE employee_id = 103678;

他の表を参照するDML

シャード表のDMLは、他のシャード表、重複表またはローカル表を参照できます。

例8-16 重複表を参照するDML

この例では、employeesはシャード表で、ref_jobsは重複表です。 

DELETE employees
            WHERE job_id IN (SELECT job_id FROM ref_jobs
                            WHERE job_id = 'SA_REP');

例8-17 別のシャード表を参照するDML

UPDATE departments SET department_name = 'ABC‘
            WHERE department_id IN (SELECT department_id
                                    FROM employees
                                    WHERE salary < 10000);

例8-18 ローカル表からのINSERT AS SELECT

INSERT INTO employees SELECT * FROM local_employees;

例8-19 1つのシャードに影響するDML

DML文は、1つのシャードのみに影響することも、複数のシャードに影響することもあります。たとえば、ここに示すDELETE文は、WHERE句のシャーディング・キー(employee_id)に述語があるため、1つのシャードにのみ影響します。 

DELETE employees WHERE employee_id = 103678;

例8-20 複数のシャードに影響するDML

次の文は、WHERE句がないため、EMPLOYEES表のすべての行に影響します。 

UPDATE employees SET salary = salary *1.1;

このUPDATE文をすべてのシャードで実行するために、シャード・コーディネータがすべてのプライマリ・データベースに対して反復処理を行い、UPDATE文のリモート実行を呼び出します。コーディネータは分散トランザクションを開始し、2フェーズ・コミットを実行して分散トランザクションの一貫性を保証します。インダウト・トランザクションがある場合は、手動でリカバリする必要があります。

MERGE文の例

MERGE文で、シャード表または重複表をターゲットにできます。MERGE操作自体をシャードにプッシュできるかぎり、マージは許可されます。

例8-21 シャード表employeesをターゲット表とするMerge文

この例では、employee_id列がシャーディング・キーで、ソース問合せの結合述語がシャーディング・キーにあるため、MERGE文は実行されるすべてのシャードにプッシュされます。 

MERGE INTO employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

例8-22 重複表をターゲット表とするMerge文

この例では、ターゲット表は重複表ref_employeesです。ソース問合せはシャード表employeesを参照し、結合述語はシャーディング・キーemployee_idにあります。 

MERGE INTO ref_employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
      WHERE (S.salary <= 8000);

マルチシャードDMLのサポートの制限事項

次のDML機能は、Oracle ShardingのマルチシャードDMLではサポートされません。

  • パラレルDML パラレルDMLはマルチシャードDMLではサポートされません。マルチシャードDMLでは、DMLは常に一度に1つのシャードに対して(シリアルに)実行されます。
  • エラー・ロギング DMLのERROR LOG句は、マルチシャードDMLではサポートされません。この場合、ユーザー・エラーが発生します。
  • 配列DML 配列DMLはマルチシャードDMLではサポートされません。この場合、ORA-2681が発生します。
  • RETURNING句RETURNING INTO句は通常の分散DMLでサポートされないため、Oracle Shardingでサポートされません。マルチシャードDMLでRETURNING INTO句の使用を試みると、ORA-22816が発生します。
  • MERGEおよびUPSERT MERGE文はOracle Shardingで部分的にサポートされます。つまり、単一のシャードのみに影響するMERGE文はサポートされます。MERGE文で複数のシャードの変更が必要となる場合は、ORAエラーが発生します。
  • 複数表へのINSERT 複数表への挿入はデータベース・リンクでサポートされないため、Oracle Shardingでサポートされません。
  • 更新可能な結合ビュー 更新可能な結合ビューでシャード表がシャーディング・キーで結合されている場合、ORA-1779がスローされます。このエラーの理由は、シャード表に定義されている主キーが内部列SYS_HASHVAL + シャーディング・キーの組合せであるため、更新可能な結合ビューにSYS_HASHVALを指定できないからです。この制限により、キー保存表を設定できないため、ORA-1779が発生します。
  • トリガー

シャード表のオプティマイザ統計の収集

コーディネータ・データベースからシャード表の統計を収集できます。

統計プリファレンス・パラメータCOORDINATOR_TRIGGER_SHARDがすべてのシャードでTRUEに設定されている場合、コーディネータ・データベースはシャードで収集された統計をインポートできます。

PL/SQLプロシージャDBMS_STATS.GATHER_SCHEMA_STATS()およびDBMS_STATS.GATHER_TABLE_STATS()は、シャードおよびコーディネータ・データベース内のシャード表および重複表に関する統計を収集します。REPORT_GATHER_TABLE_STATS関数も参照してください。

手動統計収集

  1. すべてのシャードでCOORDINATOR_TRIGGER_SHARDをTRUEに設定します。

    このステップは、1回のみシャードで実行されます。たとえば、sharduserという名前のスキーマがあるとします。 

    connect / as sysdba
EXECUTE DBMS_STATS.SET_SCHEMA_PREFS('SHARDUSER','COORDINATOR_TRIGGER_SHARD','TRUE');

  2. シャード全体の統計を収集します。

    ユーザーは全シャード・ユーザーであり、ディクショナリ表にアクセスする権限を持っている必要があります。

    1. シャードで、次を実行します。
      connect sharduser/password
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SHARDUSER', options => 'GATHER');
    2. すべてのシャードが完了したら、集計統計をプルするためにコーディネータで次を実行します。
      connect sharduser/password
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SHARDUSER', options => 'GATHER');
    3. すべてのシャードの統計を確認します。
      connect sharduser/password

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
  col TABLE_NAME form a40
  set pagesize 200 linesize 200

SELECT TABLE_NAME, NUM_ROWS, sharded, duplicated, last_analyzed
  FROM user_tables
  WHERE table_name not like 'MLOG%' and table_name not like 'RUPD%'
  and table_name not like 'USLOG%';

自動統計収集

  1. すべてのシャードでCOORDINATOR_TRIGGER_SHARDをTRUEに設定します。

    このステップは、1回のみシャードで実行されます。たとえば、sharduserという名前のスキーマがあるとします。 

    connect / as sysdba
EXECUTE DBMS_STATS.SET_SCHEMA_PREFS('SHARDUSER','COORDINATOR_TRIGGER_SHARD','TRUE');

  2. シャードおよびコーディネータ・データベースで集計された統計をプルするジョブをスケジュールします。

    ユーザーは全シャード・ユーザーであり、ディクショナリ表にアクセスする権限を持っている必要があります。

    シャードで次のジョブを開始します。

    connect sharduser/password
BEGIN 
DBMS_SCHEDULER.CREATE_JOB ( 
   job_name => 'Gather_Stats_2', 
   job_type => 'PLSQL_BLOCK',
   job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''DEMO'', options => ''GATHER''); END;',
   start_date => SYSDATE,
   repeat_interval => 'freq=daily;byday=MON,TUE,WED,THU,FRI,SAT,SUN;byhour=14;byminute=10;bysecond=00',
   end_date => NULL,
   enabled => TRUE,
   comments => 'Gather table statistics');
END; 
/

    すべてのシャードのジョブが終了したら、コーディネータで次のジョブを開始します。

    connect sharduser/password
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'Gather_Stats_2',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''DEMO'', options => ''GATHER''); END;',
   start_date           =>  SYSDATE,
   repeat_interval      => 'freq=daily;byday=MON,TUE,WED,THU,FRI,SAT,SUN;byhour=15;byminute=10;bysecond=00', 
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/