Oracle and DB2, Comparison and Compatibility/Storage Model/Physical Storage/Summary

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Summary[edit | edit source]

Both Oracle and IBM have the same fundamental construct that is stored on disk, fetched by the DBMS and held in the database buffer pool. IBM calls this a Page, whereas Oracle calls it a block. Both have a fixed header structure, but the contents are different. In IBM the header stores information about the location and size of free space in the page. In Oracle, the header stores directory information for tables and rows. In Oracle, data in the block fills the block from the bottom up, essentially squeezing the free space in the block between the data at the bottom, and directory information at the top. There is a fixed header overhead in both Oracle and IBM, but in Oracle part of the header are the table and row directories, which grow downward from the header – essentially the fixed and variable overhead of Oracle means the header is variable. In IBM, the header is fixed, and the information about where in the page data is located is stored in the ID Map which grows from the bottom of the page upward. A new 2 byte entry is made in the ID Map for each element in the page. The IBM page header keeps track of free space in the page, but as the page fills from the top down, the bottom of the table is comprised of contiguous free space, with the ID Map beneath this. When an insert of a row is attempted into a page (either an update of an existing row, or a new row) and there is no space available, DB2 will de-fragment the page to make more space. If this is still insufficient, the whole row is copied to another page and a pointer is left in the original position to this new page. In Oracle, when an update or insert does not have enough space, the row can be split into row pieces and the row pieces chained across blocks to overcome this. In Oracle, data and indexes can be mixed in a block, whereas in DB2 data and indexes have to be in their own page.

While it can be seen that there is a common unit of currency between disk and memory (be it a block or a page), that is fetched and written as a whole, and looks the same in both places, information is located in different places in each. The maps or directories to elements in a block or a page are in different places, and blocks and pages are filled differently with data (top down or bottom up).

Even though these constructs are structurally different, they do the same thing. The differences are largely academic since the DBMS is responsible for the writing, organization and management of blocks or pages, all the DBMS user knows is that in each case the block or page is read from disk into memory where its constituent parts (data and indexes) are available to their applications. It is useful however to understand these differences when considering compatibility, since they have ramifications for such techniques as data clustering. Clustering is the same thing in each DBMS (row re-sequencing and index clustering, for example), but the implementation is just different enough that this valuable facility is not completely transparent. This will be covered later once we have covered the similarities and differences in table structures, index structures and datatypes in each DBMS.