Re: Optimize Select on update column

egkua,
If you have any processing in between you would not be able to use forall. But, to get the rows to be processed bulk collect would be a good option... Also, for updating the rows a simple
UPDATE Table1
SET status = 'Y'
WHERE status = 'N'
AND non_unique_key_code = <key>;
would be good enough....

Cheers,
Kal

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076561958.805219_at_yasure>...
> 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
Received on Thu Feb 12 2004 - 05:55:37 CST