IndexCol関数の使用
Oracle Analyticsでは、CASE文は通常、計算が変数値に基づいて分岐する場合に使用されます。CASE文で変数が参照される場合、生成されるSQLコードの効率性を向上させるには、かわりにIndexCol関数を使用する方が適しています。このトピックでは、IndexCol関数とその使用条件について説明します。
IndexCol関数について
IndexCol関数は、計算の列または値がセッション、リポジトリまたはプレゼンテーション変数の値に応じて変化する場合に使用します。
IndexCol関数の構文は次のとおりです:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
ここで、第1引数は整数に解決され、<<expr_list>>で構成される項目は第1引数の使用可能な値の数に対応します。これらの項目の1つが、第1引数の値に基づいてSQL文で使用されます。
たとえば、<<integer_literal>>引数に3つの使用可能な値がある場合、<<expr_list>>引数に3つの引数を指定して、それぞれが<<integer_literal>>の使用可能な値に対応している必要があります。
通常、第1引数はセッション変数または変数に関連するCASE文の値に基づいています。IndexCol関数は、リポジトリ(RPD)ファイルまたはレポート列で直接モデル化できます。複数のIndexCol関数をネストして1つの文を構成できます。
IndexCol関数のメリット
<<case when>>文を使用した計算は、その全体が物理SQLコードにプッシュされます。これに対し、IndexCol関数では、必要な列または式のみがデータベースにプッシュされます。IndexCol関数は、物理SQLコードが生成される前に評価されるためです。
値リストでの選択が可能な変数プロンプトと組み合せると、パフォーマンスのコストを増加させずにレポート構造を大幅に変更できます。
IndexCol関数のデメリットの1つは、整数計算でlikeと組み合せて使用できないことです(ただし、式のリストではlikeを使用できます)。整数計算でlikeが必要な場合は、かわりにCASE文を使用する必要があります。
例
ユーザーの優先通貨を設定するPREFERRED_CURRENCYというセッション変数があるとします。次に、セッション変数の値に基づいて、収益が現在のユーザー指定通貨で表示されます。
セッション変数の値に基づいて正しい通貨を返すために、2つの計算が作成されました。
最初のものは、次のようにCASE文を使用します:
CASE
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'USD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd"
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'EUR' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur"
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'AUD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud"
ELSE NULL
END2番目のものは、次のようにIndexCol関数を使用します:
INDEXCOL(
CASE VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY")
WHEN 'USD' THEN 0
WHEN 'EUR' THEN 1
WHEN 'AUD' THEN 2
END ,
"01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")IndexCol関数の第1引数は整数に解決される必要があるため、解決のためにCASE文が使用されます。
問合せがCASE文の計算を使用して実行されると、CASE文は実行時に評価されるため、CASE文の全体がデータベースにプッシュされます。場合によっては、これによりオプティマイザで問題が発生します。
WITH
SAWITH0 AS (select sum(case when 'USD' = 'USD' then T42437.Revenue_Usd when 'EUR' = 'USD' then T42437.Revenue_Eur when 'AUD' = 'USD' then T42437.Revenue_Aud else NULL end ) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1
The same query run using the IndexCol function pushes down only the expression needed to satisfy the query, because the IndexCol function is resolved prior to SQL generation. This helps avoid issues with the Optimizer.
WITH
SAWITH0 AS (select sum(T42437.Revenue_Usd) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1ワークブックとIndexCol
ワークブックでIndexCol関数を使用できます。
この例では、IndexCol関数を使用してビジュアライゼーションの期間の細分度を変更します。
- 列セレクタとして使用するパラメータを作成し、期間単位(この例では「月」または「四半期」)を選択します。
IndexCol関数を実行するカスタム計算を作成します。計算は次のとおりです:indexcol(case when @parameter("Time Selector Value")('Month')='Month' then 0 else 1 end, "HCM - Workforce Core"."Time"."Month Name", "HCM - Workforce Core"."Time"."Quarter")- パラメータをワークブックのフィルタ・バーに追加します。次に、ユーザーは、列セレクタ・フィルタから「月」または「四半期」を選択して、レポートの細分度を変更できます。
