For instance, every value in the NAME field of the BILLS table is unique. When using a compound index, place the most selective field first in the column list. That is, place the field that you expect to select most often at the beginning of the list. (The
order in which the column names appear in the CREATE INDEX statement does not have to be the same as their order within the table.) Assume you are routinely using a statement such as the following:
SQL> SELECT * FROM BILLS WHERE NAME = "Cable TV Company";
To achieve performance gains, you must create an index using the NAME field as the leading column. Here are two examples:
SQL> CREATE INDEX NAME_INDEX ON BILLS(NAME, AMOUNT);
or
SQL> CREATE INDEX NAME_INDEX ON BILLS(NAME);
The NAME field is the left-most column for both of these indexes, so the preceding query would be optimized to search on the NAME field.
Composite indexes are also used to combine two or more columns that by themselves may
have low selectivity. For an example of selectivity, examine the BANK_ACCOUNTS table: ACCOUNT_ID TYPE BALANCE BANK
1 Checking 500 First Federal
2 Money Market 1200 First Investor's
3 Checking 90 Credit Union
4 Savings 400 First Federal
5 Checking 2500 Second Mutual
6 Business 4500 Fidelity
Notice that out of six records, the value Checking appears in three of them. This column has a lower selectivity than the ACCOUNT_ID field. Notice that every value of the ACCOUNT_ID field is unique. To improve the selectivity of your index, you could combine the TYPE and ACCOUNT_ID fields in a new index. This step would create a unique index value (which, of course, is the highest selectivity you can get).
NOTE: An index containing multiple columns is often referred to as a
composite index. Performance issues may sway your decision on whether to use
a single-column or composite index. In Oracle, for example, you may decide
to use a single-column index if most of your queries involve one particular
column as part of a condition; on the other hand, you would probably
create a composite index if the columns in that index are often used
together as conditions for a query. Check your specific implementation on
guidance when creating multiple-column indexes.
Using the UNIQUE Keyword with CREATE INDEX
Composite indexes are often used with the UNIQUE keyword to prevent multiple records from appearing with the same data. Suppose you wanted to force the BILLS table to have the following built-in "rule": Each bill paid to a company must come from a different bank account. You would create a UNIQUE index on the NAME and ACCOUNT_ID fields.
Unfortunately, Oracle7 does not support the UNIQUE syntax. Instead, it implements the UNIQUE feature using the UNIQUE integrity constraint. The following example demonstrates the UNIQUE keyword with CREATE INDEX using Sybase's Transact-SQL
language.
INPUT:
1> create unique index unique_id_name
2> on BILLS(ACCOUNT_ID, NAME)
3> go
1> select * from BILLS
2> go
OUTPUT:
NAME AMOUNT ACCOUNT_ID
Florida Water Company 20 1
Power Company 75 1
Phone Company 125 1
Software Company 250 1
Record Club 25 2
Cable TV Company 35 3
Debtor's Credit Card 35 4
U-O-Us Insurance Company 125 5
Joe's Car Palace 350 5
S.C. Student Loan 200 6
Now try to insert a record into the BILLS table that duplicates data that already exists.
INPUT:
1> insert BILLS (NAME, AMOUNT, ACCOUNT_ID)
2> values("Power Company", 125, 1)
3> go
ANALYSIS:
You should have received an error message telling you that the INSERT command was not allowed. This type of error message can be trapped within an application program,
and a message could tell the user he or she inserted invalid data.
Example 10.3
Create an index on the BILLS table that will sort the AMOUNT field in descending order.
INPUT/OUTPUT: