Re: VARCHAR2(1) vs CHAR(1)

Hi Marco,

When processing data (say your many million of rows), what takes the time is finding all the blocks in question. Either determining if the block(s) are already in memory or reading them into memory if not and then pinning the block for access. Actually reading data from the block is relatively quick and I would imagine reading a length byte or accessing some other part of memory to determine the length of a column would be no quicker or indeed slower. The one method to read a column's value regardless of it's datatype seems a reasonable way to go (but you need to speak to the Oracle developers for the nitty gritty). Note that dates, timestamps and the such all fit into the same category.

Note it's actually possible for Char to worsen performance rather than improve it (although not so much in your example). Why, because large char values potentially waste space. Wasted space means you potentially need more blocks to fit the data. More blocks means that Oracle potentially needs to do more work to retrieve x number of rows (full table scans and the such).

The nice thing about chars are that updates don't cause char values to increase in length. If updates don't affect row size then row migration will not be a problem and potential issues there can be avoided.

But store the length Chars most certainly do my friend.

Cheers

Richard

"Marco Muracchioli" <reply.on_at_group.please> wrote in message news:Xgq19.156062$vm5.5258194_at_news2.tin.it...
> Hi Richard,
>
> this may be correct, but now I ask:
> it a char column is always filled up to it's length, why Oracle don't read
DD
> and then always use the field length value for a fast reading?
> If u have to read just one record may be slower, but in data warehouse
> application, for example, where u read many million rows each time should
be
> faster.
>
> Few months ago, we ask to italian Oracle support the same question that
> Stjepan, or "if we use 1 or 2 char length field, wich datatype is the
better
> choice for speed?"
> Oracle support answer the same way I do: use char, because it don't need
> length before data.
>
> I really don't know what think about. :)
> Maybe this morning I'll get one of my datafile and an hex editor... :)
>
> Marco
>
> "Richard Foote" <richard.foote_at_bigpond.com> ha scritto nel messaggio
> news:Gvk19.47019$Hj3.142967_at_newsfeeds.bigpond.com...
> > Hi Marco,
> >
> > If you take a squiz at the Concepts Manual, the chapter that discusses
> > Schema objects has a discussion of the format of a row,
> >
> > Young Conner (so he tells me) is absolutely correct in saying that
Oracle
> > stores Char and Varchar2 in the same way (column length followed by
column
> > value). The reason being that as Oracle ploughs through a row taking out
> > data of interest, it requires the length portion of the column to
determine
> > how much to read (else it would require access to the DD to determine
it's
> > length, I don't think so).
> >
> > Cheers
> >
> > Richard
>
>
Received on Tue Jul 30 2002 - 08:56:14 CDT