Home » SQL & PL/SQL » SQL & PL/SQL » which kind of index should I use?
which kind of index should I use? [message #9582] Fri, 21 November 2003 08:32 Go to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
I have a table with six columns. one of the colums is called status, which will only have a value of 0 or 1.
Normally you would want to use a bitmap index for this, but I have seen many sites that say not to use bitmaps if the table will have a lot of inserts, updates, and deletes, which will be the case for this table.
Any suggestions?

Thanks in advance.
Paul
Re: which kind of index should I use? [message #9584 is a reply to message #9582] Fri, 21 November 2003 10:10 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Thats true. If you have lots of inserts,updates,deletes on a table,than bitmap index may not be a good choice inspite of the very low cardinality.

If its a DW system you will be dealing with bulk loads and reporting queries and the bitmap indexes are perfectly suitable here.

If its an OLTP system, querying based on status column ( 0 or 1 ) may not be a good idea.

-Thiru
Re: which kind of index should I use? [message #9586 is a reply to message #9584] Fri, 21 November 2003 11:14 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
Oracle needs to lock part of the bitmap, together with the affected rows, in order to update it, which is why they are often better suited to a batch load/reporting environment.

If your queries are of the form
"WHERE somekey = :x AND status = :y"
then it might be worth trying a compressed index on (status,somekey), i.e. with the low-cardinality column in the leading position so that it can take advantage of index key compression.

Depending on your application you might also be able to turn "status" into an optional column where only the status(es) you are interested in are populated. If you are generally only interested in the 10% of records with status "X", the index could be a made lot more efficient by leaving other statuses null. Again this will depend on the values, distribution, queries, business rules etc etc.
Previous Topic: how to change prompt as login name
Next Topic: storage structures in Oracle 9i
Goto Forum:
  


Current Time: Thu Mar 28 06:31:27 CDT 2024