which kind of index should I use? [message #9582] |
Fri, 21 November 2003 08:32 |
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 |
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 |
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.
|
|
|