CODEWITHSUNDEEP
sqloracle-databaseselect
Mohamed Iqzas
Mohamed Iqzas

Reputation: 1046

performance of select queries oracle

from millions of records in a table if i want to select few records depending on several where conditions, are there any facts to be considered for the sequence of where conditions?

for example, in a table of students in a state where conditions might include the following:

  1. Institute Id,
  2. DeptId (unique for each institute, i.e. deptId in a institute cannot be present on another institute.)

Now, if i have to select a list of students in a particular dept, deptId is enough for the where condition, because deptId of the students will be present only in one particular institute.

But will it improve performance if i include instituteId also before the deptid in where conditions so that records can be filtered based on institute first, and then based on deptId?

Will the order of where conditions have impact on query performance? Thanks.

Upvotes: 0

Views: 97

Answers (3)

Serpiton
Serpiton

Reputation: 3684

Not per se but depending on the index: if you have an index with Institute ID, DeptID the using only DeptID in the where condition will not use the index and performe a table scan (there is a lot more in this but that's the basic). Try always to create a where condition covered by the PK or another index on the table with every column in that index, if you have an index on a, b and c and a where on a and c that will not use the index.

The order of the column in the where condition will be re-organized by the DB to fit the index definition

Upvotes: 2

Andy Lester
Andy Lester

Reputation: 93636

It all depends on how your indexes are set up. If you have no indexes on your table, then it doesn't make a bit of difference because every query is going to have to scan the entire table anyway.

http://use-the-index-luke.com is an excellent introduction to indexes and how they work and how they should be set up.

Upvotes: 1

Ryx5
Ryx5

Reputation: 1366

Order of where conditions won't have impact on query performance. Because your RDBMS will reorganise depending on best indexed columns.

Anyway, if you have indexes on both columns you should use only DeptId. Otherwise RDBMS will perform 2 filters instead of 1, in theory it could be slower to use more conditions (depending on indexes).

But, you can try both ways to check excecution time, many things can affect performance (specially with huge bulk of data) so just test it.

Try to make 1 index for 2 columns in same time, so it could be interesting to use 2 conditions. (depending on RDBMS)

Upvotes: 3

Related Questions