Re: Table's pctfree value

From: Steve Cosner <stevec_at_zimmer.CSUFresno.EDU>
Date: 1995/07/05
Message-ID: <DB9134.Ly4_at_CSUFresno.EDU>#1/1


In article <DB5pAr.C8u_at_mecati.mecasoft.ch> unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) writes:
>Please clear or confirm this sentences (Oracle7):
>
>1) A VARCHAR2 datatype columns only allocates the needed space,
> while a CHAR datatype columns allocates the space for all the
> possible chars.
> (This MUST be right!)
>
I'm not sure about values less than the full length, but I am SURE that null values take up 1 byte (for the place-holder), or 0 byte if the column and all others following in the row are null.

My guess is less-than-full length values do not take up the full space, but that when Oracle returns the value, it blank-pads the value at that time.

>2) A NUMBER datatype columns always allocates the space for all
> the possible digits.

Max size is 38 digits (I think). There's no way Oracle would use the maximum required length for each number. It only uses the length required by the specific value.

>
>3) A DATE datatype columns always allocates the space for a date,
> (7 bits).

This is true.

>
>
>If sentences 2 and 3 are right, then:
 ...But 2 is not right...
>
>4) A table having only NUMBER and/or DATE as columns datatype
> is supposed to have pctfree=0, because no future updates will
> require extra space.
>
>By the way:
>
>5) An index is supposed to always have pctfree=0, because his
> values (table's primary keys) will be never changed.

WRONG WRONG WRONG! You need to allow space for the keys for the new rows inserted after the index is initially built! These can't be appended to the end of the index, or it wouldn't be an index.

>
>I suppose 2 and 3 (and 4) are wrongs...
>Can you confirm?
>
> ____________________________
>______________________| Stefano Unternaehrer |__________________________
>\ Mecasoft SA | Oracle DBA | fax: +41 93 335 507 /
> \ 6600 Muralto | and PL/SQL, Pro*C | voice: +41 93 337 444 /
> \ | C, XWindow & Motif | /
> / | Software Developer | email: \
> / Switzerland Europe |____________________________| dba,unter_at_mecasoft.ch \
>/________________________) (____________________________\
>

Steve Cosner (stevec_at_csufresno.edu.us Received on Wed Jul 05 1995 - 00:00:00 CEST

Original text of this message