buppcpp wrote:
Everytime I issue a query that has 3 or more table in it, and each table is joined on index fields with high selectivity (mainly primary key fields), the first table in the plan ALWAYS does a table scan ('NATURAL' in PLAN lingo).
Answer from Ann Harrison:
Lets think a minute about what you're asking for and how the database can resolve your query.
select a.name, b.department, c.company
from employees a
join departments b on a.DeptID = b.DeptID
join companies c on b.CompanyID = c.CompanyID
You've asked for the names of all employees with the names of their departments and companies. The fastest way to get all the employee names is to read the employee table from front to back - a natural scan. Once you have an employee record, you can lookup the department by its key value, DeptID. Once you have the department record, you can look up the company by its key value, CompanyID. But when you start, you don't have any key to lookup in the employee table.
If you add a where clause that restricts the employee records you want to see based on the value of an indexed field in that table, then the database can and will use an index on that table.