Bitmap Indexes Wrap-Up-Indexes-1

When in doubt, try it out (in your non-OLTP system, of course). It is trivial to add a bitmap index to a table (or a bunch of them) and see what it does for you. Also, you can usually create bitmap indexes much faster than B*Tree indexes. Experimentation is the best way to see if they are suited for your environment. I am frequently asked, “What defines low cardinality?” There is no cut-and-dried answer for this. Sometimes, it is 3 values out of 100,000. Sometimes, it is 10,000 values out of 1,000,000. Low cardinality doesn’t imply single-digit counts of distinct values. Experimentation is the way to discover if a bitmap is a good idea for your application. In general, if you have a large, mostly read-only environment with lots of ad hoc queries, a set of bitmap indexes may be exactly what you need.

Function-Based Indexes

Function-based indexes give us the ability to index computed columns and use these indexes in a query. In a nutshell, this capability allows you to have case-insensitive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them.

There are many reasons why you would want to use a function-based index, with the following chief among them:
•\ They are easy to implement and provide immediate value.
•\ They can be used to speed up existing applications without changing any of their logic or queries.

The following subsections provide relevant cases of implementing function-based indexes.
A Simple Function-Based Index Example
Consider the following example. We want to perform a case-insensitive search on the ENAME column of the EMP table. Prior to function-based indexes, we would have approached this in a very different manner. We would have added an extra column to the EMP table called UPPER_ENAME, for example. This column would have been maintained by a database trigger on INSERT and UPDATE; that trigger would simply have set NEW.UPPER_ NAME := UPPER(:NEW.ENAME). This extra column would have been indexed. Now with function-based indexes, we remove the need for the extra column.

We begin by creating a copy of the demo EMP table in the SCOTT schema and adding some data to it:
$ sqlplus eoda/foo@PDB1
SQL> create table emp asselect *from scott.empwhere 1=0;Table created.

Next, we will create an index on the UPPER value of the ENAME column, effectively creating a case-insensitive index:
SQL> create index emp_upper_idx on emp(upper(ename)); Index created.

Finally, we’ll analyze the table since, as noted previously, we need to make use of the CBO to use function-based indexes. This step is technically unnecessary, as the CBO is used by default and dynamic sampling would gather the needed information, but gathering statistics is a more correct approach:
SQL> exec dbms_stats.gather_table_stats(user,’EMP’,cascade=>true); PL/SQL procedure successfully completed.

We now have an index on the UPPER value of a column. Any application that already issues case-insensitive queries like the following will make use of this index, gaining the performance boost an index can deliver:
SQL> set autotrace traceonly explain
SQL> select * from emp where upper(ename) = ‘KING’;

Before this feature was available, every row in the EMP table would have been scanned, uppercased, and compared. In contrast, with the index on UPPER(ENAME), the query takes the constant KING to the index, range scans a little data, and accesses the table by rowid to get the data. This is very fast.

This performance boost is most visible when indexing user-written functions on columns. For example, say we see something like this:
SQL> select my_function(ename)from empwhere some_other_function(empno) > 10;

This was great because we could now effectively extend the SQL language to include application-specific functions. Unfortunately, however, the performance of the preceding query was a bit disappointing at times. Say the EMP table had 1000 rows in it. The function SOME_OTHER_FUNCTION would be executed 1000 times during the query, once per row. In addition, assuming the function took one-hundredth of a second to execute, this relatively simple query now takes at least ten seconds.

Let’s look at a real example, where we’ll implement a modified SOUNDEX routine in PL/SQL. Additionally, we’ll use a package global variable as a counter in our procedure, which will allow us to execute queries that make use of the MY_SOUNDEX function and see exactly how many times it was called:
SQL> create or replace package stats ascnt number default 0;end;/

Notice in this function, we are using a keyword, DETERMINISTIC. This declares that the preceding function, when given the same inputs, will always return the exact same output. This is needed to create a function-based index on a user-written function. We must tell Oracle that the function is DETERMINISTIC and will return a consistent result given the same inputs. We are telling Oracle that this function should be trusted to return the same value, call after call, given the same inputs. If this were not the case, we would receive different answers when accessing the data via the index vs. a full table scan. This deterministic setting implies, for example, that we cannot create an index on the function DBMS_RANDOM.RANDOM, the random number generator. Its results are not deterministic; given the same inputs, we’ll get random output. The built-in SQL function UPPER used in the first example, on the other hand, is deterministic, so we can create an index on the UPPER value of a column.

Now that we have the function MY_SOUNDEX, let’s see how it performs without an index. This uses the EMP table we created earlier with about 10,000 rows in it:

SQL> set autotrace on explain

Predicate Information (identified by operation id):
1 – filter(“MY_SOUNDEX”(“ENAME”)=”MY_SOUNDEX”(‘Kings’))
SQL> set autotrace off
SQL> set serverout on
SQL> begindbms_output.put_line