AW: Maximum row length in bytes (9.2.0.5)
- From: "Stefan Jahnke" <Stefan.Jahnke@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Apr 2004 16:43:58 +0200
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@xxxxxxxxxxxxx
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@xxxxxx
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@xxxxxxx]
Gesendet: Friday, April 23, 2004 12:16 AM
An: oracle-l@xxxxxxxxxxxxx
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » AW: Maximum row length in bytes (9.2.0.5)
