| |||
| |||
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.
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 abitmap index.
When dealing with bitmap indexes, the RDBMS will actually use matrix algebra to find the rows that are being looked up.
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_NAMEON PEOPLE (NAME);
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 indexon 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.
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_LOWERON PEOPLE (LOWER(NAME));
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 atable and a primary key index in one data structure.
Normal, non-IOT tables, actually store the table rows in anunsorted order. But IOT’s on the other hand store table rows in a B-tree index structure, which is thensorted 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.
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;
Would you like to thankProgrammerInterview.com for being a helpful free resource?Then why not tell a friend about us, orsimply add a link to this page from your webpage using the HTML below.
Link to this page:
Please bookmark with social media, your votes are noticed and appreciated:
