Re: 2 questions

In article <lld8mse7zp.fsf_at_dsto.defence.gov.au>, David Rajaratnam <David.Rajaratnam_at_dsto.defence.gov.au> wrote:
>
>I've got a couple of questions I was hoping someone could help
>me with. I've done a search through dejanews but couldn't find
>the answer so...I know what I think the answers are I just
>wouldn't mind confirmation.
>
>1) What sort of percision does the DATE type go to? From what
> I can gather it only does to the nearest second. Is this
> correct?

Date columns use 7 bytes, unless the value is null. You are correct, it stores down to the second.

>2) Does oracle allocate space for entries in a record that
> have no data in them? I suspect that it does (except for
> variable length datatypes such as VARCHAR2). The reason
> I ask is that a table structure I'm creating will probably
> be very sparse (eg. given table with columns 1...10, for
> every record I might only have 1 or 2 columns with actual
> data in them). Will oracle be able to optimise the space
> or should I look at creating a more flexible (and complex)
> set of tables to model what I want?

Values that are null take up one byte, except when they are at the end of the row, where they take no space. A row is stored with only the last non-null column taking any space, so you should create your table with the most commonly non-null columns first.

Except for the one-byte space, columns with no value take up no other space, unless you define a char(n) column. In that case, non-null values are blank-padded to fill the entire width of the column--using this datatype is generally not a good idea because the blank padding causes programming headaches.

HTH
Steve Cosner Received on Wed Sep 10 1997 - 00:00:00 CDT