| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Maximum row length in bytes (9.2.0.5)
> 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.
-- 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 Thu Apr 22 2004 - 17:13:33 CDT
 |
 |