Bitmap index design question

From: <luke_airig_at_hotmail.com>
Date: 31 Oct 2005 11:36:10 -0800
Message-ID: <1130787370.036398.178900@g44g2000cwa.googlegroups.com>

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production, resident on UNIX SunOS 5.8

I can use some help on whether or not to utilize a bitmap index on a new table that is being created.

Here is the table create script:

create table test.recording_info_xmit_status

  ( loan_id                        varchar2 ( 20 ) not null


  , doc_type                       number   ( 22 ) not null

  , ri_status_flag                 varchar2 (  1 )

  , ri_status_flag_updt_dt         date

  )
;

alter table test.recording_info_xmit_status   add constraint pk_recording_info_xmit_status   primary key ( loan_id , doc_type )
  using index
  tablespace testindx
;

Here is a summary of the application processing that we are trying to address:

1. This new table will be inserted to and updated via a .net application screen one loan at a time, approximately 2000 times a day. On very rare occasions a row may be deleted via the application. ri_status_flag can have 4 values: null, 'A' (added), 'C' (changed) and 'S' (sent).
2. Once a day a batch background job will identify all rows that have been inserted or updated since the last batch run, write a record to a flat file and update the ri_status_flag to indicate that the row has been extracted. My volume tests show the job will run in about 15 minutes with no index on ri_status_flag (full table scan). I realize the batch job run time will take longer over time as the table grows.

I want to minimize the introduction of any new performance issues with the .net application so I am leery of creating a bitmap index on ri_status_flag with the attendant index overhead. The Oracle book that I am referencing indicates bitmap indexes should only be used if the data is infrequently updated. I am interpreting 2000 rows/day as frequent.

Can anyone give me some idea if this is a reasonable design/approach? Is there anything else that I should be considering?. Any suggestions on how to mitigate the application performance considerations with the ever-growing table and longer batch run time?

TIA for any help on this.

Luke Received on Mon Oct 31 2005 - 13:36:10 CST