Hi,
I have two SQL statements where the first statement is running on a table with over 40 millions rows and the second statement is running on a table with over 6 million rows. When they are run on their own they each take about 0.15 seconds to run, but when they are combined they take 20 minutes to run, (the second SQL statement is inserted into the WHERE clause of the first statement). It would appear that after combining these statements, the first statement is going through all 40 million rows before it performs the SELECT in the WHERE clause. I believe that what's needed is to ensure the SELECT in the WHERE clause is executed first.....or something like that!!! Does anyone have any ideas on how to combine these statements yet not suffer the performance impact?
The first statement is:
select csi.instance_id,
oel.ordered_item
from apps.csi_item_instances csi,
apps.oe_order_lines_all oel
where csi.instance_id in
(1718000,3698000,48740202)
and csi.last_oe_order_line_id = oel.line_id;
The second statement is:
select /*+ INDEX (iea (attribute_id)) */
iea.instance_id
from apps.csi_iea_values iea
where iea.attribute_id = 10004
and iea.attribute_value is not null;
The combined statement is:
select csi.instance_id,
oel.ordered_item
from apps.csi_item_instances csi,
apps.oe_order_lines_all oel
where csi.instance_id in
(select /*+ INDEX (iea (attribute_id)) */
iea.instance_id
from apps.csi_iea_values iea
where iea.attribute_id = 10004
and iea.attribute_value is not null)
and csi.last_oe_order_line_id = oel.line_id;
Thanks for any assistance,
Mike