Managing Master/Detail non-equijoin links

Hi,
I have a pair of tables which are related in a manner pretty similar to the EMP/SALGRADE pair from the standard Oracle scott/tiger set - in other words, my master table (call it CATEGORY) contains fields CATEGORY_CODE (the activity category) and ACT_CODE_LOW and ACT_CODE_HIGH (the low and high values of activity codes which sit in the specified category). My detail table (call it WORK_ITEM) contains an ACTIVITY_CODE field.

There can be more than one WORK_ITEM with a given ACTIVITY_CODE, but each ACTIVITY_CODE maps into precisely *one* CATEGORY_CODE via the CATEGORY table. I don't know for sure whether each CATEGORY_CODE is identified by just one range, or whether there can be multiple CATEGORY table entries with the same CATEGORY_CODE, but I do know that the ranges don't overlap.

This is pretty much the same as EMP/SALGRADE. But my problem is that WORK_ITEM has hundreds of thousands of rows, and CATEGORY has hundreds, maybe thousands of rows. So performance is abysmal, because the Oracle optimiser insists on doing full-table scans of WORK_ITEM within a nested loop on CATEGORY :-(

I believe the performance problemn is because the optimiser does not know that each activity code maps to only one category code (ie, the category ranges don't overlap). The table definitions and indexes don't give it this information.

My question is, is it possible to express this relationship in terms which allow the Oracle database to see that the "correct" way to drive this sort of query is by a full-table scan on WORK_ITEM, with some form of index range lookup or simple check on CATEGORY for each WORK_ITEM row?

Sadly, my remit doesn't allow me to redesign the data structures of the database, although I can add indexes (or possibly constraints) if that would help.

Thanks for any suggestions

Paul Moore. Received on Fri Feb 05 1999 - 10:56:09 CST