Pro Oracle Database 23c Administration by 2024

Author:2024
Language: eng
Format: epub

Chapter 8 IndexeS

The SET LONG command in this example tells SQL*Plus to display up to 500

characters from the COLUMN_EXPRESSION column, which is of type LONG.

Creating Unique Indexes

When you create a B-tree index, you can also specify that the index be unique. Doing so ensures that non-NULL values are unique when you insert or update columns in a table.

Suppose you have identified a column (or combination of columns) in the table (outside the primary key) that is used heavily in the WHERE clause. In addition, this column (or combination of columns) has the requirement that it be unique within a table. This is a good scenario in which to use a unique index. Use the UNIQUE clause to create a unique index:

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

The unique index does not enforce uniqueness for NULL values inserted into the table. In other words, you can insert the value NULL into the indexed columns for multiple rows.

You must be aware of some interesting nuances regarding unique indexes, primary key constraints, and unique key constraints (see Chapter 7 for a detailed discussion of primary key constraints and unique key constraints). When you create a primary key constraint or a unique key constraint, Oracle automatically creates a unique index and a corresponding constraint that is visible in DBA/ALL/USER_CONSTRAINTS.

When you create a unique index explicitly (as in the example in this section), Oracle creates a unique index but does not add an entry for a constraint in DBA/ALL/USER_

CONSTRAINTS. Why does this matter? Consider this scenario:

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

SQL> insert into cust values(500,'JOHN','DEERE'),

(501,'JOHN','DEERE');

Here is the corresponding error message that is thrown:

ERROR at line 1:

ORA-00001: unique constraint (MV_MAINT.CUST_UK1) violated

248

Chapter 8 IndexeS

If you are asked to troubleshoot this issue, the first place you look is in DBA_

CONSTRAINTS for a constraint named CUST_IDX1. However, there is no information: SQL> select constraint_name

from dba_constraints where constraint_name='CUST_UK1';

Here is the output:

no rows selected

The no rows selected message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data dictionary views. In this situation, you have to look at DBA_INDEXES and DBA_IND_COLUMNS to view the details of the unique index that has been created:

SQL> select a.owner, a.index_name, a.uniqueness, b.column_name

from dba_indexes a, dba_ind_columns b

where a.index_name='CUST_UK1'

and a.table_owner = b.table_owner

and a.index_name = b.index_name;

If you want to have information related to the constraint in the DBA/ALL/USER_

CONSTRAINTS views, you can explicitly associate a constraint after the index has been created:

SQL> alter table cust add constraint cust_idx1 unique(first_name,

last_name);

In this situation, you can enable and disable the constraint independent of the index.

However, because the index was created as unique, the index still enforces uniqueness regardless of whether the constraint has been disabled.

When should you explicitly create a unique index versus creating a constraint and having Oracle automatically create the index? There are no hard-and-fast rules. I prefer creating a unique key constraint and letting Oracle automatically create the unique index, because then I get information in both the DBA/ALL/USER_CONSTRAINTS and DBA/

ALL/USER_INDEXES views.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.