Secondary Keys

When evaluating multiple queries, you find situations where one table needs more than one key. Secondary indexes (see page Secondary Indexes) can provide a secondary key and can be employed in these circumstances, but indexes must be used with discretion, as they add overhead to update, delete, and insert operations.

For example, perhaps the administration department decides empno is the appropriate key for the employee table, but the shipping department prefers address as the key column of the table. Secondary indexes can alleviate this problem, but you have to weigh factors, such as the number of times a particular query is executed, the acceptable response time for a query, the time of day the query is likely to be executed, and the importance of a query in the global view of the application.

In evaluating how to key the employee table, each query type is ranked as in the following example:

	Query	Number Executed Per Day	Acceptable Response Time	Time of Day
1	select * from employee where empno = 123; KEY = empno	2000	1 second	7-4
2	select name from employee order by empno; no key, but sorted by empno	1	2 hours	after 5
3	select salary from employee where name = 'Shigio'; KEY = name	20	30 sec	9-5
4	select name from employee where comment = 'Fire'; KEY = comment	1	30 sec	9-5

The most important query to key in this list is Query 1 because it is executed frequently, requires fast response, and is pivotal to the application. The key choice for employee table is the empno column.

Query 2 does not contain a restriction, so no key decision must be made. Also, this report can be run at night, so CPU time is not crucial. Therefore, B-tree on empno is a good choice of storage structure and key, because both Query 1 and Query 2 benefit.

Query 3 is important, but it is not executed as frequently, nor does it require as immediate a response. A secondary key on name is appropriate.

Query 4 is not executed frequently, and although the importance rating for this query was high, it is advantageous to either work out a different implementation strategy or discourage the user from using this query often. The comment field is particularly large and empty and, therefore, is not a good key choice. A separate fired table can be set up that lists the employees who had been fired that day; this table is joined to the employee table.