Re: Does having primary key at the end of table take up space

In article <39116724.738D163F_at_netvision.net.il>,   eric_b_at_netvision.net.il wrote:
> Null values in columns does not take space. this is independent of the
> column position in the table.
>
> tim.mcconechy_at_runtime.dk wrote:
>
> > HI!
> >
> > I was wondering about a comment a colleque made and if it is
 correct or
> > not. Does having a PK at the end of the table take up unneccessary
 space
> > when they are left as null?
> >
> > Say I have a Table like:
> > CHK10MEMO VARCHAR2(4000)
> > CHK20MEMO VARCHAR2(4000)
> > RECNO NOT NULL NUMBER(20)
> > USER_NAME VARCHAR2(15)
> > LASTEDIT DATE
> >
> > And the columns Chk10memo and chk20memo
> > contain no data.
> >
> > Because the recno field is required and filled in does this mean we
 are
> > taking up space up to this point on the other columns?
> >
> > Would it be better to do:
> >
> > RECNO NOT NULL NUMBER(20)
> > USER_NAME VARCHAR2(15)
> > LASTEDIT DATE
> > CHK10MEMO VARCHAR2(4000)
> > CHK20MEMO VARCHAR2(4000)
> >
> > I tried to varify this claim on 8.0.5 and it seemed to not be a
 problem.
> >
> > Thanks a lot
> >

Actually null columns placed before columns that always contain a value requires Oracle to store length bytes in the row that do not have to be stored when the nullable columns are defined after the required data columns and they are null. That is if the, say, five last columns of the row are null Oracle does not have to store any length indicators for these columns. Oracle just knows the rest of the columns in the rows are null. On a large table with millions of rows where the nullable columns are sparely populated then this can save a fairly significant amount of space. Also depending on how Oracle actually handles row retrieval and expansion there may be some performance benefit to having indexed columns toward the front of the row.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.