AJI Issue

General

AJI Issue

Hi All,

I am facing a problem with AJI utilization

I have a AJI Definition like this

Select a,

b,

c,

d,

SUM(e)

SUM(f)

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 ?

3 REPLIES

Re: AJI Issue

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.

Shraddha.

Re: AJI Issue

Shradha,

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 ?

Thanks

Atta

Re: AJI Issue

Atta,

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!

HTH!

Charudatt