Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Column position
Thanks for all replies
I just did the test - run number of queries against the original
database, added the column using ALTER TABLE ... command
and run the same tests, compared logs and trace files. There was
practically no difference in performance so I am not going to bother
with recreating tables with the column in different position and
reloading.
Well the performance sucked first when I added the extra column
but I realized than I should've analyzed the tables. After analyzing
everything worked OK.
Since we mentioned the memory - doesn't the varchar2 column keep any information about itself so that even if it is null and last column it takes some bits or bytes?
Witold
On 22 Jun 2000, at 11:01, MTPConsulting_at_AOL.COM wrote:
> This is not true. If the last column in a row is null, no bytes at all are
> taken up. This has always been the case for Oracle.
>
> Not sure how much of a performance impact one byte per row will have for your
> system though. Seems unlikely to be much.
>
> Marc Perkowitz
> MTP Systems Consulting
>
> In a message dated 6/21/00 2:16:06 PM Central Daylight Time,
> rajagopalvr_at_excite.com writes:
>
> << Hi,
>
> Either way, Oracle will still consume atleast 1 byte for storing
> the null indicator value. Does not really matter where this column
> is positioned.
>
> Regards
> Rajagopal Venkataramany
>
> On Tue, 20 Jun 2000 09:33:18 -0800, ORACLE-L_at_fatcity.com wrote:
>
> > Hi all
> >
> > I have a table defined as:
> >
> > my_table:
> > prt_name VARCHAR2(10) NOT NULL,
> > imp_date DATE NOT NULL,
> > atr_type CHAR(5) NOT NULL,
> > atr_data VARCHAR2(512)
> >
> > There are millions of records in this table
> > I would like to add another column:
> > exp_id VARCHAR2(10)
> >
> > I can do it using the ALTER TABLE command and the column will
> > end up as the last in the table or I can recreate the table, putting
> > the column in different position, and reload the data. The column
> > like atr_data is VARCHAR2 and can contain nulls. The table is just
> > an example, I have number of tables to which I need to add another
> > column.
> >
> > Would there be a performance difference depending on the column
> > position?
> >
> > Thanks
> >
> > Witold
> >
> > --
> > Author: Witold Iwaniec
> > INET: wiwaniec_at_novalistech.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> Regards
> Rajagopal Venkataramany
> >>
> --
> Author:
> INET: MTPConsulting_at_aol.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).