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ファイルが小さくなり、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関数コールを実行すると、複数回実行される場合があります。

これらの関数コールは、一致するデータベース内に検出された各行で実行されます。さらに、この構成では、非効率なSQLコンテキスト切替えにPL/SQLが必要です。

ベスト・プラクティスとして、WHERE句でのPL/SQLコールを回避し、かわりに実表を結合してフィルタを追加します。

システム・デュアル表を使用しない

sysdateまたは他の定数を返すためにシステムDUAL表を使用することはできません。不要な場合は、システム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コールの回避

グループまたは行レベル内の要素でのパッケージ関数コールは許可されません。パッケージ関数は、データ・モデルの実行リクエストごとに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-to-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

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

インライン問合せは、各行の各列に対して実行します。たとえば、主問合せに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の説明

複数値のパラメータに関するヒント

レポート・コンシューマは、多くの場合、特定の条件をサポートするレポートを実行する必要があります。

パラメータが選択されない場合(null)、すべてが返されます。
複数パラメータの値の選択が許可されます

このような場合、NVL()の使用は機能しないため、次を使用する必要があります

Oracle Databaseに対する問合せの場合はCOALESCE()
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の説明

ブレークのあるデータ構造を次に示します:

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