Re: Optimize Select on update column

Tony,

There will be thousands of records where status = 'N'. Indexing on update column will give bad performance. If there are gig on the non_unique_key_code column, would you think that it is a good idea to have it indexed on the two columns?

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0402120430.7dc4ca31_at_posting.google.com>...
> Eye-Gee.Kua_at_shell.com (egkua) wrote in message news:<46a2c097.0402112016.21e7c031_at_posting.google.com>...
> > 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;
>
> If the number of records where status='N' is relatively small,
> indexing on (non_unique_key_code, status) may help. Or to get really
> funky, an index on DECODE(status, 'N', non_unique_key) and then change
> the cursor to:
>
> CURSOR c1 IS SELECT rowid, col1
> FROM Table1
> WHERE DECODE(status, 'N', non_unique_key) = <key>;
Received on Thu Feb 12 2004 - 19:15:22 CST