IDS Forum

Re: Blob Text Byte

Posted By: Art Kagel
Date: Tuesday, 11 June 2013, at 9:09 a.m.

In Response To: Blob Text Byte (André Luiz Rufino)

You can easily make these columns LVARCHAR(2000), however, note that you
may not save as much disk space as you think you will, especially if the
very wide columns are mostly full. If they are not, you MUST set
MAX_FILL_DATA_PAGES to '1' in your ONCONFIG file otherwise the engine will
only put another row on an existing page if the maximum length of the new
row would fit in the free space on the pages. With this parameter set then
the engine will put a new row on a page as long as the current size of the
row plus some allowance for growth will fit. This may still leave some
unused space on a page even if a small row might fit there.

However, my Data Architect head tells me that you should probably reexamine
those very wide columns. Why were they created:

1. to hold a single string entity that may or may not exist but will

nearly fill the column if it does

- Make a child table with the wide CHAR column(s) in it.

2. to hold a string that may grow or shrink over time if its value is

changed

- Change to an LVARCHAR (or VARCHAR if less than 255 bytes)

3. to allow for the entry of ad hoc text of varying length

- Change to a child table containing an additional sequence number

key column and a MUCH smaller CHAR type column (say 60 to 100 bytes) to

hold multiple lines of text one per row.

- This has the advantage of not limiting the user to <N> bytes of

text (I personally HATE those little comment boxes on web sites
that have a

little number under them that counts down from 1000 while I type. Much

better to let the users enter whatever they want. You can never
waste more

than 59 bytes (plus the key lengths) if you only use part of one of the

child table comments!

- Another added bonus is that you can retain any formatting that the

user used to enter the text which might make it easier to read for the

consumer of the user comments!

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino <andre_rufino@msn.com>wrote:

> Hi everyone,
> In my production environment, I have a lot of tables with row buffer than
> page
> size. It's because we have a lot of char(2000), and text columns. They are
> on
> the same place that normal rows. I dont know what can be more efficent:
> change
> those columns to a blob ou sblos space, or change those columns to a
> varchar
> or lvarchar? First of all, Can I do it? Change a column char (2000) to a
> text
> in a blob space?
> Thanks,
> André Luiz Rufino
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a11c285146197c204dee09be8

Messages In This Thread

• Blob Text Byte
  André Luiz Rufino -- Tuesday, 11 June 2013, at 7:43 a.m.
  • RE: Blob Text Byte
    Alexandre Marini -- Tuesday, 11 June 2013, at 8:26 a.m.
    • RE: Blob Text Byte
      André Luiz Rufino -- Tuesday, 11 June 2013, at 9:04 a.m.
  • Re: Blob Text Byte
    Art Kagel -- Tuesday, 11 June 2013, at 9:09 a.m.
  • Re: Blob Text Byte
    Art Kagel -- Tuesday, 11 June 2013, at 9:15 a.m.
    • RE: Blob Text Byte
      André Luiz Rufino -- Tuesday, 11 June 2013, at 9:42 a.m.
      • Re: Blob Text Byte
        Art Kagel -- Tuesday, 11 June 2013, at 10:36 a.m.
        • RE: Blob Text Byte
          André Luiz Rufino -- Tuesday, 11 June 2013, at 11:32 a.m.
          • Re: Blob Text Byte
            Art Kagel -- Tuesday, 11 June 2013, at 12:08 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.