Indexes - Expert Oracle Database Architecture - page 485

Multiple Indexes on the Same Column Combinations
Prior to Oracle Database 12 c , you could not have multiple indexes defined on one table with the exact same
combination of columns. For example:
EODA@ORA11GR2> create table t(x int);
Table created.
EODA@ORA11GR2> create index ti on t(x);
Index created.
EODA@ORA11GR2> create bitmap index tb on t(x) invisible;
ERROR at line 1:
ORA-01408: such column list already indexed
Starting with 12 c , you can define multiple indexes on the same set of columns. However, you can only do this if
the indexes are physically different; for example, when one index is created as a B*Tree index, and the second index as
a bitmap index. Also, there can be only one visible index for the same combination of columns on a table. Therefore,
running the prior CREATE INDEX statements works in an Oracle 12 c database:
EODA@ORA12CR1> create table t(x int);
Table created.
EODA@ORA12CR1> create index ti on t(x);
Index created
EODA@ORA12CR1> create bitmap index tb on t(x) invisible;
Index created
Why would you want two indexes defined on the same set of columns? Say you had originally built a data
warehouse star schema with all B*Tree indexes on the fact table foreign key columns, and later discover through
testing that bitmap indexes will perform better for the types of queries applied to the star schema. Therefore, you want
to convert to bitmap indexes as seamlessly as possible. So you first build the bitmap indexes as invisible. Then when
you're ready, you can drop the B*Tree indexes and then alter the bitmap indexes to be visible.
Indexing Extended Columns
With the advent of Oracle 12 c , the VARCHAR2 , NVARCHAR2 , and RAW datatypes can now be configured to store up to
32,767 bytes of information (previously, the limit was 4,000 bytes for VARCHAR2 and NVARCHAR2 , and 2000 bytes for RAW ).
Since Chapter 12 contains the details for enabling a database with extended datatypes, I won't repeat that information
here. The focus of this section is to explore indexing extended columns.
Let's start by creating a table with an extended column and then try to create a regular B*Tree index on that
column:
EODA@O12CE> create table t(x varchar2(32767));
Table created.