Regarding varchar
Question: what does the second number mean in a column declared VARCHAR(100,1)? Answered clearly: the syntax is VARCHAR(max, reserved), where 'reserved' is the minimum space always allocated per row (plus 1 byte holding the actual length). Values shorter than the reserved size still consume the reserved bytes (filled with garbage, not blanks); longer values use only their actual length, up to max. Reserving space lets updates that grow the value stay in place rather than relocating the row, but wastes space on insert-only or mostly-null columns. Art verified the behaviour with oncheck -pp; it was also noted IDS can't light-scan tables containing varchars.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello everybody, I have one table in which varchar is declared as varchar(100,1) whats the significance of 1 in the above varchar(). any idea. -- Regards, Prateek Jain
Prateek Jain wrote: > Hello everybody, > I have one table in which varchar is declared as > varchar(100,1) > whats the significance of 1 in the above varchar(). > any idea. This is explained in the Guide to SQL Reference and the Guide to SQL Syntax even. Q&D you declare a VARCHAR (variable length character) column as (max, reserved) - the engine allocates each new row with at least 'reserved' + 1 bytes (to record the column's actual length) on the row's data page(s) or the actual number of bytes in the column's data + 1 byte (if the data is longer than 'reserved') up to 'max' bytes + 1. Older OnLine documentation seemed to indicate that when a VARCHAR column was expanded to accomodate a longer value it was expanded by at least 'reserved' bytes perhaps to minimize row relocations for variable length rows that grow over time. It's not clear in the docs if this was true then or if it was that it's still true in IDS. Art S. Kagel
Prateek Jain wrote: > Hello everybody, > I have one table in which varchar is declared as > varchar(100,1) > whats the significance of 1 in the above varchar(). > any idea. This is explained in the Guide to SQL Reference and the Guide to SQL Syntax even. Q&D you declare a VARCHAR (variable length character) column as (max, reserved) - the engine allocates each new row with at least 'reserved' + 1 bytes (to record the column's actual length) on the row's data page(s) or the actual number of bytes in the column's data + 1 byte (if the data is longer than 'reserved') up to 'max' bytes + 1. Older OnLine documentation seemed to indicate that when a VARCHAR column was expanded to accomodate a longer value it was expanded by at least 'reserved' bytes perhaps to minimize row relocations for variable length rows that grow over time. It's not clear in the docs if this was true then or if it was that it's still true in IDS. Art S. Kagel
On varchars....
varchars are a bit of an odd beast...if you declare a minimum, we
allocate at least (min+1), but not in a way you'd expect. We allocate
the length byte at the front, then whatever you insert in the column.
IF it's longer than the max, it's truncated. IF it's shorter than the
min, we actually "pad with the end of the row" however many bytes
needed to get to the min asked for. Notice I said "pad with the end of
the row", versus "pad the end of the row"..there is a difference. We
don't blank fill or anything like that - we just mark off x number of
bytes to round out the varchar min. And yes, it's at the end of the
row.
oncheck -p[pP] is a great way to see this...create table with say INT,VARCHAR(whatever), INT. insert different rows of values, playing with
the length of the varchar column insert. dump the page with oncheck
-p[pP] and you'll see all kinds of things. the INT's are there just to
frame the varchar...just a habit. guess if the varchar is the only
column you don't need them.
One point of clarification - if a varchar column is null, there are
actually 2 bytes allocated - one for the length byte, and one for the
null, which is actually represeneted as a ZERO in a page dump.
And don't forget - with IDS you cannot "light scan" a table w/ a
varchar. You can w/ XPS. I am onsite helping with some large table
migrations, and light scans would have been ideal. But there are
varchars everywhere. Did suggest we ALTER and make them CHAR to allow
light scans, but that was vetoed.
Thanks -
Mark Scranton
Xtivia Inc.
Lead Database Architect
Informix 1995-retirement
Art S. Kagel wrote:
> Prateek Jain wrote:
> > Hello everybody,
> > I have one table in which varchar is declared as
> > varchar(100,1)
> > whats the significance of 1 in the above varchar().
> > any idea.
>
> This is explained in the Guide to SQL Reference and the Guide to SQL Syntax
> even. Q&D you declare a VARCHAR (variable length character) column as (max,
> reserved) - the engine allocates each new row with at least 'reserved' + 1
> bytes (to record the column's actual length) on the row's data page(s) or
> the actual number of bytes in the column's data + 1 byte (if the data is
> longer than 'reserved') up to 'max' bytes + 1. Older OnLine documentation
> seemed to indicate that when a VARCHAR column was expanded to accomodate a
> longer value it was expanded by at least 'reserved' bytes perhaps to
> minimize row relocations for variable length rows that grow over time. It's
> not clear in the docs if this was true then or if it was that it's still
> true in IDS.
>
> Art S. Kagel
mark.scranton@gmail.com wrote:
> On varchars....
Mark, do you know if my memory is correct? I have a notion that if a
VARCHAR is declared as VARCHAR(250,10) and you insert an 11 character string
then 21 bytes are reserved in the row. Guess I could check it out with
oncheck -pp as you say....
Art S. Kagel
> varchars are a bit of an odd beast...if you declare a minimum, we
> allocate at least (min+1), but not in a way you'd expect. We allocate
> the length byte at the front, then whatever you insert in the column.
> IF it's longer than the max, it's truncated. IF it's shorter than the
> min, we actually "pad with the end of the row" however many bytes
> needed to get to the min asked for. Notice I said "pad with the end of
> the row", versus "pad the end of the row"..there is a difference. We
> don't blank fill or anything like that - we just mark off x number of
> bytes to round out the varchar min. And yes, it's at the end of the
> row.
>
> oncheck -p[pP] is a great way to see this...create table with say INT,> VARCHAR(whatever), INT. insert different rows of values, playing with
> the length of the varchar column insert. dump the page with oncheck
> -p[pP] and you'll see all kinds of things. the INT's are there just to
> frame the varchar...just a habit. guess if the varchar is the only
> column you don't need them.
>
> One point of clarification - if a varchar column is null, there are
> actually 2 bytes allocated - one for the length byte, and one for the
> null, which is actually represeneted as a ZERO in a page dump.
>
> And don't forget - with IDS you cannot "light scan" a table w/ a
> varchar. You can w/ XPS. I am onsite helping with some large table
> migrations, and light scans would have been ideal. But there are
> varchars everywhere. Did suggest we ALTER and make them CHAR to allow
> light scans, but that was vetoed.
>
> Thanks -
> Mark Scranton
> Xtivia Inc.
> Lead Database Architect
> Informix 1995-retirement
mark.scranton@gmail.com wrote: > On varchars.... OK, I've tried it. IDS preallocates the reserved space for varchar values shorted than the reserved amount (ie the offset of the next row on the page includes the space for the full reserved length. As Mark asserted, data in columns following the varchar are positioned immediately after the actual length followed by (reserved - length(varcharcol)) bytes of garbage. If the actual length of the varchar is longer than reserved only the actual space is allocated. Art S. Kagel
In the definition "column1 varchar(p,s)"
"s" is the minimum amount of space to use. So varchars smaller will
allocate s bytes and varchars larger will allocate the actual required
space.
The reason this is nice is that it allows Informix to do in place
updates of records more frequently. As a segeway to this explanation, I
don't see how varchar(100,1) would be helpful to anyone at all.
Here is an example:
create table foobar(
id serial,
name varchar(50,25)
);
insert into foobar values(0,"Dog");
insert into foobar values(0,"Cat");
insert into foobar values(0,"Eagle");
--A record with 30 bytes is written ( 4 for the serial and 26 for the
varchar, 1 byte is for the length.) The length will of course read 3.
Now, if you update this with the following statement:
update foobar set name="twenty-five characters!!" where id = 1;
This statement can execute faster because the field can be updated in
the current place on disk.
update foobar set name="twenty-six characters!!!!" where id = 1;
This statement has to possibly move the record to a new location on
disk because there isn't enough room to ad the 26th character.
So, this only saves time on updates. Someone used it improperly on one
of our databases making insert only tables have varchar(64,16) fields.
Many of these fields contained null values. I went through the schema
and removed all of these preallocation on insert only tables and saved
several gigs of space.
Art S. Kagel wrote:
> mark.scranton@gmail.com wrote:
> > On varchars....
>
> OK, I've tried it. IDS preallocates the reserved space for varchar values
> shorted than the reserved amount (ie the offset of the next row on the page
> includes the space for the full reserved length. As Mark asserted, data in
> columns following the varchar are positioned immediately after the actual
> length followed by (reserved - length(varcharcol)) bytes of garbage. If the
> actual length of the varchar is longer than reserved only the actual space
> is allocated.
>
> Art S. Kagel