AJI usage in SQLs which have subqueries

Database
Enthusiast

AJI usage in SQLs which have subqueries

Hi All,

I observed the following in my AJI testing and would like to know if this is expected behaviour.
If yes what could be the reason for it and if there are any workaround to make this query use the AJI.

AJI Definition:
Create join Index Test_Agg_Jidx as
Select Tbl1.a,Tbl2.b, Tbl3.c, sum(Tbl1.d)
From Tbl1 , Tbl2 , Tbl3
on
Tbl1.x= Tbl2.x and
Tbl1.y = Tbl3. y

Reporting SQL:

Select Tbl1.a,Tbl2.b, Tbl3.c, sum(Tbl1.d)
From Tbl1 , Tbl2 , Tbl3
on
Tbl1.x= Tbl2.x and
Tbl1.y = Tbl3. y and
Tbl1.a in (Select a from Tbl2 where r ='XXX')

The above reporting sql is NOT covered by the AJI. ('XXX' is passed dynamically during run time)
If i dont have the subquery and instead pass values to the IN clause then the AJI is used by the SQL.

Though Tbl1.a is present in the AJI its not being used if i have the subquery.

Any reason why the optimizer behaves in this way?

Also please let me know if there is any workaround to make the SQL use the AJI.

Regards,
Annal T
2 REPLIES
Enthusiast

Re: AJI usage in SQLs which have subqueries

Try collecting Statistics on Tbl1 and AGI.
Then Optimizer may use it.

Amit
Enthusiast

Re: AJI usage in SQLs which have subqueries

Hi Annal,

We also had similar issue with AJI's,i think AJI's will not be used if an subquery exists in the SQL.

Thanks