IndexCol-funktion käyttäminen
Oracle Analytics -palvelussa CASE-lauseita käytetään usein tapauksissa, joissa laskutoimitus haarautuu muuttujan arvon perusteella. Jos CASE-lauseessa viitataan muuttujaan, on suositeltavaa käyttää sen sijaan IndexCol-funktiota, jolla voidaan parantaa luodun SQL-koodin tehokkuutta. Seuraavassa kuvataan IndexCol-funktiota ja sitä, milloin sitä kannattaa käyttää.
Tietoja IndexCol-funktiosta
IndexCol-funktiota käytetään, kun laskutoimituksen sarakkeet tai arvot määrittyvät istunnon, tietovaraston tai esityksen muuttujan arvon mukaan.
IndexCol-funktion syntaksi::
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Ensimmäisen argumentin tuloksena on kokonaisluku, ja argumentin <<expr_list>> muodostavat alkiot vastaavat ensimmäisen argumentin mahdollisten arvojen määrää. Yhtä näistä alkioista käytetään sitten SQL-lauseessa ensimmäisen argumentin arvon perusteella.
Esimerkki: jos argumentilla <<integer_literal>> on kolme mahdollista arvoa, argumentissa <<expr_list>> on oltava kolme argumenttia eli yksi argumentin <<integer_literal>> kullekin mahdolliselle arvolle.
Ensimmäinen argumentti perustuu usein jonkin istuntomuuttujan arvoon tai CASE-lauseeseen, joka viittaa muuttujiin. Voit mallintaa IndexCol-funktion tietovarastotiedostossa (RPD) tai suoraan raportin sarakkeessa. Voit muodostaa yksittäisen lauseen lisäämällä sisäkkäisiä IndexCol-funktioita.
IndexCol-funktion hyödyt
Lausetta <<case when>> käyttävä laskutoimitus viedään fyysiseen SQL-koodiin kokonaisuudessaan. Toisaalta IndexCol-funktio vie vain tarvittavan sarakkeen tai lausekkeen tietokantaan. Tähän on syynä se, että IndexCol-funktio arvioidaan ennen fyysisen SQL-koodin luontia.
Kun funktiota käytetään yhdessä muuttujakehotteiden kanssa, jotka mahdollistavat valinnan valintalistasta, voidaan raportin rakennetta muokata merkittävästi suorituskykyä heikentämättä.
Eräs IndexCol-funktion haittapuoli on se, että sitä ei voi käyttää yhdessä like-ehdon kanssa kokonaislukulaskutoimituksissa, vaikka like-ehtoa voi käyttää lausekeluettelossa. Jos kokonaislukulaskutoimituksessa tarvitaan like-ehtoa, se on korvattava CASE-lauseella.
Esimerkki
Kuvitellaan, että on istuntomuuttuja nimeltä PREFERRED_CURRENCY, joka määrittää käyttäjän valitseman valuutan. Tällöin voitto voidaan näyttää istuntomuuttujan arvon perusteella käyttäjän määrittämässä valuutassa.
Tätä varten on luotu kaksi laskutoimitusta, jotka antavat tulokseksi oikean valuutan istuntomuuttujan arvon perusteella.
Ensimmäisessä käytetään CASE-lausetta tämän esimerkin tapaan:
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
ENDToisessa käytetään IndexCol-funktiota tämän esimerkin tapaan:
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")Koska IndexCol-funktion ensimmäisen argumentin tuloksen on oltava kokonaisluku, ratkaisuun käytetään CASE-lausetta.
Kun kysely ajetaan käyttämällä CASE-lauseen laskutoimitusta, CASE-lause viedään kokonaisuudessaan tietokantaan, koska CASE-lause arvioidaan ajon aikana. Joissakin tapauksissa tästä aiheutuu ongelmia optimoinnin kanssa.
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 ) D1Työkirjat ja IndexCol
IndexCol-funktiota voi käyttää myös työkirjoissa.
Seuraavassa esimerkissä IndexCol-funktion avulla muutetaan jakson tarkkuutta visualisoinnissa:
- Luo sarakevalitsimena käytettävä parametri, jolla valitaan jakson tarkkuus (tässä tapauksessa kuukausi tai neljännesvuosi).
- Luo mukautettu laskutoimitus
IndexCol-funktion suorittamiseen. Laskutoimitus on seuraava: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") - Lisää parametri työkirjan suodatinpalkkiin. Käyttäjät voivat nyt valita raportin tarkkuuden valitsemalla sarakevalitsimen suodattimesta joko kuukauden tai neljännesvuoden.
