Cluster,IOT,Bit Map Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Cluster,IOT,Bit Map Index

  1. #1
    Join Date
    Nov 2000
    Posts
    57
    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

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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.

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    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 .
    Radhakrishnan.M

  4. #4
    Join Date
    Nov 2000
    Posts
    57

    Cluster,IOT,Bit Map Index

    Can anyone explore any other possibilities....?

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width