Provide some examples of the different types of indexes available in Oracle. Also, what are some of the best practices with indexes in Oracle?
Other than the normal indexing options that are available, Oracle has some other indexing options available that we will discuss here. Let’s go through those options with some examples here.
Bitmap index example in Oracle
Oracle has what are called bitmap indexes, which are meant to be used on lowcardinality columns. A low cardinality column just means that the column has relatively few unique values. For example, a column called Sex which has only “Male” and “Female” as the two possible values is considered low cardinality because there are only two unique values in the column.
A bitmap index will create separate structures for each unique value of the column(s) – so in our example, there will be one structure for “Male” and another for “Female”. And each structure will contain the same number of rows as the table. For each row inside each of those structures there will be a binary bit of 0 or 1. A 0 means that in that row corresponding to the table the structure value is not present, but a 1 means that it is present. That is why it’s called a bitmap index.
When dealing with bitmap indexes, the RDBMS will actually use matrix algebra to find the rows that are being looked up.
Syntax for creating a bit map index in Oracle
Here’s an example of what the syntax would look like to create a bit map index in Oracle – note the use of the keyword BITMAP in the syntax below:
CREATE BITMAP INDEX IX_PEOPLE_NAME ON PEOPLE (NAME);
Function based indexes in Oracle
Function based indexes are another indexing option available in Oracle.
Suppose we include a function in a SQL predicate. For our example, let’s use the LOWER function, which converts text to lower case. So, let’s say we have some SQL that looks like this – where NAME is a column in a table that is called PEOPLE:
WHERE LOWER(NAME) = 'joe smith';
Usually having a predicate that uses a function would mean that an index can not be used on that query. But, Oracle actually lets you create an index on a function. This means that you can actually create an index on the “LOWER(NAME)” function call, and then you can use that index for any query that uses the call to the function “LOWER(NAME)”.
Before you create a function based index, you should know that there are some prerequisites – so be sure to consult the Oracle documentation on this matter.
Syntax for creating a function based index in Oracle
When creating a function based index, you just specify the function call where you would normally specify the column name. So, here’s an example of what creating a function based index would look like in Oracle – assuming that the table name is PEOPLE, and the function is LOWER(NAME):
CREATE INDEX IX_NAME_LOWER ON PEOPLE (LOWER(NAME));
Index Organized Tables in Oracle
Creating an index on the primary key of a table is generally considered a good idea. But some tables have very few columns, and if that is true, then what typically happens is that almost all of the data that’s in the table is duplicated in the index. Remember that an index will also store the column data for any columns on which the index is defined. So, for tables with very few columns, creating a normal index could be redundant.
So, Oracle actually has a solution to this problem, and let’s you store the entire table inside the index. This solution is known as an Index Organized Table, or IOT for short. So, an IOT is actually a table and a primary key index in one data structure.
Index Organized Tables versus Normal Tables
Normal, non-IOT tables, actually store the table rows in an unsorted order. But IOT’s on the other hand store table rows in a B-tree index structure, which is then sorted by the primary key of the table. Because an IOT is still a table, you can actually create more indexes on the IOT, but doing that may not make sense depending on your data situation.
Syntax for creating an index organized table in Oracle
Here’s what the syntax to create an IOT would look like in Oracle – assuming we are creating a table called People :
CREATE TABLE PEOPLE ( NAME VARCHAR(50), ADDRESS VARCHAR(70), CONSTRAINT PK_NAME PRIMARY KEY(NAME)) ORGANIZATION INDEX;