| Previous | Table of Contents | Next |
When an SQL request is commonly issued using multiple columns, a concatenated or multi-column index can be used to improve performance. Oracle supports the use of multi-valued indexes, but there are some important limitations. Unlike other relational databases, Oracle requires that all columns in the index be sorted in the same order, either ascending or descending. For example, if we needed to index on customer_last_name ascending, followed immediately by gross_pay descending, we would not be able to use a multi-valued index.
Sometimes two columns—each with poor selectivity (i.e., the columns have few unique values)—can be combined into an index that has better selectivity. For example, we could combine a status field that has three values (good, neutral, bad) with another column such as state_name (only 50 unique values), thereby creating a multi-valued index that has far better selectivity than each column would have if indexed separately.
Another reason for creating concatenated indexes is to speed the execution of queries that reference all of the values in the index. For example, consider the following query:
SELECT customer_last_name,
customer_status,
customer_zip_code
FROM CUSTOMER
ORDER BY customer_last_name;
Now, we create an index as follows:
CREATE INDEX last_status_zip ON CUSTOMER (customer_last_name, customer_status, customer_zip_code) ascending;
If this query were to be issued against the customer table, Oracle would never need to access any rows in the base table! Since all of the key values are contained in the index and the high-order key (customer_last_name) is in the ORDER BY clause, Oracle can scan the index, retrieving the data without ever touching the base table.
With the assistance of this feature, the savvy Oracle developer may also add columns to the end of the concatenated index so that the base table is never touched. For example, if the above query also returned the value of the customer_address column, this column could be added to the concatenated index, dramatically improving performance.
In summary, the following guidelines apply when creating a concatenated index:
It is interesting to note that the fastest execution for an individual task may not always be the best choice. For example, consider the following query against a customer table:
SELECT customer_name
FROM CUSTOMER
WHERE
credit_rating = 'POOR'
AND
amount_due > 1000
AND
state = 'IOWA'
AND
job_description like lower('%computer%');
Here we see a query where a full-table scan would be the most efficient processing method. Because of the complex conditions and the use of Oracle extensions in the SQL, it might be faster to perform a full-table scan. However, the fast execution of this task may be done at the expense of other tasks on the system as the buffer pool becomes flushed.
In general, the type of optimizer will determine how indexes are used. As we know, the Oracle optimizer can run as either rule-based or cost-based. As a general rule, Oracle is intelligent enough to use an index if it exists, but there are exceptions to this rule. The most notable exception is the n-way join with a complex WHERE clause. The rule-based optimizer will get “confused” and invoke a full-table scan on at least one of the tables, even if the appropriate foreign key indexes exist for all of the tables. The only remedy to this problem is to use the cost-based optimizer, which involves analyzing statistics for each table. This problem is discussed at length in Chapter 4, Tuning Oracle SQL.
We always need to remember that Oracle will only use an index when the index column is specified in its “pure” form. The use of the substr, upper, lower, and other functions will invalidate the index. However, we do have a few tricks to help us get around this obstacle. Consider the two equivalent SQL queries:
SELECT * FROM CUSTOMER WHERE total_purchases/10 > 5000; SELECT * FROM CUSTOMER WHERE total_purchases > 5000*10;
The second query, by virtue of the fact that it does not alter the index column, would be able to use an index on the total_purchases column.
Several parameters can be used to ensure that all data stored within Oracle tables remains in an optimal configuration. Consider the following Oracle table definition:
CREATE TABLE ITEM (
item_nbr NUMBER,
item_name VARCHAR(30),
item_description VARCHAR(50))
STORAGE( INITIAL 50K
NEXT 50K
PCTFREE 10
PCTUSED 60
FREELISTS 1);
PCTFREE tells Oracle how much space to reserve in each Oracle block for future updates and can have an important impact on the performance of an Oracle database if it is set too low. For example, if a row contains a lot of VARCHAR data types and the rows are initially inserted without values, later updates of values into the VARCHAR fields will cause the row to expand on the block. If the target block does not have enough room, Oracle must fragment the row and store some row information on the next physical block in the tablespace. If this block is also full, Oracle may create a very long chain until it finds a block with enough room to store the new column data. This condition can lead to many unnecessary I/Os when the row is retrieved, since many database blocks must be read to retrieve the row. The rule here is simple: Determine the average row length and predicted growth for each row, then use PCTFREE to reserve that amount of space on each block. For static read-only tables, it is acceptable to set PCTFREE to a very small number in order to fully occupy each database block. Again, the PCTFREE parameter is only useful for the SQL UPDATE statement—and only when the UPDATE will cause the row to grow in size. If this is not the case, then PCTFREE should be set to 5, reserving only 5 percent of each database block for updates.
| Previous | Table of Contents | Next |