-1

Does a RDBMS store the row data one after another in a data block? Once data block is full, is the new block assigned continuous?

Per my understanding when table is created, DBMS vendor must be keeping the some number of contiguous blocks reserved for a table. Once that chunk of blocks is full, another set of contiguous blocks must be assigned to that table. Isn't it? It will help the DBMS to efficiently read the range scan.

So to me looks like answer to first question is certainly yes. For second question it is yes till it has blocks available.

I have got some idea from Oracle which is more or less similar to my understanding

1
1

Step 1: Read about BTrees. Better yet, read about the B+Tree variant, since that is what InnoDB uses. (I recommend Wikipedia.)

The data is ordered by the PRIMARY KEY in one B+Tree.

Each secondary key is ordered by that key in another B+Tree, with the leaf nodes containing the Primary Key column(s) so that it can reach over into the data B+Tree to get the whole record.

Yes, there are blocks, but no they are not necessarily consecutive. Instead, the B+Tree has a mechanism to provide a "tree" to find any particular block and for finding the 'next' and 'previous' blocks.

Within a 16KB block is several "rows", plus block overhead, row overhead, and column overhead. A block contains info for only one table (or one secondary index).

When any operation is performed on a column in a row, the following steps occur:

  1. Fetch the entire block from disk. Often, the block is already cached in the "buffer_pool", so this step is fast.
  2. Locate the row in the block. Or the place in the block where the PRIMARY KEY says it should be.
  3. Depending on the operation:
    • For SELECT, fetch the desired column(s).
    • For INSERT, the row is inserted. If this makes the block too big, then a "block split" occurs.
    • For DELETE, the row is deleted. This could lead to merging two blocks together.
    • For UPDATE, a new copy of the row is made. If it is bigger or smaller, then the above steps may happen.
  4. The block is marked as "dirty". Eventually, it will be written back to disk.

I left out transactional 'ACID', index details, etc.

Caveat: What I have said applies to MySQL's InnoDB. And it does not apply to FULLTEXT or SPATIAL indexes. Other vendors do other things.

5
  • Rick my basic question is how data is stored for a table . I believe block reserved for a table will store data only for that table not for other table. Is that right ? – user3198603 Feb 24 '20 at 9:23
  • @user3198603 - Correct. A 16KB block will contain data for only one table. (More specifically for either data or secondary index for that table.) Disk allocation is a bunch of compromises... 16KB is bigger than the OS "block" size; tiny tables "waste" a lot of space; tables are segregated; indexes are segregated; some blocks are prematurely allocated; blocks are not (in general) released back to the OS; etc. – Rick James Feb 24 '20 at 19:19
  • Thanks. One related point is even If i have to update one specific field of row/column, first I have to read complete block containing that row and write back the complete content to that block. Right ? – user3198603 Feb 25 '20 at 1:09
  • @user3198603 - Correct. See what I added. – Rick James Feb 25 '20 at 5:28
  • @user3198603 - If you want to drill down some narrower topic, start a new Question. This one is threatening to get out of hand. – Rick James Feb 25 '20 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.