AJI ignored with analytic function's usage

Analytics
Enthusiast

AJI ignored with analytic function's usage

I am testing AJI usage on a query and am trying to understand why the using the analytics would ignore the AJI….

The following query snapshot uses the JI (JI3_TRANS_DETAIL_GL)

SELECT Fiscal_Month.Year14 (NAMED Year1 ) ,
SUM ( T2.Billed_Revenue___Rental ) (NAMED Billed_Revenue__Rental ) ,
SUM ( T2.Billed_Revenue___Rental_Owned ) (NAMED Billed_Revenue__Rental_Owned ) ,
SUM ( T2.c33 ) (NAMED c4 ) ,
SUM ( T2.c34 ) (NAMED c5 ) ,
SUM ( T2.c34 ) (NAMED c6 ) ,
Fiscal_Month.Year_Of_Calendar (NAMED Year_Of_Calendar )
FROM....
GROUP BY Fiscal_Month.Year14, Fiscal_Month.Year_Of_Calendar;

Explain...
This query is optimized using type 2 profile T2_Linux64, profileid 21.
1) First, we lock D_EDW_JI_DB.JI3_TRANS_DETAIL_GL for access, we lock
D_BI_EDWTAB_DB.FISCAL_MONTH for access, and we lock
D_BI_EDWTAB_DB.FISCALCALENDAR for access.

However, when we use the analytic function, the optimizer doesn’t use the JI

SELECT Fiscal_Month.Year14 (NAMED Year1 ) ,
SUM ( T2.Billed_Revenue___Rental ) OVER ( PARTITION BY Fiscal_Month.Year_Of_Calendar ) (NAMED Billed_Revenue__Rental ) ,
SUM ( T2.Billed_Revenue___Rental_Owned ) OVER ( PARTITION BY Fiscal_Month.Year_Of_Calendar ) (NAMED Billed_Revenue__Rental_Owned ) ,
SUM ( T2.c33 ) OVER ( PARTITION BY Fiscal_Month.Year_Of_Calendar ) (NAMED c4 ) ,
SUM ( T2.c34 ) OVER ( PARTITION BY Fiscal_Month.Year_Of_Calendar ) (NAMED c5 ) ,
SUM ( T2.c34 ) OVER ( PARTITION BY Fiscal_Month.Year_Of_Calendar ) (NAMED c6 ) ,
Fiscal_Month.Year_Of_Calendar (NAMED Year_Of_Calendar )
FROM...

Explain...
This query is optimized using type 2 profile T2_Linux64, profileid 21.
1) First, we lock D_BI_EDWTAB_DB.FISCAL_MONTH for access, we lock
D_BI_EDWTAB_DB.M3_TRANSACTION_DETAIL_GL for access, we lock
D_BI_EDWTAB_DB.FISCALCALENDAR for access, and we lock
D_BI_EDWTAB_DB.CURRENCY_CONV_RATE for access.

2) Next, we execute the following steps in parallel.

Any insight is appreciated.

Thanks

Sanji

Tags (2)