Rule Based Optimizer   «Prev  Next»
 Lesson 5 Positioning statements in the FROM clause Objective Correctly order table names in the FROM clause.

# Positioning statements in FROM Clause

The order of the table names in the FROM clause determines the driving table for the rule-based query. It is very important to place the table that will return the smallest number of rows last in our FROM clause.

## The driving table

To review, rows from the driving table are retrieved first, and rows from the other table are then probed for corresponding values.

Therefore, it is essential that the second table return the least amount of rows based on the WHERE clause. The driving table is the table that will return the smallest number of rows, based on the filters in the WHERE clause. This is not always the table with the smallest number of rows.

### An example

For example, let’s assume that we have a distributed Oracle database with customer tables on several locations. One customer table resides in London and another in New York, and the distribution of data is shown below.

The problem is that the data is skewed. We have the vast majority of customers in New York, so the London table should be last for unqualified queries. For an unqualified query against these tables, we should specify the New York table first, since London has the least amount of returned rows:
```SELECT
Customer_name
FROM
customer@new_york,
customer@london
;
```

However, if we query for bad credit, we should put the New York table last, since New York has far fewer deadbeats than London.
```SELECT
Customer_name
FROM
customer@london,
customer@new_york
WHERE