A ROWID is a method of identifying the physical location of a row in an Oracle Database. An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.
A ROWID (called an Extended ROWID) consists of 4 components :
- DataObject Number
- DataFile Number Relative to the Tablespace
- DataBlock Number (within the DataFile)
- RowNumber within the DataBlock
A Partitioned Table actually consists of multiple segments. Each segment has a different DataObject Number.
Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :
The Normal Table :
SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;
Table created.
SQL>
SQL> insert into NONPARTITIONED
2 values (1, dbms_random.string('X',999));
1 row created.
SQL> insert into NONPARTITIONED
2 values (2,'This is the second row');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from NONPARTITIONED
3 order by id_col
4 /
ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA 1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA 2 This is the second row
SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /
ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81652 32 147
2 81652 32 151
SQL>
Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block. Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652). Is this really the Object ID ?
SQL> l
1 create table PARTITIONED (id_col number, data_col varchar2(1000))
2 partition by range (id_col)
3 (
4 partition P_1 values less than (2) tablespace HEMANT_DATA,
5 partition P_2 values less than (3) tablespace HEMANT_DATA,
6 partition P_3 values less than (4) tablespace HEMANT_DATA,
7 partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
8 )
9* tablespace HEMANT_DATA
SQL> /
Table created.
SQL>
SQL> insert into PARTITIONED
2 values (1, dbms_random.string('X',999));
1 row created.
SQL> insert into PARTITIONED
2 values (2,'This is the second row');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from PARTITIONED
3 order by id_col
4 /
ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA 1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA 2 This is the second row
SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from PARTITIONED
6* order by id_col
SQL> /
ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81659 31 631
2 81660 31 1655
SQL>
In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.
(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)
In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the Segment, not the Object Number of the Table.
Thus, to verify the Segments of the two tables, I can query and check :
SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /
OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81652
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662
6 rows selected.
SQL>
Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.
But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.
Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.
The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID (81659 and 81660).
We know that when we rebuild a Table, the ROWID changes. But this is actually because a new Segment is allocated.
Thus, if I were to do a MOVE of the "Normal" Table :
SQL> alter table NONPARTITIONED move;
Table altered.
SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /
ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81663 32 155
2 81663 32 156
SQL>
SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /
OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81663
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662
6 rows selected.
SQL>
Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.
For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald
No comments:
Post a Comment