sort with overrides

[david gang]

Hi Everyone,
I would like to take your advice in the folloing topic.


We have a database with one global schema and many local schemas.
In the global schema we have a table of titles

title {
TITLE_ID BIGINT,
ITEM_ID BIGINT,
TITLE VARCHAR(200),
}

We have then in every instance a table lcl_title where we can have overrides of these titles


lcl_title {
INTERNAL_ID BIGINT,
TITLE_ID BIGINT,
TITLE VARCHAR(200),
}

The overrides will be a tiny fraction of the table. We have millions of titles and hundreds of overrides.

We make searches on items and want to sort them acoording to title. For every instance (which corresponds to another user) We should take then the title overrides into account. This means that i have to make a left join between the two tables title and lcl_title and make a sort on coalesce. This is a performance killer. Another option is to store in every table a table with the computed titles acoording to title overrides and the original titles. This would cost in storage and additional index burden on the key buffer. Is there a third solution i can use?

Thanks,
David