pg.ddx.io  University INGRES on Usenet (1985-1995): threads about Berkeley INGRES, QUEL and EQUEL from comp.databases.ingres, comp.databases and net.database  
help / color / mirror / Atom feed
VARCHAR, WHY THE 2000 Character Limit ??
17+ messages / 11 participants
[nested] [flat]

* VARCHAR, WHY THE 2000 Character Limit ??
@ 1995-11-25 00:00 ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Chris McDonald <Chris@macomp.demon.co.uk>
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? pbrown@triplerock.CS.Berkeley.EDU
  1995-11-27 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? james.lawrence@epamail.epa.gov
  1995-11-29 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Lucky Leavell <ris@iglou.com>
  0 siblings, 5 replies; 17+ messages in thread

From: ghharrac@ouray.cudenver.edu @ 1995-11-25 00:00 UTC (permalink / raw)

Has anyone wondered why varchar/varchar2 has this magical limit of 2000
characters? Well I have, this limit seems to exist in two databases that
I have used, namely ORACLE and INGRES. I have looked around for documentation
to determine if this is a SQL standard but I haven't found anything.

If you can help with this question please send me email, and post the answer.
Often, its been my experience that developers want more than 2000 characters
and the oly way to implement this is with raw data types where supported,
of by busting their data up into 2000 character records and adding a 
sequence number for reassembly.

Answers, thoughts anyone ?

ghharrac@neodata.com
ghharrac@ouray.cudenver.edu

 








^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
@ 1995-11-26 00:00 ` Chris McDonald <Chris@macomp.demon.co.uk>
  4 siblings, 0 replies; 17+ messages in thread

From: Chris McDonald @ 1995-11-26 00:00 UTC (permalink / raw)

In article: <49794h$pof@carbon.cudenver.edu>  ghharrac@ouray.cudenver.edu (George/Jorge) 
writes:
> 
> Has anyone wondered why varchar/varchar2 has this magical limit of 2000
> characters? Well I have, this limit seems to exist in two databases that
> I have used, namely ORACLE and INGRES. I have looked around for documentation
> to determine if this is a SQL standard but I haven't found anything.
> 
> If you can help with this question please send me email, and post the answer.
> Often, its been my experience that developers want more than 2000 characters
> and the oly way to implement this is with raw data types where supported,
> of by busting their data up into 2000 character records and adding a 
> sequence number for reassembly.
> 
> Answers, thoughts anyone ?
> 
> ghharrac@neodata.com
> ghharrac@ouray.cudenver.edu
> 
>  
> 
> 
> 
>

My understanding of this is that INGRES (as perhaps other RDBMS) uses 2K pages at the 
physical end. A tuple in INGRES cannot extend beyond a single page in length. So 
irrespective of whether you have a single 2000 character varchar or x columns with a total 
tuple width of 2K, you cannot bust this limit.

You have already identified the solution in your question. 
-- 
Chris McDonald







^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
@ 1995-11-26 00:00 ` Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kmill@hawkeyes.rchland.ibm.com
  1995-11-30 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? mal@winternet.com
  4 siblings, 2 replies; 17+ messages in thread

From: Keith McCammon @ 1995-11-26 00:00 UTC (permalink / raw)
  To: ghharrac@ouray.cudenver.edu

ghharrac@ouray.cudenver.edu (George/Jorge) wrote:
>Has anyone wondered why varchar/varchar2 has this magical limit of 2000
>characters? Well I have, this limit seems to exist in two databases that
>I have used, namely ORACLE and INGRES. I have looked around for documentation
>to determine if this is a SQL standard but I haven't found anything.


I'm not sure if there is a size limit specified by ANSI 92, but 
I know that R:BASE supports 256MB VARCHARs, so I'm sure the 
ANSI standard is at least that much. Perhaps the Oracle and 
Ingres limitations are related to the maximum row size (?)

Keith McCammon
keithm@microrim.com
http://www.microrim.com
--------------------------------------------------------------







^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
@ 1995-11-27 00:00   ` kmill@hawkeyes.rchland.ibm.com
  1995-11-28 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kbradley@us.oracle.com
  1995-11-29 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? enduser@enduser.com
  1 sibling, 2 replies; 17+ messages in thread

From: kmill@hawkeyes.rchland.ibm.com @ 1995-11-27 00:00 UTC (permalink / raw)

DB2/400 is not limited to 2000 chars for Varchar and Varchar2 fields.
I'd have to agree that the Oracle and Ingres limits are tied to
their maximum row size limits.

In article <49a8b8$546@news1.wolfe.net>, Keith McCammon <keithm@microrim.com> writes:
|> ghharrac@ouray.cudenver.edu (George/Jorge) wrote:
|> >Has anyone wondered why varchar/varchar2 has this magical limit of 2000
|> >characters? Well I have, this limit seems to exist in two databases that
|> >I have used, namely ORACLE and INGRES. I have looked around for documentation
|> >to determine if this is a SQL standard but I haven't found anything.
|> 
|> 
|> I'm not sure if there is a size limit specified by ANSI 92, but 
|> I know that R:BASE supports 256MB VARCHARs, so I'm sure the 
|> ANSI standard is at least that much. Perhaps the Oracle and 
|> Ingres limitations are related to the maximum row size (?)
|> 
|> Keith McCammon
|> keithm@microrim.com
|> http://www.microrim.com
|> --------------------------------------------------------------
|> 

-- 
Kenton Milligan
DB2/400 Development 
IBM Rochester-AS/400 Division
kmill@rchland.vnet.ibm.com
GO HAWKEYES!!
(opinions stated are not necessarily those of my employer)






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kmill@hawkeyes.rchland.ibm.com
@ 1995-11-28 00:00     ` kbradley@us.oracle.com
  1995-11-30 00:00       ` Re: VARCHAR, WHY THE 2000 Character Limit ?? james.lawrence@epamail.epa.gov
  1 sibling, 1 reply; 17+ messages in thread

From: kbradley@us.oracle.com @ 1995-11-28 00:00 UTC (permalink / raw)


Sorry. not so for Oracle. We allow (and always have I'm sure) rows to
split over block boundaries. And a user can define the size of the block.
Typically blocks are 2K (e.g. VMS and Unix), 4K (e.g. VM and MVS) or 8K (64
bit Dec). You set them up at init time. We have a limit (today) of 254 columns
per row but the row can span many blocks. In a way it had to cause we didn't
have any fixed length column types (well, DATE) until recently. Everything
was variable length. 

In article <49dj7c$11m6@locutus.rchland.ibm.com>, kmill@vnet.ibm.com wrote:

> DB2/400 is not limited to 2000 chars for Varchar and Varchar2 fields.
> I'd have to agree that the Oracle and Ingres limits are tied to
> their maximum row size limits.
> 
> In article <49a8b8$546@news1.wolfe.net>, Keith McCammon
<keithm@microrim.com> writes:
> |> ghharrac@ouray.cudenver.edu (George/Jorge) wrote:
> |> >Has anyone wondered why varchar/varchar2 has this magical limit of 2000
> |> >characters? Well I have, this limit seems to exist in two databases that
> |> >I have used, namely ORACLE and INGRES. I have looked around for
documentation
> |> >to determine if this is a SQL standard but I haven't found anything.
> |> 
> |> 
> |> I'm not sure if there is a size limit specified by ANSI 92, but 
> |> I know that R:BASE supports 256MB VARCHARs, so I'm sure the 
> |> ANSI standard is at least that much. Perhaps the Oracle and 
> |> Ingres limitations are related to the maximum row size (?)
> |> 
> |> Keith McCammon
> |> keithm@microrim.com
> |> http://www.microrim.com
> |> --------------------------------------------------------------
> |> 
> 
> -- 
> Kenton Milligan
> DB2/400 Development 
> IBM Rochester-AS/400 Division
> kmill@rchland.vnet.ibm.com
> GO HAWKEYES!!
> (opinions stated are not necessarily those of my employer)






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kmill@hawkeyes.rchland.ibm.com
  1995-11-28 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kbradley@us.oracle.com
@ 1995-11-30 00:00       ` james.lawrence@epamail.epa.gov
  1995-12-11 00:00         ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kbradley@us.oracle.com
  0 siblings, 1 reply; 17+ messages in thread

From: james.lawrence@epamail.epa.gov @ 1995-11-30 00:00 UTC (permalink / raw)

In article <kbradley-2811951216250001@kbradley-mac.us.oracle.com> kbradley@us.oracle.com (Kirk Bradley) writes:
>From: kbradley@us.oracle.com (Kirk Bradley)
>Subject: Re: VARCHAR, WHY THE 2000 Character Limit ??
>Date: Tue, 28 Nov 1995 12:16:25 -0800


>Sorry. not so for Oracle. We allow (and always have I'm sure) rows to
>split over block boundaries. And a user can define the size of the block.
>Typically blocks are 2K (e.g. VMS and Unix), 4K (e.g. VM and MVS) or 8K (64
>bit Dec). You set them up at init time. We have a limit (today) of 254 columns
>per row but the row can span many blocks. In a way it had to cause we didn't
>have any fixed length column types (well, DATE) until recently. Everything
>was variable length. 

Yes, but do you allow a column to span physical blocks?






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kmill@hawkeyes.rchland.ibm.com
  1995-11-28 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kbradley@us.oracle.com
  1995-11-30 00:00       ` Re: VARCHAR, WHY THE 2000 Character Limit ?? james.lawrence@epamail.epa.gov
@ 1995-12-11 00:00         ` kbradley@us.oracle.com
  0 siblings, 0 replies; 17+ messages in thread

From: kbradley@us.oracle.com @ 1995-12-11 00:00 UTC (permalink / raw)


Don't know if my post got through.. YES.. We do allow a column to span
blocks. We kind of have to since a user may elect to have a 1K block
and a 2K column


In article <james.lawrence.126.000A9D0A@epamail.epa.gov>,
james.lawrence@epamail.epa.gov (Lawrence James) wrote:

> In article <kbradley-2811951216250001@kbradley-mac.us.oracle.com>
kbradley@us.oracle.com (Kirk Bradley) writes:
> >From: kbradley@us.oracle.com (Kirk Bradley)
> >Subject: Re: VARCHAR, WHY THE 2000 Character Limit ??
> >Date: Tue, 28 Nov 1995 12:16:25 -0800
> 
> 
> >Sorry. not so for Oracle. We allow (and always have I'm sure) rows to
> >split over block boundaries. And a user can define the size of the block.
> >Typically blocks are 2K (e.g. VMS and Unix), 4K (e.g. VM and MVS) or 8K (64
> >bit Dec). You set them up at init time. We have a limit (today) of 254
columns
> >per row but the row can span many blocks. In a way it had to cause we didn't
> >have any fixed length column types (well, DATE) until recently. Everything
> >was variable length. 
> 
> Yes, but do you allow a column to span physical blocks?






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-27 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? kmill@hawkeyes.rchland.ibm.com
@ 1995-11-29 00:00     ` enduser@enduser.com
  1 sibling, 0 replies; 17+ messages in thread

From: enduser@enduser.com @ 1995-11-29 00:00 UTC (permalink / raw)

wrong.  the varchar 2000 is actually upgraded from earlier sizes of 255.
if you want to store large, gigabytes of chars, use long and long raw in oracle.
there you will find a more than adequate facility.  in other words. comparing
like below is incorrect since you are looking in the wrong place for the answer.
do not compare the datatypes.

also, db2, ingres, and informix fall far short in the speed with which
theyhandle
large character fields.


--

In article <49dj7c$11m6@locutus.rchland.ibm.com>, kmill@vnet.ibm.com wrote:

> DB2/400 is not limited to 2000 chars for Varchar and Varchar2 fields.
> I'd have to agree that the Oracle and Ingres limits are tied to
> their maximum row size limits.
> 
> In article <49a8b8$546@news1.wolfe.net>, Keith McCammon
<keithm@microrim.com> writes:
> |> ghharrac@ouray.cudenver.edu (George/Jorge) wrote:
> |> >Has anyone wondered why varchar/varchar2 has this magical limit of 2000
> |> >characters? Well I have, this limit seems to exist in two databases that
> |> >I have used, namely ORACLE and INGRES. I have looked around for
documentation
> |> >to determine if this is a SQL standard but I haven't found anything.
> |> 
> |> 
> |> I'm not sure if there is a size limit specified by ANSI 92, but 
> |> I know that R:BASE supports 256MB VARCHARs, so I'm sure the 
> |> ANSI standard is at least that much. Perhaps the Oracle and 
> |> Ingres limitations are related to the maximum row size (?)
> |> 
> |> Keith McCammon
> |> keithm@microrim.com
> |> http://www.microrim.com
> |> --------------------------------------------------------------
> |> 
> 
> -- 
> Kenton Milligan
> DB2/400 Development 
> IBM Rochester-AS/400 Division
> kmill@rchland.vnet.ibm.com
> GO HAWKEYES!!
> (opinions stated are not necessarily those of my employer)






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
@ 1995-11-30 00:00   ` mal@winternet.com
  1995-11-30 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? gainer@gainer.engr.sgi.com
  1 sibling, 1 reply; 17+ messages in thread

From: mal@winternet.com @ 1995-11-30 00:00 UTC (permalink / raw)


In article <enduser-2911951505120001@dial-cup1-6.iway.aimnet.com> enduser@enduser.com (EndUser) writes:

>
>wrong.  the varchar 2000 is actually upgraded from earlier sizes of 255.
>if you want to store large, gigabytes of chars, use long and long raw in oracle.

I believe even University Ingres used the 2K page size.  I've used
commercial Ingres since 3.x, and it always had 2K page size.  

I don't find any references to the 512 pages size in the Stonebraker
book "The Ingres Papers" either.

>there you will find a more than adequate facility.  in other words. comparing
>like below is incorrect since you are looking in the wrong place for the answer.
>do not compare the datatypes.
>
>also, db2, ingres, and informix fall far short in the speed with which
>theyhandle
>large character fields.
>

How did you do this comparison?  Define "large".  Please be more
specific.  I don't understand what you are comparing.

Cheers,

|--------------------------------------------------------------------------|
| Michael Leo            | The Ingres FAQ is at ftp.adc.com, /pub/ingres.  |
| York & Associates, Inc.| Also check out /pub/ingres/utilities/NAIUA for  |
| Minneapolis, MN, USA   | the NAIUA Tool Kit.  Lastly, access all this via|
| (612) 921-8083 (voice) | WWW at http://www.adc.com/ingres/ing-top.html.  |
| mal@winternet.com      | All constructive suggestions/criticism welcome. |
|--------------------------------------------------------------------------|







^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-30 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? mal@winternet.com
@ 1995-11-30 00:00     ` gainer@gainer.engr.sgi.com
  1995-12-01 00:00       ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-12-02 00:00       ` Re: VARCHAR, WHY THE 2000 Character Limit ?? enduser@enduser.com
  0 siblings, 2 replies; 17+ messages in thread

From: gainer@gainer.engr.sgi.com @ 1995-11-30 00:00 UTC (permalink / raw)

In article <MAL.95Nov30090944@mermie>, Michael Leo <mal@winternet.com> wrote:
>In article <enduser-2911951505120001@dial-cup1-6.iway.aimnet.com> enduser@enduser.com (EndUser) writes:
>
>>there you will find a more than adequate facility.  in other words. comparing
>>like below is incorrect since you are looking in the wrong place for the answer.
>>do not compare the datatypes.
>>
>>also, db2, ingres, and informix fall far short in the speed with which
>>theyhandle
>>large character fields.
>>
>
>How did you do this comparison?  Define "large".  Please be more
>specific.  I don't understand what you are comparing.

He is just being a dufus. From his comments it is clear he has not
done much BLOB work.

Oracle has just about the worst BLOB support of any of the big 3 RDBMS
vendors. Informix and Sybase aren't too bad but upstart DB2/unix 2.1 is by far
the best. I don't have the time or inclination to post a feature-by-feature
comparison but I have looked at these products and there is a clear winner.

Frankly, I'd be astonished if DB2/unix 2.1 didn't absolutely cream Oracle
in BLOB manipulation speed *and* function.

Pat







^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-30 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? mal@winternet.com
  1995-11-30 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? gainer@gainer.engr.sgi.com
@ 1995-12-01 00:00       ` Keith McCammon <keithm@microrim.com>
  1 sibling, 0 replies; 17+ messages in thread

From: Keith McCammon @ 1995-12-01 00:00 UTC (permalink / raw)

gainer@gainer.engr.sgi.com (Patrick Gainer) wrote:
DB2/unix 2.1 is by far
>the best. I don't have the time or inclination to post a feature-by-feature
>comparison but I have looked at these products and there is a clear winner.
>
>Frankly, I'd be astonished if DB2/unix 2.1 didn't absolutely cream Oracle
>in BLOB manipulation speed *and* function.

If you want to include upstart DBMS's in your evaluation, I 
would have to conclude that Illustra is the hands down best 
database for 'BLOB-type' data.

The innovative server-side 'Datablade' modules include an Image 
Query Module.

___________________________________________________
Keith McCammon
keithm@microrim.com
http://www.microrim.com
Opinions are my own and not those of Microrim, Inc.
---------------------------------------------------








^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-26 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  1995-11-30 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? mal@winternet.com
  1995-11-30 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? gainer@gainer.engr.sgi.com
@ 1995-12-02 00:00       ` enduser@enduser.com
  1 sibling, 0 replies; 17+ messages in thread

From: enduser@enduser.com @ 1995-12-02 00:00 UTC (permalink / raw)

in sybase, the api requires that you use handles to get the blob out, you cannot
query them directlyinto program arrays, you are toasted, bud.  

--

In article <49kqil$db@fido.asd.sgi.com>, gainer@gainer.engr.sgi.com
(Patrick Gainer) wrote:

> In article <MAL.95Nov30090944@mermie>, Michael Leo <mal@winternet.com> wrote:
> >In article <enduser-2911951505120001@dial-cup1-6.iway.aimnet.com>
enduser@enduser.com (EndUser) writes:
> >
> >>there you will find a more than adequate facility.  in other words.
comparing
> >>like below is incorrect since you are looking in the wrong place for
the answer.
> >>do not compare the datatypes.
> >>
> >>also, db2, ingres, and informix fall far short in the speed with which
> >>theyhandle
> >>large character fields.
> >>
> >
> >How did you do this comparison?  Define "large".  Please be more
> >specific.  I don't understand what you are comparing.
> 
> He is just being a dufus. From his comments it is clear he has not
> done much BLOB work.
> 
> Oracle has just about the worst BLOB support of any of the big 3 RDBMS
> vendors. Informix and Sybase aren't too bad but upstart DB2/unix 2.1 is by far
> the best. I don't have the time or inclination to post a feature-by-feature
> comparison but I have looked at these products and there is a clear winner.
> 
> Frankly, I'd be astonished if DB2/unix 2.1 didn't absolutely cream Oracle
> in BLOB manipulation speed *and* function.
> 
> Pat






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
@ 1995-11-27 00:00 ` pbrown@triplerock.CS.Berkeley.EDU
  4 siblings, 0 replies; 17+ messages in thread

From: pbrown@triplerock.CS.Berkeley.EDU @ 1995-11-27 00:00 UTC (permalink / raw)

George/Jorge (ghharrac@ouray.cudenver.edu) wrote:
: Has anyone wondered why varchar/varchar2 has this magical limit of 2000
: characters? Well I have, this limit seems to exist in two databases that
: I have used, namely ORACLE and INGRES. I have looked around for documentation
: to determine if this is a SQL standard but I haven't found anything.

  This is related to the physical limitations of the underlying page
 size. All data in these Relational Products must fit onto a data
 page, because their locking and logging systems are page-oriented.
 Not a bad thing, just one of the trade-off decisions you need to make.
 Check out Date or Ullman for a rounded treatment of why pages are considered
 so important.

  Informix uses a flexible page size approach to this problem, but my
 guess is that even it doesn't deal with megabyte documents.   
 
  Hope is at hand however. Extensible DBMS's allow you to store documents
 of arbitrary length as attributes of the table. Check out the usual
 suspects to see where this is headed (Object Foo Inc, UniSQL, O2, Illustra)
 and be mindful that all of the capital-R vendors are working towards
 this type of extension.

--
  =====================================================================
     Paul Brown                                     ^..^
     pbrown@postgres.Berkeley.EDU                   (oo)  -  Oink!
     #include <std_disclaimer.h>
            "Think global - act loco!" - Zippy the Pinhead
  =====================================================================








^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
@ 1995-11-27 00:00 ` james.lawrence@epamail.epa.gov
  4 siblings, 0 replies; 17+ messages in thread

From: james.lawrence@epamail.epa.gov @ 1995-11-27 00:00 UTC (permalink / raw)

In article <49794h$pof@carbon.cudenver.edu> ghharrac@ouray.cudenver.edu (George/Jorge) writes:
>From: ghharrac@ouray.cudenver.edu (George/Jorge)
>Subject: VARCHAR, WHY THE 2000 Character Limit ??
>Date: 25 Nov 1995 14:27:29 GMT
>Summary: Why does VARCHAR have a limit of 2000 characters, is it SQL?
>Keywords: VARCHAR 2000 Data Types SQL Standards standard char character

>Has anyone wondered why varchar/varchar2 has this magical limit of 2000
>characters? Well I have, this limit seems to exist in two databases that
>I have used, namely ORACLE and INGRES. I have looked around for documentation
>to determine if this is a SQL standard but I haven't found anything.

>If you can help with this question please send me email, and post the answer.
>Often, its been my experience that developers want more than 2000 characters
>and the oly way to implement this is with raw data types where supported,
>of by busting their data up into 2000 character records and adding a 
>sequence number for reassembly.

>Answers, thoughts anyone ?

Maybe thoughts: They don't want to split the column across physical blocks? 
I understood the earlier limit of 255, they used a single byte to contain the 
length.  But 2000 only needs an additional 3 bits so why not take a whole 
nother byte and get 64k?

Oracle's long data type doesn't have to be raw so it can be used for what you 
want.  I'd be pretty cautious about sticking it in any table you may do 
full scans on though.  

Lawrence.....

>ghharrac@neodata.com
>ghharrac@ouray.cudenver.edu

> 









^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
@ 1995-11-29 00:00 ` Lucky Leavell <ris@iglou.com>
  1995-11-29 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? gainer@gainer.engr.sgi.com
  4 siblings, 1 reply; 17+ messages in thread

From: Lucky Leavell @ 1995-11-29 00:00 UTC (permalink / raw)

On Sat, 25 Nov 1995, George/Jorge wrote:

> Has anyone wondered why varchar/varchar2 has this magical limit of 2000
> characters? Well I have, this limit seems to exist in two databases that
> I have used, namely ORACLE and INGRES. I have looked around for documentation
> to determine if this is a SQL standard but I haven't found anything.
> 
>
I believe the Ingres limit comes from their use of a page size of 2048 
bytes.  Removing the 48 bytes used by the RDBMS engine, leaves 2000 
bytes. OpenIngres does add, among others, two new data types:

	long byte - up to 2 GB of binary data
	long varchar - up to 2GB of character data

Perhaps the next question is: why did Ingres use the 2K page size instead 
of, say, 4K or 8K? I don't know; ask Michael Stonebraker, et al.

Regards,
Lucky 

Lucky Leavell                            Phone: (812) 945-6555
Relational Information Systems, Inc.       FAX: (812) 949-9233
4727 Grant Line Road                     Email: ris@iglou.com (Internet)
New Albany, IN 47150-2013                       71534,2674 (CompuServe)
                                 WWW Home Page:  http://wl.iglou.com/ris







^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-29 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Lucky Leavell <ris@iglou.com>
@ 1995-11-29 00:00   ` gainer@gainer.engr.sgi.com
  1995-11-30 00:00     ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Keith McCammon <keithm@microrim.com>
  0 siblings, 1 reply; 17+ messages in thread

From: gainer@gainer.engr.sgi.com @ 1995-11-29 00:00 UTC (permalink / raw)

In article <Pine.SOL.3.91.951129105036.782A-100000@iglou>,
Lucky Leavell  <ris@iglou.com> wrote:
>On Sat, 25 Nov 1995, George/Jorge wrote:
>
>> Has anyone wondered why varchar/varchar2 has this magical limit of 2000
>> characters? Well I have, this limit seems to exist in two databases that
>> I have used, namely ORACLE and INGRES. I have looked around for documentation
>> to determine if this is a SQL standard but I haven't found anything.
>> 
>>
>I believe the Ingres limit comes from their use of a page size of 2048 
>bytes.  Removing the 48 bytes used by the RDBMS engine, leaves 2000 
>bytes. OpenIngres does add, among others, two new data types:
>
>	long byte - up to 2 GB of binary data
>	long varchar - up to 2GB of character data
>
>Perhaps the next question is: why did Ingres use the 2K page size instead 
>of, say, 4K or 8K? I don't know; ask Michael Stonebraker, et al.

I am curious about the "long byte" support you mentioned. To what degree are
long byte types available for use in SQL statements? What sorts of operations
may be performed on them? Are they very useful? Or are they there just to
check off the "BLOB support" box? 

Pat






^ permalink  raw  [nested|flat] 17+ messages in thread

* Re: VARCHAR, WHY THE 2000 Character Limit ??
  1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
  1995-11-29 00:00 ` Re: VARCHAR, WHY THE 2000 Character Limit ?? Lucky Leavell <ris@iglou.com>
  1995-11-29 00:00   ` Re: VARCHAR, WHY THE 2000 Character Limit ?? gainer@gainer.engr.sgi.com
@ 1995-11-30 00:00     ` Keith McCammon <keithm@microrim.com>
  0 siblings, 0 replies; 17+ messages in thread

From: Keith McCammon @ 1995-11-30 00:00 UTC (permalink / raw)

gainer@gainer.engr.sgi.com (Patrick Gainer) wrote:
sorts of operations
>may be performed on them? Are they very useful? Or are they there just to
>check off the "BLOB support" box? 
>
>Pat

Pat-

Most databases have client side BLOB query. I think DB2 might 
now have engine level BLOB query ability...I know Illustra has 
engine-level plug in Datablades for this...

Keith McCammon
keithm@microrim.com
http://www.microrim.com








^ permalink  raw  [nested|flat] 17+ messages in thread


end of thread, other threads:[~1995-12-11 00:00 UTC | newest]

Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1995-11-25 00:00 VARCHAR, WHY THE 2000 Character Limit ?? ghharrac@ouray.cudenver.edu
1995-11-26 00:00 ` Chris McDonald <Chris@macomp.demon.co.uk>
1995-11-26 00:00 ` Keith McCammon <keithm@microrim.com>
1995-11-27 00:00   ` kmill@hawkeyes.rchland.ibm.com
1995-11-28 00:00     ` kbradley@us.oracle.com
1995-11-30 00:00       ` james.lawrence@epamail.epa.gov
1995-12-11 00:00         ` kbradley@us.oracle.com
1995-11-29 00:00     ` enduser@enduser.com
1995-11-30 00:00   ` mal@winternet.com
1995-11-30 00:00     ` gainer@gainer.engr.sgi.com
1995-12-01 00:00       ` Keith McCammon <keithm@microrim.com>
1995-12-02 00:00       ` enduser@enduser.com
1995-11-27 00:00 ` pbrown@triplerock.CS.Berkeley.EDU
1995-11-27 00:00 ` james.lawrence@epamail.epa.gov
1995-11-29 00:00 ` Lucky Leavell <ris@iglou.com>
1995-11-29 00:00   ` gainer@gainer.engr.sgi.com
1995-11-30 00:00     ` Keith McCammon <keithm@microrim.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox