Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 38

In Chapter 30, we presented table constraints such as PRIMARY KEY and CHECK. In that same chapter, we introduced unique indexes as a way to enforce a PRIMARY KEY constraint. In addition to using
indexes to enforce constraints, you can use indexes to boost the performance of queries significantly by reducing the amount of time needed to retrieve rows from a table instead of reading every row in the table to find the row or rows you are looking for. However, too many indexes on a table can be just as bad as not enough.
In this chapter, we delve more deeply into how to use indexes most effectively, how to manage indexes, and how to monitor index usage. Finally, we show how you can use the Oracle Database XE GUI to see the structure of the indexes in the database and create domain indexes, another type of Oracle index.

Understanding Oracle Index Types

Also in Chapter 30, we introduced two types of indexes: B-tree and bitmap. They both accomplish a common goal: reducing the amount of time required to retrieve rows from a table. However, they are constructed differently, and you choose one or the other based on the existing and expected type and distribution of the data in the column or columns to be indexed. Unless your tables are very small, your queries will benefit from indexed columns. Traversing an index to find a particular row or many rows using the conditions in the WHERE clause will typically take less time than reading every row of the table itself.
Indexes are both logically and physically independent of the rows in the indexed tables. The
indexes themselves can be dropped and added without affecting the table data or the queries you use against the tables (except for affecting the performance of the query). Oracle automatically main- tains entries in an index as rows in the indexed table are added, modified, or deleted. When you drop a table, Oracle drops all associated indexes as well.

■Note We discuss another type of index, a domain index, later in this chapter in the section “Using Oracle Text.”

In the following sections, we give you a bit more detail about B-tree and bitmap indexes and how they are constructed. B-tree indexes have several subtypes. We identify and explain each of the subtypes and when to use them.

661

B-tree Indexes

B-tree indexes are the most common type of index; they are created by default if you do not specify a type in the CREATE INDEX statement. B-tree, which stands for balanced-tree, looks like an inverted tree with two types of blocks: branch blocks and leaf blocks. Figure 38-1 provides a high-level view of a B-tree index with a depth of three levels: two for the branch blocks and one for the leaf blocks. The leaf blocks are always one level deep. Branch blocks contain partial keys and pointers to other branch blocks or leaf blocks. The leaf blocks contain the pointers (ROWIDs) to the actual row of data containing the indexed column or columns.

Figure 38-1. A B-tree index

The performance of a B-tree index is consistent regardless of the row you are searching for. Because the tree is always balanced, the search of the tree for a given column’s key value will always traverse the same number of levels in the tree to find the leaf block with the row you are looking for.
Here are the different types of B-tree indexes:

• Unique: By default, Oracle creates a nonunique index unless you specify the UNIQUE keyword in your CREATE INDEX statement. As the name implies, there are no duplicate values in a unique index. Oracle uses unique indexes to enforce a primary key (PK) constraint.
• Reverse: A reverse key index stores key values in reverse order. If an indexed column contains ascending values, a reverse key index may improve performance by reducing the contention on a particular leaf block. For example, a regular index will likely store the row pointers for rows with values 101456, 101457, and 101458 in the same leaf block. In contrast, a reverse key index will most likely store row pointers for 654101, 754101, and 854101 in different leaf blocks.
• Function-based: A function-based index is created on an expression containing one or more columns in a table instead of just the columns themselves. For example, you may create a function-based index on UPPER(LAST_NAME) to facilitate case-insensitive searches on the LAST_NAME column while avoiding a full table scan.
• Index-organized: An index-organized table (IOT) is a special type of B-tree index that stores both the index and the data within the same database segment. This may save many I/O opera- tions for lookup tables, tables with only a few columns, or tables that are relatively static.

Bitmap Indexes

A bitmap index uses a string of binary ones and zeros to represent the existence or nonexistence of a particular column value in any row of a table. For each distinct value of a column in a table, a bitmap index stores a string of binary ones and zeros with a length of the number of rows in the table. This makes your index storage requirements very low as long as the cardinality (the number of distinct values) of the column is low.
Queries using AND and OR conditions that compare several columns with bitmap indexes are very efficient. This also applies to joining multiple tables on columns with bitmap indexes. Table 38-1 shows a few rows from the EMPLOYEES table along with the bitmap indexes for the GENDER column, a typical low-cardinality column. Since the cardinality of the GENDER column is 2, the index maintains
two bitmaps. For any given row, only one bit in the corresponding bitmaps is a 1; the rest are 0.

Table 38-1. Bitmap Index on Gender in the EMPLOYEES Table

Employee Name Gender Bitmap for M Bitmap for F
Karen Colmenares F 0 1
Adam Fripp M 1 0
Shanta Vollman F 0 1
Julia Nayer F 0 1
Irene Mikkilineni F 0 1
Laura Bissot F 0 1
Steven Markle M 1 0
Alexander Khoo M 1 0
Oliver Tuvault M 1 0

Creating, Dropping, and Maintaining Indexes

You use the CREATE INDEX statement to create a B-tree or bitmap index. The basic syntax looks like this:

CREATE [BITMAP | UNIQUE] INDEX indexname
ON tablename (column1, column2, ...) [REVERSE];

If you do not specify BITMAP, Oracle assumes a B-tree index. The UNIQUE keyword ensures that the index will not contain duplicate values. The REVERSE keyword creates a reverse key index, discussed in the previous section. The name of the index must be unique among all indexes within a schema (user). However, the namespace for indexes is different from the namespace for table names. This means you could create an index named EMPLOYEES on the LAST_NAME column of the EMPLOYEES table. This may lead to confusion, though, especially if you want to have more than one index on the EMPLOYEES table. One possible naming convention is to include the table name, the column name, and the index type in the index name, as in this example:

CREATE INDEX employees_last_name_ix ON employees(last_name);

Dropping an index is quite intuitive if you are familiar with other Oracle Database XE statements. Use the DROP INDEX statement like this:

DROP INDEX employees_last_name_ix;

Using the Oracle Database XE GUI makes it even easier to create an index; no knowledge of syntax is required. However, it is good to know the syntax when you are (infrequently) stuck with only a SQL command-line interface. Start at the Oracle Database XE home page, click Object Browser, and select Indexes in the drop-down box at the top of the left navigation pane. Logged in as the user HR, you will see the indexes owned by HR. Clicking the EMP_EMP_ID_PK index name in the left navigation area shows you the details for the primary key (unique) index on the EMPLOYEE_ID column of the EMPLOYEES table in Figure 38-2.

Figure 38-2. Browsing indexes owned by HR

In the following scenario, your queries against the EMPLOYEES table seem to be slow (or at least your users tell you they are slow). You suspect it is because you might not have a column indexed. Here is a typical management query against the EMPLOYEES table:
select * from employees where salary > 5000 order by salary desc;

Entering this query in the SQL Commands window and clicking the Explain tab shows you how Oracle Database XE accesses each of the tables in the query, along with a list of the indexed columns and the table columns, as you can see in Figure 38-3.

Figure 38-3. Using the Explain function on the SQL Commands page

It seems clear from the Explain tab on the SQL Commands window that Oracle Database XE reads the entire EMPLOYEES table when you filter by the SALARY column. This line in the Query Plan section of Figure 38-3 spells it out for you:

TABLE ACCESS FULL EMPLOYEES

Therefore, you decide to create a nonunique B-tree index on the SALARY column. From the Create drop-down box shown earlier in Figure 38-2, select Index. Enter the EMPLOYEES table name in the Table Name box, or select it from the drop-down button to the right of the box. You will see the page shown in Figure 38-4. For Type of Index, be sure that the Normal radio button is selected. We talk about text-based indexes in the “Using Oracle Text” section later in this chapter. Click the Next button.

Figure 38-4. Specifying the table name

You decide to keep Oracle’s suggested name for the index as EMPLOYEES_IDX1, as shown in Figure 38-5. The index will not be unique (many employees will have the same salary), and you select SALARY as the indexed column in the Index Column 1 drop-down box.
Click the Next button and you see a confirmation page. Click Finish to create the index. The new
index appears in the list on the Object Browser page shown in Figure 38-6.

Figure 38-5. Specifying indexed columns

Figure 38-6. Reviewing the details of the new index, EMPLOYEES_IDX1.

Monitoring Index Usage

Too many indexes on a table is bad for two reasons. First, an index occupies disk space that might otherwise be better used elsewhere. Second, indexes must be updated whenever you add, delete, or modify rows in a table. So how can you be sure that an index is even used? As of Oracle9i, you can use the dynamic performance view V$OBJECT_USAGE (see Chapter 35 for more information on dynamic performance views) to track whether an index has been used for a given time period.
To turn on monitoring, use the ALTER INDEX <index name> MONITORING USAGE command on the new index, as follows:

ALTER INDEX employees_idx1 MONITORING USAGE;

Right after running this command, check the view V$OBJECT_USAGE to make sure the index is being monitored:

SELECT index_name, table_name, monitoring, used, start_monitoring
FROM v$object_usage WHERE index_name = 'EMPLOYEES_IDX1';

The results from this query are shown in Figure 38-7. Notice that the column USED will be set to YES whenever Oracle uses the index to access rows in the EMPLOYEES table when you use SALARY in the WHERE clause; initially, this column is set to NO until the index is used.

Figure 38-7. Querying V$OBJECT_USAGE for index status

Now that the index is being monitored, wait a day, or long enough for the regular business cycles to complete at least once, and query this view again. If the USED column has a value of YES, as in Figure 38-8, you should probably keep the index.

Figure 38-8. Querying V$OBJECT_USAGE for index status after index usage

In any case, once you determine the index usage statistics, turn off the monitoring of the index using the NOMONITORING keyword:

ALTER INDEX employees_idx1 NOMONITORING USAGE;

Oracle incurs a slight overhead for every access to the EMPLOYEES table if one of its indexes is being monitored; so if you do not need to monitor it, turn it off. Note also that since V$OBJECT_USAGE is a dynamic performance view, its contents are not retained after the database is shut down and restarted.

Using Oracle Text

A standard index, like the ones we created in previous chapters and earlier in this chapter, helps you quickly access numeric values in one or more columns in the database. For text fields, you can create an index on the entire text field. When you query a text field, Oracle uses the index when you specify a comparison operator in the WHERE clause such as the following query against the TICKET table created in Chapter 37:

SELECT ticket_id, username, title, description FROM ticket
WHERE title = 'Login problems';

However, what if you want to search for any ticket with the word problems in the title? You could certainly use the LIKE clause, as follows:

SELECT ticket_id, username, title, description FROM ticket
WHERE title LIKE '%problems';

The problem is Oracle can leverage an index only if the search string is exact or the wildcard character is at the end of the search string, as in these examples:

WHERE title LIKE 'Login problems'; WHERE title LIKE 'Login%';

Otherwise, if the wildcard is at the beginning of the string, Oracle cannot leverage an index. Here is an example of a search string with the wildcard character at the beginning:

WHERE title LIKE '%problems';

Oracle cannot use an index on the TITLE column and will perform a full table scan to find the requested search string. The same performance hit occurs when you want to perform a case-insensitive search. Oracle makes it easy to create indexes on relatively static unstructured text documents such as Microsoft Word documents, Web sites, or digital libraries. To address searches beyond the capa- bilities of basic Oracle indexes, you can use an advanced indexing feature called Oracle Text. For example, Oracle Text can easily search for the word key near the word stuck in a sentence within a document or a Web page, excluding sentences with the word printer near the word stuck; this partic- ular search is useful if you want to find keyboard issues and not printer jams in your support ticket database.
An Oracle Text index is another category of indexes called domain indexes, which typically are
supported by PL/SQL packages and involve much more logic and processing overhead than B-tree or bitmap indexes. Oracle Text queries usually consist of words or phrases. Numeric or date/timestamp columns are best indexed by standard B-tree or bitmap indexes introduced earlier in the chapter. Although a complete discussion of Oracle Text is beyond the scope of this book, you can get a good feel for the capabilities of Oracle Text by trying out the examples in the following paragraphs.
By default, no nonprivileged accounts have access to Oracle Text, so you will have to run a few SQL statements for the user that will create the Oracle Text indexes. The first step is to use a privileged account to grant the role CTXAPP to the HR account, as follows (see Chapter 31 for more information on privileges and roles):

GRANT CTXAPP TO HR;

Next, grant privileges on the Oracle Text packages to the HR account using these GRANT statements:

GRANT EXECUTE ON CTXSYS.CTX_CLS TO hr; GRANT EXECUTE ON CTXSYS.CTX_DDL TO hr; GRANT EXECUTE ON CTXSYS.CTX_DOC TO hr; GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO hr; GRANT EXECUTE ON CTXSYS.CTX_QUERY TO hr; GRANT EXECUTE ON CTXSYS.CTX_REPORT TO hr; GRANT EXECUTE ON CTXSYS.CTX_THES TO hr; GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO hr;

The HR account user is now free to create and drop any type of Oracle Text index without any further intervention from the DBA. To create an Oracle Text index, the syntax is similar to a standard CREATE INDEX with the addition of the INDEXTYPE clause:

CREATE INDEX indexname
ON tablename (column) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('context_parameter1 context_parameter2 . . . ')
;

■Note The CONTEXT index type is an index on a single text column. The other available index types in the CTXSYS
package are CTXCAT for combinations of a text field and one or more other columns, and CTXRULE to associate English-language query terms with document categories—for example, associating vanilla, chocolate, and straw- berry with the category ice cream.

The PARAMETERS clause is specific to the index type. These parameters can specify the type of text stored, how to search the text, and whether the text index is refreshed automatically when the value of the indexed text column changes due to an UPDATE or INSERT.
For our problem ticket table in Chapter 37, we will create an Oracle Text index on the
DESCRIPTION column using this CREATE INDEX statement with no parameters:

CREATE INDEX ticket_desc_ix ON ticket(description) INDEXTYPE IS CTXSYS.CONTEXT;
You search a CONTEXT index by using the CONTAINS clause, as in this example:

select * from ticket
where contains(description,'sTuCk') > 0;

The results of the query are shown in Figure 38-9. Note that the search string can be any case;
by default, the search is case insensitive.

Figure 38-9. Results from an Oracle Text CONTEXT index search

CONTEXT text indexes are not automatically synchronized (unlike B-tree, bitmap, and other Oracle Text index types) when rows are changed, deleted, or added. You must use the procedure CTX_DDL.SYNC_INDEX to refresh the index at the desired interval. The reason for using this index type on static tables is because the refresh operation on a CONTEXT index can be significantly higher than creating a new B-tree or bitmap index, and of course becomes inconsistent the moment you perform any DML statements on the table. In this example, you manually refresh the index TICKET_DESC_IX using the following anonymous PL/SQL block:

BEGIN CTX_DDL.SYNC_INDEX('ticket_desc_ix');
END;

You can accomplish many of the previous tasks using the Oracle Database XE GUI. First, drop the CONTEXT index with this SQL statement, and we will recreate it later:

DROP INDEX ticket_desc_ix;

Next, start from the Object Browser page shown earlier in Figure 38-2, click the Create button, and select Index. Enter TICKET for the Table Name and select the Text radio button, as shown in Figure 38-10.

Figure 38-10. Specifying the table name for a Text index in the Object Browser

After you click the Next button, you are on the page shown in Figure 38-11 and you specify the single text column you wish to index as well as the option to change the default index name, TICKET_CTX1.
After you click the Next button, you see a confirmation page. Click Finish to create the index.
The new index appears in the list on the Object Browser page shown in Figure 38-12.

■Tip On every confirmation page, you can click the SQL button to see the SQL statements that the GUI application
will use to create the object.

Figure 38-11. Specifying the index name and indexed column for a CONTEXT index

Figure 38-12. Using the Object Browser to view the definition of the CONTEXT index

Summary

This chapter continued the discussion of indexes from Chapter 30, explaining how to optimize the use of indexes by creating the right index for the job, as well as how to decide whether you need to create a specific index at all. We also showed you how to most effectively use the Oracle Database XE GUI to view and maintain indexes in your database. Finally, we showed you how to use Oracle Text to easily and efficiently find a string in almost any type of unstructured data in your database.
In the next chapter we’ll break free of the confines of your database and show you how to get data both in and out of your database: importing from spreadsheets, exporting to a text file, or exchanging data with other instances of Oracle Database XE.