Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.5 Indexes

The
problem of listing indexes for a
table is much the same as that of
listing constraints on a table. You have a master-detail relationship
between the index and its columns, and you may have multiple indexes
on one table.

To list only the indexes on a table, query the
all_indexes view:

SELECT index_name, index_type, uniqueness
FROM all_indexes
WHERE owner = UPPER('&owner')
AND table_name = UPPER('&table_name');

Listing the indexes alone is seldom enough. You need to know at least
the columns involved in each index. To that end, join with
all_ind_columns. Example 10-8 shows the query.

Example 10-8. A script to list all indexes on a table

COLUMN index_name FORMAT A20
COLUMN index_type FORMAT A10
COLUMN UNIQUENESS FORMAT A10
COLUMN column_name FORMAT A15
SELECT ai.index_name, ai.index_type, ai.uniqueness, aic.column_name
FROM all_indexes ai INNER JOIN all_ind_columns aic
ON ai.owner = aic.index_owner
AND ai.index_name = aic.index_name
WHERE ai.owner = UPPER('&owner')
AND ai.table_name = UPPER('&table_name')
ORDER BY aic.column_position;

Here's a run of Example 10-8 showing
the two indexes on the employee table:

SQL> @ex10-8
Enter value for owner: gennick
old 5: WHERE ai.owner = UPPER('&owner')
new 5: WHERE ai.owner = UPPER('gennick')
Enter value for table_name: employee
old 6: AND ai.table_name = UPPER('&table_name')
new 6: AND ai.table_name = UPPER('employee')
INDEX_NAME INDEX_TYPE UNIQUENESS COLUMN_NAME
-------------------- ---------- ---------- ---------------
EMPLOYEE_PK NORMAL UNIQUE EMPLOYEE_ID
EMPLOYEE_BY_NAME NORMAL NONUNIQUE EMPLOYEE_NAME One thing to keep in mind when working with unique indexes is that
Oracle will report a unique index violation as if it were a
constraint violation. The error message you get is the same as the
one used when you violate a unique constraint, and it looks like
this:

ORA-00001: unique constraint (GENNICK.UNIQUE_BILLING_RATE) violated The reason for this is no doubt because Oracle enforces unique and
primary key constraints by creating indexes on the constrained
fields. If you do get the error message just shown, you might want to
check two things. First, list the constraints on the table you are
updating. Second, if you don't find one with a name
that matches the one in the error message, check to see whether there
happens to be a unique index with that same name.

Beginning with Oracle8 i Database, defining a unique or primary key constraint without having a corresponding index created is possible.