jump to navigation

Unique Bitmap Indexes Part II (You Can’t Do That) March 30, 2010

Posted by Richard Foote in Bitmap Indexes, Oracle Indexes, Unique Indexes.
trackback

As discussed in the previous post, a Bitmap index on a unique column will be larger than a corresponding Btree index due to the additional overheads associated with each index entry (such as the additional rowid, the additional column length bytes and the bitmap column itself). Oracle therefore attempts to protect you from explicitly creating such a “Unique Bitmap” index. 

For example, you can not specify both UNIQUE and BITMAP when creating an index. To do so would make little sense.
  
A bitmap index must therefore be Non-Unique by definition. Any attempt to explicitly create a Unique Bitmap index will fail.
 
SQL> drop index bowie_bitmap_i;
 
Index dropped.
 
SQL> create unique bitmap index bowie_bitmap_i on bowie(id) pctfree 0;
create unique bitmap index bowie_bitmap_i on bowie(id) pctfree 0
              *
ERROR at line 1:
ORA-00968: missing INDEX keyword
 

SQL> create bitmap unique index bowie_bitmap_i on bowie(id) pctfree 0;
create bitmap unique index bowie_bitmap_i on bowie(id) pctfree 0
              *
ERROR at line 1:
ORA-00968: missing INDEX keyword
 

The CREATE INDEX syntax only caters for either the BITMAP or the UNIQUE option.
 
Although Oracle permits the use of a Non-Unique index to police either a Primary Key (PK) or Unique Key (UK) constraint, a bitmap index is not permitted to police such constraints. Again, it makes little sense having a bitmap index police such constraints as an equivalent Btree index is going to be more efficient.
 
If an existing bitmap index exists on a column, Oracle can not use it to police the constraint:
 
SQL> create bitmap index bowie_bitmap_i on bowie(id) pctfree 0;
 
Index created.
 
SQL> alter table bowie add primary key (id);
alter table bowie add primary key (id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
 

Oracle is attempting to create a Btree index to police the new PK constraint but it can’t create it as an existing bitmap index already exists. Oracle will not create a Btree index if the same column list is already indexed.
 
It makes no difference if we if declare the constraint as deferrable (or invalidate) where a Non-Unique index is required:
 

SQL> alter table bowie add primary key (id) novalidate;
alter table bowie add primary key (id) novalidate
*
ERROR at line 1:
ORA-01408: such column list already indexed
 

SQL> alter table bowie add primary key (id) deferrable;
alter table bowie add primary key (id) deferrable
*
ERROR at line 1:
ORA-01408: such column list already indexed
 

Attempting to create a Bitmap index at the same time as the constraint is equally fruitless:

SQL> alter table bowie add primary key (id) using index (create bitmap index bowie_bitmap_i on bowie(id));
alter table bowie add primary key (id) using index (create bitmap index bowie_bitmap_i on bowie(id))
                                                           *
ERROR at line 1:
ORA-00968: missing INDEX keyword
 

SQL> alter table bowie add primary key (id) using index (create bitmap index bowie_bitmap_i on bowie(id) deferrable);
alter table bowie add primary key (id) using index (create bitmap index bowie_bitmap_i on bowie(id) deferrable)
                                                           *
ERROR at line 1:
ORA-00968: missing INDEX keyword
 

So definitely, looking at creating a Bitmap index on a unique column is not a sensible thing to attempt both because the resultant bitmap index would be larger than a corresponding Btree index if permitted and because in many scenarios as discussed, Oracle simply won’t let you do it anyways.

OK, so a unique column is not suitable for a Bitmap index. The question remains at what point does it make sense to create a bitmap index ? The answer is reasonably obvious when one understands the structure of both types of indexes although the answer may surprise some folks. I’ll look at this question next …

About these ads

Comments»

1. Andy - April 1, 2010

Hi Richard,

ok, I am bored :p I saw this comment by someone on a DB2 blog. Guess what, I learned something new today! IOT = Index-only table :p It looks like IOT is closely tied to IFFS …. and something about ‘multiblock reading in linear order’. Very educational indeed.

http://www..com/concepts/index_only_tables.htm ( Note that I put in in the domain name)

*****************Quoted from the site **************************
……. Oracle recognized that by using a special table-access method called an index fast full scan, the index could be queried without actually touching the data itself.

Oracle codified this idea with its use of index-only table (IOT) structure. When using an IOT, Oracle does not create the actual table but instead keeps all of the required information inside the Oracle index.

At query time, the Oracle SQL optimizer recognizes that all of the values necessary to service the query exist within the index tree, at which time the Oracle cost-based optimizer has a choice of either reading through the index tree nodes to pull the information in sorted order or invoke an index fast full scan, which will read the table in the same fashion as a full table scan, using sequential prefetch (as defined by the db_file_multiblock_read_count parameter).

The multiblock read facility allows Oracle to very quickly scan index blocks in linear order, quickly reading every block within the index tablespace
***********************************************

2. Andy - April 1, 2010

Dang. The quotes I used around the domain name actually made the domain name disappear! Well, it is from that BC site, you know ;)

Richard Foote - April 1, 2010

Hi Andy

No worries, easily found the article:

http://www.dba-oracle.com/concepts/index_only_tables.htm

Oh boy, so much wrong in such a little article !!

I’m not sure what’s worse. Not knowing the correct name for these types of segments, the somewhat odd connection between IOT and IFFS, the incorrect description of a MBR reading every block within a tablespace, the fact that no, not all required information might be in the index, the ever present syntax errors with the example given:

CREATE TABLE emp_iot (
emp_id number,
ename varchar2(20),
sal number(9,2),
deptno number,
CONSTRAINT pk_emp_iot_index PRIMARY KEY (emp_id) )
ORGANIZATION index
TABLESPACE spc_demo_ts_01
PCTHRESHOLD 20 INCLUDING ename;

having at least two clear errors (it’s actually PCTTHRESHOLD and Oracle will complain about not having an OVERFLOW declared with the INCLUDING clause in this case). An example that clearly has never actually ever been run before.

But seriously, who’s at all surprised :(

Actually, you’ve reminded me that I ready should discuss IOT one of these days. They’re interesting, somewhat under-used segments that deserve better treatment than this.

3. Andy - April 2, 2010

Yes! Some IOT in your blog please! :)

I support a product that uses partitioned IOT heavily. There can be a few thousand such tables in a single database ( varies from site to site). You can say it is over-used in a very odd environment.

While I wait for your article, I shall go entertain myself with more educational materials out there …. :p

Richard Foote - April 13, 2010

Hi Andy

I’ll cover IOTs and secondary indexes on IOTs sometime “soon”.

4. So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2) « Richard Foote’s Oracle Blog - April 13, 2010

[...] in Bitmap Indexes, Non-Unique Indexes, Oracle Indexes, Oracle Myths. trackback As I’ve discussed previously, using a Bitmap index on a unique column makes little sense as the underling index must be larger [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,901 other followers

%d bloggers like this: