Re: How to modify ENUM datatypes?

On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain  wrote:
> Merlin Moncure wrote:
> > I think you're being a little too hard on enums here.  I was actually
> > in the anti-enum camp until it was demonstrated to me (and in my own
> > testing) that using enum for natural ordering vs. fielding the
> > ordering of the type out to a join is can be a huge win in such cases
> > where it is important.  Relational theory is all well and good, but in
> > practical terms things like record size, index size, and query
> > performance are important.
> >
>
>  Uhm. Sorry what? Can you demonstrate this particular use?
>  When I first saw discussion about enumns I kinda hoped they
>  will be implemented as kind of macro to really map to a table.
>  But here you go. I'm still looking for a good example to
>  demonstrate the usefullness of enums (same for arrays for that
>  matter)

You must not be aware that enums are naturally ordered to make that
statement.  Suppose your application needs to order a large table by
a,b,c where b is the an 'enum' type of data.  With an enum, the order
is inlined into the key order, otherwise it's out of line, meaning
your you key is larger (enum is 4 bytes, varchar is guaranteed to be
larger), and you need to join out to get the ordering position, use a
functional index, or cache it in the main table.

I agree with disagree with you on arrays.  I think they are generally
a bad idea in terms of using them as a column type.  However they are
useful passing data to/from functions and back/forth from the client.

merlin