IndexCol 함수
Oracle Analytics에서 Case 문은 변수 값을 기반으로 계산이 "분기"될 때 자주 사용됩니다. Case 문에서 변수가 참조되는 경우 생성된 SQL 코드의 효율성을 향상시키기 위해 IndexCol 함수로 대체하는 것이 좋습니다. 이 항목에서는 IndexCol 함수와 사용 시기에 대해 설명합니다.
IndexCol 함수
계산의 열이나 값이 세션, 저장소 또는 프리젠테이션 변수의 값에 따라 달라질 때 IndexCol 함수를 사용합니다.
IndexCol 함수의 구문은 다음과 같습니다.
INDEXCOL(<<integer_literal>>, <<expr_list>>)
첫번째 인수는 정수로 분석되고 <<expr_list>>를 구성하는 항목은 첫번째 인수의 가능한 값 수에 해당합니다. 그런 다음 이러한 항목 중 하나가 첫번째 인수 값을 기반으로 SQL 문에서 사용됩니다.
예를 들어, <<integer_literal>> 인수의 가능한 값이 세 개인 경우 <<expr_list>> 인수에는 <<integer_literal>>의 가능한 값마다 하나씩, 세 개의 인수가 있어야 합니다.
첫번째 인수는 세션 변수의 값 또는 변수를 참조하는 Case 문을 기반으로 하는 경우가 많습니다. 저장소(.rpd) 파일에서 또는 보고서 열에서 직접 IndexCol 함수를 모델링할 수 있습니다. 여러 IndexCol 함수를 중첩하여 단일 문을 구성할 수 있습니다.
함수의 이점
<<case when>> 문을 사용한 계산은 전부 물리적 SQL 코드에 푸시됩니다. 이에 비해 IndexCol 함수는 필요한 열 또는 표현식만 데이터베이스에 푸시다운합니다. 이는 물리적 SQL 코드가 생성되기 전 IndexCol 함수가 평가되기 때문입니다.
값 목록에서 선택할 수 있는 변수 프롬프트와 결합되면 성능을 위한 비용 증가 없이 보고서 구조를 크게 수정할 수 있습니다.
IndexCol 함수의 한 가지 단점은 표현식 목록에서는 "like"를 사용할 수 있지만 정수 계산에서는 "like"와 함께 사용할 수 없다는 것입니다. 정수 계산에 "like"가 필요한 경우 대신 Case 문을 사용해야 합니다.
예:
사용자의 선호 통화를 설정하는 PREFERRED_CURRENCY라는 세션 변수가 있다고 가정하고, 세션 변수 값에 따라 Revenue가 사용자 지정 통화로 표시됩니다.
세션 변수 값을 기반으로 올바른 통화를 반환하기 위해 두 가지 계산이 생성되었습니다.
첫번째는 다음 예에서와 같이 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 END
두번째는 이 예에서와 같이 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 함수의 첫번째 인수는 정수로 분석되어야 하므로 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") - 워크북의 필터 모음에 매개변수를 넣습니다. 사용자는 열 선택기 필터에서 '월' 또는 '분기'를 선택하여 보고서의 세분성을 변경할 수 있습니다.
