Heap-Organized table 和 Index-Organized table 說明

 

官網的兩個串連如下：

 

Tables and Table Clusters

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/tablecls.htm#i20438

 

Indexes and Index-Organized Tables

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#CBBFIFAB

 

 

 

這2個文章講的比較詳細，在這裡我將一些內容粘貼出來。

 

 

You can create a relational table with the following organizational characteristics:

       （1）A heap-organized table does not store rows in any particular order. The CREATE TABLE statement creates a heap-organized table by default.

       （2）An index-organized table orders rows according to the primary key values. For some applications, index-organized tables enhance performance and use disk space more efficiently. See "Overview of Index-Organized Tables".

 

 

 

Rowid Data Types

       Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address (rowid) of every row in the database. Rowids fall into the following categories:

       （1）Physical rowids store the addresses of rows in heap-organized tables, table clusters, and table and index partitions.

       （2）Logical rowids store the addresses of rows in index-organized tables.

       （3）Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.

 

       A data type called the universal rowid, or UROWID, supports all kinds of rowids.

 

 

Use of Rowids

       Oracle Database uses rowids internally for the construction of indexes. A B-tree index, which is the most common type, contains an ordered list of keys divided into ranges. Each key is associated with a rowid that points to the associated row's address for fast access. End users and application developers can also use rowids for several important functions:

       （1）Rowids are the fastest means of accessing particular rows.

       （2）Rowids provide the ability to see how a table is organized.

       （3）Rowids are unique identifiers for rows in a given table.

 

       You can also create tables with columns defined using the ROWID data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on.

 

 

Overview of Table Clusters

       A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables. For example, a block can store rows from both the employees and departments tables rather than from only a single table.

       The cluster key is the column or columns that the clustered tables have in common. For example, the employees and departments tables share the department_id column. You specify the cluster key when creating the table cluster and when creating every table added to the table cluster.

       The cluster key value is the value of the cluster key columns for a particular set of rows. All data that contains the same cluster key value, such as department_id=20, is physically stored together. Each cluster key value is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.

      

       For an analogy, suppose an HR manager has two book cases: one with boxes of employees folders and the other with boxes of departments folders. Users often ask for the folders for all employees in a particular department. To make retrieval easier, the manager rearranges all the boxes in a single book case. She divides the boxes by department ID. Thus, all folders for employees in department 20 and the folder for department 20 itself are in one box; the folders for employees in department 100 and the folder for department 100 are in a different box, and so on.

 

       You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:

       （1）Disk I/O is reduced for joins of clustered tables.

       （2）Access time improves for joins of clustered tables.

       （3）Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.

 

Typically, clustering tables is not appropriate in the following situations:

       （1）The tables are frequently updated.

       （2）The tables frequently require a full table scan.

       （3）The tables require truncating.

 

 

Overview of Index-Organized Tables

       An index-organized table is a table stored in a variation of a B-tree index structure.

       In a heap-organized table, rows are inserted where they fit.

       In an index-organized table, rows are stored in an index defined on the primary key for the table.

 

       Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

 

       For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, and so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf location of the next box in the sequence.

 

       Folders containing employee records are stored in each box. The folders are sorted by employee ID. Employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. The folder for employee 101 is on top of 100, 102 is on top of 101, and so on until box 1 is full. The next folder in the sequence is at the bottom of box 2.

      

       In this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain a separate index. Suppose a user requests the records for employees 107, 120, and 122. Instead of searching an index in one step and retrieving the folders in a separate step, the manager can search the folders in sequential order and retrieve each folder as found.

 

       Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O.

       For example, the salary of employee 100 is stored in the index row itself. Also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/Os. Another benefit is the avoidance of the space overhead of a separate primary key index.

      

       Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial (see "Overview of Oracle Spatial"), and OLAP applications (see "OLAP").

 

 

Index-Organized Table Characteristics

       The database system performs all operations on index-organized tables by manipulating the B-tree index structure. Table 3-4 summarizes the differences between index-organized tables and heap-organized tables.

 

Table 3-4 Comparison of Heap-Organized Tables with Index-Organized Tables

Heap-Organized Table	Index-Organized Table
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.	Primary key uniquely identifies a row. Primary key constraint must be defined.
Physical rowid in ROWID pseudocolumn