Viewing Index Metadata- Tables and Constraints

Oracle provides two types of views containing details about the structure of the indexes:

•     INDEX_STATS

•     DBA/ALL/USER_INDEXES

The INDEX_STATS view contains information regarding the HEIGHT (number of blocks from root to leaf blocks), LF_ROWS (number of index entries), and so on. The INDEX_STATS view is populated only after you analyze the structure of the index; for example,

SQL> analyze index cust_idx1 validate structure;

The DBA/ALL/USER_INDEXES views contain statistics, such as BLEVEL (number of blocks from root to branch blocks; this equals HEIGHT – 1); LEAF_BLOCKS (number of leaf blocks); and so on. The DBA/ALL/USER_INDEXES views are populated automatically when the index is created and refreshed via the DBMS_STATS package.

Creating Concatenated Indexes

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as concatenated indexes. These indexes are especially effective when you often use multiple columns in the WHERE clause when accessing a table.

Suppose you have this scenario, in which two columns from the same table are used in the WHERE clause:

SQL> select first_name, last_name from cust

where first_name = ‘JIM’ and last_name = ‘STARK’;

Because both FIRST_NAME and LAST_NAME are often used in WHERE clauses for retrieving data, it may be efficient to create a concatenated index on the two columns:

SQL> create index cust_idx2 on cust(first_name, last_name);

Often, it is not clear whether a concatenated index is more efficient than a single-column index. For the previous SQL statement, you may wonder whether it is more efficient to create two single-column indexes on FIRST_NAME and LAST_NAME, suchas this:

SQL> create index cust_idx3 on cust(first_name); SQL> create index cust_idx4 on cust(last_name);

In this scenario, if you are consistently using the combination of columns that appear in the WHERE clause, then the optimizer will most likely use the concatenated index and not the single-column indexes. Using a concatenated index, in these situations, is usually much more efficient. You can verify that the optimizer chooses the concatenated index by generating an explain plan; for example:

SQL> set autotrace trace explain;

Then, run this query:

SQL> select first_name, last_name from cust

where first_name = ‘JIM’ and last_name = ‘STARK’;

Here is some sample output, indicating that the optimizer uses the concatenated index on CUST_IDX2 to retrieve data:

—————————————————————————| Id | Operation               | Name           | Rows | Bytes | Cost (%CPU)| Time —-| —————————————————————————| 0 | SELECT STATEMENT |                    | 1        | 44        | 1 (0)           | 00:00:01 | |* 1 | INDEX RANGE SCAN| CUST_IDX2 | 1                      | 44                                      | 1 (0)         | 00:00:01 | —————————————————————————

The optimizer can use a concatenated index even if the leading-edge column (or columns) is not present in the WHERE clause. This ability to use an index without reference to leading-edge columns is known as the skip-scan feature.

A concatenated index that is used for skip scanning can, in certain situations, be more efficient than a full-table scan. However, you should try to create concatenated indexes that use the leading column. If you are consistently using only a lagging-edge column of a concatenated index, then consider creating a single-column index on the lagging column.