Is there a bitfield type field in oracle?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dean - 30 Jan 2008 15:50 GMT

Hello all,

Oracle 10g.

I have a general question here on how best to implement this: We have
a table called TRAIN that holds around 10K records of railroad data. A
client of ours wants to be able add a field that specifies which of
the 365 days of a year the train runs.

I can think of a few ways of doing this:

1) Add 365 fields of varchar2(1), each of which holds a 'Y' or a 'N'
entry to state whether the train runs that day.

2) Add a foreign key to an external table that holds unique date
ranges.

3) Is there a bitmap-kind of field that could hold one entry in a bit
of an integer type. Such things are available to software developers
who need to compress the data as much as possible.

I'd appreciate a pointer to the right area here, if (3) is available.

Thanks

Dean

Reply to this Message

malcolm - 30 Jan 2008 17:51 GMT

> Hello all,
>
[quoted text clipped - 22 lines]
>
> Dean

I was going to write how this would not work, because although there
are bit manipulation functions in PL/SQL you would need a 365 bit
number (366 for leap years), and I thought this would be out of range.

But then I tried it, and it worked:

SQL> select bitand(power(2, 366), 1) from dual;

BITAND(POWER(2,366),1)
----------------------
1

Well, no exception anyway... But the answer is wrong. It should be
0. Weird. I chalk this up to a floating point inaccuracy.

But anyway, if I were designing this, I would have another table
containing a foreign key back to the train table, with a row for each
days of the years that the train runs.

Reply to this Message

malcolm - 30 Jan 2008 17:58 GMT

> > 3) Is there a bitmap-kind of field that could hold one entry in a bit
> > of an integer type. Such things are available to software developers
> > who need to compress the data as much as possible.

> I was going to write how this would not work, because although there
> are bit manipulation functions in PL/SQL you would need a 365 bit
[quoted text clipped - 10 lines]
> Well, no exception anyway... But the answer is wrong. It should be
> 0. Weird. I chalk this up to a floating point inaccuracy.

Seems to silently give the wrong answer at 2^63. I'm using 64 bit
Oracle.

SQL> select bitand(power(2, 63), 1) from dual;

BITAND(POWER(2,63),1)
---------------------
1

SQL> select bitand(power(2, 62), 1) from dual;

BITAND(POWER(2,62),1)
---------------------
0

I think that's pretty crap. It should throw an overflow exception...

Reply to this Message

tb - 30 Jan 2008 17:53 GMT

Hello Dean,

sorry, a bitmap typ doesn't exist. Solution 1) not realy a good idea
(sorry 2008 have 366 days).
Solution 3) looks like a relational database. Go one step more.

REF_TBL_TRAIN (ID, NAME) reference table for all trains
(1,'TRAIN A')
(2,'TRAIN B')
(3,'TRAIN C')
REF_TBL_WEEK (ID,WEEK) reference table for all weeks in
the year (54 entries for one year)
(1,'Week 1') ... (52,'Week 52')

REF_TBL_DAY_WEEK (ID,RUNNING_DAY) reference table to specify the
days a train is running (create only combinations you need)
(1,'YYYYYYY'), (2,'YYYYYYN') or something more simpler.

The table TRAIN_HELP(ID_TRAIN,ID_WEEK,ID_TBL_WEEK) contains 3 values
for the 3 reference tables. If there is no entry use a good default
value.
Most trains will run monday till friday. A better solutions for
holidays is the next step.

Best regards
thomas

Reply to this Message

Walt - 30 Jan 2008 19:17 GMT

> Solution 3) looks like a relational database. Go one step more.
>
[quoted text clipped - 9 lines]
> days a train is running (create only combinations you need)
> (1,'YYYYYYY'), (2,'YYYYYYN') or something more simpler.

I've had to work with 'NYNNY' data designs like this, and all I can say
is that the label is apt.

1) Composite fields (i.e. a column that holds more than one piece of
information) are a red flag for bad data design.

2) Representing date information by "rolling your own" means a lot of
extra work, and probably errors (from leap years etc.)

A relational database that takes advantage of the date datatype would
look something like this:

TRAINS (ID NUMBER PK,
NAME Varchar2(64) )

> (1,'TRAIN A')
> (2,'TRAIN B')
> (3,'TRAIN C')

TRAIN_RUN_DATES (ID NUMBER FK to TRAINS table,
D DATE constraint trunc(d)=d (or use a trigger),
IS_RUNNING varchar2(1) )

> (1,01/01/2008,'Y')
> (1,01/02/2008,'Y')
> (2,01/01/2008,'N')

//Walt

Reply to this Message

Walt - 30 Jan 2008 18:00 GMT

> I have a general question here on how best to implement this: We have
> a table called TRAIN that holds around 10K records of railroad data. A
[quoted text clipped - 14 lines]
>
> I'd appreciate a pointer to the right area here, if (3) is available.

Well, you could use a varchar2 of length 365 and enter strings of zeros
and ones (or Y's and N's) Of course, I'd only recommend this if you
were planning on leaving the project soon and you really hate the people
who'll pick it up.

I'm having a hard time deciding whether this is better or worse than
option 1.

My $.02 is that anytime you're using dates, use the Oracle DATE datatype
so you don't have to reinvent the wheel regarding things like leap
years, how many days are in a month, which day of the week it is,
formatting the user output etc.

Also, it's a one-to-many relationship (one train, many days). So use a
table.

//Walt

Reply to this Message

Robert Klemme - 31 Jan 2008 08:17 GMT

> I have a general question here on how best to implement this: We have
> a table called TRAIN that holds around 10K records of railroad data. A
> client of ours wants to be able add a field that specifies which of
> the 365 days of a year the train runs.

Are you talking about days of every year (i.e. day 176) or are you
talking about particular dates (2nd March 2009)?

> I can think of a few ways of doing this:
>
[quoted text clipped - 3 lines]
> 2) Add a foreign key to an external table that holds unique date
> ranges.

Why would you want to add a FK to your TRAINS table? IMHO a more
natural solution would be to add another table TRAIN_RUNS with two
columns, TRAIN_PK (FK into TRAINS), DATE or NUMBER(3) and enter all
valid combinations.

Kind regards

robert

Reply to this Message