Single block i/o and Multi block i/o

Whenever we are doing single block i/o on a table which is having index, the optimizer only has to traverse through the root—-branch—leaf and it will do a random i/o on the block address which has been received through the index rowed.

So, suppose we have 1 million blocks and we need only 4 rows details, single block i/o will only have to traverser 4 blocks (3 index+ 1 data block) and hence it will be very effective.

Since the data in the block are distributed randomly and not sequentially in order, we need to index to find out the orderness on the data (clustering factor)

But, think on scenarios where we need to get 25% of the million block (250k blocks), then this approach had to iterate through all those 250k lacs rows and it will be very bad.

In this case, we need the optimizer to do a multi block i/o, which will not do a random i/o rather it will do a sequential i/o of multiple block (128) at a time and will be much faster.

What if your table size is in millions of block and multi terabytes, in that cases even a full table scan will be very costly, so what we are doing to do in such huge vldb cases?

In that case we need to partition the table and use full partition scan.

 515 total views,  1 views today