Multiple Indexes on the Same Column Combinations-Indexes

Prior to Oracle Database 12c, you could not have multiple indexes defined on one table with the exact same combination of columns. For example:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x int);Table created.
SQL> create index ti on t(x);Index created.
SQL> create bitmap index tb on t(x) invisible;

Starting with 12c, 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 12c database:
SQL> create table t(x int);Table created.
SQL> create index ti on t(x);Index created
SQL> 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 BTree 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 BTree indexes and then alter the bitmap indexes to be visible.

Indexing Extended Columns

With the advent of Oracle 12c, the VARCHAR2, NVARCHAR2, and RAW datatypes can now be configured to store up to 32,767 bytes of information (previously, the limit was 4000 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:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x varchar2(32767)); Table created.

Note If you attempt to create a table with a VARCHAR2 column greater than 4000 bytes in a database that hasn’t been configured for extended datatypes, Oracle will throw an ORA-00910: specified length too long for its datatype message.

Next, we attempt to create an index on the extended column:
SQL> create index ti on t(x);create index ti on t(x)

We’ve seen this error previously in this chapter. An error is thrown because Oracle imposes a maximum length on the index key, which is about three-fourths of the block size (the block size for the database in this example is 8K). Even though there aren’t any entries in this index yet, Oracle knows that it’s possible that index key could be larger than 6398 bytes for a column that can contain up to 32,767 bytes and therefore won’t allow you to create an index in this scenario.

That doesn’t mean you can’t index extended columns; rather, you have to use techniques that limit the length of the index key to less than 6398 bytes. With that in mind, a few options become apparent:
•\ Create a virtual column based on SUBSTR or STANDARD_HASH unctions, and then create an index on the virtual column.
•\ Create a function-based index using SUBSTR or STANDARD_HASHfunctions.
•\ Create a tablespace based on a larger block size; for example, a 16K block size would allow for index keys the size of approximately 12,000 bytes. Having said that, if you need 12,000 bytes for an index key, then you’re probably doing something wrong and need to rethink what you’re doing. This method will not be explored.

Let’s start by looking at the virtual column solution.