I am facing a problem with AJI utilization
I have a AJI Definition like this
From Table A
group by 1,2,3,4
This query above utlizes the AJI because all the the columns and aggregates are in AJI Definition but if i comment out 1 or 2 columns from query it ignores the AJI.
I have tried this with other queires which utilizes the other AJI's but didnt faced this issue
Not sure why it is ignoring the AJI
Any thaughts or ideas ?
If You comment out any of the columns a,b,c,d the result set will change for SUM(E) & SUM(F).
Teradata will need to re-do the aggregation and hence will not use the AJI.
AJIs are supposed to be used only for very specific queries.
Thanks for your reply.
I did the same testing for other queries which have other AJI's and it was using the AJI after commenting out some columns.
But for specific one case it is not hitting the AJI
How would than other queries can use AJI after commenting out if re do aggregation takes place ?
Your AJI should be acting as a covering query. You original query in which you are looking for AJI access, should ideally involve all the defined columns for the same table in AJI as well.
If your query is like the below one -
<quote> Sel a,c,sum(e) from Tab_A group by 1,2 <unquote>
Then as per your AJI definition, it will choose AJI access path, instead of doing the whole aggregation once again.
However, if you are executing below query -
<quote> Sel a,z,sum(e) from Tab_A group by 1,2 <unquote>
Then, AJI will not be accessed.
Please also remember; to check whether you are using direct table or a view in your query!