Re: Bitmap index design question

On 31 Oct 2005 11:36:10 -0800, luke_airig_at_hotmail.com wrote:

>
>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

The question you have to ask first is how skewed the status column is. Some statuses may occur so frequently that the optimizer will decide in favor of a full table scan anyway. This already happens at 10-15 percent.
The second thing you have to realize is an ordinary index will also come into play when your data is skewed. As an alternative, Tom Kyte has demonstrated at http://asktom.oracle.com that if your criteria boil down to true and null, a function based index will do the tric, decoding all the codes you don't need to null (so they won't be in the index). The biggest disadvantage of a bitmap index is that it locks more records.

As to your update frequency, I wouldn't classify it as frequent. You have 2000 'inserts and updates' per day, which means less than 10 'inserts and updates' per hour. What could be a problem, is that a colum changes often. In your case however, it probably changes 2 or 3 times, and then doesn't change anymore for ever.

--
Sybrand Bakker, Senior Oracle DBA