Chunking tables 3: working with blocks

So far we’ve decided to access a table chunk by chunk, each chunk containing one twelfth of the tables’s blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?

Current list of posts:

Basics first

As a reminder:

  • Oracle stores table data in blocks (of size 8K by default)
  • Oracle allocates logically contiguous blocks in extents
  • All the extents for a data object (non-partitioned table, partition or subpartition) belong to a segment
  • A ROWID returns the address of a row, which is based on the data object number, the relative file number, the block id and the position of the row in the block

There is no direct way to access data by block id, but there is a way to access data by ROWID. If we know what blocks interest us, and if we can calculate the ROWIDs of the interesting blocks, then we should be able to access the data we want.

ROWID ranges

Let’s assume for a moment we can determine the interesting blocks and generate ROWIDs from them. How would we query the table based on those ROWIDs? By using ROWID ranges. Would that query be efficient? Let’s see…

I’m going to create a table that, in my environment, is going to have one extent of 8 8K blocks. The first 3 blocks are “bitmap blocks” for ASSM and the segment header block. There will be 10 rows per block in the remaining 5 blocks, for a total of 50 rows.

create table t as
select rpad('x', 710, 'x') x
from dual
connect by level <= 50;

select relative_fno, block_id,
count(*) num_rows,
min(rid) min_rowid, max(rid) max_rowid
from (
  select rowid rid,
  dbms_rowid.rowid_relative_fno(rowid) relative_fno,
  dbms_rowid.rowid_block_number(rowid) block_id
  from t
)
group by relative_fno, block_id
order by relative_fno, block_id;
RELATIVE_FNO BLOCK_ID NUM_ROWS MIN_ROWID MAX_ROWID
7 131 10 AAAgz5AAHAAAACDA AAAgz5AAHAAAACDJ
7 132 10 AAAgz5AAHAAAACEA AAAgz5AAHAAAACEJ
7 133 10 AAAgz5AAHAAAACFA AAAgz5AAHAAAACFJ
7 134 10 AAAgz5AAHAAAACGA AAAgz5AAHAAAACGJ
7 135 10 AAAgz5AAHAAAACHA AAAgz5AAHAAAACHJ

 

If we access the whole table, we will get the 5 blocks with rows in them, plus 2 blocks of overhead:

SQL> select /*+ gather_plan_statistics */ count(*) from t;

  COUNT(*)
----------
        50

SQL> select * from table(dbms_xplan.display_cursor(
  null,null,'IOSTATS LAST -ROWS -PREDICATE -NOTE'
));

----------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      1 |00:00:00.01 |       7 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| T    |      1 |     50 |00:00:00.01 |       7 |
----------------------------------------------------------------------------

Now let’s access just the rows in block 133:

SQL> select /*+ gather_plan_statistics */ count(*) from t
where rowid between chartorowid('AAAgz5AAHAAAACFAAA')
  and chartorowid('AAAgz5AAHAAAACFAAJ');

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor(
  null,null,'IOSTATS LAST -ROWS -PREDICATE -NOTE'
)); 

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY ROWID RANGE| T    |      1 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------

We now see the operation “Table access by ROWID range”, which (outside of the 2 blocks of overhead) accessed the one and only block containing the data we wanted.

This is great news. If we can just identify the blocks we want and generate ROWIDs from them, we will be able to access each chunk very efficiently.

Identifying blocks

At last, an easy part! Just query DBA_EXTENTS.

select relative_fno, block_id, blocks
from dba_extents
where (owner, segment_name) = ((user, 'T'));
RELATIVE_FNO BLOCK_ID BLOCKS
7 128 8

 

The blocks we need go from 128 through 128+8-1, or 135.

Generating the ROWIDs

Again we’re in luck: the package DBMS_ROWID has a ROWID_CREATE function. It asks us for:

  • rowid_type: trust me, it’s 1 since Oracle 8
  • object_number: this is the data object number, which we’ll get from DBA_OBJECTS
  • relative_fno: we have this from DBA_EXTENTS
  • block_number: we have this from DBA_EXTENTS
  • row_number: we make this 0 at the beginning of a block and 32767 at the end of a block.
    (I copied 32767 from Bryn.)

Putting it all together

Once we take partitions and subpartitions into account, we can join DBA_EXTENTS to DBA_OBJECTS to get all the raw material we need for creating ROWIDs.

select o.data_object_id, e.file_id, e.block_id, e.blocks
from dba_extents e
join all_objects o
on e.owner = o.owner
  and e.segment_name = o.object_name
  and e.segment_type = o.object_type
  and decode(e.partition_name, o.subobject_name, 0, 1) = 0
where e.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
  and e.owner = user
  and e.segment_name = 'T';
DATA_OBJECT_ID FILE_ID BLOCK_ID BLOCKS
134393 7 128 8

 

Sanity check

Let’s generate ROWIDs from the information we got here and see if we get all the rows in table T.

select count(*) from t
where rowid between dbms_rowid.rowid_create(1,134393,7,128,0)
                and dbms_rowid.rowid_create(1,134393,7,135,32767);

The answer is 50, as expected.

In the next post, we’ll start chunking!

Current list of posts: 

Advertisements

3 thoughts on “Chunking tables 3: working with blocks

  1. Is there a chance that you pasted the same values under min_rowid and max_rowid by mistake?

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