Differences between Oracle and DB2 data storage modes

The knowledge about Oracle and DB2 data storage modes is what we will introduce in this article. Let's take a look at this part. "There is no sequence for storing data in Oracle normal tables, that is, heap tables. Oracle indexes organize tables to store the data in the table based on the primary key order ."
I remember the first time I learned about this feature of Oracle, I was so anxious that it was a disruption to the database world view. Realize that the two mainstream RDBMS can have such a big difference. For Oracle, the data storage of most tables is unordered. For DB2, the data storage of most tables is sorted by Cluster Indxe. That is to say, most of the tables in DB2 belong to the index organization table according to Oracle classification rules.
The only exception to DB2 is that the table does not have an Index-as long as an Index is not explicitly specified as a Cluster Index, DB2 tries its best to store table data in the key order of this index.
"For normal tables, Oracle ensures that the physical address ROWID of the data will not change after the data is inserted into the table. Of course, except for operations that explicitly change the table data location after moving the table or modifying the partition key value of the partition table after the enable row movement. That is to say, normal addition, deletion, and modification will not change the physical address of the existing record.
Even if the length of the record changes, the current data block cannot accommodate this record, Oracle will leave a ROWID in the original position, the ROWID information can be used to locate the new location of the record. This is the implementation of row migration and row link. Although additional IO is added, the ROWID is not changed. "
This is the so-called Position Update, that is, normal Update does not change the physical location of the record. Of course, there are exceptions:
1. If the table partition to which the record belongs changes, the record must be moved to the physical file corresponding to the target partition, And the location change is inevitable;
2. The record itself is a Variable Length record. Here the Variable Length refers to the "physical Variable Length", not only the record containing the Variable Length field (Variable Length, it also refers to the record with the table attribute "compress yes" (because the data compress of DB2 z is row compress). When the record is updated with a longer length, the physical length may change, which is usually shortened to no problem, position Update can still be implemented. However, if it increases, it is possible that the original physical location does not have enough space to store the increasing records. Therefore, the record can only find a suitable space for security, the original physical location stores a pointer pointing to the new location (of course, the pointer itself must be very short, the original location is enough to store), which is called Overflow.
That is, the original physical location pointed by ROWID is a pointer, And the pointer points to a new location (or it may also point to another pointer, but it will eventually point to the actual physical location of the record, to form a long Pointer Chain, of course, this situation will cause more damage to performance ).
"We can see that the MOVE mentioned above and some partition operations that change the ROWID will make the index unavailable while making the ROWID change ."
The problem arises. How does the ROWID change cause the index to be unavailable? In DB2, the physical location or ROWID of a record changes, and the corresponding Index Entry changes accordingly. In other words, if an update involves changes to the index key columns, the update contains at least two parts, namely the update of the table and the update of the index.
"Now there is a problem. For indexed organizational tables, to ensure that data storage is performed in the primary key order, you must adjust the data location in the table at any time based on the data addition, deletion, and modification, which makes the condition that the ROWID does not change. For the index organization table, the second index requires a method to locate the specific location of the table data. Therefore, the logic ROWID is available ."
The technical differences are reflected here. For the index organization table, Oracle strictly ensures that the data storage is arranged in the order of indexes, that is, when the record is modified, the record position is adjusted at the front end. DB2, however, does not. DB2 tries its best to ensure that data is arranged in the order of indexes (clustering), but not strictly and forcibly, if records cannot be stored in the optimal location (the ideal location for sorting by index), they can be stored in the nearest secondary location or farther away from the optimal location. As more records are modified, the Cluster Ratio (Cluster Ratio) is less efficient. Therefore, REORG utility (REORG utility) is required, that is, DB2 uses REORG at the backend to adjust the record location. Therefore, REORG is more important than Oracle in DB2.
However, in the world of DB2, the second Index, or Secondary Index, is not a Clustered Index ), the record's physical location is still found through the record's ROWID. There is no concept of logical ROWID. However, for an index with good Cluster efficiency (Cluster Ratio = 100), the relationship between entries pointing to the data page through ROWID on the index Leaf page is like combing, ordered (as shown in index IX above ). The relationship between entries from non-clustered indexes and the table data page is out of order (the index IX2 is shown below ). Even if the data is restructured, the table records are reordered by the clustered index. The Cluster Ratio of the top index is 100, instead of making a qualitative change to the Cluster Ratio of non-clustered indexes below.

 
"Although the storage location of the index organization table may change frequently, the primary key must exist. If the record cannot be searched by physical location, you can also find the record by using the primary key. However, the implementation of Oracle is not that simple. In addition to the table's primary key information, the logical ROWID also contains the physical address information of the record during index creation. For more information about the logical ROWID belief structure, see http://yangtingkun.itpub.net/post/468/11363.and this address information.
If the physical guess can find this record in the target data block, the efficiency is the same as that of the physical ROWID. Only one IO is required to find the target. If the corresponding record cannot be found through physical guesses, Oracle can only locate this record through the primary key information contained in the logical ROWID through primary key scan. Based on the index height, this operation may consume several more IO operations. "
For DB2, the storage location of the record is usually not easy to change, and update is mainly Position Update, although this is a type of damage to the cluster rules, however, DB2 relies on the back-end REORG for repair, in exchange for the benefit of recording the performance of modifications on the front-end. Both clustered indexes and non-clustered indexes use ROWID to directly locate the physical location of the record. Therefore, it is always a physical ROWID without the concept of a logical ROWID. According to the citation, the logical ROWID of the Oracle secondary index contains the physical location recorded during index creation.
However, when the record is updated multiple times, the probability that this logical ROWID can hit will be significantly reduced, and the Primary Key (Primary Key) has to be used) the information is then moved back to the Cluster Index to locate the location of the data record. There are two issues worth noting:
1. In order to maintain cluster rules, Oracle has poor performance at the front end of record modification;
2. Even so, the cluster rules will still be damaged, and the hit rate of the logical ROWID is low. Instead, I/O operations must be performed several times, that is, the non-clustered index is used to round back the clustered index. Therefore, Oracle has a higher dependence on Clustered indexes.
Conclusion:
DB2 always uses ROWID to locate the physical location of the record. Both clustered indexes and non-clustered indexes are the same. Oracle uses the ROWID of clustered indexes to locate the physical location of the record, the ROWID of a non-clustered index also contains the primary key information to use the clustered index, but the "physical guess" is used as a shortcut, that is, the physical location of the expected record does not change after the non-clustered index is created.
It can be seen that most of the table data stored in DB2 is sorted by index, while the storage of Oracle table data is unordered (what a huge difference ). There are some restrictions on the application of such indexes to organize tables (for example, it is more suitable for read-only tables, and so on), and the update performance will be poor.
This article introduces the differences between Oracle and DB2 data storage modes. I hope this introduction will help you gain some benefits!