Problem solve Get help with specific problems with your technologies, process and projects.

Table join sequence for performance

How do I arrange table sequence in FROM clause and conditions in WHERE clase to get best performance for query?

All things being equal, index your tables and trust your database optimizer.

All optimizers will strive to access the most restrictive table first. The "most restrictive" table is the one which produces the fewest rows that satisfy the conditions that apply to it in the WHERE clause. This means fewer rows to join to the next table, and thus better performance overall.

However, if your tables have no indexes on them, then the optimizer has far less to work with.

For example, suppose you had a table of employees and a table of departments. Suppose you wanted a list of all the managers in the HR department --

select empname
  from employees, departments
where empdeptid = deptid
   and empmanager = 'Y'
   and deptname = 'HR'

It would appear that the WHERE condition that limits departments to HR is the most restrictive. Just the one HR record, right? But without indexes, the optimizer has no way of knowing how many records are going to satisfy the conditions. So it might instead decide to loop through the employees looking for managers, and then take the result and for each manager employee row, join it to its department to see if this manager is in the HR department. Some optimizers have a default rule that, in the absence of indexes, they will access the tables in the order that the tables are listed in the FROM clause. So if you cannot index the tables, then list them in the FROM clause in the order that you know is most restrictive first.

But with indexes, everything changes. Usually the optimizer can figure out how restrictive a WHERE condition is by looking at the statistics it keeps for each index. If empmanager is indexed, the optimizer will know how many rows are managers and how many aren't. If deptname is indexed, it will know how many HR departments there are. If empdeptid has an index, the optimizer can access the employee rows in the HR department without scanning the entire employees table.

Tip: Most databases have a function or command that will allow you to update index statistics. Make sure you do this at least once during development, again after loading the database with its live (full volume) data, and any time after substantial inserts have been made.

Dig Deeper on Oracle and SQL