Re: want to know the reason for varchar2(4000) limit

"Max Newell" <30xq23i02_at_sneakemail.com> wrote in message news:3714cf67.0310030534.21442f61_at_posting.google.com...
> Hi all,
>
> Despite several days of digging, I haven't been able to determine the
> real reason why Oracle limits varchar2 to 4000 chars. I understand
> that it is desirable to avoid row chaining, but if one designed a
> tablespace with larger block sizes, wider varchar2 columns wouldn't be
> likely to cause chaining.
>
> Some competing databases apparently support much wider varchar-style
> columns -- SQL Server is 8000 bytes (yes, I know that SQL Server has a
> hard row limit of ~8K bytes per row, but that's a different story),
> and apparently Informix supports 32K bytes. I haven't been able to dig
> up a storage reason why Oracle couldn't support wider varchar2. And
> (almost) everybody hates CLOBs.
>
> Does anybody have a good explanation?
>

Hi Max,

I don't know about a good explanation, but here goes.

There are issues with very large rows, the main one's being:

1. Full table scan performance would be impacted because you'll have by definition more data blocks and a more "distant" HWM
2. Rows that don't fit within a single block (i.e. chain) incur additional I/O to access, even through indexes.

If you were allowed massive VARCHAR2 fields, you would be causing the above issues. A limit of 4000 is more than reasonable IMHO from this perspective.

Plus, there's the alternative, which you suggest most hate but I would either argue the point or suggest those that hate CLOBs don't actually understand them.

Because the beauty with CLOBs is that they are not stored "in line" or with the other columns in the row. Actually, they could be inline if they're less than roughly 4000 bytes but unless they very frequently accessed, I would argue against such a move. By storing such large column data separately from the rest of the row (you only store the LOB locator or pointer to the clob in the row), you dramatically reduce the length of these rows. When performing FTS, you don't have to plough through all this potentially unnecessary data and so dramatically improve performance here. Although technically still chained rows, they're therefore generally more efficient to access.

Another point that's often ignored is the fact that rollback info is not stored with the rollback/undo segments, but rather within the configurable structure of the LOB itself. Therefore, if you perform heaps of changes to these columns, you also reduce stress and associated issues with the undo segments. Plus you have the flexibility of whether or not you want these changes to be logged or not or accesses to be buffer cached or not ....

Although some may argue a larger varchar2 field would give more options and flexibility, I disagree and suggest learning to "love" LOBs ;)

Cheers

Richard Received on Fri Oct 03 2003 - 09:06:46 CDT