How Much Space is Required to Store a Whole Lot of Nothing?

29 04 2010

April 29, 2010

While looking for something completely unrelated I encountered one of the test cases that I posted to an OTN forum thread about a year ago.  There are a couple of interesting items in the test case, so I thought that I would reproduce it here, verifying the test case on Oracle 11.2.0.1.  The OTN forum thread asked if NULL values in table columns take up space, and if so does it depend on the data type of the column.  I answered the question like this:

It is my understanding that NULL values in the last columns (non-null values appear only in the first set of columns defined for the table) in of a row do not consume space, while NULL values in the first set of columns, if they are followed by columns containing non-NULL values will consume space. At this time, I am having trouble locating a reference which backs up my understanding of how NULLs affect storage space.

The test case was originally constructed to be executed against Oracle Database 10.2.0.4, and the output showed what was achieved with an ASSM autoallocate tablespace with an 8KB block size.  So, what happens when the test case is executed on Oracle Database 11.2.0.1 with an ASSM autoallocate tablespace with an 8KB block size?  The results may surprise you.

First, we need to create a table with a couple of different column data types, and then collect statistics for the table:

CREATE TABLE T1 (
  C1 VARCHAR2(300),
  C2 NUMBER(22,4),
  C3 DATE,
  C4 BLOB,
  C5 CLOB);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

Surprised by the results yet?

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
         0          0            0           0                   0

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

no rows selected

Probably not if you read about Oracle Database 11.2.0.1's deferred segment creation feature.  Yes, the above results differed from that of Oracle Database 10.2.0.4, which showed the following for the second SQL statement:

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

Next, insert 1,000,000 rows containing NULL values in all columns and re-gather the statistics:

INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

Now let's check the table's statistics:

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
   1000000       1630            0           1                   0

Oracle Database 10.2.0.4 was not quite as accurate when estimating the number of rows in the table:

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
    984619       1630            0           1                   0

Next let's take a look at the extent allocation, which was the same for both releases of Oracle Database:

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128
        27    1048576        128

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The above shows that the table is using 1630 blocks to store the rows with the completely NULL values, there are 1664 blocks allocated to extents used by the table, and 13,631,488 bytes used by the extents.  NULLs consuming space?

A little fun with the ROWID pseudocolumn, dissecting the components of that pseudocolumn (side note, I do not recall where I originally determined how to break apart the 18 byte long displayed ROWID, but it is found in the documentation):

SELECT
  SUBSTR(ROWID,1,6) OBJECT_ID,
  SUBSTR(ROWID,7,3) FILE_ID,
  SUBSTR(ROWID,10,6) BLOCK_ID,
  SUBSTR(ROWID,16,3) ROW_ID,
  LENGTHB(ROWID) LEN,
  COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN,
  SUM(LENGTH(ROWID)) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES
FROM
  T1
WHERE
  ROWNUM<=1400;

OBJECT FIL BLOCK_ ROW        LEN       ROWN      BYTES
------ --- ------ --- ---------- ---------- ----------
AAAULA AAH AAGLHT AAA         18          1         18
AAAULA AAH AAGLHT AAB         18          2         36
AAAULA AAH AAGLHT AAC         18          3         54
AAAULA AAH AAGLHT AAD         18          4         72
AAAULA AAH AAGLHT AAE         18          5         90
AAAULA AAH AAGLHT AAF         18          6        108
AAAULA AAH AAGLHT AAG         18          7        126
AAAULA AAH AAGLHT AAH         18          8        144
AAAULA AAH AAGLHT AAI         18          9        162
AAAULA AAH AAGLHT AAJ         18         10        180
AAAULA AAH AAGLHT AAK         18         11        198
AAAULA AAH AAGLHT AAL         18         12        216
AAAULA AAH AAGLHT AAM         18         13        234
AAAULA AAH AAGLHT AAN         18         14        252
AAAULA AAH AAGLHT AAO         18         15        270
AAAULA AAH AAGLHT AAP         18         16        288
AAAULA AAH AAGLHT AAQ         18         17        306
AAAULA AAH AAGLHT AAR         18         18        324
AAAULA AAH AAGLHT AAS         18         19        342
AAAULA AAH AAGLHT AAT         18         20        360
...
AAAULA AAH AAGLHT AKP         18        656      11808
AAAULA AAH AAGLHT AKQ         18        657      11826
AAAULA AAH AAGLHT AKR         18        658      11844
AAAULA AAH AAGLHT AKS         18        659      11862
AAAULA AAH AAGLHT AKT         18        660      11880
AAAULA AAH AAGLHU AAA         18          1         18
AAAULA AAH AAGLHU AAB         18          2         36
AAAULA AAH AAGLHU AAC         18          3         54
AAAULA AAH AAGLHU AAD         18          4         72
AAAULA AAH AAGLHU AAE         18          5         90
AAAULA AAH AAGLHU AAF         18          6        108
AAAULA AAH AAGLHU AAG         18          7        126
AAAULA AAH AAGLHU AAH         18          8        144
AAAULA AAH AAGLHU AAI         18          9        162
AAAULA AAH AAGLHU AAJ         18         10        180
AAAULA AAH AAGLHU AAK         18         11        198
AAAULA AAH AAGLHU AAL         18         12        216
AAAULA AAH AAGLHU AAM         18         13        234
AAAULA AAH AAGLHU AAN         18         14        252
...
AAAULA AAH AAGLHU AKP         18        656      11808
AAAULA AAH AAGLHU AKQ         18        657      11826
AAAULA AAH AAGLHU AKR         18        658      11844
AAAULA AAH AAGLHU AKS         18        659      11862
AAAULA AAH AAGLHU AKT         18        660      11880
AAAULA AAH AAGLHV AAA         18          1         18
AAAULA AAH AAGLHV AAB         18          2         36
AAAULA AAH AAGLHV AAC         18          3         54
AAAULA AAH AAGLHV AAD         18          4         72
AAAULA AAH AAGLHV AAE         18          5         90
AAAULA AAH AAGLHV AAF         18          6        108
AAAULA AAH AAGLHV AAG         18          7        126
AAAULA AAH AAGLHV AAH         18          8        144
AAAULA AAH AAGLHV AAI         18          9        162
AAAULA AAH AAGLHV AAJ         18         10        180
AAAULA AAH AAGLHV AAK         18         11        198
AAAULA AAH AAGLHV AAL         18         12        216
AAAULA AAH AAGLHV AAM         18         13        234
AAAULA AAH AAGLHV AAN         18         14        252
...
AAAULA AAH AAGLHV ABM         18         77       1386
AAAULA AAH AAGLHV ABN         18         78       1404
AAAULA AAH AAGLHV ABO         18         79       1422
AAAULA AAH AAGLHV ABP         18         80       1440

The above shows that Oracle was packing 660 of these completely NULL rows into each 8KB block with a default PCT_FREE of 10%, leaving about 7370 bytes of space for storing the 660 rows per block, indicating that each row was consuming about 11 bytes.  Obviously from the above, the actual ROWID for a row does not occupy a full 18 bytes of data storage per row.  It is actually 10 bytes (reference) that is externalized as a 18 byte (reference) base 64 encoded character string.  In retrospect, I probably should have done the following instead to decode the ROWID using the DBMS_ROWID package, and calculate the per row overhead plus the space required (1 byte) to store the data:

SELECT
  DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,
  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NUMBER,
  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW_ID,
  COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN,
  SUM(11) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES
FROM
  T1
WHERE
  ROWNUM<=1400;

 OBJECT_ID    FILE_ID BLOCK_NUMBER     ROW_ID       ROWN      BYTES

---------- ---------- ------------ ---------- ---------- ----------
     82624          7      1618387          0          1         11
     82624          7      1618387          1          2         22
     82624          7      1618387          2          3         33
     82624          7      1618387          3          4         44
     82624          7      1618387          4          5         55
     82624          7      1618387          5          6         66
     82624          7      1618387          6          7         77
     82624          7      1618387          7          8         88
     82624          7      1618387          8          9         99
     82624          7      1618387          9         10        110
     82624          7      1618387         10         11        121
     82624          7      1618387         11         12        132
     82624          7      1618387         12         13        143
     82624          7      1618387         13         14        154
     82624          7      1618387         14         15        165
...
     82624          7      1618387        655        656       7216
     82624          7      1618387        656        657       7227
     82624          7      1618387        657        658       7238
     82624          7      1618387        658        659       7249
     82624          7      1618387        659        660       7260
     82624          7      1618388          0          1         11
     82624          7      1618388          1          2         22
     82624          7      1618388          2          3         33
     82624          7      1618388          3          4         44
     82624          7      1618388          4          5         55
     82624          7      1618388          5          6         66
     82624          7      1618388          6          7         77
     82624          7      1618388          7          8         88
     82624          7      1618388          8          9         99
     82624          7      1618388          9         10        110
...
     82624          7      1618388        656        657       7227
     82624          7      1618388        657        658       7238
     82624          7      1618388        658        659       7249
     82624          7      1618388        659        660       7260
     82624          7      1618389          0          1         11
     82624          7      1618389          1          2         22
     82624          7      1618389          2          3         33
     82624          7      1618389          3          4         44
     82624          7      1618389          4          5         55
     82624          7      1618389          5          6         66
     82624          7      1618389          6          7         77
     82624          7      1618389          7          8         88
     82624          7      1618389          8          9         99
     82624          7      1618389          9         10        110
...
     82624          7      1618389         77         78        858
     82624          7      1618389         78         79        869
     82624          7      1618389         79         80        880

A comparison test with a second table containing a single column:

CREATE TABLE T2 (
  C1 VARCHAR2(1));

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

If the columns containing NULL values required storage space in table T1, then we should expect that table T2 should require less space than that for table T1.

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T2'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The 13,631,488 bytes and 1,664 blocks appears to be the same values as displayed for table T1.

For extra credit we might try something like this:

UPDATE
  T1
SET
  C2=1;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
   1000000       1630            0           4                   0

The average row length increased from 1 to 4 when the second column of the table was set to a value of 1 for all rows in the table - now the NULL values in column 1 require a single byte of storage space.

Feel free to experiment with this example.


Actions

Information

7 responses

29 04 2010
Jonathan Lewis

Charles,
The eleven bytes per row comes from the following data requirement for each row (when it’s completely null)

Row directory entry 2 bytes
Column count byte
Lock byte
Flag byte
Reserved 6 bytes for rowid that might be needed for row migration.

I don’t know exactly how Oracle tracks it, but it knows that the smallest possible row that can go into a block will be this 11 bytes – i.e. no data abd a forwarding rowid.

It’s interesting to dump one of your “empty” blocks and check the free space information:

fsbo=0x53a
fseo=0x17e4
avsp=0x332
tosp=0x332

Note how there's 0x12AA bytes between the beginning and end of free space, but the block still records only 0x332 bytes since (decimal) 660 * 6 bytes have been reserved for forwarding rowids.

The row entries look like this:

tab 0, row 23, @0x1ce8
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 24, @0x1ceb
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 25, @0x1cee
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 26, @0x1cf1
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0

Interestingly, looking at the complete block dump for the first couple of blocks, there seems to be an interesting "array insert" which operates around a limit of 255 rows or the end of block - whichever is smaller.

Regards
Jonathan Lewis

29 04 2010
Charles Hooper

Jonathan,

Thanks for suggesting the block dump – I think that I now understand what you are showing with your block dumps. I agree that it is interesting. I see slightly different results, but that might be a symptom of ASSM. If I understand the output that you posted correctly, each of the rows in the block is occupying only 3 bytes – even though Oracle considered the blocks filled as if each row would occupy 11 bytes. That would also explain why, when I set the second column of each row to a value of 1, the number of blocks did not increase. If I am calculating correctly, that should have increased the row length by at least 2 bytes, and if it was just a 2 byte increase, that would be (11 as we previously determined + 2) * 660 = 8,580 bytes – which of course will not fit into an 8KB block.

After recreating the table, this is what one of the above SQL statements showed:

 OBJECT_ID    FILE_ID BLOCK_NUMBER     ROW_ID       ROWN      BYTES
---------- ---------- ------------ ---------- ---------- ----------
     82729          7      1618387          0          1         11
     82729          7      1618387          1          2         22
     82729          7      1618387          2          3         33
     82729          7      1618387          3          4         44
     82729          7      1618387          4          5         55
     82729          7      1618387          5          6         66
     82729          7      1618387          6          7         77
     82729          7      1618387          7          8         88
     82729          7      1618387          8          9         99
     82729          7      1618387          9         10        110
...

Dropping and recreating the table put the table into the same set of blocks. Oddly, it seemed that I had to drop/purge the table, bounce the database, and then recreate the table so that the trace file showed the correct information - if I did not bounce the database I saw the block information from the dropped/purged table.)

To dump the blocks, I executed the following:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'D2BLOCK_1618387';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1618387;
 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'D2BLOCK_1618388';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1618388;
 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'D2BLOCK_1618389';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1618389;
 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'D2BLOCK_1618390';
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 1618390;

This is what I see in the trace file:

fsbo=0x53a
fseo=0x17dc
avsp=0x32a
tosp=0x32a
tab 0, row 23, @0x1f50
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 24, @0x1f4d
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 25, @0x1f4a
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 26, @0x1f47
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0

The above seems to show that the addresses are in descending order, while your output shows ascending order (this might be an ASSM characteristic).

17dc - 53a = 12A2 = 4770
1f50 - 1f4d = 3

--

This is from the first block dump that I performed before dropping/purging table T1, after column C2 was set to 1:

fsbo=0x53a
fseo=0x6ab
avsp=0x32a
tosp=0x32a
tab 0, row 23, @0x1ef0
tl: 7 fb: --H-FL-- lb: 0x2  cc: 2
col  0: *NULL*
col  1: [ 2]  c1 02
tab 0, row 24, @0x1ee9
tl: 7 fb: --H-FL-- lb: 0x2  cc: 2
col  0: *NULL*
col  1: [ 2]  c1 02
tab 0, row 25, @0x1ee2
tl: 7 fb: --H-FL-- lb: 0x2  cc: 2
col  0: *NULL*
col  1: [ 2]  c1 02
tab 0, row 26, @0x1edb
tl: 7 fb: --H-FL-- lb: 0x2  cc: 2
col  0: *NULL*
col  1: [ 2]  c1 02

6ab - 53a = HEX171 = 369
1ef0 - 1ee9 = 7

Unless I am misunderstanding the above, each row grew by 4 bytes, and with 660 rows per block, that is an increase of 1,980 bytes per block. That seems to imply that there should still be about 2,790 bytes unused in the block, not 369, when the second column was set to a value of 1.

30 04 2010
Jonathan Lewis

Charles,

Interesting – when I repeated the experiment with ASSM, the insertion pattern changed: the “apparent 255 array size” disappeared and the physical placement of the rows in the blocks was reversed as you can see comparing these two extracts of row 23 to 26, one from non-ASSM, the other from ASSM:

tab 0, row 23, @0x1ce8
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 24, @0x1ceb
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 25, @0x1cee
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 26, @0x1cf1
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0


tab 0, row 23, @0x1f50
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 24, @0x1f4d
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 25, @0x1f4a
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0
tab 0, row 26, @0x1f47
tl: 3 fb: --H-FL-- lb: 0x1  cc: 0

Note how the location in block (@0xNNNN) increases as the row directory entry increases in the original non-ASSM, but decreases in the ASSM.

Regarding the space:
a) You multiplied by 3, not 4: you used an extra 2,640 bytes, not 1,980.
b) The four bytes are:

one byte to say that column one has no data
one byte to say that column two has two bytes
two bytes for the column two data

You can afford to add another two bytes to every row before the actual block usage expands - for example set c1 = 'XX', this will change the "length byte" for C1 from 0 to 2, and insert two bytes of data.

The apparent inconsistencies around fseo (end of free space), fsbo (beginning of free space), avsp (available space), and tosp (total space) relate to the way updates are done, how the space is reused, and the problems of other transactions sharing the blocks.

The most significant detail in your case is that there is a free space area between the row stack (end of block) and the block header - this is definded by fsbo and fseo. When you update your rows, they get bigger, so the new version has to be copied into the free space area leaving a little hole behind. THe holes account for a lot of space which (to you) is part of the total space and the available space - but Oracle doesn't tidy up the block until it really has to, so all you see is that there is lots of space available but the gap between fseo and fsbo is getting small.

(I wrote a short note about heap block compression a little while ago that makes some noises about the timing of housekeeping: http://jonathanlewis.wordpress.com/2010/03/30/heap-block-compress/ )

Your final comment in the other comment about rowids and the lack of clarity in the documents and metalink notes is appropriate. Bottom line - if I am a row in a block, I know which object I belong to, which file I'm in, and which block I'm in (all these are written just once on the block) so the only space "rowid" takes up is the two bytes that is my entry in the "row directory" at the top of the block. If other people need to make a note of where to find me then there are lots of variations. I'll have to write a blog note to see if I can list them all - I bet I miss a couple the first time around !

30 04 2010
Charles Hooper

Jonathan,

I appreciate the time that you spent providing the above comments.

For your point a) above – that was a good catch. I stated that the difference was 4 bytes, but apparently typed 3 into the calculator when performing the calculation. I recall that something did not look correct about the result, but it was getting to be late at night, so I did not double-check the post as well as I should have.

The link that you provided above, as well as the comments, are very helpful. I am looking forward to your future blog note.

29 04 2010
Charles Hooper

Jonathan,

I appreciate your comments – there is always something new to learn about Oracle Database, and your contributions certainly help make the journey of learning about the database behavior interesting. I still need quite a bit of practice with reading block dumps.

Your description of the reason why each row requires 11 bytes makes a lot of sense, but I have encountered conflicting information – where the documentation seems to conflict with itself. My article above stated that each ROWID required 10 bytes (the documentation reference was provided in the article), but with the additional information that you stated are stored with each row, it would not be possible to place 660 of the test rows into a single block as happened in my test case.

The journey through the documentation:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT1130
“Rowids of Row Pieces
A rowid is effectively a 10-byte physical address of a row. As explained in “Rowid Data Types”, every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.”

-

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i3855
“You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the rowids of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns: values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to store pertinent column data.”

Note that the above two quotes seem to conflict with each other.

-

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#918
“A physical rowid datatype has one of two formats:
* The extended rowid format supports tablespace-relative data block addresses and efficiently identifies rows in partitioned tables and indexes as well as nonpartitioned tables and indexes. Tables and indexes created by an Oracle8i (or higher) server always have extended rowids.
* A restricted rowid format is also available for backward compatibility with applications developed with Oracle7 or earlier releases.”

-

http://blog.tanelpoder.com/2008/10/21/transportable-tablespaces-and-rowid-uniqueness/
“It’s a well known fact that the old Oracle7 style restricted rowids (which contained only File#, block# and row#) may not be unique in Oracle8+ databases which can have 1022 datafiles per tablespace not per database as previously. That’s why the 10-byte extended rowids were introduced, which also included the data object ID of a segment inside the rowid.

Note that with local indexes and non-partitioned tables the rowids stored in indexes are old 6-byte restricted rowids. They have 4 bytes for data block address consisting of 10bits for file# and 22bits for block#. The other 2 bytes specify the row# in block.”

-

Metalink Doc ID 10640.1 – Extent and Block Space Calculation and Usage in Oracle Databases:
“Once the combined column length of an average index entry has been calculated,
the total average entry size can be calculated, using the formula:

bytes per entry = entry header + ROWID length + F + V + D
where:
entry header = 1 byte

ROWID length = 6 bytes

F = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)

V = total length bytes of all columns with 3 byte column
lengths (VARCHAR2 and RAW datatypes)

D = combined data space of all columns (from above)

Here again it applies that there would be one byte for columns
with an actual length between 1 and 250. Then for lengths from
251 to 64K, we use 3 bytes (0xFE marker byte, 2 bytes of actual length).
Note 231214.1 describes this aspect more accurately.

For example, given that D is calculated to be 22 bytes (from the table
calculations above), and that the index is comprised of three CHAR columns, the
total average entry size of the index is:

bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes
= 32 bytes”

The above Metalink article points back to Metalink Doc ID 231214.1 as the ultimate source, and that Doc ID covers Oracle Database 7.3.

Here is a small test case that creates a column of type ROWID:

CREATE TABLE T2 (
  C1 VARCHAR2(300),
  C2 NUMBER(22,4),
  C3 DATE,
  C4 BLOB,
  C5 CLOB,
  C6 ROWID);
 
INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;
 
INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;
 
COMMIT;
 
UPDATE
  T2
SET
  C6=ROWID;
 
COMMIT;

This output is from 11.1.0.7:

SELECT
  C6,
  DUMP(C6),
  DUMP(ROWID)
FROM
  T2
WHERE
  ROWNUM=1;
 
C6                 DUMP(C6)                                  DUMP(ROWID)
------------------ ----------------------------------------- -----------------------------------------
AAAWOIAAFAAFCvzAAA Typ=69 Len=10: 0,1,99,136,1,84,43,243,0,0 Typ=69 Len=10: 0,1,99,136,1,84,43,243,0,0

It could very well be the case that I am misunderstanding...

---

OK, I just realized what is happening with my original logic. There is no point in the full ROWID being recorded with each row in a block as described in the Metalink article (and also in this article) - the full ROWID is needed to find the specific datafile and block within that datafile, but for the row entry in the block we would only need to identify the row within the block.

Doc bug, Metalink bug.

Thanks Jonathan. I need to do a bit more research about what you posted here.

9 05 2010
Rowid « Oracle Scratchpad

[...] — Jonathan Lewis @ 7:47 pm UTC May 9,2010 In a recent discussion in the comments of a blog item by Charles Hooper, I made a comment about how hard it is to be accurate (and unambiguous) when talking about the [...]

21 11 2013
Le ROWID et la place qu’il prend, par Jonathan Lewis | Oracle – Concepts et Exemples

[…] une récente discussion sur le blog un article de Charles Hooper , j’ai fait un commentaire disant qu’il est difficile d’être précis et […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: