Edwin A. Epstein, III wrote:

> I have a table with 14 million records in it. As part of certain operations
> I need to do against the records I flag them individually as part of a que.
> I am using a field called DNC_QUED which is a VarChar(1) and ASCII character
> set. I have non-unique index on just the field alone. There are no null
> values and the only values are '1' or '0'.
>
> When I pull a SELECT FIELD1 FROM TABLE1 WHERE DNC_QUED = '1' and attempt to
> fetch all records it takes a very long time (30 minutes plus). I checked
> the plan and it is using the index.
>
> Is there anything I can do to increase the performance of that select
> statement?

My guess:
You update a lot of records setting/removing a flag in DNC_QUED. When you
access the records this later on (with the SELECT) there will be some
garbage collecting of old record versions, and cleaning up in the index.
The "cleaning up" will take some time, and with very-low-selectivity
indexes it will be worse.
You might try to append something (e.g. the PK) to the index definition to
make it unique (or 'almost unique'). Hopefully, it should speed up the
cleaning part.


--
Aage J.