Organization of common tables and Indexes

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

(1)
Heap-organized table does not store rows in any special order. The create table statement creates a heap-organized table by default.

(2)
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.

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 particle 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 orders ments 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 orders ments 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 special 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
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 orders folders. users often ask for the folders for all employees in a participant 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
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 already CED
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
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
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 Pseudo column allows building secondary indexes.	Logical rowid in rowid pseudo docolumn allows building secondary indexes.
Individual rows may be accessed directly by rowid.	Access to individual rows may be achieved indirectly by primary key.
Sequential Full table scan returns all rows in some order.	A Full index scan or fast full index scan returns all rows in some order.
Can be stored in Table cluster with other tables.	Cannot be stored in a table cluster.
Can contain a column of the long data type and columns Lob data types.	Can contain lob columns but not long columns.
Can contain Virtual columns (only relational heap tables are supported ).	Cannot contain virtual columns.

 

Figure 3-3 analyze strates the structure of an index-organized partition table. the leaf blocks contain the rows of the table, ordered sequentially by primary key. for example, the first value in the first leaf block shows a department ID of 20, Department
Name of marketing, manager ID of 201, and location ID of 1800.

Figure 3-3 index-organized table

Description of "Figure 3-3 Index-Organized Table"

An index-organized table stores all data in the same structure and does not need to store the rowid. As shown in

Figure 3-3, leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key:

20, Marketing, ,,1800

30, Purchasing, 114,1700

 

Leaf block 2 in an index-organized table might contain entries as follows:

50, Shipping, 121,1500

60, IT, 103,1400

 

A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:

1. Block 1

2. Block 2

 

To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized tables table segment contains rows as follows:

50, Shipping, 121,1500

20, Marketing, ,,1800

 

Block 2 contains rows for the same table as follows:

30, Purchasing, 114,1700

60, IT, 103,1400

 

A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is

Rowid:

20, AAAPeXAAFAAAAAyAAD

30, AAAPeXAAFAAAAAyAAA

50, AAAPeXAAFAAAAAyAAC

60, AAAPeXAAFAAAAAyAAB

 

A scan of the table rows in primary key order reads the table segment blocks in the following sequence:

1. Block 1

2. Block 2

3. Block 1

4. Block 2

Thus, the number of block I/OS in this example is double the number in the index-organized example.

 

See also:

·
"Table Organization"

·
"Introduction to Logical Storage Structures"

 

Index-Organized Tables with Row Overflow Area

When creating an index-organized table, you can specify a separate segment as a row overflow area. in index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful.
In contrast, B-tree entries are usually small because they consist of the key and rowid.

 

If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:

(1) The index entry

This part contains column values for all the primary key columns, a physical rowid that points to the overflow part of the row, and Optionally a few of the non-key columns. this part is stored in the index segment.

 

(2) The overflow part

This part contains column values for the remaining non-key columns. This part is stored in the overflow storage area segment.

 

 

Secondary indexes on index-organized tables

A secondary index is an index on an index-organized table. in a sense, it is an index on an index. the secondary index is an independent schema object and is stored separately from the index-organized table.

 

As explained in
"Rowid Data Types", Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded representation of the table primary key. the logical rowid length depends on the primary key length.

 

Rows in index leaf blocks can move within or between blocks because of insertions. Rows in index-organized tables do not migrate as heap-organized rows do (see

"Chained and Migrated Rows"). Because rows in index-organized tables do not have permanent physical addresses, the database uses logical rowbased IDs on primary key.

 

For example, assume that the specified ments table is index-organized. The location_id column stores the ID of each department. The table stores rows as follows, with the last value as the location ID:

10, Administration

20, marketing, ,,1800

30, purchasing, 114,1700

40, human resources, 203,2400

 

A secondary index on the location_id column might have index entries as follows, where the value following the comma is the logical rowid:

1700, * bafajqocwr/+

1700, * bafajqocwqv +

1800, * bafajqocwrx +

2400, * bafajqocwsn +

 

Secondary indexes provide fast and efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key. for example, a query of the names of specified ments whose ID is greater than 1700 cocould use
The secondary index to speed data access.

 

Logical rowids and physical guesses

Secondary indexes use the logical rowids to locate table rows. A logical rowid between des a physical guess, which is the physical rowid of the index entry when it was first made. oracle Database can use physical guesses to probe directly into the leaf
Block of the index-organized table, bypassing the primary key search. When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale.

 

For a heap-organized table, access by a secondary index involves a scan of the secondary index and an additional I/O to fetch

Data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:

1. Without physical guesses, access involves two index scans: a scan of the secondary index followed by a scan of the primary key index.

2. With physical guesses, access depends on their accuracy:

(1) With accurate physical guesses, access involves a secondary index scan and an additional I/O to fetch the data block containing the row.

(2) With inaccurate physical guesses, access involves a secondary index scan and an I/O to fetch the wrong data block (as indicated by the guess ), followed by an index unique scan of the index organized table by primary key value.

 

Bitmap Indexes on Index-Organized Tables

A secondary index on an index-organized table can be
Bitmap index. As explained in
"Bitmap indexes", a bitmap index stores a bitmap for each index key.

When Bitmap indexes exist on an index-organized table, all the bitmap indexes use a heap-organized mapping table. the mapping table stores the logical rowids of the index-organized table. each mapping table row stores one logical rowid for the corresponding
Index-organized table row.

 

The database accesses a bitmap index using a search key. If the database finds the key, then the bitmap entry is converted to a physical rowid.

With heap-organized tables, the database uses the physical rowid to access the base table.

With Index-organized tables, the database uses the physical rowid to access the mapping table, which in turn yields a logical rowid that the database uses to access the index-organized table.

Figure 3-4 analyze strates index access for a query of the departments_iot table.

 

Figure 3-4 bitmap index on index-organized table