Re: max # of columns in the table (again!)

Hello!

There will probably be different opinions on this, but I've sometimes overcome "too-many-columns" problem just by using one number type column for several records with boolean or Yes/No and low-cardinality static types like Male/Female, Single/Married/Widowed/Divorced; and have been using bit operations/AND comparisions to store/retrieve values from them. This way I can reduce the number of columns.

Just to do an illustration, instead of a table with columns gender (with possible values 'M', 'F'), marital_status ('S', 'M', 'W', 'D') I just could have one number type composite column there, where I'd store my information bitwise:
bit 0: 0 = 'M', 1='F'
bit 1,2: 00='S', 01='M', 10='W', 11='D'

So, when we store a single female to this composite column, wed have following bit layout: 001 (bits are counted from right to left as you know). Note that this is not physical layout in oracle row, at least if you use number datatype.

So if we just do a normal select from this single female, we see a number 1 as a result. If we select a divorced male, then we'd see number 6 (bits 110) as result.
Further, we do have a function BITAND which helps us to extract just the information we need and BITAND together with DECODE can be used to store the composite field.

You could even use indexes on this column (even for some bits in the middle using function based indexes), but since we are mainly talking about low-cardinality data, then you probably don't want to index.

Also, you might want to store your information in a RAW field, to eliminate overhead of NUMBER datatype, but read
http://www.jlcomp.demon.co.uk/faq/bitwise.html first.

Drawbacks? Since you have hardcoded the meanings of bits somewhere, this method isn't very flexible, or at least you have pay more attention where & how you get your data. But for static stuff it works well.

Tanel.

"dias" <ydias_at_hotmail.com> wrote in message news:55a68b47.0302152244.37694f38_at_posting.google.com...
> Thank you all,
>
> I agree, it's a design problem.
>
> But I'm serching for arguments to prove that this design is bad.
>
> I have to migrate data from an old application to Oracle, the database
> users, it's a DTWH, don't want to change the data model, and prefer to
> work with wide tables, rather than small ones whith joins.
>
> Thanks.
>
> ydias_at_hotmail.com (dias) wrote in message
news:<55a68b47.0302142315.39934901_at_posting.google.com>...
> > Hi,
> >
> > 1000 columns is the max for a table (8i and 9i).
> >
> > Oracle (8i) has an internal limit of 255. That means that if any
> > columns beyond 255 are not null, that row will always be chained.
> >
> > Is this still the case in 9i ?
> >
> > Thanks
Received on Sun Feb 16 2003 - 09:29:22 CST