Explain about Oracle Database Data Block ? | Interview Q&A
Published in
4 min readJan 29, 2022
Here we are going to understand about database blocks in oracle
Data blocks:
- All the data in the Oracle database is stored In blocks in both discs and memory.
- Oracle database blocks subject is important to understand well. Because Oracle does not store a whole table in one block and return it from there directly.
- A block is the smallest unit of the database for storing the data.
- It is a logical unit that consists of multiple Operating System blocks.
- There can be millions or billions of blocks and they store the actual data in it.
- A block can have a whole table or a couple of rows of a table. Or sometimes, a block can have rows of different tables When multiple tables are clustered.
- A block has a specific size and cannot be extended directly.
- A block can have a size from 2KB to 32KB but generally, it is set to 8KB by default.
- This size is specified by the DBAs on the database installation. also , 8KB is not a small size.
- It can have hundreds or maybe thousands of rows based on the size of your row data of course. Because depending on the row size of your tables, your rows can be a couple of bytes or even a couple of bits.
Block header:
- A block consists of the block header and the rows.
- A block header includes the information about block type information, whether it is a row block or an index block, and the information of tables that have the rows in it, and the row directory, means the addresses of each row in this block.
- It is a logical block and each row will be stored in real blocks of memory, and these blocks have specific addresses.
- Logical block and each row will be stored in real blocks of memory, and these blocks have specific addresses.
ROWID:
- It is an address where we can directly go to the exact location of that row and read it easily.
- A block header has lots of data, so it has about 100 bytes of data generally. The rest of the block has rows and some free spaces.
- There are a lot of rows and spaces after the rows and large spaces after the total rows.
- These white areas next to the rows and at the bottom of all rows represents space here.
- These spaces are important. Because, if you make an update and increase the size of a row, it will be costly to take this row and carry it to another place in that block or maybe to another block.
- Oracle leaves some space after each row. So if the size of this row increases, it simply writes it into the same place.
- This will improve the performance so much. But sometimes the new row size can exceed the total size of the row and the space after it. If such a thing happens, if there is enough space in the block, this row is deleted from here and written into the big space area.
- But if there is not enough space in the block, this time it is written into another block.
- It is generally like so, but when you are creating a table, you can use PCTFREE or PCTUSE parameters to specify how much free space will be left in a block.
- So you can change these free space sizes or you can say that does not leave any space, just use all the space.
- But if you don’t leave any free space in a block, each update will most probably change the place of the row, and this will decrease the performance. Because the IO operations will increase significantly and IO means cost in tuning.
- When Oracle tries to find your row, it first finds the block that your row is in and then goes to the address of that row using the rowid.
- A row also has some additional data in it. A row area in a block does not consist of only the column data.
- It has some additional data like row overhead, several columns in this row, cluster key id if it is clustered,rowid of that column, column lengths and the column values.
- As can be seen from the schema, in the row header area, it has the row overhead first, then the number of columns means how many columns are there in that row, then if this row is a part of a cluster, its cluster id, and then the rowid of that row.
- After the row header, the column data comes. For each column in this row.
- It has the length of that column and then the column value. So each column is stored right after the previous one.
- But before each column, it has its length. The length is to show how many bytes will be read for that column.
- Because the reader process needs to know where to start and where to end.
- It cannot know which one is data. With this kind of algorithm, the server can read the data without any confusion.
- It is the way of reading data in the Oracle database.
Conclusion:
- As you can see, Oracle stores not only the exact data but also some additional information about the rows.
- Because it uses this information in so many places to increase the performance.
