Thread: Creating a functional index on a cast?

I have two large tables with related data, one of which has a key that is
char(8), the other has the same key, but it is varchar(8).

The tables come from two different legacy environments which are being
merged, part of the work at this point is settling on a unified data
dictionary.  Side question:  This is an 8 character membership ID which
will always be eight characters long if defined but may occasionally be
null, does it make any significant difference in storage or performance
whether I use char(8) or varchar(8)?

However, before I can rebuild all the data tables using either char or
varchar uniformly, I needed to be able to update the table with the varchar
field from values in the other table, but this seems to take forever, even
though both tables are indexed on the key field.

It doesn't appear that I can cast one of the keys to the other format and
have it work efficiently unless I can also cast a functional index.  But
I get errors when I try to create an index like this:

   CREATE INDEX TEST_IND ON TEST (CAST (KEY AS VARCHAR));

This is mostly an academic question, to actually do the work I created
a work table from one of the tables converting the key to varchar(8)).
--
Mike Nolan

On Tue, Apr 29, 2003 at 08:31:51PM -0500, nolan@celery.tssi.com wrote:
> I have two large tables with related data, one of which has a key that is
> char(8), the other has the same key, but it is varchar(8).
>
> The tables come from two different legacy environments which are being
> merged, part of the work at this point is settling on a unified data
> dictionary.  Side question:  This is an 8 character membership ID which
> will always be eight characters long if defined but may occasionally be
> null, does it make any significant difference in storage or performance
> whether I use char(8) or varchar(8)?

Not significant, no.

> However, before I can rebuild all the data tables using either char or
> varchar uniformly, I needed to be able to update the table with the varchar
> field from values in the other table, but this seems to take forever, even
> though both tables are indexed on the key field.
>
> It doesn't appear that I can cast one of the keys to the other format and
> have it work efficiently unless I can also cast a functional index.  But
> I get errors when I try to create an index like this:

Hmm, I usually can use a cast and it works ok (between char() and text).
Mind you, if the only difference in the spaces, you can just use trim on the
column without a join? Unless there is something else at work here...

If the join doesn't work, please post both the query and the explain.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington