Cluster,IOT,Bit Map Index

[shrik_m]

Hi,

We are working in a retail environment. Most of the table in our database has Company code, Region Code, Branch Code as common columns. Inorder to improve performance, we have to create table with the specified columns by having a Cluster or Index Organized Table(IOT) or Bitmap Index. Also, when we create a Cluster, we cannot create a IOT or Bitmap Index and Vice versa.

The query which I would like to ask is which among the three(Cluster,IOT,Bitmap Index) will be faster to update and query for the tables.

Thanks in anticipation,
Srikanth

[pwoneill]

I suppose you're looking for real life experience, I don't have any yet and am curious as to what others think about the question, but I can point you to some docs to read in the meantime.

Check out [url]http://technet.oracle.com/doc/oracle8i_816/server.816/a76992/toc.htm[/url] , you'll need a free technet registration. Just look for the sections on Bitmap Indexes, Clusters, and IOTs, I didn't see one place comparing/contrasting the three, but you can read all the 'when to use' and 'when not to use' of the three individually.

[omegamark]

The order I think is Bitmap,IOT,cluster.

You create a bitmap index on a Normal table.We are using Bitmap indexes a lot i have not seen any decrease in performance while inserting or updating.

If you want to improve performance pls try partitions also.
You can go for range partitions from a-z,numbers,date also.

Normally in many cases i tried to avoid bitmap indexes and tried partitions on those columns you will not belive the performance gain what you get.

So in addition to all these psl consider partitions also .

[shrik_m]

Can anyone explore any other possibilities....?

[tamilselvan]

Cluster is not suitable for heavy updated tables.

If the column used in bit map index is updated frequently, then you will experience heavy lock on the table. This will hurt performance.

IOT is the best choice.

You can cache the small tables in buffer pool keep area to get good performance. Also think of partitioning the big table, Parallel Query Option, etc.

Every system is unique. After loading data, post your Query here. We may suggest to improve the query performance.