Pro Oracle Database 11g Administration (35 page)

Read Pro Oracle Database 11g Administration Online

Authors: Darl Kuhn

Tags: #Oracle DBA

BOOK: Pro Oracle Database 11g Administration
6.96Mb size Format: txt, pdf, ePub

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 . = .; Bitmap join indexes are appropriate in situations where you’re joining two tables using the foreign-key column(s) in one table that relate to primary-key column(s) in another table. For example, suppose you typically retrieve the CUSTOMER_NAME from the D_CUSTOMERS table while joining to a large F_SHIPMENTS fact table. This next example creates a bitmap join index between the F_SHIPMENTS

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:

Other books

Her Royal Spyness by Rhys Bowen
The Color of Ivy by Peggy Ann Craig
Split by Lisa Michaels
Anatomy of Restlessness by Bruce Chatwin
Another Summer by Georgia Bockoven
The Sometime Bride by Ginny Baird
Deadly Sins by Kylie Brant
After and Again by McLellan, Michael