Re: How to modify ENUM datatypes?

On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <tino(at)wildenhain(dot)de> 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