Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

Indexes

Two views return information about indexes. Each index gets an entry in the ALL_INDEXES view, and the ALL_IND_COLUMNS view returns the columns included in each index.

Listing Indexes for a Table

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. There are two approaches you can take. One approach is to write a script that describes one particular index, while the other is to write a script that lists all indexes on a table.

The tradeoff between these two approaches is that one lets you look at indexes by name, while the other lets you look at indexes by table. The problem is that you probably need both ways. Sometimes, as when a unique index is violated, you do need to look up an index by name. Other times, you just want to see what indexes have been defined on a particular table.

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 (JEFF.UNIQUE_BILLING_RATE) violated

The reason for this is no doubt because Oracle enforces both unique and primary key constraints by creating indexes on the constrained fields. If you do get the error message just shown, there are two ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata