Re: Performance question - Thanks!

Hi Eric,

The Concepts manual (Chapter 11 Schema Objects, Tables, How Table Data is Stored) covers all this (although I think Howard does cover it better).

Cheers

Richard
"Eric Levinson" <nospam_at_nospam.com> wrote in message news:F0eJ8.4934$3w2.22700_at_typhoon.sonic.net...
> Thanks so much for your explanation. I don't claim to be a DBA or
anything,
> just trying to figure out how I can get more performance out of
operations,
> it looks like from what you are saying, LENGTH doesn't cause any
performance
> issues since it just looks up the length in the row information.
>
> Can someone point me to a link which shows this so I can convince people
> here that this is the way it works? I looked in the oracle7 Server admin
> guide and online for how a row looks datawize but can't find it.
>
> Thanks,
> Eric
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:SbcJ8.20$5i3.38_at_news.oracle.com...
> > I have actually answered this already. The "n" is what gets stored with
> the
> > row, so there is no overhead in processing that field. The row says
"next
> 5
> > characters is surname", "SMITH". Even though the field is defined as
> > varchar2(10). In your terminology, that means the "n" is the actual
> > bytecount per row.
> >
> > As for where the varchar2(10) gets stored, where do we store anything
> about
> > the definition of a table? In the data dictionary.
> >
> > HJR
> >
> >
> > "Eric Levinson" <nospam_at_nospam.com> wrote in message
> > news:4LbJ8.4858$3w2.22336_at_typhoon.sonic.net...
> > > I can't find it. I am looking in a Oracle 7 guide in appendix A
> > > "Initialization Parameters" but it isn't there.
> > >
> > > I found something online which shows:
> > >
> > > COLUMN NAME = TICKER
> > > TYPE = VARCHAR2
> > > LENGTH = 10
> > > PRECISION =
> > > SCALE =
> > >
> > >
> > > As an example.
> > >
> > > My question though, once again, is the LENGTH the length the 'n' in
the
> > > VARCHAR (n) definition, or is the length the actual bytecount? If it
is
> > the
> > > actual bytecount, then where does the 'n' from the definition stored?
> > >
> > >
> > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > > news:ufafin8tr37ue0_at_corp.supernews.com...
> > > > The length is stored with the data, expect for date columns (fixed
> > length
> > > of
> > > > 7 bytes).
> > > > This used to be in Appendix A of the Oracle Server Administrators
> > Manual.
> > > A
> > > > search on 'datatypes' on http://tahiti.oracle.com will probably
> provide
> > > the
> > > > correct reference.
> > > >
> > > > Hth
> > > >
> > > >
> > > > --
> > > > Sybrand Bakker
> > > > Senior Oracle DBA
> > > >
> > > > to reply remove '-verwijderdit' from my e-mail address
> > > >
> > > > "Eric Levinson" <nospam_at_nospam.com> wrote in message
> > > > news:%h8J8.4759$3w2.21577_at_typhoon.sonic.net...
> > > > > Oops, I made a mistake, you were confirming my original quesiton,
> that
> > > > yes,
> > > > > the length of the column is stored with the data, can anyone else
> > > confirm
> > > > > this? Is this documented in any Oracle documentation? I can't
find
> > it
> > > > > anywhere.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Eric
> > > > >
> > > > > "Eric Levinson" <nospam_at_nospam.com> wrote in message
> > > > > news:ie8J8.4757$3w2.21689_at_typhoon.sonic.net...
> > > > > > Thanks for your answer, Howard, however it was not what I was
> > asking.
> > > > > >
> > > > > > For CHAR(10), what you stated applies.
> > > > > > For VARCHAR(10), the column is variable size regardless of the
> "10"
> > > > > >
> > > > > > My question is, if I store the word 'test' in a VARCHAR2(10)
> column,
> > > is
> > > > > > there another piece of data ONLY for varchar2 or any other
> datatype
> > > > which
> > > > > is
> > > > > > variable width which would store the number 4, so that LENGTH
> > returns
> > > > this
> > > > > > value, or is this data NOT stored, and everytime LENGTH function
> is
> > > > > called,
> > > > > > the database has to count characters in the VARCHAR2 field till
it
> > > > reaches
> > > > > > some end of field mark (like hex FF)
> > > > > >
> > > > > >
> > > > > > Thanks,
> > > > > > Eric
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > > > > news:ad14go$gkk$1_at_lust.ihug.co.nz...
> > > > > > > The format of a row is: row header, column length - value;
> column
> > > > > length -
> > > > > > > value; column length - value; and so ad infinitum (nearly).
> > > > > > >
> > > > > > > The length of a field is stored as an attribute within the
row.
> If
> > > it
> > > > > was
> > > > > > > Name Char(10) it would be stored as :10:Smith-----: If it was
> Name
> > > > > > > varchar2(10) it would be stored as :5:Smith:
> > > > > > >
> > > > > > > Regards
> > > > > > > HJR
> > > > > > >
> > > > > > > "Eric Levinson" <nospam_at_nospam.com> wrote in message
> > > > > > > news:DjUI8.4416$3w2.19388_at_typhoon.sonic.net...
> > > > > > > > When a VARCHAR2 type column is defined, how does the LENGTH
> > > function
> > > > > get
> > > > > > > the
> > > > > > > > length of the varchar contained in the column? Does it
count
> up
> > > the
> > > > > > > number
> > > > > > > > of bytes to some end of string marker, or is there an
> internally
> > > > held
> > > > > > and
> > > > > > > > maintained attribute of varchar which maintains the size?
> > > > > > > >
> > > > > > > > Reason why I am asking, there might be a performance hit to
> use
> > > > LENGTH
> > > > > > if
> > > > > > > it
> > > > > > > > has to calculate it every time.
> > > > > > > >
> > > > > > > > Any help would be appreciated
> > > > > > > >
> > > > > > > > oracle at levinsong dot com
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed May 29 2002 - 19:39:32 CDT