Hello, Ask Tom Team.
I want to know to put the correct order in the filter in a SQL query. I mean, in the WHERE condition.
1. How can we help the optimizer in order to have the best performance? I know that it tries to execute the best plan based on the statistics it has but I want to do it the best way.
E.g. I have a query with mandatory date filter and then some other conditions:
SELECT t1.col1,t1.col2,t1.col3,t2.col4
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id=t2.id
WHERE t1.first_date >= TO_DATE('01/04/2020','dd/mm/yyyy')
AND t1.last_date < TO_DATE('09/04/2020','dd/mm/yyyy')
AND t1.col1='some_value'
AND t2.col4='some_value'
2. It is recommended to index the date columns and put them first in the query?
Thanks in advanced.
Regards,
1. The optimizer decides which order to apply the conditions. Which order you place filters in the WHERE clause has no effect. Relying on this can lead to some unexpected outcomes:
That said...
That query outer joins T2 and filters on it in the WHERE CLAUSE:
SELECT ...
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id=t2.id
WHERE t2.col4='some_value'
This turns the query into an INNER join. If you want to filter outer joined rows, you need to do this in the JOIN ... ON ... clause:
SELECT ...
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id=t2.id
AND t2.col4='some_value'
WHERE ...
2. Assuming you're searching for a date range which is a "small" fraction of the rows in the table, indexing the dates can be helpful.
For that query, I'd consider creating an index on:
create index i on table1 ( col1, first_date, last_date );
And on the other table too:
create index i2 on table2 ( id, col4 );
The location of these columns within the WHERE clause is irrelevant. Though which clause (WHERE, JOIN, ORDER, etc.) they're in does matter whether the database will use the index and how effective it is.