Re: Questions on Index

Thank you and others who advised. The "problem" with this denormalization (is this the correct word?) for me is, I *am* doing denormalization; I run a script at midnight to insert some values from one table into another. The new table is used later during the day. My script takes about 4 hours to finish because it has "WHERE upper(replace(replace(name,'-'),' '))=..." type of constructs in SELECT statements. The input table, sent to us 2 or 3 times a week, contains a lot of entries of inconsistent usage of '-', ' ' etc. due to manual insertion. I use a Perl script to do pattern matching to find the right rows to be inserted into the new table.

It seems there's no better way. I'll live with it.

Yong Huang
Email:yong_at_shell.com

Stewart Burnett wrote:

> If you want to improve the performance of you select this;
>
> 1. Create another column called say upper_mycol
> 2. UPDATE mytable SET upper_mycol = UPPER(mycol)
> 3. Create a trigger to set upper_mycol on Inserts and Updates
> 4. Create an index on upper_mycol
> 5. Use upper_mycol instead of UPPER(mycol) in your select!
Received on Tue Oct 27 1998 - 10:18:25 CST