AJI not getting invoked

Database
Enthusiast

AJI not getting invoked

Hi All,

We had created a aggregate join Index using the tables.
Fact1, Dim1, Lkp1, Dim2, Lkp2

FV1, DV1, LV1, DV2, LV2 are respective simple SELCT * views created on top of the tables.

Also
A view ConsV1 is created joining Dim1 and Lkp1
A view ConsV2 is created joining Dim2 and Lkp2

Now if we tried to do run a SQL with FV1, ConsV1 and ConsV2 AJI is not getting invoked.

But if we create a SQL with FV1, DV1, LV1, DV2, LV2 then AJI is getting invoked.

Please advise on this behavior and what can be done to invoke the AJI.

Thanks
2 REPLIES
Enthusiast

Re: AJI not getting invoked

Is this because we are trying to run a SQL with consolidated views ConV1, ConsV2 on the Fact FV1?

Is this a bug in Teradata?

advise,

Thanks,
Toad
Enthusiast

Re: AJI not getting invoked

All,

Please find below the AJI and view definitions and the query which is causing problem and let me know your inputs.

CREATE JOIN INDEX JI_ADHOC
AS
SELECT
Month_Year,
Product_Id,
D5.Branch_Id,
L5.Rep_Id,
SUM(Amt) AS Amt,
SUM(YTD_Amt) AS YTD_Amt,
FROM MyDB.Fact_Adhoc Adh
JOIN MyDB.DIM_CUST D5
ON Adh.Rep_Id=D5.Rep_Id
JOIN MyDB.LKUP_CUST L5
ON L5.Rep_Id=D5.Rep_Id
GROUP BY 1,2,3,4
PRIMARY INDEX (Month_Year, Product_Id, Branch_Id,Rep_Id)
PARTITION BY RANGE_N (Month_Year
BETWEEN 200901 AND 200912 EACH 1,
201001 AND 201012 EACH 1,
201101 AND 201112 EACH 1,
201201 AND 201212 EACH 1);

-- View Defn
REPLACE VIEW MyDB.DIM_CUST_Cons AS
SELECT
D5.Rep_Id,
L5.Rep_Name,
D5.Branch_Id,
FROM MyDB.DIM_CUST D5
JOIN MyDB.LKUP_CUST L5
ON D5.Rep_Id = L5.Rep_Id;

-- AJI Getting Invoked if we use a table

SELECT
Month_Year,
Product_Id,
D5.Branch_Id,
D5.Rep_Id,
SUM(Amt) AS Amt,
SUM(YTD_Amt) AS YTD_Amt,
FROM MyDB.Fact_Adhoc Adh
JOIN MyDB.DIM_CUST D5
ON Adh.Rep_Id=D5.Rep_Id
GROUP BY 1,2,3,4;

-- AJI NOT Invoked if we use a complex view as defined above.

SELECT
Month_Year,
Product_Id,
D5.Branch_Id,
D5.Rep_Id,
SUM(Amt) AS Amt,
SUM(YTD_Amt) AS YTD_Amt,
FROM MyDB.Fact_Adhoc Adh
JOIN MyDB.DIM_CUST_Cons D5
ON Adh.Rep_Id=D5.Rep_Id
GROUP BY 1,2,3,4;

Thanks,
Toad