Database Reference
In-Depth Information
Execution Plan
----------------------------------------------------------
Plan hash value: 4261295901
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)...
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 849K | 6139 (1)...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10000 | 849K | 6139 (1)...
| 2 | BITMAP CONVERSION TO ROWIDS | | | | ...
|* 3 | BITMAP INDEX SINGLE VALUE | EMP_BM_IDX | | | ...
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."SYS_NC00009$"='SALES')
Bitmap join indexes do have a prerequisite. The join condition must join to a primary or unique key in the other
table. In the preceding example,
DEPT.DEPTNO
is the primary key of the
DEPT
table, and the primary key must be in
place, otherwise an error will occur:
EODA@ORA12CR1> create bitmap index emp_bm_idx
2 on emp( d.dname )
3 from emp e, dept d
4 where e.deptno = d.deptno
5 /
from emp e, dept d
*
ERROR at line 3:
ORA-25954: missing primary key or unique constraint on dimension
Bitmap Indexes Wrap-up
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 were added to Oracle 8.1.5. They are now a feature of Standard Edition, whereas in releases
prior to Oracle9
i
Release 2 they were a feature of Enterprise Edition.
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.
