| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> AW: Maximum row length in bytes (9.2.0.5)
Hi Tanel
Thank you for your detailed answer. I thought there was an explicit = limit for the row length in bytes in Oracle. But it looks like the only = limit is roughly the size for the biggest (in bytes) data type = multiplied by the maximum number of columns per table plus some = overhead. I wasn't able to find additional information on that topic.
Thanks,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Tel.: +49 201/45 13-298
Fax: +49 201/45 13-144
mailto:stefan.jahnke_at_nospam.bov.de
Please remove nospam to contact me via email.
http://www.bov.de
Abonnieren Sie unseren Newsletter: http://www.bov.de/enews
Optimieren Sie durch Portale Ihr taegliches Geschaeft: Wie Anwendungen = in einem dynamischen Prozesskontext Ihre Informationsprozesse deutlich = beschleunigen koennen, erfahren Sie von mailto:martin.ostrowski_at_bov.de
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht = unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde = bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz = die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und = Aeusserungen ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be = copied or manipulated by third parties. For this reason we would ask for = your understanding that, for your own protection and ours, we must = decline all legal responsibility for the validity of the statements and = comments given above.
-----Urspr=A8=B9ngliche Nachricht-----
Von: Tanel P?der [mailto:tanel.poder.003_at_mail.ee]
Gesendet: Friday, April 23, 2004 12:16 AM
An: oracle-l_at_freelists.org
Betreff: Re: Maximum row length in bytes (9.2.0.5)
> hi,Stefan Jahnke
>
> do not use lobs , one table may have 1000 columns , varchar2=3D
> ,4000*1000 =3D3D 4000000 bytes ?
> >Maybe I'm blind, but I couldn't find any information on the size=3D
> limit =3D3D
> >of a single row. Does anybody know to what maximum number of=3D
> bytes a row =3D3D
> >can grow in length? Does the usage of "inline" (B|C)LOBs effect=3D
> that =3D3D
> >limit somehow?
Hi!
You can have 1000 columns in a table starting from 8.0.
You can have your last column as LONG.
This means 999 columns storing varchar2(4000) for example + one LONG col
storing 2GB.
Every char column having size larger than 250 bytes will require 3 bytes =
for
specifying column length.
Since there is only one byte for column count in internal row structure, =
row
chaining (even into the same block, if space permits) is used to get
additional column count bytes for the row - so the row is practically =
split
into 3 pieces.
Every row piece is internally like a different row, so every one has a
3-byte header (row flag, lock byte and column count), but also in case =
of
chained row, every piece except the last one have additional 6 bytes for
storing the next piece's address (4 bytes for DBA, 2 bytes for row# =
inside
the block).
Also, since this large row definitely wouldn't fit into a single block,
normal chaining because of block space lack is needed anyway, so a 4MB =
row
would be split into hundreds of pieces anyway, so many additional =
chaining
bytes are needed in beginning of row pieces.
I don't know that much of LONG internals, there might be some issues as
well, but nevertheless, this formula of calculating the max row size is =
very
complex and depends on many things (even more than I've mentioned here).
If you have a "enable storage in row" type LOB larger than 3964 bytes, =
it
won't be stored in the row anymore, it goes automatically to LOB segment
then.
Why exactly 3964? - 3964 bytes LOB data + 20 bytes lob locator + 16 =
bytes
lob inode =3D 4000 bytes and 4000 bytes seems to be some kind of =
internal limi
tation in Oracle data interface.
Tanel.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Apr 26 2004 - 09:41:56 CDT
 |
 |