Functional Indexes?

Ullo chaps. Each table in our db has a set of columns at the end, two of which indicate record status - they hold an integer value and a char(20) code which are ALWAYS related, i.e. the ref and code are a pair. Crap design but unfortunately historical (hysterical?) and the coders being a lazy bunch tend to query on the status code (e.g. where status_code = "active") when they can't be bothered to remember that the related status_ref is 88, and so on. So far, so hoopy. As there are nearly 300 Million rows in the database that means about 3.5Gb of extraneous char(20)s that could be ditched. How much performance would I expect to lose by removing the status_codes and putting a functional index on the ref, with a stored procedure to convert the ref to the relevant status string? Ta Malc

Just a bit of outside-the-box suggestion here. How about rename the table, drop the 20 char code column, create a ref & code lookup table, and replace the original tablename with a view that maps in the code? Storage saved and performance should be fine. Art S. Kagel ----- Original Message ----- From: Malc <malc_p@BTINTERNET.COM> At: 5/16 16:34 > Ullo chaps. > Each table in our db has a set of columns at the end, two of which indicate > record status - they hold an integer value and a char(20) code which are > ALWAYS related, i.e. the ref and code are a pair. Crap design but > unfortunately historical (hysterical?) and the coders being a lazy bunch > tend to query on the status code (e.g. where status_code = "active") when > they can't be bothered to remember that the related status_ref is 88, and > so on. > So far, so hoopy. As there are nearly 300 Million rows in the database that > means about 3.5Gb of extraneous char(20)s that could be ditched. > How much performance would I expect to lose by removing the status_codes and > putting a functional index on the ref, with a stored procedure to convert > the ref to the relevant status string? > > Ta > > Malc