Help, my developers are killing me with varchar2(4000)

On Mon, 11 Aug 2008 13:21:42 +0000 (UTC), Wolfram Roesler
wrote:

> Neither has the question been asked why Oracle doesn't support
>a random length string datatype.

That question doesn't need to be asked.
First of all Oracle already has CLOBs for this purpose, since 8.0, so
in the previous milennium.
Secondly, a random length string datatype would cause heavy row
chaining, so kill performance.

--
Sybrand Bakker
Senior Oracle DBA

sybrandb@hccnet.nl wrote:
> On Mon, 11 Aug 2008 13:21:42 +0000 (UTC), Wolfram Roesler
> wrote:
>
>> Neither has the question been asked why Oracle doesn't support
>> a random length string datatype.
>
> That question doesn't need to be asked.
> First of all Oracle already has CLOBs for this purpose, since 8.0, so
> in the previous milennium.
> Secondly, a random length string datatype would cause heavy row
> chaining, so kill performance.
>
Aren't there some severe limitations on what you can do with a CLOB?

There seems to be a general movement in the industry to support random
length strings while keeping row-chaining in check.
That is to inline LOBs in the row as far as is possible without chaining
while providing a full complement of (fast) string manipulation function
on them.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote,on my timestamp of 11/08/2008 11:53 PM:

>>
> Aren't there some severe limitations on what you can do with a CLOB?
>
> There seems to be a general movement in the industry to support random
> length strings while keeping row-chaining in check.
> That is to inline LOBs in the row as far as is possible without chaining
> while providing a full complement of (fast) string manipulation function
> on them.

you have got to re-read the doco for 10g and 11g,
been available for a while. most of the liitations
have been removed. most of the string manipulation
functions now work quite well with clobs.
and the in-lining has been there since clob's
started in 8.

sybrandb@hccnet.nl wrote in news:sng0a4p22d5tnep5em9cn9qnjqfgcs709c@
4ax.com:

>> Neither has the question been asked why Oracle doesn't support
>>a random length string datatype.
>
> That question doesn't need to be asked.

Why not?

> First of all Oracle already has CLOBs for this purpose, since 8.0, so
> in the previous milennium.

Can a CLOB be used in the same way a VARCHAR2 can be used, e. g. in
SELECT, UPDATE, WHERE, ORDER BY, GROUP etc.?

> Secondly, a random length string datatype would cause heavy row
> chaining, so kill performance.

Could you elaborate on this?

Thanks and best regards
W. Rösler

sybrandb@hccnet.nl wrote:
> On Mon, 11 Aug 2008 13:21:42 +0000 (UTC), Wolfram Roesler
> wrote:
>
> > Neither has the question been asked why Oracle doesn't support
> >a random length string datatype.
>
> That question doesn't need to be asked.
> First of all Oracle already has CLOBs for this purpose, since 8.0, so
> in the previous milennium.

Using Perl's DBD::Oracle, CLOBs have several undesirable side effects
if what one wants is a varchar2 but without the arbitrary limit. I don't
know if other drivers do a better job of circumventing these problems or
not.

> Secondly, a random length string datatype would cause heavy row
> chaining, so kill performance.

If 99.99% of the data has short strings and 0.01% needs row chaining, the
performance impact will be negligible, while the benefits of not having
0.01% of your data trigger the collapse of your application are great.

The A, the I, and the D of ACID all "kill" performance, too. Yet they
are worth it.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

Wolfram Roesler wrote:
> sybrandb@hccnet.nl wrote in news:sng0a4p22d5tnep5em9cn9qnjqfgcs709c@
> 4ax.com:
>
>>> Neither has the question been asked why Oracle doesn't support
>>> a random length string datatype.
>> That question doesn't need to be asked.
>
> Why not?

Well for one thing there's SYS.ANYDATA.

>> First of all Oracle already has CLOBs for this purpose, since 8.0, so
>> in the previous milennium.
>
> Can a CLOB be used in the same way a VARCHAR2 can be used, e. g. in
> SELECT, UPDATE, WHERE, ORDER BY, GROUP etc.?

With, in some cases, the use of the DBMS_LOB package: Yes.

>> Secondly, a random length string datatype would cause heavy row
>> chaining, so kill performance.
>
> Could you elaborate on this?

Go to http://tahiti.oracle.com and look up row chaining. If you don't
understand the concept and the implications reading the docs is better
than a few random sentences thrown over the cubicle wall.

> Thanks and best regards
> W. Rösler

Serge: You really need to start spending more time in Redwood Shores.
Maybe take a job there.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Aug 11, 11:21 pm, Wolfram Roesler wrote:

> Of course it is bad style, but the question why this actualls IS bad (i. e.
> what bad things will happen with it) still hasn't been answered in this
> discussion.

The difference is one of outlook. Many application developers take the
position that the DB is part of the application; it's just a bucket
you can toss things into and fish out later on. Almost everybody else
takes the view that the DB is a separate thing, and the app is just
one of a number of tools that use it. This latter group includes the
DBA, the bean counters who prowl the data, the managers who read the
reports from the bean counters, and pretty much all the other
stakeholders who depend on access to accurate data to do their jobs.

These people generally take the view that the database should contain
as much semantic information as possible about the data that it
contains, so making all your data long strings is heading in the
opposite direction to what you want. Rather, you want to be as
specific as possible at all times. That means if you're designing a
database and you don't know what's going in a particular column, you
find out. Don't be lazy and just say "oh, let's make it a big string
and code around that"; find out what it is. If it's a date, use a
date. If it's an integer, use an integer. If it's a PDF, use a BLOB.
Most modern DBMSs have enough types to satisfy just about any
requirement. Using them appropriately is a good thing. It conveys
useful information. It also catches a whole raft of programming
errors, such as when people try to stuff a string into a date column.
If the column is correctly typed, that can't happen, but if it's a
string, it will.

Theere are also issues of scalability, both in data size and number of
users. The world gets a lot more complex when your database leaves
your test environment, where it had a few dozen rows per table and
three or four users, and suddenly has a few million rows and a few
hundred users. In such circumstances, I have seen few implementations
of the "big bucket" data model survive for long. Most fail pretty
quickly, because you need to do everything right if you want to scale
up substantially.

-- Phil

"phil_herring@yahoo.com.au" writes:

> On Aug 11, 11:21 pm, Wolfram Roesler wrote:
>
>> Of course it is bad style, but the question why this actualls IS bad (i. e.
>> what bad things will happen with it) still hasn't been answered in this
>> discussion.
>
> The difference is one of outlook. Many application developers take the
> position that the DB is part of the application; it's just a bucket
> you can toss things into and fish out later on. Almost everybody else
> takes the view that the DB is a separate thing, and the app is just
> one of a number of tools that use it. This latter group includes the
> DBA, the bean counters who prowl the data, the managers who read the
> reports from the bean counters, and pretty much all the other
> stakeholders who depend on access to accurate data to do their jobs.
>
> These people generally take the view that the database should contain
> as much semantic information as possible about the data that it
> contains, so making all your data long strings is heading in the
> opposite direction to what you want. Rather, you want to be as
> specific as possible at all times. That means if you're designing a
> database and you don't know what's going in a particular column, you
> find out. Don't be lazy and just say "oh, let's make it a big string
> and code around that"; find out what it is. If it's a date, use a
> date. If it's an integer, use an integer. If it's a PDF, use a BLOB.
> Most modern DBMSs have enough types to satisfy just about any
> requirement. Using them appropriately is a good thing. It conveys
> useful information. It also catches a whole raft of programming
> errors, such as when people try to stuff a string into a date column.
> If the column is correctly typed, that can't happen, but if it's a
> string, it will.
>
> Theere are also issues of scalability, both in data size and number of
> users. The world gets a lot more complex when your database leaves
> your test environment, where it had a few dozen rows per table and
> three or four users, and suddenly has a few million rows and a few
> hundred users. In such circumstances, I have seen few implementations
> of the "big bucket" data model survive for long. Most fail pretty
> quickly, because you need to do everything right if you want to scale
> up substantially.
>
> -- Phil

I second that. The database isn't supposed to just be a bit bucket. It
is supposed to be a representation of the underlying data model. As
such, it provides, or at least can provide, a lot of valuable
maintenance information and error checking and 'free' data integrity
management through the use of the right types, various constraints
(including maximum data sizes) etc.

In a reasonable number of years doing database development, I've not yet
come across any character type that I couldn't identify a maximum length
for that I would want to process/manipulate as a varchar2 (or similar
types in other products). maybe I've been lucky, but by the time I've
encountered something that wouldn't fit inside the 4k limit, it usually
meant it wasn't something I needed to manipulate in a string like
manner, but instead treated as just a big blob of something. Of course,
the landscape is changing a bit with XML, but Oracle has alternative
types and support for XML anyway.

Tim

--
tcross (at) rapttech dot com dot au

phil_herring@yahoo.com.au wrote,on my timestamp of 12/08/2008 1:38 PM:

>big snip
> Theere are also issues of scalability, both in data size and number of
> users. The world gets a lot more complex when your database leaves
> your test environment, where it had a few dozen rows per table and
> three or four users, and suddenly has a few million rows and a few
> hundred users. In such circumstances, I have seen few implementations
> of the "big bucket" data model survive for long. Most fail pretty
> quickly, because you need to do everything right if you want to scale
> up substantially.
>


very true. To use a db as a bit bucket is the negation
of the very idea of building on previous work. Which is
a characteristic of some current deranged development
methodologies: nothing like re-inventing the wheel at every
corner to guarantee a continuous stream of income, ain't it?

Fact is: dbs have inbuilt mechanisms for type diversity
and coherence as well as scalability. Already debugged,
optimized and ready to work for any project, not just the
flavour-de-jour. To ignore such a resource in the name of
vague design notions never proven in any successful
delivery borders on criminal...