5.1 How do I…Determine all of the indexes on a particular table?Problem
The most commonly used method to improve data queries is the creation of indexes. Many times, it is important to know how a table has been indexed. How do I determine all of the indexes on a particular table?
Technique
The DBA_INDEXES data dictionary view contains information on each index within the database. By querying this view, you can determine which indexes exist on which tables. In this How-To, a sample table and indexes will be created. Then, the process of determining the indexes on the table will be shown.
Steps
1. For regular indexes to exist, you must first create a table. Connect to SQL*Plus as the WAITE account and run CHP5_1.SQL, which will create a sample table used throughout this How-To.
SQL> GET CHP5_1.sql
1 CREATE TABLE EMPLOYEE05
2 (EMPLOYEE_NAME VARCHAR2(30) NOT NULL,
3 SSN NUMBER,
4 SALARY NUMBER,
5 STATE VARCHAR2(2),
6 JOB_DESCRIPTION VARCHAR2(100),
7 DATE_EMPLOYED DATE)
8 TABLESPACE USER_DATA PCTFREE 10, PCTUSED 90
9 STORAGE ( INITIAL 1M NEXT 500K PCTINCREASE 50
10 MINEXTENTS 1 MAXEXTENTS 100);
SQL> /
Table Created.
2. Now that you have created the EMPLOYEE05 table, you are ready to create some sample indexes. A discussion of sizing indexes is discussed in How-To 5.3. CHP5_2.SQL contains a CREATE INDEX command that will create an index on the SSN column of the EMPLOYEE05 table. The SSN column was chosen for an index because employees are selected by their SSN in this example, and an index will speed up such queries. Run CHP5_2.SQL to create the SSN_INDEX index.
SQL> GET CHP5_2
1 CREATE INDEX SSN_INDEX
2 ON EMPLOYEE05 (SSN)
3 TABLESPACE USER_DATA
SQL> /
Index Created.
3. At this point, the EMPLOYEE05 table has been created with an index on the SSN column. CHP5_3.SQL, shown in Figure 5.1, contains a script that will list all indexes on a table.
4. When the script is run, the user will be prompted for an owner and table name. Enter WAITE for the owner, and EMPLOYEE05 for the table. This is shown in Figure 5.2. The script can be run for any owner and any table, making it a simple, yet valuable, tool for a developer or database administrator.
How It Works
The DBA_INDEXES data dictionary view contains information on each index in the database. Step 1 runs the CHP5_1.SQL to create the EMPLOYEE05 sample table. Step 2 runs the CHP5_2.SQL to create an index on the EMPLOYEE05 table. Step 3 queries the DBA_INDEXES data dictionary view for the WAITE owner and the EMPLOYEE05 table. The result is a list of all indexes on the EMPLOYEE05 table.
Comments
Indexes may improve the performance of SELECT statements. They will slow down inserts, updates, and deletes, as any of these manipulations on the table will also be performed on the index. Five thousand record inserts on a table, for instance, will also result in 5,000 index inserts, slowing performance. Be sure to understand what the index will be used for, and determine if the trade-off is worthwhile.
The impact of an index on a query will depend on the cardinality of the columns that the index is comprised of. For instance, a column that only contains “True” or “False” as values (low cardinality) will be a poor choice for a regular index. A column with many possible values, such as Social Security numbers (high cardinality), will be a good choice for a regular index. How-To 5.8 explains how to create a bitmapped index, which is better suited for low cardinality columns.