Storage of over 253 internal data columns in Oracle

Source: Internet
Author: User

Table creation statement
Declare
V_ SQL varchar2 (32767 );
Begin
V_ SQL: = 'create table test (';
For I in 1 .. 500 loop
V_ SQL: = v_ SQL | 'name' | I | 'varchar2 (2000 ),';
End loop;
V_ SQL: = substr (v_ SQL, 1, length (v_ SQL)-1 );
V_ SQL: = v_ SQL | ');';
Dbms_output.put_line (v_ SQL );
End;
Copy, paste, and create a table

_ Dex @ DAVID> desc test
Name Null? Type
-------------------------------------------------------------------------------------------------
NAME1 VARCHAR2 (2000)
NAME2 VARCHAR2 (2000)
NAME3 VARCHAR2 (2000)
.....
NAME500 VARCHAR2 (2000)

_ Dex @ DAVID> insert into test (name500) values (lpad (1,300, 'D '));

1 row created.


_ Dex @ DAVID> select
2 length (t. name500 ),
3 dbms_rowid.rowid_relative_fno (t. rowid) as "FNO #",
4 dbms_rowid.rowid_block_number (t. rowid) as "BLK #",
5 dbms_rowid.rowid_row_number (t. rowid) as "ROW #"
6 from dex. test t
7/

LENGTH (T. NAME500) FNO # BLK # ROW #
-----------------------------------------------
300 4 925 1
1 4 925 3
300 4 925 5


View with bbed

First, we can see that row # is 1 and the length is 300.
BBED> set DBAs 4,925
DBA 0x0100039d (16778141 4,925)

BBED> p kdbr
Sb2 kdbr [0] @ 142 7504
Sb2 kdbr [1] @ 144 7250
Sb2 kdbr [2] @ 146 6991
Sb2 kdbr [3] @ 148 6737
Sb2 kdbr [4] @ 150 6177
Sb2 kdbr [5] @ 152 5923

BBED> p * kdbr [1]
Rowdata [1327]
-------------
Ub1 rowdata [1327] @ 7374 0x28

BBED> dump/v offset 7374 count 128
File:/u01/apps/Oracle/oradata/david/users01.dbf (4)
Block: 925 Offsets: 7374 to 7501 Dba: 0x0100039d
-------------------------------------------------------
2801f501 00039d00 ffffff l (. Large .............
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................

<16 bytes per line>

These are row piece headers.
2801f501 00039d00

1-byte flag = 28
Hexadecimal 28 = binary 00101000 = -- H-F --- = head of rowpiece + first data piece
1 byte lb (itl slot) = 01
1-byte cc column count = f5 = 245 (because the last name500 column is not empty, it must be represented by ff for null, that is, 500 columns of data need to be stored)

4-byte dba
01 00039d = use dbms_utility.DATA_BLOCK_ADDRESS_FILE and dbms_utility.DATA_BLOCK_ADDRESS_BLOCK to query files and block numbers.
Details:
Select dbms_utility.DATA_BLOCK_ADDRESS_FILE (to_number ('0100039d', 'xxxxxxxxxx') file #,
Dbms_utility.DATA_BLOCK_ADDRESS_BLOCK (to_number ('0100039d', 'xxxxxxxxxx') block #
From dual;

_ Sys @ DAVID>/

FILE # BLOCK #
--------------------
4 925
That is, datafile 4 block 925 is in this block.

2-byte row #
00 00
Kdbr [0]
The rest is the ff that represents null.

BBED> p * kdbr [1]
Rowdata [1327]
-------------
Ub1 rowdata [1327] @ 7374 0x28

BBED> p * kdbr [0]
Rowdata [1581]
-------------
Ub1 rowdata [1581] @ 7628 0x04

7628-7374 = 254 bytes (from offset7374 to 7628, excluding 7628)
Where
9-byte row header
Therefore, the space for storing row data is 254-9 = 245 bytes.
Because 245 columns have null values and the last column is not empty, all null values are represented by ff (one byte), consuming a total of 245 bytes.

Let's take a look at its last data piece.

BBED> p * kdbr [0]
Rowdata [1581]
-------------
Ub1 rowdata [1581] @ 7628 0x04


BBED> dump/v offset 7628 count 1000
File:/u01/apps/oracle/oradata/david/users01.dbf (4)
Block: 925 Offsets: 7628 to 8191 Dba: 0x0100039d
-------------------------------------------------------
0401 ffff ffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Fffe2c01 64646464 64646464 64646464 l. t,. dddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646431 l ddddddddddddddd1
01060aa4 l... Enabled

These are row piece headers.
0401ff
Where
1-byte flag = 04
Hexadecimal 4 = binary 00000100 = ----- L -- = last data piece
1 byte lb (itl slot) = 01
1-byte cc column count = ff = 255 (because the last name500 column is not empty, ff is required for null)

In summary, when the number of columns exceeds 253, the row will be split into multiple data piece Storage (similar to the row chain ), each time, we still use the column count of 1 byte to indicate the number of columns.

In addition, the data type of the last column is varchar2 (2000)
Here, we use 3 bytes to store its length.
Fe2c01
How to calculate it?
The last two bytes are used for representation.
Details:
The last 1 byte indicates the number of 256. For example, if the value is 01, the value is 256*1 = 256.
Plus the number of the first byte 2c = 44
256 + 44 = 300
It is estimated that fe is a computing identifier. Because no relevant information is found, it can only be confirmed by experiments.

Similarly,
253 = fefd00
255 = feff00
256 = fe0001
257 = fe0101
258 = fe0201
259 = fe0301

510 = fefe01
512 = fe0002
513 = fe0102


2000 = fed007

After continuous lab tests
When the length is greater than 250
It will use three bytes similar
Fe fb00 indicates the length.
The length is 250 and below.
Indicates the length in one byte.


Fe is the length bit, and the last two digits are the length values. Because it is little endian, the last two bytes need to be reversed.


510 = fefe01
512 = fe0002
513 = fe0102

Actually, it is in hexadecimal format.
510 = 01fe
512 = 0200
513 = 0201

Thank you for your answers.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.