Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

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

Answered by: Chris Saxon - Last updated: April 09, 2020 - 5:05 pm UTC

Category: Database Development - Version: 18.6

Viewed 1000+ times

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

and you rated our response

  (3 ratings)

Reviews

Review

April 09, 2020 - 3:24 pm UTC

Reviewer: Geraldo

Really helpful.

Thanks for the help, Chris.

Follow Up

April 09, 2020 - 3:25 pm UTC

Reviewer: Geraldo

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

Followup  

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

April 09, 2020 - 5:42 pm UTC

Reviewer: Geraldo

Got it. Thanks.

More to Explore

Performance

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