Implementing Function-Based Indexes
Function-based indexes
are created with functions or expressions in their definitions. Sometimes, function-based indexes are required when queries use SQL functions. For example, consider the following query that uses a SQL UPPER function:
SQL> select emp_name from emp where UPPER(emp_name) = 'DAVE';
In this scenario, there may be a normal B-tree index on the EMP_NAME column, but Oracle won’t use a regular index that exists on a column when a function is applied to it.
In this situation, you can create a function-based index to improve performance of queries that use a SQL function in the WHERE clause. This example creates a function-based index on UPPER(EMP_NAME): SQL> create index user_upper_idx on emp(upper(emp_name));
Function-based indexes allow index lookups on columns referenced by functions in the WHERE
clause of a SQL query. The index can be as simple as the preceding example, or it can be based on complex logic stored in a PL/SQL function.
■
Note
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index.
177
CHAPTER 8 ■ INDEXES
If you want to see the definition of a function-based index, select from the DBA/ALL/USER_IND_EXPRESSIONS view to display the SQL associated with the index. If you’re using SQL*Plus, be sure to issue a SET LONG command first. For example:
SQL> SET LONG 500
SQL> select index_name, column_expression from user_ind_expressions; The SET LONG command in this example tells SQL*Plus to display up to 500 characters from the COLUMN_EXPRESSION column, which is of type LONG.
Creating Unique Indexes
When you create a B-tree index, you can also specify that the index be unique. Doing so ensures that non-NULL values are unique when you insert or update columns in a table.
Suppose you’ve identified a column (or combination of columns) in the table (outside of the primary key) that is used heavily in the WHERE clause. In addition, this column (or combination of columns) has the requirement that it be unique within a table. This is a good scenario in which to use a unique index. Use the UNIQUE clause to create a unique index:
SQL> create unique index inv_uidx1 on inv(sku_id);
■
Note
The unique index doesn’t enforce uniqueness for NULL values inserted into the table. In other words, you can insert the value NULL into the indexed column for multiple rows.
You must be aware of some interesting nuances regarding unique indexes, primary-key constraints, and unique-key constraints. For a detailed discussion of primary-key constraints and unique-key constraints, see Chapter 7. When you create a primary-key constraint or a unique-key constraint, Oracle automatically creates a unique index and a corresponding constraint that is visible in DBA/ALL/USER_CONSTRAINTS.
When you only create a unique index explicitly (as in the example in this section), Oracle creates a unique index but doesn’t add an entry for a constraint in DBA/ALL/USER_CONSTRAINTS. Why does this matter? Consider this scenario:
SQL> create unique index inv_uidx1 on inv(sku_id);
SQL> insert into inv(sku_id) values (1);
SQL> insert into inv(sku_id) values (1);
Here’s the corresponding error message that is thrown:
ERROR at line 1:
ORA-00001: unique constraint (INV_MGMT.INV_UIDX1) violated
If you’re asked to troubleshoot this issue, the first place you look is in DBA_CONSTRAINTS for a constraint named INV_UIDX1. However, there is no information:
select
constraint_name
from dba_constraints
where constraint_name='INV_UIDX1';
no rows selected
178
CHAPTER 8 ■ INDEXES
The “no rows selected” message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data-dictionary views. In this situation, you have to look at DBA_INDEXES to view the details of the unique index that has been created.
If you want to have information related to the constraint in the DBA/ALL/USER_CONSTRAINTS views, you can explicitly associate a constraint after the index has been created: SQL> alter table inv add constraint inv_uidx1 unique(sku_id);
In this situation, you can enable and disable the constraint independent of the index. However, because the index was created as unique, the index still enforces uniqueness regardless of whether the constraint has been disabled.
When should you explicitly create a unique index versus creating a constraint and having Oracle automatically create the index? There are no hard and fast rules. I prefer to create a unique-key constraint and let Oracle automatically create the unique index, because then I get information in both the DBA/ALL/USER_CONSTRAINTS views and the DBA/ALL/USER_INDEXES views.
But Oracle’s documentation recommends that if you have a scenario where you’re strictly using a unique constraint to improve query performance, it’s preferable to create only the unique index. This is fine. If you take this approach, just be aware that you may not find any information in the constraint-related data-dictionary views.
Using Bitmap Indexes
Bitmap indexes are recommended for columns with a relatively low number of distinct values (low cardinality). You shouldn’t use bitmap indexes on OLTP databases with high INSERT/UPDATE/DELETE
activities, due to of locking issues. This is the case because the structure of the bitmap index results in potentially many rows being locked during DML operations, which results in locking problems for high-transaction OLTP systems.
Bitmap indexes are commonly used in data-warehouse environments. A typical star schema structure consists of a large fact table and many small dimension (lookup) tables. In these scenarios, it’s common to create bitmap indexes on fact table foreign-key columns. The fact tables are typically loaded on a daily basis and (usually) aren’t updated or deleted from.
Listed next is a simple example that demonstrates the creation and structure of a bitmap index. First you create a LOCATIONS table:
create table locations(
location_id number
,region varchar2(10)
);
Now, insert seven rows into the table:
insert into locations values(1,'NORTH');
insert into locations values(2,'EAST');
insert into locations values(3,'NORTH');
insert into locations values(4,'WEST');
insert into locations values(5,'EAST');
insert into locations values(6,'NORTH');
insert into locations values(7,'NORTH');
You use the BITMAP keyword to create a bitmap index. The next line of code creates a bitmap index on the REGION column of the LOCATIONS table:
SQL> create bitmap index reg_idx1 on locations(region);
179
CHAPTER 8 ■ INDEXES
A bitmap index stores the ROWID of a row and a corresponding bitmap. You can think of the bitmap as a combination of ones and zeros. A 1 indicates the presence of a value, and a 0 indicates that the value doesn’t exist. Table 8–3 shows the resulting structure of the bitmap index.
Table 8–3.
Structure of the REG_IDX1 Bitmap Index
Value/Row Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
EAST
0 1 0 0 1 0 0
NORTH
1 0 1 0 0 1 1
WEST
0 0 0 1 0 0 0
For each value of REGION (EAST, NORTH, and WEST), an array of values is stored that indicates which rows contain a value for a particular REGION. For example, the EAST location has bit settings in row 2
and row 5 (meaning that the EAST location is present for those two rows).
Bitmap indexes are effective at retrieving rows when multiple AND and OR conditions appear in the WHERE clause. For example, to perform the task “find all rows with a region of EAST or WEST,” a Boolean algebra OR operation is performed on the EAST and WEST bitmaps to quickly return the rows 2, 4, and 5.
Table 8–4 shows the OR operation on the EAST and WEST bitmap as the last row.
Table 8–4.
Results of an OR Operation
Value/Row Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
EAST
0 1 0 0 1 0 0
NORTH
1 0 1 0 0 1 1
WEST
0 0 0 1 0 0 0
Boolean
0 1 0 1 1 0 0
OR on
EAST and
WEST
■
Note
Bitmap indexes and bitmap join indexes are available only with the Oracle Enterprise Edition of the database.
Creating Bitmap Join Indexes
Bitmap join indexes store the results of a join between two tables in an index. Bitmap indexes are beneficial because they avoid joining tables to retrieve results. The syntax for a bitmap join index differs 180
CHAPTER 8 ■ INDEXES
from a regular bitmap index in that it contains FROM and WHERE clauses. Here’s the basic syntax for creating a bitmap join index:
create bitmap index
on
from
where
and D_CUSTOMERS tables:
create bitmap index f_shipments_bm_idx1
on f_shipments(d_customers.cust_name)
from f_shipments, d_customers
where f_shipments.d_cust_id = d_customers.d_cust_id;
Now, consider a query such as this:
select
d.cust_name
from f_shipments f, d_customers d
where f.d_cust_id = d.d_cust_id
and d.cust_name = 'Sun';
The optimizer can choose to use the bitmap join index and thus avoid the expense of having to join the tables.
Implementing Reverse-Key Indexes
Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created. For example, if the index values are 201, 202, and 203, the reverse-key index values are 102, 202, and 302:
Index value Reverse key value
------------- --------------------
201 102
202 202
203 302
Reverse-key indexes can perform better in scenarios where you need a way to evenly distribute index data that would otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid having I/O concentrated in one physical disk location within the index during large inserts of sequential values.
Use the REVERSE clause to create a reverse-key index:
SQL> create index inv_idx1 on inv(inv_id) reverse;
You can verify that an index is reverse-key by running the following query: SQL> select index_name, index_type from user_indexes;
Here’s some sample output showing that the INV_IDX1 index is reverse-key: 181
CHAPTER 8 ■ INDEXES
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
INV_IDX1 NORMAL/REV
USERS_IDX1 NORMAL
■
Note
You can’t specify REVERSE for a bitmap index or an index-organized table.
Creating Key-Compressed Indexes
Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated. Compressed indexes in these situations have the following advantages:
• Reduced storage
• More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index
Suppose you have a table defined as follows:
create table users(
last_name varchar2(30)
,first_name varchar2(30)
,address_id number);
You want to create a concatenated index on the LAST_NAME and FIRST_NAME columns. You know from examining the data that there is duplication in the LAST_NAME column. Use the COMPRESS
N
clause to create a compressed index:
SQL> create index users_idx1 on users(last_name, first_name) compress 2; The prior line of code instructs Oracle to create a compressed index on two columns. You can verify that an index is compressed as follows:
select
index_name
,compression
from
user_indexes where index_name like 'USERS%';
Here’s some sample output indicating that compression is enabled for the index: INDEX_NAME COMPRESS
------------------------------ --------
USERS_IDX1 ENABLED
■
Note
You can’t create a key-compressed index on a bitmap index.
182
CHAPTER 8 ■ INDEXES
Parallelizing Index Creation
In large database environments where you’re attempting to create an index on a table that is populated with many rows, you may be able to reduce the time it takes to create the index by using the PARALLEL
clause:
create index inv_idx1 on inv(inv_id)
parallel 2
tablespace inv_mgmt_data;
If you don’t specify a degree of parallelism, Oracle selects a degree based on the number of CPUs on the box times the value of PARALLEL_THREADS_PER_CPU.
Avoiding Redo Generation When Creating an Index
You can optionally create an index with the NOLOGGING clause. Doing so has these implications: