confusion with joins

[tomesc]

My connector does not support joins. Thus my translator does overwrite the following support methods only:

supportsCompareCriteriaEquals()

supportsCompareCriteriaOrdered()

supportsOrCriteria()

supportsInCriteria()

supportsOnlyLiteralComparison()

I have two tables:

- FILE_0063 { ISN, AA }

- FILE_0243 { ISN, AA }

The column ISN is the primary key in both tables.

The following JOINS are working as expected:

select t1.ISN,t1.AA,t2.ISN,t2.AA from FILE_0063 t1 join FILE_0243 t2 on t2.AA=t1.AA where t1.AA='20050089';

select t1.ISN,t1.AA,t2.ISN,t2.AA from FILE_0063 t1 join FILE_0243 t2 on t2.ISN=t1.ISN where t1.ISN='5409';

select t1.ISN,t1.AA,t2.ISN,t2.AA from FILE_0063 t1 join FILE_0243 t2 on t2.AA=t1.AA where t1.ISN='5409';

But the next is not working:

select t1.ISN,t1.AA,t2.ISN,t2.AA from FILE_0063 t1 join FILE_0243 t2 on t2.ISN=t1.ISN where t1.AA='20050089';

In this case the "createResultSetExecution" method is called twice:

SELECT FILE_0063.ISN, FILE_0063.AA FROM FILE_0063 WHERE FILE_0063.AA = '20050089'

SELECT FILE_0243.ISN, FILE_0243.AA FROM FILE_0243

As you see in the second call the WHERE clause is missing.

If I define the column AA as an Index it is working as well. The following statements are called:

SELECT FILE_0063.ISN, FILE_0063.AA FROM FILE_0063 WHERE FILE_0063.AA = '20050089'

SELECT FILE_0243.ISN, FILE_0243.AA FROM FILE_0243 WHERE FILE_0063.ISN = '5409'

Is it really necessary to define the column AA as an Index to get it running? This would mean that all colums should be defines as Index.

Or did I ignore some facts?

Thank's in advance.

Thomas

[shawkins]

> Is it really necessary to define the column AA as an Index to get it running? This would mean that all colums should be defines as Index.

> Or did I ignore some facts?

You need to check the plan and the documentation around "dependent joins".  In situations where there isn't enough information to determine the relative size of the rows being fetched from FILE_0063 and FILE_0243, then we'll default to a standard join so that the source access can be processed in parallel.  An index, cardinality/ndv stats, or a hint will choose a dependent join.