Re: Size of "0" versus NULL ?

Frank Rizzo wrote:

> Hi,
>
> I have not had any reply and I was wondering if I've asked
> something that is a FAQ (can someone point me to a faq, document,
> manual, or anything else so i can work this out).
>
> In article <MPG.18adb55b35fd6ff4989683_at_news.cdn.telstra.com.au>,
> franklynrizzo_at_REMOVE_THIS_BIT.today.com.au says...
> >
> > Greetings,
> >
> > I have a table with NUMBER(4). The table has ~ 7 million records. I
> > want to know what the space saving is if i convert all the zeroes to
> > NULLs ?
> >
> > How do I go about calculating this accurately.
> >
> > It will be something like:
> >
> > 7 million * (sizeof zero - sizeoff NULL) ??
> >
> > How do I find the size of storing a single zero (does it change if the
> > column was NUMBER(30), for instance ???) and the size of storing a NULL.
> >
> > Thanks in advance for any info.
> >
> >
> >

Is hard disk so precious that you consider this a reasonable line of inquiry? I mean even if you had 7 millions rows containing 4 bytes and converted them all to nulls the calculation (7,000,000 x 4 byes) would only yield 28MB. An almost meaningless amount of disk.

I'd be far more concerned with the fact that Oracle doesn't index NULLs and that convering zeros to nulls will likely result in full-table scans.

In the time spent thinking about this ... at a reasonable rate of pay ... more money has been spent on considering this question than the cost of 28MB of disk space.

Daniel Morgan Received on Sun Feb 16 2003 - 21:34:54 CST