Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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.Received on Thu May 04 2000 - 00:00:00 CDT
![]() |
![]() |