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 - 06:30:51 CST
