Indexes - Pro Oracle Database 12c Administration - page 219

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:
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 to create a unique key constraint and let Oracle
automatically create the unique index, because then I get information in both the DBA/ALL/USER_CONSTRAINTS and
DBA/ALL/USER_INDEXES views.
But, Oracle's documentation recommends that if you have a scenario in which you're strictly using a unique
constraint to improve query performance, it's preferable to create only the unique index. This is appropriate. If you take
this approach, just be aware that you may not find any information in the constraint-related data dictionary views.
Implementing Bitmap Indexes
Bitmap indexes are recommended for columns with a relatively low degree of distinct values (low cardinality). You
shouldn't use bitmap indexes in OLTP databases with high INSERT / UPDATE / DELETE activities, owing to locking issues;
the structure of the bitmap index results in many rows' potentially being locked during DML operations, which causes
locking problems for high-transaction OLTP systems.
Bitmap indexes are commonly used in data warehouse environments. A typical star schema structure consists of
a large fact table and many small dimension (lookup) tables. In these scenarios it's common to create bitmap indexes
on fact table foreign key columns. The fact tables are typically inserted into on a daily basis and usually aren't updated
or deleted from.
Listed next is a simple example that demonstrates the creation and structure of a bitmap index. First, create a
LOCATIONS table:
create table locations(
location_id number
,region varchar2(10));
Now, insert the following rows into the table:
insert into locations values(1,'NORTH');
insert into locations values(2,'EAST');
insert into locations values(3,'NORTH');
insert into locations values(4,'WEST');
insert into locations values(5,'EAST');
insert into locations values(6,'NORTH');
insert into locations values(7,'NORTH');