Re: Best Practices Question

Patrick

You could revert to a pre-8i trick of getting around the lack of function-based indexes which was to have an additional column in your UNIX Oracle table storing the first 11 characters of the account number. Then index it. This way it doesn't interfere with the index on the 12 character account number column that your loads and MIS queries are currently based on.

As far as populating this column goes, you have three choices:-

1. populate this additional column as part of your standard update/insert batch procedures;
2. create a trigger to populate this column on an AFTER INSERT/UPDATE basis; or
3. have a single piece of SQL to kick in after your batch run to run a full update of all NULL values in the additional column (unless of course you have something like transferred account numbers where you might have account numbers changing so you would need a full refresh of this column each time).

If your testing and release procedures in your place are erroneous, I would probably opt for method 3 in a straight .sql script. One transaction, easy testing and release. I would preferably go for method 1 for consistency of approach etc.

Whichever way you do it, you have an indexed equality predicate...

Of course, the UNIX "DBA" will have to give a little and add the column. With all the troubles going on in the world, another column on a table is really not a big deal is it? - he will still have food and water tonight in his nice warm house...

Hope this helps.
Regards
Adrian Received on Wed Feb 13 2002 - 15:52:32 CST