Order of WHERE clause conditions

Database
Enthusiast

Order of WHERE clause conditions

I am running a normal insert-select query on a huge teradata table to populate another teradata table. There are 3 filter conditions in the where clause of which 2 are date field filters and the other is a integer field filter.
Will there be any change in performance if the order of the filters are changed? The 2 date field filters are dominant and does huge filtering of records and the int field filter needs to filter from the records filtered by date fields.
Let me know the impacts on various combinations of using these 3 fields as filter.
3 REPLIES
Teradata Employee

Re: Order of WHERE clause conditions

My recommendation would be, to try out the different combinations, as it can vary from system to system and type of data and offcourse the SQL written!

Regards,

Adeel
SN
Enthusiast

Re: Order of WHERE clause conditions

hi,

I understand you are trying out with just different filter condition sequence. but since you said bulk of filtering is done with dates, you can improve the query performance by defining a Partition on these date fields on your source table.

thx
Teradata Employee

Re: Order of WHERE clause conditions

You can always see exactly how the optimizer will execute your sql by running the EXPLAIN plan. If the variations of sql produce the same EXPLAIN then there will be no difference in performance.