Re: Optimize Select on update column

egkua wrote:

> I am selecting based on an update column (low cardinality column) and
> a non unique key column. Since the second select criteria is based on
> non unique key column, I reckon that the select would be slow when it
> has gig of records on the key column. I can't create a bitmap index as
> the 1st criteria since it is an update column.
>
> Anyone, any idea on how to make it faster when records grow to gig?
>
> I have the following processes:
>
> CURSOR c1 IS SELECT rowid, col1
> FROM Table1
> WHERE status = 'N'
> AND non_unique_key_code = <key>;
>
> LOOP
> processing...
>
> UPDATE Table1
> SET status = 'Y'
> WHERE rowid = c.rowid;
>
> END LOOP;
Exactly what does a column being updatable have to do with indexing?

Anyway ... the solution is to stop writing version 7 PL/SQL and use more efficient ways to accomplish your goals. In this case I would look at using bulk binding and FORALL.

For a demo:
http://www.psoug.org/reference/bulk_collect.html

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)