|Oracle9i Database Concepts
Release 1 (9.0.1)
Part Number A88856-02
This chapter discusses the different types of database objects contained in a user's schema. It includes:
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:
Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:
Schema objects are logical data storage structures. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles. For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle allocates for the object within the tablespace's datafiles.
There is no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces.
Figure 11-1 illustrates the relationship among objects, tablespaces, and datafiles.
Tables are the basic unit of data storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name (such as
EMP) and set of columns. You give each column a column name (such as
EMPNO, ENAME, and
VARCHAR2, DATE, or
DATE. If columns are of the
NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
You can specify rules for each column of a table. These rules are called integrity constraints. One example is a
NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.
After you create a table, insert rows of data using SQL statements. Table data can then be queried, deleted, or updated using SQL.
Figure 11-2 shows a sample table named
When you create a table, Oracle automatically allocates a data segment in a tablespace to hold the table's future data. You can control the allocation of space for a table's data segment and use of this reserved space in the following ways:
PCTUSEDparameters for the data segment.
Oracle stores data for a clustered table in the data segment created for the cluster instead of in a data segment in a tablespace. Storage parameters cannot be specified when a clustered table is created or altered. The storage parameters set for the cluster always control the storage of all tables in the cluster.
The tablespace that contains a nonclustered table's data segment is either the table owner's default tablespace or a tablespace specifically named in the
CREATE TABLE statement.
Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.
When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block.If the row fits in the block, users do not see a hit in I/O performance, because there is no extra I/O operation required to retrieve the rest of the row.
Each row piece, chained or unchained, contains a row header and data for all or some of the row's columns. Individual columns can also span row pieces and, consequently, data blocks. Figure 11-3 shows the format of a row piece:
The row header precedes the data and contains information about:
A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.
To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length.
Clustered rows contain the same information as nonclustered rows. In addition, they contain information that references the cluster key to which they belong.
The rowid identifies each row piece by its location or address. Once assigned, a given row piece retains its rowid until the corresponding row is deleted or exported and imported using the Export and Import utilities. For clustered tables, if the cluster key values of a row change, then the row keeps the same rowid but also gets an additional pointer rowid for the new values.
Because rowids are constant for the lifetime of a row piece, it is useful to reference rowids in SQL statements such as
SELECT, UPDATE, and
The column order is the same for all rows in a given table. Columns are usually stored in the order in which they were listed in the
CREATE TABLE statement, but this is not guaranteed. For example, if you create a table with a column of datatype
LONG, then Oracle always stores this column last. Also, if a table is altered so that a new column is added, then the new column becomes the last column stored.
In general, try to place columns that frequently contain nulls last so that rows take less space. Note, though, that if the table you are creating includes a
LONG column as well, then the benefits of placing frequently null columns last are lost.
A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero. A column allows nulls unless a
NOT NULL or
PRIMARY KEY integrity constraint has been defined for the column, in which case no row can be inserted without a value for that column.
Nulls are stored in the database if they fall between columns with data values. In these cases they require 1 byte to store the length of the column (zero).
Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, no information is stored for those columns. In tables with many columns, the columns more likely to contain nulls should be defined last to conserve disk space.
Most comparisons between nulls and other values are by definition neither true nor false, but unknown. To identify nulls in SQL, use the
IS NULL predicate. Use the SQL function
NVL to convert nulls to non-null values.
Nulls are not indexed, except when the cluster key column value is null or the index is a bitmap index.
You can assign a default value to a column of a table so that when a new row is inserted and a value for the column is omitted or keyword
DEFAULT is supplied, a default value is supplied automatically. Default column values work as though an
INSERT statement actually specifies the default value.
The datatype of the default literal or expression must match or be convertible to the column datatype.
If a default value is not explicitly defined for a column, then the default for the column is implicitly set to
Integrity constraint checking occurs after the row with a default value is inserted. For example, in Figure 11-4, a row is inserted into the
EMP table that does not include a value for the employee's department number. Because no value is supplied for the department number, Oracle inserts the
DEPTNO column's default value of 20. After inserting the default value, Oracle checks the
FOREIGN KEY integrity constraint defined on the
Chapter 23, "Data Integrity" for more information about integrity constraints
Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Indexes can be partitioned in similar fashion. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
You can create a table with a column whose datatype is another table. That is, tables can be nested within other tables as values in a column. The Oracle server stores nested table data out of line from the rows of the parent table, using a store table that is associated with the nested table column. The parent row contains a unique set identifier value associated with a nested table instance.
In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.
CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The
LOCK statement has no effect on a temporary table, because each session has its own private data.
TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.
DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance crash.
You can create indexes for temporary tables using the
CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.
You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
The Export and Import utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the
ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data.
Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first
CREATE TABLE AS SELECT) is performed. This means that if a
SELECT, UPDATE, or
DELETE is performed before the first
INSERT, then the table appears to be empty.
You can perform DDL statements
(ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an
INSERT is performed on it. The session gets unbound by a
TRUNCATE, at session termination, or by doing a
ABORT for a transaction-specific temporary table.
Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
Transaction-specific temporary tables are accessible by user transactions and their child transactions. However, a given transaction-specific temporary table cannot be used concurrently by two transactions in the same session, although it can be used by transactions in different sessions.
If a user transaction does an
INSERT into the temporary table, then none of its child transactions can use the temporary table afterward.
If a child transaction does an
INSERT into the temporary table, then at the end of the child transaction, the data associated with the temporary table goes away. After that, either the user transaction or any other child transaction can access the temporary table.
Oracle9i, Release 1 (9.0.1), provides a way to access data in external sources as if it were in a table in the database. You can connect to the database and create metadata for the external table, using DDL. The DDL for an external table consists of two parts: one part that describes the Oracle column types, another part (the access parameters) which describes the mapping of the external data to the Oracle data columns.
An external table does not describe any data that is stored in the database. Nor does it describe how data is stored in the external source. Instead, it describes how the external table layer needs to present the data to the server. It is the responsibility of the access driver and the external table layer to do the necessary transformations required on the data in the data file so that it matches the external table definition.
External tables are read-only; therefore, no DML operations are possible, and no index can be created on them. You can load Oracle data into flat files and publish it. You can also use external tables to export data in parallel.
When the database server needs to access data in an external source, it calls the appropriate access driver to get the data from an external source in a form that the database server expects. Oracle9i, Release 1 (9.0.1), provides a default access driver that satisfies most requirements for accessing data in files.
It is important to remember that the description of the data in the data source is separate from the definition of the external table. The source file can contain more or fewer fields than there are columns in the table. Also, the datatypes for fields in the data source can be different from the columns in the table. The access driver takes care of ensuring the data from the data source is processed so that it matches the definition of the external table.
The main use for external tables is to use them as a row source for loading data into a "real" table in the database. After you create an external table, you can then use a
CREATE TABLE AS SELECT or INSERT INTO ... AS SELECT statement, using the external table as the source of the
When you access the external table through a SQL statement, the fields of the external table can be used just like any other field in a "normal" table. In particular, you can use the fields as arguments for any SQL built-in function, PL/SQL function, or Java function. This lets you manipulate data from the external source. For data warehousing, you can do more sophisticated transformations in this way than you can with simple datatype conversions. You can also use this mechanism in data warehousing to do data cleansing.
While external tables cannot contain a column object, constructor functions can be used to build a column object from attributes in the external table
Once the metadata for an external table is created, you can query the external data directly and in parallel, using SQL. As a result, the external table acts as a view, which lets you run any SQL query against external data without loading the external data into the database.
The degree of parallel access to an external table is specified using standard parallel hints and with the parallel clause. Using parallelism on an external table allows for concurrent access to the data files that comprise an external table. Whether a single file is accessed concurrently or not is dependent upon the access driver implementation, and attributes of the data file(s) being accessed (for example, record formats or seekable media).
A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
For example, the
EMP table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access.
Figure 11-5 shows an example of a view called
STAFF derived from the base table
EMP. Notice that the view shows only five of the columns in the base table.
Because views are derived from tables, they have many similarities. For example, you can define views with up to 1000 columns, just like a table. You can query views, and with some restrictions you can update, insert into, and delete from views. All operations performed on a view actually affect data in some base table of the view and are subject to the integrity constraints and triggers of the base tables.
Unlike a table, a view is not allocated any storage space, nor does a view actually contain data. Rather, a view is defined by a query that extracts or derives data from the tables that the view references. These tables are called base tables. Base tables can in turn be actual tables or can be views themselves (including materialized views). Because a view is based on other objects, a view requires no storage other than storage for the definition of the view (the stored query) in the data dictionary.
Views provide a means to present a different representation of the data that resides within the base tables. Views are very powerful because they let you tailor the presentation of data to different types of users. Views are often used to:
For example, Figure 11-5 shows how the
STAFF view does not show the
COMM columns of the base table
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.
For example, views allow users to select information from multiple tables without actually knowing how to perform a join.
For example, the columns of a view can be renamed without affecting the tables on which the view is based.
For example, if a view's defining query references three columns of a four column table, and a fifth column is added to the table, then the view's definition is not affected, and all applications using the view are not affected.
For example, a view can be defined that joins a
GROUP BY view with a table, or a view can be defined that joins a
UNION view with a table.
For example, a query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.
Oracle9i SQL Reference for information about the
Oracle stores a view's definition in the data dictionary as the text of the query that defines the view. When you reference a view in a SQL statement, Oracle:
Oracle parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, you obtain the benefit of reduced memory usage associated with shared SQL when you use views.
When Oracle evaluates views containing string literals or SQL functions that have Globalization Support parameters as arguments (such as
Oracle9i Globalization Support Guide for information about Globalization Support
Oracle determines whether to use indexes for a query against a view by transforming the original query when merging it with the view's defining query.
Consider the following view:
CREATE VIEW emp_view AS SELECT empno, ename, sal, loc FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.deptno = 10;
Now consider the following user-issued query:
The final query constructed by Oracle is:
SELECT ename FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.deptno = 10 AND emp.empno = 9876;
In all possible cases, Oracle merges a query against a view with the view's defining query and those of any underlying views. Oracle optimizes the merged query as if you issued the query without referencing the views. Therefore, Oracle can use indexes on any referenced base table columns, whether the columns are referenced in the view definition or in the user query against the view.
In some cases, Oracle cannot merge the view definition with the user-issued query. In such cases, Oracle may not use all indexes on referenced columns.
Oracle9i Database Performance Guide and Reference for more information about query optimization
Because a view is defined by a query that references other objects (tables, materialized views, or other views), a view depends on the referenced objects. Oracle automatically handles the dependencies for views. For example, if you drop a base table of a view and then create it again, Oracle determines whether the new base table is acceptable to the existing definition of the view.
Chapter 19, "Dependencies Among Schema Objects" for a complete discussion of dependencies in a database
A join view is defined as a view that has more than one table or view in its
FROM clause (a join) and that does not use any of these clauses:
DISTINCT, AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations
(UNION ALL, INTERSECT, and so on).
An updatable join view is a join view that involves two or more base tables or views, where
UPDATE, INSERT, and
DELETE operations are permitted. The data dictionary views
ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and
USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable. In order to be inherently updatable, a view cannot contain any of the following constructs:
Views that are not updatable can be modified using
INSTEAD OF triggers.
In the Oracle object-relational database, object views let you retrieve, update, insert, and delete relational data as if they were stored as object types. You can also define views with columns that are object datatypes, such as objects,
REFs, and collections (nested tables and
An inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.
For example, this query joins the summary table
SUMTAB to an inline view
V defined on the
TIME table to obtain
T.YEAR, and then rolls up the aggregates in
SUMTAB to the
SELECT v.year, s.prod_name, SUM(s.sum_sales)
Oracle9i SQL Reference for information about subqueries
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:
Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.
Materialized views are similar to indexes in several ways:
Unlike indexes, materialized views can be accessed directly using a
SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an
INSERT, UPDATE, or
A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view.
Data warehousing applications recognize multidimensional data in the Oracle database by identifying Referential Integrity (RI) constraints in the relational schema. RI constraints represent primary and foreign key relationships among tables. By querying the Oracle data dictionary, applications can recognize RI constraints and therefore recognize the multidimensional data in the database. In some environments DBAs, for schema complexity or security reasons, define views on fact and dimension tables. Oracle9i, Release 1 (9.0.1), provides the ability to constrain views. By allowing constraint definitions between views, DBAs can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in a restricted environment.
Only logical constraints can be defined on views (that is, declarative and not enforced by Oracle), because the purpose is not to enforce any business rules but to identify multidimensional data. The following constraints can be defined on views:
Given that view constraints are declarative,
DISABLE, NOVALIDATE is the only valid state for a view constraint. However, the
NORELY state is also allowed, because constraints on views may be used to enable more sophisticated query rewrites; a view constraint in the
RELY state allows query rewrites to occur when the rewrite integrity level is set to trusted mode.
Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables.
Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their master tables can be refreshed whenever a transaction commits its changes to the master tables.
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.
A dimension is a schema object that defines hierarchical relationships between pairs of columns or column sets. A hierarchical relationship is a functional dependency from one level of a hierarchy to the next level in the hierarchy. A dimension is a container of logical relationships between columns and does not have any data storage assigned to it.
CREATE DIMENSION statement specifies:
LEVELclauses, each of which identifies a column or column set in the dimension
HIERARCHYclauses that specify the parent/child relationships between adjacent levels
ATTRIBUTEclauses, each of which identifies an additional column or column set associated with an individual level
The columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized). To define a dimension over columns from multiple tables, connect the tables using the
JOIN clause of the
For example, a normalized time dimension can include a date table, a month table, and a year table, with join conditions that connect each date row to a month row, and each month row to a year row. In a fully denormalized time dimension, the date, month, and year columns are all in the same table. Whether normalized or denormalized, the hierarchical relationships among the columns need to be specified in the
CREATE DIMENSION statement.
The sequence generator provides a sequential series of numbers. The sequence generator is especially useful in multiuser environments for generating unique sequential numbers without the overhead of disk I/O or transaction locking. Therefore, the sequence generator reduces serialization where the statements of two transactions must generate sequential numbers at the same time. By avoiding the serialization that results when multiple users wait for each other to generate and use a sequence number, the sequence generator improves transaction throughput, and a user's wait is considerably shorter.
Sequence numbers are Oracle integers defined in the database of up to 38 digits. A sequence definition indicates general information:
Oracle stores the definitions of all sequences for a particular database as rows in a single data dictionary table in the
SYSTEM tablespace. Therefore, all sequence definitions are always available, because the
SYSTEM tablespace is always online.
Sequence numbers are used by SQL statements that reference the sequence. You can issue a statement to generate a new sequence number or use the current sequence number. After a statement in a user's session generates a sequence number, the particular sequence number is available only to that session. Each user that references a sequence has access to its own current sequence number.
Sequence numbers are generated independently of tables. Therefore, the same sequence generator can be used for more than one table. Sequence number generation is useful to generate unique primary keys for your data automatically and to coordinate keys across multiple rows or tables. Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back. Applications can make provisions to catch and reuse these sequence numbers, if desired.
If accountability for all sequence numbers is required - that is, if your application can never lose sequence numbers - you cannot use Oracle sequences, and you may choose to store sequence numbers in database tables.
Care should be taken when implementing sequence generators using database tables. Even in a single instance configuration, for a high rate of sequence values generation, there will be a performance overhead associated with the cost of locking the row that stores the sequence value.
A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
Synonyms are often used for security and convenience. For example, they can do the following:
You can create both public and private synonyms. A public synonym is owned by the special user group named
PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.
Synonyms are very useful in both distributed and nondistributed database environments because they hide the identity of the underlying object, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to function without modification.
Synonyms can also simplify SQL statements for users in a distributed database system. The following example shows how and why public synonyms are often created by a database administrator to hide the identity of a base table and reduce the complexity of SQL statements. Assume the following:
SALES_DATAis in the schema owned by the user
SELECTprivilege for the
SALES_DATAtable is granted to
At this point, you have to query the table
SALES_DATA with a SQL statement similar to the following:
Notice how you must include both the schema that contains the table along with the table name to perform the query.
Assume that the database administrator creates a public synonym with the following SQL statement:
After the public synonym is created, you can query the table
SALES_DATA with a simple SQL statement:
Notice that the public synonym
SALES hides the name of the table
SALES_DATA and the name of the schema that contains the table.
Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used.
You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:
You cannot create an index that references only one column in a table if another such index already exists.
Oracle provides several indexing schemes, which provide complementary performance functionality:
Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.
The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.
Indexes are logically and physically independent of the data in the associated table. You can create or drop an index at any time without affecting the base tables or other indexes. If you drop an index, all applications continue to work. However, access of previously indexed data can be slower. Indexes, as independent structures, require storage space.
Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.
Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table.
The optimizer can use an existing index to build another index. This results in a much faster index build.
Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values.
Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table.
Alternatively, you can define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.
Oracle9i Database Administrator's Guide for information about creating unique indexes explicitly
A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table.
Composite indexes can speed retrieval of data for
SELECT statements in which the
WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.
Figure 11-6 illustrates the
VENDOR_PARTS table that has a composite index on the
No more than 32 columns can form a regular composite index. For a bitmap index, the maximum number columns is 30. A key value cannot exceed roughly one-half (minus some overhead) the available data space in a data block.
Oracle9i Database Performance Guide and Reference for more information about using composite indexes
Although the terms are often used interchangeably, there is a distinction between indexes and keys. Indexes are structures actually stored in the database, which users create, alter, and drop using SQL statements. You create an index to provide a fast access path to table data. Keys are strictly a logical concept. Keys correspond to another feature of Oracle called integrity constraints, which enforce the business rules of a database.
Because Oracle uses indexes to enforce some integrity constraints, the terms key and index are often are used interchangeably. However, do not confuse them with each other.
NULL values in indexes are considered to be distinct except when all the non-
NULL values in two or more rows of an index are identical, in which case the rows are considered to be identical. Therefore,
UNIQUE indexes prevent rows containing
NULL values from being treated as identical. This does not apply if there are no non-
NULL values--in other words, if the rows are entirely
Oracle does not index table rows in which all key columns are NULL, except in the case of bitmap indexes or when the cluster key column value is null.
You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.
The function used for building the index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. The expression cannot contain any aggregate functions, and it must be
DETERMINISTIC. For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a
REF, or nested table column, nor can you build a function-based index if the object type contains a
LOB, REF, or nested table.
Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their
WHERE clauses. The value of the expression is computed and stored in the index. When it processes
UPDATE statements, however, Oracle must still evaluate the function to process the statement.
For example, if you create the following index:
then Oracle can use it when processing queries such as this:
Function-based indexes defined on
) can facilitate case-insensitive searches. For example, the following index:
can facilitate processing queries such as this:
A function-based index can also be used for a Globalization Support sort index that provides efficient linguistic collation in SQL statements.
Oracle9i Globalization Support Guide for information about Globalization Support sort indexes
You must gather statistics about function-based indexes for the optimizer. Otherwise, the indexes cannot be used to process SQL statements. Rule-based optimization never uses function-based indexes.
Cost-based optimization can use an index range scan on a function-based index for queries with expressions in
WHERE clause. For example, in this query:
the optimizer can use index range scan if an index is built on a+b. The range scan access path is especially beneficial when the predicate
(WHERE clause) has low selectivity. In addition, the optimizer can estimate the selectivity of predicates involving expressions more accurately if the expressions are materialized in a function-based index.
The optimizer performs expression matching by parsing the expression in a SQL statement and then comparing the expression trees of the statement and the function-based index. This comparison is case-insensitive and ignores blank spaces.
Oracle9i Database Performance Guide and Reference for more information about gathering statistics
Function-based indexes depend on the function used in the expression that defines the index. If the function is a PL/SQL function or package function, the index is disabled by any changes to the function specification.
PL/SQL functions used in defining function-based indexes must be
DETERMINISTIC. The index owner needs the
EXECUTE privilege on the defining function. If the
EXECUTE privilege is revoked, then the function-based index is marked
When you create an index, Oracle automatically allocates an index segment to hold the index's data in a tablespace. You can control allocation of space for an index's segment and use of this reserved space in the following ways:
PCTFREEparameter for the index segment to control the free space in the data blocks that constitute the index segment's extents.
The tablespace of an index's segment is either the owner's default tablespace or a tablespace specifically named in the
CREATE INDEX statement. You do not have to place an index in the same tablespace as its associated table. Furthermore, you can improve performance of queries that use an index by storing an index and its table in different tablespaces located on different disk drives, because Oracle can retrieve both index and table data in parallel.
Space available for index data is the Oracle block size minus block overhead, entry overhead, rowid, and one length byte for each value indexed. The number of bytes required for the overhead of an index block depends on the operating system.
When you create an index, Oracle fetches and sorts the columns to be indexed and stores the rowid along with the index value for each row. Then Oracle loads the index from the bottom up. For example, consider the statement:
Oracle sorts the
EMP table on the
ENAME column. It then loads the index with the
ENAME and corresponding rowid values in this sorted order. When it uses the index, Oracle does a quick search through the sorted
ENAME values and then uses the associated rowid values to locate the rows having the sought
Although Oracle accepts the keywords
ASC, DESC, COMPRESS, and
NOCOMPRESS in the
CREATE INDEX statement, they have no effect on index data, which is stored using rear compression in the branch nodes but not in the leaf nodes.
Oracle uses B Trees to store indexes to speed up data access. If there are no indexes then you have to do a sequential scan on the data to find a value. For n rows, the average number of rows searched will be n/2. Obviously this does not scale very well as data volumes increase.
If we had an ordered list of the values, then we could divide it into block wide ranges (leaf blocks). The end points of the ranges along with pointers to the blocks could be stored in a search tree and we could find a value in log(n) time for n entries. This is the basic principle behind Oracle indexes.
Figure 11-7 illustrates the structure of a B-tree index.
The upper blocks (branch blocks) of a B-tree index contain index data that points to lower-level index blocks. The lowest level index blocks (leaf blocks) contain every indexed data value and a corresponding rowid used to locate the actual row. The leaf blocks are doubly linked. Indexes in columns containing character data are based on the binary values of the characters in the database character set.
For a unique index, there is one rowid for each data value. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls without violating a unique index.
There are two kinds of blocks
Branch blocks store the following:
If the blocks have n keys then they have n+1 pointers. The number of keys and pointers is limited by the block size.
All leaf blocks are at the same depth from the root branch block. Leaf blocks store the following:
All key and
ROWID pairs are linked to their left and right siblings. They are sorted by (key,
The B-tree structure has the following advantages:
Index unique scan is one of the most efficient ways of accessing data. This access method is used for returning the data from B-tree indexes. The Optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions.
Figure 11-8 shows an example of an index unique scan and is described in the text that follows the figure.
If searching for Patrick:
If searching for Meg:
Index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted (in ascending order) by the
Index range scans can happen on both unique and non-unique indexes. B-tree non-unique indexes are identical to the unique B-tree indexes. However, they allow multiple values for the same key.
For a range scan, you can specify an equality condition. For example:
Alternatively, specify an interval bounded by start key and end key. For example:
name LIKE `AL%' - start key = `AL, end key < `AM'
Or, specify just a start key or an end key (unbounded range scan). For example:
order_book_date > SYSDATE - 30(orders booked in last month)
employee_hire_date < SYSDATE - 3650(employees with more than a decade of service)
Figure 11-9 shows an example of a bounded range scan and is described in the text that follows the figure.
Here, the range scans make use of the fact that all the leaf nodes are linked from left to right. In Step 7, extra filtering conditions on the index columns can be applied before accessing the table by
Range scans bounded on the left (unbounded on the right) start the same as above. However, they do not check for the end point. They continue until they reach the right-most leaf key.
Range scans bounded on the right traverse the index tree to the left-most leaf key and then follow step #6 and # 7 until they reach a key greater then the specified condition.
With range scans using the non-unique B-tree index, if searching for Nancy:
If searching for `P%':
For a descending range scan (like with the normal range scan), specify an equality condition or an interval.
Here, the range scans make use of the fact that all the leaf nodes are linked from right to left.
Figure 11-10 shows examples of a bounded range scan and is described in the text that follows the figure.
If searching for Nancy:
If searching for `P%':
Key compression lets you compress portions of the primary key column values in an index or index-organized table, which reduces the storage overhead of repeated values.
Generally, keys in an index have two pieces, a grouping piece and a unique piece. If the key is not defined to have a unique piece, Oracle provides one in the form of a rowid appended to the grouping piece. Key compression is a method of breaking off the grouping piece and storing it so it can be shared by multiple unique pieces.
Key compression breaks the index key into a prefix entry (the grouping piece) and a suffix entry (the unique piece). Compression is achieved by sharing the prefix entries among the suffix entries in an index block. Only keys in the leaf blocks of a B-tree index are compressed. In the branch blocks the key suffix can be truncated, but the key is not compressed.
Key compression is done within an index block but not across multiple index blocks. Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.
By default, the prefix consists of all key columns excluding the last one. For example, in a key made up of three columns (column1, column2, column3) the default prefix is (column1, column2). For a list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of (1,2), (1,3) in the prefix are compressed.
Alternatively, you can specify the prefix length, which is the number of columns in the prefix. For example, if you specify prefix length 1, then the prefix is column1 and the suffix is (column2, column3). For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of 1 in the prefix are compressed.
The maximum prefix length for a nonunique index is the number of key columns, and the maximum prefix length for a unique index is the number of key columns minus one.
Prefix entries are written to the index block only if the index block does not already contain a prefix entry whose value is equal to the present prefix entry. Prefix entries are available for sharing immediately after being written to the index block and remain available until the last deleted referencing suffix entry is cleaned out of the index block.
Key compression can lead to a huge saving in space, letting you store more keys in each index block, which can lead to less I/O and better performance.
Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead, because every prefix entry has an overhead of 4 bytes associated with it.
Key compression is useful in many different scenarios, such as:
(stock_ticker, transaction_time).Thousands of rows can have the same
transaction_timepreserving uniqueness. On a particular index block a
stock_tickervalue is stored only once as a prefix entry. Other entries on the index block are
transaction_timevalues stored as suffix entries that reference the common
NESTED TABLEdatatype, the object ID (OID) is repeated for each element of the collection datatype. Key compression lets you compress the repeating OID values.
In some cases, however, key compression cannot be used. For example, in a unique index with a single attribute key, key compression is not possible, because there is a unique piece, but there are no grouping pieces to share.
Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Oracle9i Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed.
Sometimes, using a reverse-key index can make an OLTP Oracle9i Real Application Clusters application faster. For example, keeping the index of mail messages in an e-mail application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent.
REVERSE keyword provides a simple mechanism for creating a reverse key index. You can specify the keyword
REVERSE along with the optional index specifications in a
CREATE INDEX statement:
You can specify the keyword
REBUILD a reverse-key index into one that is not reverse keyed:
Rebuilding a reverse-key index without the
NOREVERSE keyword produces a rebuilt, reverse-key index. You cannot rebuild a normal index as a reverse key index. You must use the
CREATE statement instead.
See Oracle9i Database New Features for more information about the features available in Oracle9i and the Oracle9i Enterprise Edition.
The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle stores each key value repeatedly with each stored rowid. In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient.
Bitmap indexing efficiently merges indexes that correspond to several conditions in a
WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
Bitmap indexing benefits data warehousing applications which have large amounts of data and ad hoc queries but a low level of concurrent transactions. For such applications, bitmap indexing provides:
Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space, because the index can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.
Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.
Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons. For example, a salary column that usually appears in
WHERE clauses in a comparison to a certain value is better served with a B-tree index. Bitmapped indexes are only useful for
AND, OR, NOT, or equality queries.
Bitmap indexes are integrated with the Oracle cost-based optimization approach and execution engine. They can be used seamlessly in combination with other Oracle execution methods. For example, the optimizer can decide to perform a hash join between two tables using a bitmap index on one table and a regular B-tree index on the other. The optimizer considers bitmap indexes and other available access methods, such as regular B-tree indexes and full table scan, and chooses the most efficient method, taking parallelism into account where appropriate.
Parallel query and parallel DML work with bitmap indexes as with traditional indexes. Bitmap indexes on partitioned tables must be local indexes. Parallel create index and concatenated indexes are also supported.
The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in complex conditions in the
WHERE clauses of queries.
For example, on a table with 1 million rows, a column with 10,000 distinct values is a candidate for a bitmap index. A bitmap index on this column can out-perform a B-tree index, particularly when this column is often queried in conjunction with other columns.
B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as
PHONE_NUMBER. In some situations, a B-tree index can be larger than the indexed data. Used appropriately, bitmap indexes can be significantly smaller than a corresponding B-tree index.
In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance.
OR conditions in the
WHERE clause of a query can be quickly resolved by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered very quickly without resorting to a full table scan of the table.
Table 11-1 shows a portion of a company's customer data.
|CUSTOMER #||MARITAL_ STATUS||REGION||GENDER||INCOME_ LEVEL|
MARITAL_STATUS, REGION, GENDER, and
INCOME_LEVEL are all low-cardinality columns. There are only three possible values for marital status and region, two possible values for gender, and four for income level. Therefore, it is appropriate to create bitmap indexes on these columns. A bitmap index should not be created on
CUSTOMER# because this is a high-cardinality column. Instead, use a unique B-tree index on this column to provide the most efficient representation and retrieval.
Table 11-2 illustrates the bitmap index for the
REGION column in this example. It consists of three separate bitmaps, one for each region.
Each entry or bit in the bitmap corresponds to a single row of the
CUSTOMER table. The value of each bit depends upon the values of the corresponding row in the table. For instance, the bitmap
REGION='east' contains a one as its first bit. This is because the region is
east in the first row of the
CUSTOMER table. The bitmap
REGION='east' has a zero for its other bits because none of the other rows of the table contain
east as their value for
An analyst investigating demographic trends of the company's customers can ask, "How many of our married customers live in the central or west regions?" This corresponds to the following SQL query:
Bitmap indexes can process this query with great efficiency by counting the number of ones in the resulting bitmap, as illustrated in Figure 11-11. To identify the specific customers who satisfy the criteria, the resulting bitmap can be used to access the table.
Bitmap indexes include rows that have
NULL values, unlike most other types of indexes. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function
Any bitmap index can be used for this query because all table rows are indexed, including those that have
NULL data. If nulls were not indexed, the optimizer could only use indexes on columns with
NOT NULL constraints.
This query can be optimized with a bitmap index on
This query can be answered by finding the bitmap for
GENDER = 'M' and subtracting the bitmap for
STATE = 'CA'.
STATE can contain null values (that is, if it does not have a
NOT NULL constraint), then the bitmaps for
STATE = 'NULL' must also be subtracted from the result.
Like other indexes, you can create bitmap indexes on partitioned tables. The only restriction is that bitmap indexes must be local to the partitioned table--they cannot be global indexes. Global bitmap indexes are supported only on nonpartitioned tables.
A join index is an index on one table that involves columns of one or more different tables through a join.
The bitmap join index, in its simplest form, is a bitmap index on a table
F based on columns from table
Di joins with
F in a star or snowflake schema as described in "Creation of a Bitmap Join Index". In the data warehousing environment, table
F is usually a fact table, table
Di is usually a dimension table, and the join condition is an equi-inner join between the primary key column(s) of the dimension tables and the foreign key column(s) in the fact table. For simplicity, from now on we call the table whose rowids are bitmapped the fact table, and the other tables participating in the join of bitmap join index the dimension tables.
The volume of data that must be joined can be reduced if join indexes are used as joins have already been precalculated. In addition, join indexes which contain multiple dimension tables can eliminate bitwise operations which are necessary in the star transformation with existing bitmap indexes. Finally, bitmap join indexes are much more efficient in storage than materialized join views which do not compress rowids of the fact tables.
The following is a description of four join models in the star query framework and how they are addressed by bitmap join indexes. The accompanying figures, 11-12 through 11-15, are described by SQL statements in the text that follows each figure.
i-- Fact table
-- Dimension table
pk-- The primary key column on the dimension table
fk-- The fact table column participating in the join with the dimension tables
sales -- The measurement column on the fact table
The model shown in Figure 11-12, a bitmap join index on
F(D.c1), can be represented by the following SQL statement:
Then the following query
can be executed by accessing the bitmap join index to avoid the join operation.
Similar to the materialized join view, a bitmap join index precomputes the join and stores it as a database object. The difference is that a materialized join view materializes the join into a table while a bitmap join index materializes the join into a bitmap index.
Figure 11-13 shows a simple extension of Model 1, requiring a concatenated bitmap join index to represent it, as follows:
The result of the following query:
can be retrieved by accessing the bitmap join index bji.
Another query which references only the leading portion of the index key can also use bitmap join index bji:
Figure 11-14 shows the third model, which requires a concatenated bitmap join index:
Figure 11-15 shows a model that involves joins between two or more dimension tables. It can be expressed by a bitmap join index. The bitmap join index can be either single or concatenated depending on the number of columns in the dimension tables to be indexed. A bitmap join index on
d1.c3 with a join between
d2 and a join between
f can be created as follows:
A bitmap join index should be able to represent joins of the combination of the above models.
Consider a star or snowflake schema with a single fact table
F and multiple dimension tables
D1,.., Dn as defined in "Bitmap Join Indexes". These are the restrictions on the bitmap join index on
F joined with
D1><D2><F,the column(s) on
D1participating in the join
D1><D2must be either the primary key column(s) or with the unique constraint.
UNIQUEattribute. See the Oracle9i SQL Reference for other restrictions.
Bitmap join index on IOT, functional bitmap join index and temporary bitmap join index are not yet allowed.
The primary key or unique constraint requirement is a correctness issue of a bitmap join index. For a regular bitmap index, there is a one-to-one mapping relation between a bit set in a bitmap and a rowid in the base table. For a bitmap join index, there should also be a one to one mapping between each row in the result set of the join and the rowids in the fact table. The primary key or unique constraint is used to enforce this one-to-one mapping.
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
As shown in Figure 11-16, the index-organized table is somewhat similar to a configuration consisting of an ordinary table and an index on one or more of the table columns, but instead of maintaining two separate storage structures, one for the table and one for the B-tree index, the database system maintains only a single B-tree index. Also, rather than having a row's rowid stored in the index entry, the nonkey column values are stored. Thus, each B-tree index entry contains
Applications manipulate the index-organized table just like an ordinary table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B-tree index.
Table 11-3 summarizes the differences between index-organized tables and ordinary tables.
|Ordinary Table||Index-Organized Table|
Rowid uniquely identifies a row. Primary key can be optionally specified
Primary key uniquely identifies a row. Primary key must be specified
Physical rowid in
Logical rowid in
Access is based on rowid
Access is based on logical rowid
Sequential scan returns all rows
Full-index scan returns all rows
Can be stored in a cluster with other tables
Cannot be stored in a cluster
Can contain a column of the
Index-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Presence of nonkey columns of a row in the B-tree leaf block itself avoids an additional block access. Also, since rows are stored in primary key order, range access by the primary key (or a valid prefix) involves minimum block accesses.
In order to allow even faster access to frequently accessed columns, you can use a row overflow storage option (as described later) to push out infrequently accessed nonkey columns from the B-tree leaf block to an optional (heap-organized) overflow storage area. This allows limiting the size and content of the portion of a row that is actually stored in the B-tree leaf block, which may lead to a higher number of rows in each leaf block and a smaller B-tree.
Unlike a configuration of heap-organized table with a primary key index where primary key columns are stored both in the table and in the index, there is no such duplication here because primary key column values are stored only in the B-tree index.
Because rows are stored in primary key order, a significant amount of additional storage space savings can be obtained through the use of key compression.
Use of primary-key based logical rowids, as opposed to physical rowids, in secondary indexes on index-organized tables allows high availability. This is because, due to the logical nature of the rowids, secondary indexes do not become unusable even after a table reorganization operation that causes movement of the base table rows. At the same time, through the use of physical guess in the logical rowid, it is possible to get secondary index based index-organized table access performance that is comparable to performance for secondary index based access to an ordinary table.
B-tree index entries are usually quite small, because they only consist of the key value and a
ROWID. In index-organized tables, however, the B-tree index entries can be large, because they consist of the entire row. This may destroy the dense clustering property of the B-tree index.
Oracle provides the
OVERFLOW clause to handle this problem. You can specify an overflow tablespace so that, if necessary, a row can be divided into the following two parts that are then stored in the index and in the overflow storage area, respectively:
OVERFLOW, you can use two clauses,
INCLUDING, to control how Oracle determines whether a row should be stored in two parts and if so, at which nonkey column to break the row. Using
PCTTHRESHOLD, you can specify a threshold value as a percentage of the block size. If all the nonkey column values can be accommodated within the specified size limit, the row will not be broken into two parts. Otherwise, starting with the first nonkey column that cannot be accommodated, the rest of the nonkey columns are all stored in the row overflow storage area for the table.
INCLUDING clause lets you specify a column name so that any nonkey column, appearing in the
CREATE TABLE statement after that specified column, will be stored in the row overflow storage area. Please note that additional nonkey columns may sometimes need to be stored in the overflow due to
Oracle9i Database Administrator's Guide for examples of using the
Secondary index support on index-organized tables provides efficient access to index-organized table using columns that are not the primary key nor a prefix of the primary key.
Oracle constructs secondary indexes on index-organized tables using logical row identifiers (logical rowids) that are based on the table's primary key. A logical rowid optionally includes a physical guess, which identifies the block location of the row. Oracle can use these physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search. Because rows in index-organized tables do not have permanent physical addresses, the physical guesses can become stale when rows are moved to new blocks.
For an ordinary table, access by a secondary index involves a scan of the secondary index and an additional I/O to fetch the data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:
Oracle9i, Release 1 (9.0.1), supports bitmap indexes on index-organized tables. A mapping table is required for creating bitmap indexes on an index-organized table.
The mapping table is a heap-organized table that stores logical rowids of the index-organized table. Specifically, each mapping table row stores one logical rowid for the corresponding index-organized table row. Thus, the mapping table provides one-to-one mapping between logical rowids of the index-organized table rows and physical rowids of the mapping table rows.
A bitmap index on an index-organized table is similar to that on a heap-organized table except that the rowids used in the bitmap index on an index-organized table are those of the mapping table as opposed to the base table. There is one mapping table for each index-organized table and it is used by all the bitmap indexes created on that index-organized table.
In both heap-organized and index-organized base tables, a bitmap index is accessed using a search key. If the key is found, the bitmap entry is converted to a physical rowid. In the case of heap-organized table, this physical rowid is then used to access the base table. However, in the case of index-organized table, the physical rowid is then used to access the mapping table. The access to the mapping table yields a logical rowid. This logical rowid is used to access the index-organized table.
Though a bitmap index on an index-organized table does not store logical rowids, it is still logical in nature.
Movement of rows in an index-organized table does not leave the bitmap indexes built on that index-organized table unusable. Movement of rows in the index-organized table does invalidate the physical guess in some of the mapping table's logical rowid entries. However, the index-organized table can still be accessed using the primary key.
You can partition an index-organized table by
HASH on column values. The partitioning columns must form a subset of the primary key columns. Just like ordinary tables, local partitioned (prefixed and non-prefixed) index as well as global partitioned (prefixed) indexes are supported for partitioned index-organized tables.
UROWID datatype columns can hold logical primary key-based rowids identifying rows of index-organized tables. Oracle9i, Release 1 (9.0.1), supports indexes on
UROWID datatypes of a heap- or index-organized table. The index supports equality predicates on
UROWID columns. For predicates other than equality or for ordering on
UROWID datatype columns, the index is not used.
The superior query performance for primary key based access, high availability aspects, and reduced storage requirements make index-organized tables ideal for the following kinds of applications:
Oracle provides extensible indexing to accommodate indexes on customized complex data types such as documents, spatial data, images, and video clips and to make use of specialized indexing techniques. With extensible indexing, you can encapsulate application-specific index management routines as an indextype schema object and define a domain index (an application-specific index) on table columns or attributes of an object type. Extensible indexing also provides efficient processing of application-specific operators.
The application software, called the cartridge, controls the structure and content of a domain index. The Oracle server interacts with the application to build, maintain, and search the domain index. The index structure itself can be stored in the Oracle database as an index-organized table or externally as a file.
Oracle9i Data Cartridge Developer's Guide for information about using data cartridges within Oracle's extensibility architecture
Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together. For example, the
DEPT table share the
DEPTNO column. When you cluster the
DEPT tables, Oracle physically stores all rows for each department from both the
DEPT tables in the same data blocks. Figure 11-17 shows what happens when you cluster the
Because clusters store related rows of different tables together in the same data blocks, properly used clusters offers these benefits:
DEPTNO)is stored just once for many rows that contain the same value in both the
Hashing is an optional way of storing table data to improve the performance of data retrieval. To use hashing, create a hash cluster and load tables into the cluster. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function.
Oracle uses a hash function to generate a distribution of numeric values, called hash values, which are based on specific cluster key values. The key of a hash cluster, like the key of an index cluster, can be a single column or composite key (multiple column key). To find or store a row in a hash cluster, Oracle applies the hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster, which Oracle then reads or writes on behalf of the issued statement.
A hash cluster is an alternative to a nonclustered table with an index or an index cluster. With an indexed table or index cluster, Oracle locates the rows in a table using key values that Oracle stores in a separate index.
To find or store a row in an indexed table or cluster, at least two I/Os must be performed:
In contrast, Oracle uses a hash function to locate a row in a hash cluster. No I/O is required. As a result, a minimum of one I/O operation is necessary to read or write a row in a hash cluster.
Oracle9i Database Administrator's Guide for information about creating and managing hash clusters