1. Oracle Analytics Serverでのピクセルパーフェクト・レポートのデザインと公開
  2. ピクセルパーフェクト・レポート用のデータのモデリング
  3. パフォーマンスのベスト・プラクティス
  4. SQLデータセットのベスト・プラクティス

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またはその他の定数を返すことは効率的ではありません。必要な場合以外は、システムのDUAL表を使用しないでください。

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

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をコールしない

グループ・レベル内または行レベル内の要素でのパッケージ関数のコールは許可されていません。パッケージ関数のコールは、グローバル要素レベルで含めることができます。これは、これらの関数がデータ・モデルの実行要求ごとに一度だけ実行されるためです。

例:

<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秒未満でも、各配分で子問合せを実行するとSTUCKスレッドが発生することがあります。

次のような場合に、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回実行されます。

次のようなインライン問合せの使用を避けます。この問合せが数行を返すだけの場合、このアプローチは十分に機能する可能性があります。ただし、問合せが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 Databaseでは、パラメータ当たり最大1000のバインド値を使用できます。

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

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

dm_bp_lov.gifの説明が続きます
図dm_bp_lov.gifの説明

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

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

  • パラメータが選択されない(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 Databaseの場合:

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

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


bp_dmeditor.gifの説明が続きます
図bp_dmeditor.gifの説明

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

<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>