LVARCHAR(n) type

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

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