Key Columns
When a key value is specified, instead of scanning the entire table, the search uses the index (or hashes the key) to go directly to the page in the table where the row with that key resides.
Choosing which columns to use as key columns is not always clear cut. To understand what a key column does, let us look again at the employee table. Consider the query:
select * from employee
where name = 'Shigio';
The column called name (assuming it is unique) is a good candidate for the key for the employee table. If the employee table is keyed on name, finding the employee record where the name is Shigio is faster than scanning the entire table.
Good columns for keys are columns referenced in the WHERE clause portion of the query, not the target list. Columns that restrict the number of rows returned and joining columns, demonstrated in the two examples below, are candidates for keys:
where name = 'Shigio'
where e.dept = d.dept
A join qualification by itself is not restrictive, so if there also exists a restrictive qualification in the WHERE clause, choose the restriction as the key column. For example:
select empno from employee
where employee.name = dept.manager
and dept.name = 'Technical Support';
The most restrictive qualification in this WHERE clause is:
dept.name = 'Technical Support'
The dept table is keyed on name. Keying dept on manager is not necessary for this query, because once the row for the department named Technical Support is identified, you know the manager. The employee table is also keyed on name, because once the manager of the dept table is known, the search can do a keyed lookup into the employee table. Empno is not a key candidate in this query, because it appears in the target list, not the WHERE clause.
Note: The order of qualifications in the WHERE clause is not important, as the Ingres optimizer decides the appropriate order of execution.
Often, there are multiple candidate keys in a single query. Generally, the most restrictive column is the best key. The following example illustrates this:
Select empno from employee
Where employee.sex = 'F'
And employee.salary > 20000'>
And employee.name like 'Shigi%';
In this case, there are three columns that are potential keys. However, these first two qualifications are not very restrictive because “M” and “F” are the only two values for the key sex, and many employees are likely to have the selected salary qualification:
employee.sex = 'F'
employee.salary > 20000
The most restrictive qualification is probably:
employee.name like 'Shigi%'
Thus, name is chosen as the key column. Once you find all rows with names beginning with Shigi, it takes little time to determine which of these rows are female and make more than 20000, because the number of rows you are looking at is only a small subset of the employee records.