I have a query running on a federated database that takes the form
select col1, col2
from nickname1
where <conditions exist>
union all
select col1,col2
from nickname2
where <conditions exist>
Nickname1 refers to a table on my primary database. Nickname2 refers
to an identically structured table in my secondary database.
The performance for this query is abysmal. I found the bottleneck to
be the second half of the query referencing nickname2. However, if I
connect to the secondary database directly and access the table with
the same query (bypassing the federated layer), the performance is
lightning fast.
The plan for the nickname2 piece uses a lot of SHIP and MSJOINs, where
the plan for nickname 1 uses hash joins. Statistics are up to date on
both primary and secondary databases. I need to figure out how to make
the federated layer choose a better plan. Any clues?
Using 8.2 FP14 on AIX 5.3
Thanks,
Evan