IDS Forum

Re: LVARCHAR(n) type

Posted By: Art Kagel
Date: Monday, 16 May 2016, at 12:06 p.m.

In Response To: LVARCHAR(n) type (FRANK)

Frank:

Directly no. Both will be stored as the size of their actual content plus
two bytes (for the length counter). However, there are issues of waste that
will be greater with the LVARCXHAR(1000) than with the LVARCHAR(500).
There is an ONCONFIG parameter, MAX_FILL_DATA_PAGES, that controls how
variable length records are stored on disk. That setting can have a big
impact on the actual disk space used by variable length records and their
performance:

- By default, and if MAX_FILL_DATA_PAGES is set to 0, the engine will

not place a new variable length row onto a page if the maximum size of the

new row does not fit there, even if the current size of the row will fit

with lots of slack space unused. This can waste disk space if the rows are

stable once inserted and that can cause an increase in the number of IOs

needed to access more than one row in a query, especially sequential scans,

and hurt performance.

- If you set MAX_FILL_DATA_PAGES to 1, the engine will place a new

variable length row on a page as long as after inserting that row 10% of

the page will still be free to allow at least some of the variable length

columns on the page to be expanded later without having to move the row to

a forwarding page.

- If you set MAX_FILL_DATA_PAGES to 1 and the data is dynamic (ie rows

typically grow after being inserted) then it is more likely that one or

more rows on a give page will have to be relocated in order for the longer

row to be updated. The engine will not change the ROWID of the expanded

row, even if it has to be relocated, so that it will not have to fix up all

of the inde entries that point to that row's original ROWID. The engine

will, instead, leave a forwarding pointer to the row's new location (ie new

ROWID) in the slot entry for the original row. That means that any query

that wants to see that row will have to perform an additional IO to get the

data which can slow down processing of queries.

So, this is not black and white. You have to balance whether setting
MAX_FILL_DATA_PAGES will help or hurt you. That will depend mostly on if
rows expand after insert, what percentage of rows are so affected, and by
how much those rows will grow. Another factor to consider is page size.
Placing variable length rows on wider pages may reduce waste regardless of
the setting you choose and, because the 10% reserve will be larger, can
reduce the likelihood of a row being relocated with MAX_FILL_DATA_PAGES set.

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.

On Mon, May 16, 2016 at 11:23 AM, FRANK <yunyaoqu@gmail.com> wrote:

> HI,
>
> A Question,
>
> LVARCHAR(1000) will use more spaces than LVARCHAR(500) if the data is
> normally less then 500 characters long?
>
> Thanks
> Frank
>
> --001a113b7a2edadf660532f734bf
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--94eb2c003172c797b20532f7cdb5

Messages In This Thread

• LVARCHAR(n) type
  FRANK -- Monday, 16 May 2016, at 11:23 a.m.
  • Re: LVARCHAR(n) type
    Art Kagel -- Monday, 16 May 2016, at 12:06 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.