loading
hello dummmy text

In Oracle database, data BLOCK is defined as the smallest storage unit in the data files. But, there are many more concepts run around the BLOCK architecture. One of them is to understand if a BLOCK can accommodate rows from distinct tables. In this article, we are going to arrive at the justifiable answer with a simple case study. It could be pretty easy to find the answer if we are able to trace out the BLOCK_ID of every row that we insert into tables.

Having said that, we are not going to deal in detail with PCT_FREE, PCT_USED and much other space-related concepts of a data BLOCK. In an earlier Orskl blog, we addressed “How to find block sizes of all Oracle Database files?” and this article will add other concepts related to data BLOCK.

Can you accommodate rows of distinct tables into a data BLOCK? #Oracle #Database Click To Tweet

Case Study:

System considerations – Oracle database 11gR2 on Oracle Enterprise Linux.

Let us quickly start with our case study, where we

1. Create a table in USERS tablespace -> insert few rows -> identify the BLOCK ID of these rows.

2. Create another table into same USERS tablespace -> insert few rows -> identify the BLOCK ID of these rows.

Exercise:

1. Creating table TAB1 in USERS tablespace

SQL> create table tab1 (c1 number,c2 varchar2(10)) tablespace users;
Table created.

SQL> insert into tab1 values(&c1,’&c2′); — When prompted dump some values and repeat to insert few rows.

/

SQL> commit;

Commit complete.

SQL> select * from tab1;

      C1 C2

————– ————-

      1  AB

      2  BC

      3  CD

2. The way to retrieve the BLOCK ID of the rows is from the ROW ID of each row in the table.

SQL> select rowid, c1,c2 from tab1;

ROWID                       C1 C2

——————————————- ———- ———-

AAASw1AAEAAAACXAAA          1  AB

AAASw1AAEAAAACXAAB          2  BC

AAASw1AAEAAAACXAAC          3  CD

3. ROW ID’s can be decrypted to the BLOCK ID’s using the Oracle defined package “DBMS_ROWID”.

SQL> select dbms_rowid.rowid_block_number(rowid) “Block No”,c1,c2 from tab1;

Block No     C1 C2

—————- ———- ———-

151          1  AB

151          2  BC

151          3  CD

As we note, all the rows are into the same BLOCK 151 as the size of each row is not more than default block size 8KB.

Take away point: A block accommodates multiple rows of a table.

4. Now create a second table TAB2 in the same tablespace and insert few rows.

SQL> create table tab2 (c1 number,c2 varchar2(10)) tablespace users;

Table created.

SQL> insert into tab2 values(&c1,’&c2′);

SQL> commit;

Commit complete.

SQL> select * from tab2;

      C1 C2

————– ————-

      1  GB

      5  TH

      6  UY

5. Check the ROW ID’s and the BLOCK ID of the rows in the table TAB2

SQL> select dbms_rowid.rowid_block_number(rowid) “Block No”,c1,c2 from tab2;

Block No     C1 C2

——————– ——- ——-

159          1  GB

159          5  TH

159          6  UY

Take away point: Clearly shows that a new block (159) has been allocated and not the block (151) for the rows in the table TAB2.

True that data BLOCK in #Oracle #Database cannot accommodate rows of distinct tables. Click To Tweet

Related references:

Oracle documentation explains storage BLOCK architecture with the help of below diagram.

23detail

Of which, “Table directory” says that ‘This portion of the data block contains information about the table having rows in this block.’

This is often misunderstood by many professionals that a “Table Directory” of a data BLOCK will have details of all the tables of rows that BLOCK accommodates.

Conclusion:

  • Oracle data BLOCK can accommodate rows of only one table at any given point.
  • What if I create multiple tables (1 billion) with one row of small size? It leads to lot of free space in each block allocated to each table.
  • So the actual space occupied is not the same as the sum of the data blocks allocated to the system.
  • You will have to always difference the FREE space in each data block to get the actual storage utilization.

You agree or have a different opinion – I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

11 Comment

    1. Pawan Kumar Yaddanapudi

      Thanks for sharing this Martin. Useful to know that table clusters allow rows of distinct tables to a single data block.

  1. pushpjeet cholkar

    Hi Pawan,
    Excellent explanation by example.

    1. Pawan Kumar Yaddanapudi

      Thanks Pushpjeet.

  2. Vinay Malla

    Thanks for the clarification Pawan. Need some more articles 🙂 🙂

    Regards
    Vinay

    1. Pawan Kumar Yaddanapudi

      Will do my best!!!

  3. Will huge Consistent Reads floods BUFFER CACHE? |

  4. jai

    one block at a time can be in one segment only.because blocks is allocated in the form of extents and one extent also in one segment at a time then how is this possible one block can have rows of multiple tables??
    I think this question itself is conceptually wrong.Isn’t it??

    1. Pawan Kumar Yaddanapudi

      Hi Jai,

      You are probably right, when you look through the concepts from LOGICAL structure of Oracle database architecture. But there are techies who would sit at rows level and relate to physical blocks. This makes much sense to those who looks at Oracle database rows through blocks in the data file.

  5. Donald Burleson

    Your conclusion is incorrect.

    A single data block may contains rows from multiple tables when using multi-table clustering

    1. Pawan Kumar Yaddanapudi

      Thanks Burleson 🙂

      This blog was not for clustered tables. And yes, you are right that data blocks contains rows from multiple clustered tables.

Write a Reply or Comment

Your email address will not be published.

Knowledge That Can Always Keep Your Inbox Informed