AJI and outer joins queries on Base table

Database

AJI and outer joins queries on Base table

Hi All,

While we are analyzing the queries for using Aggregate Join Index created on table, we have observed queries that are selecting columns available in AJI and if the base table has outer joins (left, right and Full outer) with other tables then optimizer is not choosing on AJI but for other queries the optimizer choosing the AJI.

We have queries that the base table has inner joins and outer joins (80% left outer joins) with other tables. Our TD version is 12.

Any ideas/ thoughts using AJIs for outer join queries

Any tips/suggestions for AJI and LOJ (outer joins) is greatly appreciated

Thanks,

Raghav

3 REPLIES
Enthusiast

Re: AJI and outer joins queries on Base table

Hi,

Below are restrictions on Outer Join Definitions

• FULL OUTER JOIN is not valid.

• When you specify a LEFT or RIGHT outer join, the following rules apply:

• The outer table joining column for each condition must be contained in either

column_1_name or column_2_name.

• The inner table of each join condition must have at least one non-nullable column in

either column_1_name or column_2_name.

Make sure you adhere to these.

amg
Fan

Re: AJI and outer joins queries on Base table

OUTER JOINS are not allowed in AJIs. They are only allowed in simple Join Indexes. however, would have loved if this was possible.

And of course, AJI with inner join will not be picked for the query with left outer join since, query with outer join would be a superset of inner joined AJI.

E.g.

Table A

a1  | a2

----------

1 | d1

2 | d2

Table B

b1 | b2

-------

1  | bd1

AJI with inner join on (a1=b1) , would have resultset

a1 | a2  | b1 |b2

------------------

1 | d1 | 1 | db1

whereas query with left join would have resultset

a1 | a2  | b1 |b2

------------------

1 | d1 | 1 | db1

2 | d2 | ? | ?

So, we see AJI does not have all the data needed by the query and hence AJI is not picked.

Ideally if the bottleneck is the join between tables and not aggregation, I would create a simple join index (JI) with Left join between table A and B. This, would allow JI to be picked by left join queries and no join has to be performed. However, aggregation still has to be performed.

Re: AJI and outer joins queries on Base table

Thanks a lot .. let me try this option