Korzystanie z funkcji IndexCol

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:

Utworzyć parametr, który ma być używany jako selektor kolumny do wybierania szczegółowości okresu, w tym przypadku "Month" lub "Quarter".

.jpg"

Utworzyć niestandardowe obliczenie w celu wykonania funkcji 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")

Opis "GUID-1EBB2C95-3D23-48C6-9754-EF968AFAFE98-default.jpg" znajduje się poniżej
.jpg"

Dodać parametr do paska filtrów skoroszytu. Użytkownicy będą wówczas mogli zmieniać szczegółowość raportu, wybierając opcję Month lub Quarter z filtra selektora kolumny.

.jpg"