0

I have this huge sql with a lot of left joins. Mostly on the tables primary key, so no problem. On one table though, my key could exist in either one column or the other. Syntax-wise that is no problem:

LEFT JOIN table2 blk ON (t.rid=blk.transact OR t.rid=blk.transactchild)

I have created two indices on table2, one for each of the keys, but my Sybase ASE 15.7 decides to table scan in stead. So I tried creating a single index with both of the keys and forcing it in the join, but that didn't help on performance. Showplan indicates, that the index is being used, but a scan takes forever. Is there a better way to join on either one or the other column?

1

When you have two separate indexes on transact and on transactchild the server can't use them efficiently when searching for (t.rid=blk.transact OR t.rid=blk.transactchild).

The classic way to handle this type of OR search is to split it into two simple searches and then UNION results together.

So, you can try something like this:

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transact)

UNION

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transactchild)

In this case individual simple query should be able to use corresponding index, but the extra UNION step can be quite expensive, especially if it requires a sort.

If your data is such that you know that UNION ALL would produce correct result, then use UNION ALL, which is much more efficient than UNION. UNION eliminates duplicates, which is expensive. UNION ALL simply outputs results of two queries one after another.

If values in blk.transact do not overlap with values in blk.transactchild, then you can use UNION ALL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy