プライマリ・コンテンツに移動
Oracle® Fusion Middleware Oracle Business Intelligence Publisherデータ・モデリング・ガイド
12c (12.2.1.1.0)
E77222-02
目次へ移動
目次

前
前へ
次
次へ

SQLデータ・セットのベスト・プラクティス

次のヒントを考慮に入れると、より効率的なSQLデータ・セットを作成できます。

必要なデータのみを返す

問合せは、レポートに必要なデータのみを返すようにします。超過したデータを返すと、OutOfMemory例外が発生する可能性があります。

たとえば、次のようなすべての列を返す指定を使用しないようにします。

SELECT * FROM EMPLOYEES;

*を使用しないようにします。

返されるデータを制限する2つのベスト・プラクティスは次のとおりです。

  • 必要な列のみを選択する

    たとえば、次のように入力します。

    SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM EMPLOYEES;
  • 可能なかぎりWHERE句およびバインド・パラメータを使用し、返されるデータをより厳密に制限する。

    この例では、必要な列のみおよびパラメータの値に一致する列のみを選択します。

    SELECT DEPARTMENT_ID, DEPARTMENT_NAME 
    FROM EMPLOYEES 
    WHERE DEPARTMENT_ID IN (:P_DEPT_ID)

列別名を使用してXMLファイルの長さを短縮する

列名を短縮すると、得られるXMLファイルも小さくなり、システムによる解析も速くなります。

別名を使用して列名を短縮して、I/O処理時間を短縮し、レポートの効率を改善します。

この例では、DEPARTMENT_IDが"id"に短縮され、DEPARTMENT_NAMEが"name"に短縮されます。

SELECT DEPARTMENT_ID id, DEPARTMENT_NAME nameFROM EMPLOYEES 
WHERE DEPARTMENT_ID IN (:P_DEPT_ID)

問合せを改善し、グループ・フィルタを使用しない

データ・モデル・グループ機能を使用すると、問合せで取得したレコードを削除できますが、これは中間層で処理されるため、データベース層よりも効率が低下します。

WHERE句条件を使用して、問合せで不要なレコードを削除することが、ベスト・プラクティスです。

WHERE句でPL/SQLをコールしない

WHERE句でのPL/SQL関数のコールは、複数の実行を伴います。

これらの関数のコールは、一致するデータベースで見つかったすべての行で実行されます。また、この構造ではPL/SQLからSQLへのコンテキストの変換が必要なため、効率が低下します。

ベスト・プラクティスとして、PL/SQLコールをWHERE句では実行せず、ベース表を結合してフィルタを追加してください。

システムのDual表を使用しない

システムのDUAL表を使用してsysdateまたはその他の定数を返すことは、効率的ではないため、必要な場合以外は使用しないでください。

たとえば、次のかわりに:

SELECT DEPARTMENT_ID ID, (SELECT SYSDATE FROM DUAL) TODAYS_DATE  FROM DEPARTMENTS WHERE DEPARTMENT_ID IN (:P_DEPT_ID)

次を検討してください:

SELECT DEPARTMENT_ID ID, SYSDATE TODAYS_DATE FROM DEPARTMENTS WHERE DEPARTMENT_ID IN (:P_DEPT_ID)

最初の例では、DUALは必要ではありません。SYSDATEに直接アクセスできます。

要素レベルでPL/SQLをコールしない

要素(グループ内)レベルまたは行レベルで、パッケージ関数はコールできません。ただし、これらの関数はデータ・モデルを実行するリクエストごとに1回のみ実行されるため、パッケージ関数コールをグローバル要素レベルで含めることができます。

例:

<dataStructure>
  <group name="G_order_short_text" dataType="xsd:string" source="Q_ORDER_ATTACH">
    <element name="order_attach_desc" dataType="xsd:string" value="ORDER_ATTACH_DESC"/>
    <element name="order_attach_pk" dataType="xsd:string" value="ORDER_ATTACH_PK"/>
    <element name="ORDER_TOTAL _FORMAT" dataType="xsd:string" value=" WSH_WSHRDPIK_XMLP_PKG.ORDER_TOTAL _FORMAT "/>  <!--  This is wrong should not be called within group.-->
  </group>
    <element name="S_BATCH_COUNT" function="sum" dataType="xsd:double" value="G_mo_number.pick_slip_number"/>
</dataStructure>

複数のデータ・セットを含めない

複数のレポートを処理するために、データ・モデルを複数のデータ・セットで作成することが望ましいように思われることがありますが、これによりパフォーマンスは非常に低下します。

レポートの実行時に、データ・プロセッサは、最終出力にそのデータが使用されるかどうかにかかわらず、すべてのデータ・セットを実行します。

レポートのパフォーマンスおよびメモリー効率の向上のため、単一のデータ・モデルを使用して複数のレポートをサポートする前に、十分に検討してください。

データ・セットをネストしない

データ・モデルでは、親子階層を作成するメカニズムが提供され、1つのデータ・セットが別のデータ・セットにリンクされます。

実行時、データ・プロセッサは、親問合せを実行し、親問合せのそれぞれの行に対して子問合せを実行します。データ・モデルに複数のネストされた親子関係があると、処理の速度が低下します。

データ・セットをネストしないようにするには、複数のデータ・セット問合せを、WITH句を使用して単一のデータ・セット問合せに結合します。

複数のデータ・セットを1つのデータ・セットに結合する際の、一般的なヒントを次に示します。

  • 親および子が1対1の関係、つまり1つの親行に必ず1つの子行がある場合、親および子のデータ・セットを単一の問合せにマージします。

  • 親問合せに、子問合せよりも多くの行がある場合。たとえば、請求書配布表が請求書表にリンクされている場合、分布表には請求書表に比べると膨大な数の行があります。個々の子問合せは1秒もかかりませんが、子問合せに該当する分布表ではスタック・スレッドが発生します。

次のような場合に、WITH句を使用します。

Query Q1: 
SELECT DEPARTMENT_ID EDID,EMPLOYEE_ID EID,FIRST_NAME FNAME,LAST_NAME LNAME,SALARY SAL,COMMISSION_PCT COMMFROM EMPLOYEES

Query Q2: 
SELECT DEPARTMENT_ID DID,DEPARTMENT_NAME DNAME,LOCATION_ID LOCFROM DEPARTMENTS

WITH句を使用し、これらの問合せを1つに結合するには、次のようにします。

WITH Q1 as (SELECT DEPARTMENT_ID DID,DEPARTMENT_NAME DNAME,LOCATION_ID LOC
FROM DEPARTMENTS),
Q2 as (SELECT DEPARTMENT_ID EDID,EMPLOYEE_ID EID,FIRST_NAME FNAME,LAST_NAME LNAME,SALARY SAL,COMMISSION_PCT COMM
FROM EMPLOYEES)
SELECT Q1.*, Q2.*
FROM Q1 LEFT JOIN Q2
ON Q1.DID=Q2.EDID

インライン問合せ(サマリー列として)を使用しない

インライン問合せは、1行に対して1つの列を実行します。たとえば、主問合せに100列あり、1000行を伴う場合、列問合せはそれぞれ1000回実行されます。

全体で、100掛ける1000回です。これはスケーラビリティが高くないためうまく実行できません。インライン・サブ問合せは、可能なかぎり避けてください。

次のようなインライン問合せの使用を避けます。この問合せで返される行が少量の場合、このアプローチは効果的に機能しますが、問合せが10000行を返す場合、サブ・インライン問合せが10000回実行され、問合せの結果がスタック・スレッドになる可能性があります。

SELECT
NATIONAL_IDENTIFIERS,NATIONAL_IDENTIFIER,
PERSON_NUMBER,
PERSON_ID,
STATE_CODE
FROM
(select pprd.person_id,(select REPLACE(national_identifier_number,'-') from per_
national_identifiers pni where pni.person_id = pprd.person_id and rownum<2)
 national_identifiers,(select national_identifier_number from per_national
identifiers pni where pni.person_id = pprd.person_id and rownum<2) national_
identifier,(select person_number from per_all_people_f ppf
where ppf.person_id = pprd.person_id
and :p_effective_start_date between ppf.effective_start_date and ppf.effective_
end_date) PERSON_NUMBER
(Select hg.geography_code from hz_geographies hg
where hg.GEOGRAPHY_NAME = paddr.region_2
and hg.geography_type = 'STATE')  state_code

パラメータ・バインド値を超過しない

Oracleデータベースでは、パラメータ当たり最大1000のバインド値が使用できます。

大量のパラメータ値のバインドは、非効率的です。パラメータに対して100より多い値をバインドしないでください。

メニュー・タイプのパラメータを作成し、値のリストに多数の値が含まれる場合、「複数選択」および「全選択可能」オプションの両方を有効化し、大量の値が渡されないように「NULL値が渡されました」を選択する必要があります。

複数値のパラメータのヒント

レポート利用者は、多くの場合、特定の条件をサポートするレポートを実行する必要があります。

  • パラメータが選択されない(null)と、すべてが返される。

  • 複数パラメータの値の選択が可能である

これらの場合、NVL()は使用できないため、次を使用します

  • COALESCE()。(Oracle Databaseに対する問合せの場合)

  • CASE / WHEN。(Oracle BI EE (論理)問合せの場合)

例:

SELECT EMPLOYEE_ID ID, FIRST_NAME FNAME, LAST_NAME LNAME FROM EMPLOYEES
WHERE DEPARTMENT_ID = NVL(:P_DEPT_ID, DEPARTMENT_ID

この問合せ構文は、P_DEPT_IDの値が単一値またはnullの場合のみ、正常に機能します。複数値を渡すと、この構文は機能しません。

複数値をサポートするには、次の構文を使用します。

Oracleデータベースの場合:

SELECT EMPLOYEE_ID ID, FIRST_NAME FNAME, LAST_NAME LNAME FROM EMPLOYEES
WHERE (DEPARTMENT_ID IN (:P_DEPT_ID) OR COALESCE (:P_DEPT_ID, null) is NULL)

Oracle BI EEデータ・ソースの場合:

(CASE WHEN ('null') in (:P_YEAR) THEN 1 END =1 OR "Time"."Per Name Year" in (:P_YEAR))

Oracle BI EEでは、パラメータのデータ・タイプは文字列です。数値および日付データ・タイプはサポートされていません。

グループのブレークおよびデータのソート

データ・モデルでは、グループをブレークする、およびデータをソートする機能を使用できます。

ソートは、親グループのブレーク列に対してのみサポートされます。たとえば、従業員のデータ・セットが部門と管理職でグループ化されている場合は、部門によってXMLデータをソートできます。最終的なレポートまたはテンプレートで、どのようにデータをソートしておく必要があるかがわかっている場合は、データ生成時にソートを指定するとドキュメント生成が最適化されます。SELECT句に指定した列順序は、データ構成の要素順序に完全に一致する必要があります。そうしないと、グループのブレークおよびデータのソートは実行されません。複雑になるため、異なるレベルで複数のソートを伴う複数のグループ化は実行しないでください。

例: 次に示す例では、ソートおよびグループ・ブレークは、親グループ、G_1にのみ適用されます。問合せ、データ・セット・ダイアログおよびデータ構造内の列順序に注目してください。SQL列順序は、データ構成要素のフィールドの順序に完全に一致します。一致しない場合、データが破損することがあります。

例:

SELECT  d.DEPARTMENT_ID DEPT_ID, d.DEPARTMENT_NAME  DNAME,
        E.FIRST_NAME FNAME,E.LAST_NAME LNAME,E.JOB_ID JOB,E.MANAGER_ID
FROM EMPLOYEES E,DEPARTMENTS D
     WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID 
     ORDER BY  d.DEPARTMENT_ID, d.DEPARTMENT_NAME

問合せを定義すると、次に示されているように、データ・モデル・デザイナを使用して、データ要素の選択、およびグループ・ブレークの作成が可能になります。

グループ・ブレークのあるデータ構造を次に示します。

<output rootName="DATA_DS" uniqueRowName="false">
<nodeList name="data-structure"> <dataStructure tagName="DATA_DS">
<group name="G_1" label="G_1" source="q1">
   <element name="DEPT_ID" value="DEPT_ID" label="DEPT_ID" fieldOrder="1"/>
   <element name="DNAME" value="DNAME" label="DNAME" fieldOrder="2"/>
   <group name="G_2" label="G_2" source="q1">
      <element name="FNAME" value="FNAME" label="FNAME" fieldOrder="3"/>
      <element name="LNAME" value="LNAME" label="LNAME" fieldOrder="4"/>
      <element name="JOB" value="JOB" label="JOB" fieldOrder="5"/>
      <element name="MANAGER_ID" value="MANAGER_ID" label="MANAGER_ID" fieldOrder="6"/>
   </group>
</group>
</dataStructure>
</nodeList>
</output>