Is the order in which we give conditions that affect performance?
I recently started studying oracle. I heard there will be some performance problem (please correct me if I'm wrong) if we don't follow the order of the where
conditions {especially when we join two or more tables} in SQL Query. If so, what order should be followed, and what are the factors that I should consider. Please explain in general and for the below scenario. Let's say I have 3 tables. Please find DDL and Sample Data here .
The table Part_material
has basic information with id as primary key (PK), the table Component
will have different types of components with id, part_name as PK, each id will have 30 to 40 entries, the table connection
will have connection information. I am joining these three tables as shown below:
select com.id,part.part_material,com.part_name,'TRUE',part.map_id,part.quantity,'INBOUND'
from Components com,Base_part part,connects rel
where part.id=com.id
and dbms_lob.compare(com.part_value,'Coat wi........')=0
and part.part_material in ('Barium','Tungston','Carbon')
and com.part_name='Build Material'
and rel.fromid=part.id
and rel.fromid=com.id
and rel.relname in ('Export Need','Not Molten');
If I change the above order, does it improve / decrease performance?
source to share
An Oracle-based cost optimizer is usually independent of the order of the WHERE clause.
Tom Keith also answered back in 2009:
https://asktom.oracle.com/pls/asktom/f?p=100:11%3A%3A%3A%3AP11_QUESTION_ID:1857060700346051220
source to share
The execution strategy is determined by the optimizer and is not related to the order of conditions in the where clause or the order of the table in the from clause. The syntax you used is very old and error prone: start using the JOIN syntax to write conditions between tables.
select com.id,part.part_material,com.part_name,'TRUE',part.map_id,part.quantity,'INBOUND'
from Components com
join Base_part part on part.id=com.id
join connects rel on rel.fromid=part.id
and rel.fromid=com.id
where dbms_lob.compare(com.part_value,'Coat wi........')=0
and part.part_material in ('Barium','Tungston','Carbon')
and com.part_name='Build Material'
and rel.relname in ('Export Need','Not Molten');
This is more error and clarity
source to share
if you are using a rule-based optimizer (or hint + rules), the order of the where clause matters. But it is highly recommended to use a cost optimizer. Keep in mind: As long as Oracle maintains a rule-based optimizer, you cannot be 100% sure that the rule-based stuff is hidden somewhere in the code ... no matter what Tom Kyte says ( SQL-Guru).
source to share