How to improve sorting performance
438682Nov 27 2008 — edited Dec 1 2008Hello
We are having bad performance for a query like this in:
select a.data
from a, b, c, d, e, f
where a.fk1 = b.pk
and a.fk2 = c.pk
and a.fk3 = d.pk
and a.fk4 = e.pk
and a.fk5 = f.pk
order by a.data, b.data, c.data
each foreign key has its index
we have oracle 9i
Without the order by clause, the cost is 3042; with the order by clause, it is 39842.
table 'a' is going to have millions of records, tables 'b' and 'c' are going to have thousands, and the others just hundreds.
How can i improve the performance of this query?
Thanks in advance!