Hi,

Sybase 11.5.1

I have a table with around 150 columns, all of which are 1/0 (ie
on/off) flags. I want to save space and come up with something that
users can query easily.

Is this a good candidate for a bitwise operator? What is the best
implementation of this - it seems like I could get all my flags in one
int column doing it this way.

Is it possible to then put a view over the top for the users to read?
If so, does anyone have a simple example of this that I could expand
on?

Thank you for any help
Tom

First, I would consider upgrading from 11.5.1. On a newer release you could
define all of the columns as type bit and you would use 1/8 the space. (You
can't fit 150 bits into a 4-byte integer since it only "contains" 32 bits.)
You could also define some user-friendly views via the case "statement" -
but these would be read-only.

On the other hand, the columns would still be defined fairly naturally. It
would be a matter of providing your users with the correspondences of 0/1
with false/true, off/on, no/yes, etc. Hopefully, the column names fit
naturally with one of these models instead of a mixture.

"Thomas Richards" <tom.richards (AT) rocketmail (DOT) com> wrote

Hi,

I think you could simply use integer column instead and store boolean values
on bits. The only additional thing you should do is to write database
functions to read and set specific bits in this integer field.
For example:
f_set_bit(current_value, bit_no)
f_read_bit(current_value, bit_no)

So SELECT can look like:

Reading 3rd bit from field 'field_name'
SELECT f_set_bit(field_name, 3) FROM table_name WHERE...

and UPDATE (modyfing 3rd bit in field 'field_name'):

UPDATE table_name SET field_name = f_set_bit(field_name, 3) WHERE...

Of course this solution has many disadvantages. The main is that all WHERE
instructions which include f_read_bit function will be much slower. No
indexes can be set on a specified bits as well.

Maciek

Uzytkownik "Thomas Richards" <tom.richards (AT) rocketmail (DOT) com> napisal w
wiadomosci news:f118866.0406180254.1979e7d3 (AT) posting (DOT) google.com...

A little modification...

f_get_bit(current_value, bit_no) bit_no - number of byte
f_set_bit(current_value, bit_no, bit_value) bit_value - 0/1 or
true/false

Reading 3rd bit:
SELECT f_get_bit(field_name, 3) FROM table_name WHERE...

Setting 3rd bit to 1
UPDATE table_name SET field_name = f_set_bit(field_name, 3, 1) WHERE...

Maciek

U¿ytkownik "Maciej Klimczak" <macko2001 (AT) poczta (DOT) onet.pl> napisa³ w wiadomo¶ci
news:cauje7$73m$1 (AT) news (DOT) onet.pl...

If you want to save space, use the bit datatype. The bit datatype is all
versions of Sybase--even the days of 4.x had the bit type. A bit column
only takes 1 bit to store. 8 bit column will use 1 byte per in the
database.

It's also easy to query:

select my_sw from my_table where my_sw = 1

There is no need to use int types, bit operators, or user-defined functions.


Brian


"Thomas Richards" <tom.richards (AT) rocketmail (DOT) com> wrote

I have a 1000 word article about the use of BIT columns sitting at
DBAzine right now. Basically, I attack this as a non-relational,
proprietary, hardware-dependent throwback to the days of punch cards.
My recommendation is to sit down and design a meaningful encoding
scheme for the data.

jcelko212 (AT) earthlink (DOT) net (--CELKO--) wrote in message news:<18c7b3c2.0406241725.3cd7191a (AT) posting (DOT) google.com>...
Joe, where is this article, I had a look on DBAzine and couln't find
it. Can you provide a link please?

Bit columns are proprietary. But they are far more efficient than other
types for data storage requirements. Support of bit colums is basic,
foundational. The computer, after all, is based on "bits".

All hardware supports bits. "Bits are hardware-dependent"--That's funny
and may only have meaning if you are from an alternate universe where there
is no such thing as "on" and "off".

All operating systems support bits. The question is, does the database
software expose them? Sybase: yes, MS SQL Server yes; Oracle no. It
is Oracle that doesn't bubble up the basic datatype. That is Oracle's
shortcoming.

If you want to pack your bits into integers yourself, and then encode them
on top of that, by all means, do it -- and add layers of needless complexity
and inefficiency to your database and your coding. I am sure your company
will benefit from spending a couple hundred thousand dollars on such
needless obfusicating activities.

If you are using Oracle, however, then such needless and inefficient
activities are the norm. Oracle should support bit datatypes. Oracle
should support IEEE types, but they don't. Except for bitwise-and, Oracle
doesn't support bit operations at all. My guess, is that you are an
Oracle drone and that you.

"Punch cards" support only bytes--80 of them per card. If you want byte
level database, then stick with Oracle. If you want something more
powerful, use Sybase.

Brian




"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

It is still sitting there; I don't know when it is scheduled. And my
editor is on vacation until July.

Actually, most machines tend to organize things on a word of various
sizes. Bits are too low level for any real work.

Well, except for those old BCD and character machines we used to have.
But you are right, bits are at the HARDWARE level, where things can
have two PHYSICAL states, "on" and "off" that cannot be extended.

... because operating systems are at the hardware level, as well as at
a lower level of logical abstraction. The OS has to worry about one
or two complement math, 8, 16, 32, 64 or variable length words,
floating point hardware and software, high end or low end bytes, etc.

The standard says "No, we want a portable, abstract language that can
be implemented on any hardware -- present and future." Microsoft
says "we want to tie you to us at all possible levels, slaves!";
Oracle, DB2 and others say we want to be as platform neutral as we can
be -- hardware and OS independent is the goal.

on top of that, by all means, do it -- and add layers of needless
complexity
and inefficiency to your database and your coding. <<

You miss the point; no competent RDBMS person wants to look at bits.
Why would I want to violate First Normal Form (1NF)??? You can write
a well-designed, high level database that does not look like a 1950's
mag tape file system with its arcane bit flags splattered over the
records.

But let's ignore the last 30 years of RDBMS research. Using integers
like that is not that much worse than the built-in bit operators which
have to eventually compile to the same machine instructions. Look at
the timings for an OR and an ADD on any modern hardware. Oh wait, I
forgot if my vendor has allowed NULLs in their proprietary BIT data
type (current release of SQL Server), then the library BIT routines
have to carry extra code for handling the NULLs, so it could be
slower. Speed is both invalid and not the real point.

I make a lot of my living cleaning up and porting databases, as well
as teaching SQL and writing. BIT does not port very well and neither
does that integer level bit-fiddling. When SQL Server decided to
allow NULLs in one release, the old code that was not checking for
them got screwed when people did not put NOT NULL constraints and made
other assumptions that were no longer true.

The REAL problem is that designing with non-1NF, low-level models and
proprietary code is what actually cost companies hundreds of thousand
dollars on such the needlessly low-level obfusications of poor design.
I know; I get a few thousand of it per hour

You have NEVER read anything I have written Considering I have
almost been sued by Oracle for some of my columns years ago, that is
really funny! I am a Standards maniac; I go out of my way to use only
SQL-92 in all my books and columns (about 750 of them so far). If I
do put anything that is proprietary, I try to make a note about that
in the article and use the clearest, most generic version of the
syntax I can find (go to X/Open portability guides, then to the vendor
docs). Example: MOD() is better than % because it is easier to read,
more like other programming language function calls and uses the
Standard SQL character set. Most of the world is not 'C' family
programmers.

You have NEVER actually used punch card file system. I am about to
tell you how we invented dirt and ended the stone age. The term in
those days was "unit record equipment" and not computer. They read and
tabulated one card at a time; the card had 80 or 90 (IBM versus
UNIVAC) physical columns.

The BYTE as you know did not exist yet; it was not defined until the
1960's about the the time of the ASCII standards. EBCDIC was the de
facto standard alphanumeric encoding. A card was read from left to
right, the columns were grouped into fixed length fields. Once the
record was designed, it was almost impossible to change it. A record
in a file has meaning because of the program reading it; if you change
the record, EVERY read and write statement that goes to that file has
to be changed, too. That is why we abstracted a databae layer that
was not tied to a particular language.

What you did instead was to multi-punch the cards and disassemble the
bits in the columns in the programs. This is the same way that Herman
Hollerith had first used punch cards for the US Census.

Personal example: the State of Georgia auto tag system started with a
single numeric column for the type of tag. The prestege tag fad hit
and we had more than ten type (if Georgia Tech had a tag, then
football rival University of Georgia wanted one, if these vets had a
tag, then all the wars had a tag, etc.)

1) You could not add another column to a card, even tho the "snipe
hunt" joke was to send the new guy to get a "card stretcher", so you
went into special characters if the card was full. Too bad that the
026, 027, 029 and UNIVAC punch machines all had different special
characters. You had to read the actual multi-punches and decide how
to map each machine to them.

2) If you had room at the end of the card for more data, you tended to
do "attribute splitting" -- two columns would hold what should have
been one encoding. Imagine a child playing 20 questions and get
"yes/no" answers; each question is a card column. This is the most
common flaw committed by newbie SQL programmers with bits. It is much
easier to add a column to a table than to sit down and design an
extensible encoding scheme.

Example: Newbies have an "IsMale" bit column. The ISO sex code has
(0= unknown, 1= male, 2= female, 9= lawful person (organization,
corporations, etc.) When the newbie runs into this he adds an extra
BIT column. He usually forgets he needs a CHECK() constraint and a
much of really silly rules that might not be obvious to the
programmers. How about "all organizations are female" and "all
unknown things are male" ?? This means that to count males, the
programmer must write:

SUM(CASE WHEN IsMale IS ON AND IsKnown IS OFF THEN 1 ELSE 0 END)

Sure hope nobody writes code on just one of the two columns. But they
will, because there is no way to prevent it in this DDL.

3) Another BIT problem is that no two host languages agree about how
(-0, +0, -1, +1) map into BOOLEAN datatypes. And hardware do not
agree about (-0, +0) either. But if you have a NULL in the BIT
datatype, and want to use it as a Boolean, then the three value logic
that is at the foundations of the language is destroyed. The most
basic rule is that NULLs propagate; now use that in a truth table. If
you want to make UNKNOWN the same as NULL, the predicates fail. If
you want to make UNKNOWN different from NULL, you now have an ugly
four valued logic.

4) The use of BIT as a flag to show that a row has been processed is
often a sign of procedural, not relational design. When I passed a
mag tape file thru one step of a process, I set a bit to flag it for
the next step in that process. In the Sybase/SQL Server family, look
for #temp tables to replace the scratch tapes.

Standard SQL does not mention physical implementation at all for very
good reasons.