Re: Creating a functional index on a cast?

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