Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: April 09, 2020 - 12:51 pm UTC

Last updated: April 09, 2020 - 5:05 pm UTC

Version: 18.6

Viewed 10K+ times! This question is

You Asked

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,

and Chris said...

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.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo, April 09, 2020 - 3:24 pm UTC

Really helpful.

Thanks for the help, Chris.

Follow Up

Geraldo, April 09, 2020 - 3:25 pm UTC

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 ...

Is there any performance gain putting the AND t2.col4='some_value' in the JOIN?
Chris Saxon
April 09, 2020 - 5:05 pm UTC

It's not a matter of performance, it's a matter of functionality!

If you want an outer join, all filters on the outer joined table must be in the join clause.

For inner joins it makes no difference.

Review

Geraldo, April 09, 2020 - 5:42 pm UTC

Got it. Thanks.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.