Best type of index to use...?

[slicendice]

Hi everyone

I have a situation where I'm processing the data in a large table using a PL/SQL routine. The processing has to happen in batches and when a record has been processed, a field in the table is updated to indicate this. The field in question (called PROCESSED) is a simple NUMBER(1) field, where 0 = not processed and 1 = processed. When the PL/SQL process is run, it selects records from the table that have not yet been processed, i.e. where PROCESSED = 0.

The table has around 16 million rows in it, and to start with it's fairly quick at selecting rows that haven't yet been processed. However, the more records that get processed, the longer it's taking to find records where PROCESSED = 0.

What I'm wondering is what type of index would be best on the PROCESSED field? I know bitmap indexes are good when there's a low cardinality, which there is in this case, but there are going to be a lot of updates, which I believe is not so good for bitmap indexes.

Any suggestions would be greatly appreciated

Thanks

 

[LKBrwnDBA]

BITMAP.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Beilstwh]

bitmap is not a good idea for heavily updated tables. Use a regular btreve index.

Bill
Lead Application Developer
New York State, USA

 

[taupirho]

Can I just check you are using FORALL/BULK operations in your PL/SQL

In order to understand recursion, you must first understand recursion.