Search Order When Joining Adabas Files

, , , , ,
1

I want to hear what others have experienced when JOINing Adabas tables with Connx.

I have two Adabas tables in a primary key/foreign key relationship. I’ll call them table_A and table_B The table_A org_id defined as a unique descriptor, null suppressed. table_B has org_id defined as a descriptor, null suppressed.

When I run the following query the execution plan displayed in Infonaut says that Connx will do a full table scan of table_B followed by a cross table lookup of table_A. Why won’t it use the existing key in table_A and drive the query off of that file?

SELECT *
FROM table_A A
INNER JOIN table_B B ON A.org_id = B.org.id

If I run the same query and add a WHERE clause the execution plan shows it working as I would expect with indexes being used and driving off of Table_A.

SELECT *
FROM table_A A
INNER JOIN table_B B ON A.org.id = B.org.id
WHERE A.org_id > 0

Can someone explain how Connx is thinking about this situation? Are WHERE clauses required on all querries? Thanks for your insights.

2

Hi Dave,

In your first example:

SELECT *
FROM table_A A
INNER JOIN table_B B ON A.org_id = B.org.id

You are joining on org_id - but you have not specified any criteria for org_id.
In general, if you are going to return the data for the entire table - a table scan without using the index is significantly faster than using the index to perform the same operation. If the data is scanned in physical order, then read-ahead caching will work every effectively. If the data is scanned in index order - the physical records will likely be distributed across the entire data file - and the key information is needed to jump around and find each record, likely leading to re-reads of the same records multiple records if any data page level caching is in place, and if the cache is not big enough to store the entire file (again speaking in general terms).

In your second example, we will of course use the index on org_id to scan this table since the criteria was specified.

3

Thanks Larry, that makes sense. In this case if it is going to do a table scan I want it to do it for table_A. Table_A is significantly smaller than table_B. But it doesn’t matter which order I write the query (SELECT * FROM table_A or SELECT * FROM table_B) it always choses to do the table scan of table_B. Is there some way I can influence this behavior?

4

Hi Dave,

Three ways to do this:

SELECT *
FROM table_A A
INNER JOIN table_B B ON A.org.id = B.org.id
WHERE A.org_id > 0
just use a filter on table A - it will start with that table.

SELECT *
FROM table_A {startwiththistable} A
INNER JOIN table_B B ON A.org.id = B.org.id
Use a CONNX hint to start with table A

SELECT *
FROM table_A A
left outer JOIN table_B B ON A.org.id = B.org.id
where b.org.id is not null
Use a left join - the leftmost table will start first.
The addition of the where clause to the left join will ensure
that the final result set is exactly the same as an inner join.