Creating Indexes- Tables and Constraints
As described previously, when you think about creating tables, you must think about the corresponding index architecture. Creating the appropriate indexes and using the correct index features will usually result in dramatic performance improvements. Conversely, creating indexes on the wrong columns or using features in the wrong situations can cause dramatic performance degradation.
Having said that, after giving some thought to what kind of index you need, the next logical step is to create the index. Creating indexes and implementing specific features are discussed in the next several sections.
Creating B-tree Indexes
The default index type in Oracle is a B-tree index. To create a B-tree index on an existing table, use the CREATE INDEX statement. This example creates an index on the CUST table, specifying LAST_NAME as the column:
SQL> CREATE INDEX cust_idx1 on cust(last_name);
By default, Oracle will create an index in your default permanent tablespace. Sometimes, that may be the desired behavior. But we have already discussed some reasons for having indexes in a specific tablespace:
SQL> CREATE INDEX cust_idx1 on cust(last_name) TABLESPACE reporting_index;
Because B-tree indexes are the default type and are used extensively with Oracle applications, it is worth taking some time to explain how this particular type of index works. A good way to understand the workings of an index is to show its conceptual structure, along with its relationship with a table (an index cannot exist without a table).
Take a look at Figure 8-1; the top section illustrates the CUST table, with some data. The table data are stored in two separate data files, and each data file contains two blocks. The bottom part of the diagram shows a balanced, treelike structure of a B-tree index named CUST_IDX1, created on a LAST_NAME of the CUST table. The index is stored in one data file and consists of four blocks.
Figure 8–1. Oracle B-tree hierarchical index structure and associated table
The index definition is associated with a table and column(s). The index structure stores a mapping of the table’s ROWID and the column data on which the index is built. A ROWID usually uniquely identifies a row within a database and contains information to physically locate a row (data file, block, and row position within block). The two dotted lines in Figure 8-1 depict how the ROWID (in the index structure) points to the physical row in the table for the column values of ACER.
The B-tree index has a hierarchical tree structure. When Oracle accesses the index, it starts with the top node, called the root (or header) block. Oracle uses this block to determine which second-level block (also called a branch block) to read next.
The second-level block points to several third-level blocks (leaf nodes), which contain a ROWID and the name value. In this structure, it will take three I/O operations to find the ROWID. Once the ROWID is determined, Oracle will use it to read the table block that contains the ROWID.
A couple of examples will help illustrate how an index works. Consider this query:
SQL> select last_name from cust where last_name = ‘ACER’;
Oracle accesses the index, first reading the root, block 20; then, it determines that the branch block 30 needs to be read; and, finally, it reads the index values from the lead node block 39. Conceptually, that would be three I/O operations. In this case, Oracle does not need to read the table because the index contains sufficient information to satisfy the results of the query. You can verify the access path of a query by using the autotrace utility; for example:
SQL> set autotrace trace explain;
SQL> select last_name from cust where last_name = ‘ACER’;
Note that only the index was accessed (and not the table) to return the data:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0) | 00:00:01 | |* 1 | INDEX RANGE SCAN | CUST_IDX1| 1 | 6 | 1 (0) | 00:00:01 | —————————————————————————
Also consider this query:
SQL> select first_name, last_name from cust where last_name = ‘ACER’;
Here, Oracle would follow the same index access path by reading blocks 20, 30, and 39. However, because the index structure does not contain the FIRST_NAME value, Oracle must also use the ROWID to read the appropriate rows in the CUST table (blocks 11 and 2500). Here is a snippet of the output from autotrace, indicating that the table has also been accessed:
—————————————————————————| Id | Operation | Name | Rows | Bytes |Cost
—————————————————————————| 0 | SELECT STATEMENT | | 1 | 44 | 2
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST | 1 | 44 | 2| * 2 | INDEX RANGE SCAN | CUST_IDX1 | 1 | | 1
—————————————————————————
Also note at the bottom of Figure 8-1 the bidirectional arrows between the leaf nodes. This illustrates that the leaf nodes are connected via a doubly linked list, thus making index range scans possible. For instance, suppose you have this query:
SQL> select last_name from cust where last_name >= ‘A’ and last_ name <= ‘J’;
To determine where to start the range scan, Oracle would read the root, block 20; then, the branch block 30; and, finally, the leaf node block 39. Because the leaf node blocks are linked, Oracle can navigate forward as needed to find all required blocks (and does not have to navigate up and down through branch blocks). This is a very efficient traversal mechanism for range scans.