Re: Maximum row length in bytes (9.2.0.5)
- From: "Tanel P?der" <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Apr 2004 01:16:05 +0300
> hi,Stefan Jahnke
>
> do not use lobs , one table may have 1000 columns , varchar2=
> ,4000*1000 =3D 4000000 bytes ?
> >Maybe I'm blind, but I couldn't find any information on the size=
> limit =3D
> >of a single row. Does anybody know to what maximum number of=
> bytes a row =3D
> >can grow in length? Does the usage of "inline" (B|C)LOBs effect=
> that =3D
> >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 = 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
-----------------------------------------------------------------
Other related posts:
