W Oracle Analytics instrukcje CASE
często stosuje się w sytuacjach, gdy obliczenia "rozgałęziają się" na podstawie wartości zmiennej. Gdy w instrukcji CASE
występuje odwołanie do zmiennej, lepiej zamiast tego użyć funkcji IndexCol
, aby poprawić wydajność generowanego kodu SQL. W tym temacie opisano funkcję IndexCol
oraz sytuacje, w których należy jej używać.
Funkcja IndexCol - informacje podstawowe
Funkcji IndexCol
używa się wtedy, gdy kolumny lub wartości w obliczeniach różnią się w zależności od wartości zmiennej sesyjnej, repozytoryjnej lub prezentacyjnej.
Składnia funkcji IndexCol
jest następująca:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Gdzie pierwszy argument rozstrzyga się do liczby całkowitej, a elementy tworzące listę <<expr_list>>
odpowiadają liczbie możliwych wartości tego pierwszego argumentu. Jeden z tych elementów zostaje następnie użyty w instrukcji SQL na podstawie wartości pierwszego argumentu.
Jeśli na przykład argument <<integer_literal>>
ma trzy możliwe wartości, wówczas argument <<expr_list>>
musi zawierać trzy argumenty, po jednym dla każdej możliwej wartości argumentu <<integer_literal>>
.
Pierwszy argument jest często oparty na wartości zmiennej sesyjnej lub instrukcji CASE
w odwołaniu do zmiennych. Funkcję IndexCol
można modelować w pliku repozytorium (RPD) lub bezpośrednio w kolumnie raportu. Można zagnieżdżać wiele funkcji IndexCol
w celu utworzenia pojedynczej instrukcji.
Zalety funkcji IndexCol
Obliczenie używające instrukcji <<case when>>
jest w całości wypychane do fizycznego kodu SQL. Dla porównania funkcja IndexCol
wypycha do bazy danych tylko wymaganą kolumnę lub wymagane wyrażenie. Dzieje się tak, ponieważ ewaluacja funkcji IndexCol
odbywa się przed wygenerowaniem fizycznego kodu SQL.
W połączeniu z filtrami monitującymi zmiennych, które umożliwiają wybór na liście wartości, można w ten sposób znacząco modyfikować strukturę raportu bez żadnego negatywnego wpływu na wydajność.
Wadą funkcji IndexCol
jest to, że nie można używać jej z operatorem like
w obliczeniach na liczbach całkowitych, mimo że można używać operatora like
na liście wyrażeń. Jeśli obliczenie na liczbach całkowitych wymaga operatora like
, konieczne jest użycie zamiast tego instrukcji CASE
.
Przykład
Załóżmy, że istnieje zmienna sesyjna o nazwie PREFERRED_CURRENCY
służąca do ustawiania preferowanej waluty dla użytkownika. Następnie na podstawie wartości tej zmiennej sesyjnej przychód ("Revenue") jest wyświetlany w walucie określonej przez użytkownika.
W celu zwrócenia prawidłowej waluty na podstawie wartości tej zmiennej sesyjnej zostały utworzone dwa obliczenia.
W pierwszym obliczeniu używana jest instrukcja CASE
, jak w tym przykładzie:
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
W drugim obliczeniu używana jest funkcja IndexCol
, jak w następującym przykładzie:
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")
Ponieważ pierwszy argument funkcji IndexCol
musi rozstrzygać się do liczby całkowitej, w celu uzyskania tego rozstrzygnięcia użyta została instrukcja CASE
.
Gdy zapytanie zostaje uruchomione przy użyciu obliczenia z instrukcją CASE
, cała instrukcja CASE
jest wypychana do bazy danych, ponieważ ewaluacja instrukcji CASE
odbywa się w fazie wykonawczej. W niektórych sytuacjach jest to przyczyną problemów z optymalizatorem.
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
Skoroszyty a funkcja IndexCol
Funkcji IndexCol
można używać w skoroszytach.
W tym przykładzie funkcja IndexCol
jest używana do zmiany szczegółowości okresu w wizualizacji:
IndexCol
. W tym przykładzie obliczenie jest następujące:
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")