Useful Notes From The 10gR2 Concepts Guide

Note 1 - Extents in Temporary Segments

When Oracle completes the execution of a statement requiring a temporary segment,
Oracle automatically drops the temporary segment and returns the extents allocated
for that segment to the associated tablespace. A single sort allocates its own temporary
segment in a temporary tablespace of the user issuing the statement and then returns
the extents to the tablespaces.
Multiple sorts, however, can use sort segments in temporary tablespaces designated
exclusively for sorts. These sort segments are allocated only once for the instance, and
they are not returned after the sort, but remain available for other multiple sorts.

A temporary segment in a temporary table contains data for multiple statements of a
single transaction or session. Oracle drops the temporary segment at the end of the
transaction or session, returning the extents allocated for that segment to the
associated tablespace.

Section 2 p11-12, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 2 - Extents

In general, the extents of a segment do not return to the tablespace until you drop the
schema object whose data is stored in the segment (using a DROP TABLE or DROP
CLUSTER statement). Exceptions to this include the following:

  • The owner of a table or cluster, or a user with the DELETE ANY privilege, can

truncate the table or cluster with a TRUNCATE…DROP STORAGE statement.

  • A database administrator (DBA) can deallocate unused extents using the following


Section 2 p10, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 3 - Storage Parameters

The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be
specified at the tablespace level for locally managed tablespaces. They can, however,
be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and
MINEXTENTS are used together to compute the initial size of the segment. After the
segment size is computed, internal algorithms determine the size of each extent.

Section 2 p10, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 4 - Undo Pool

The Resource Manager directive UNDO_POOL is a more
explicit way to control large transactions. This lets database administrators group
users into consumer groups, with each group assigned a maximum undo space limit.

Section 2 p15, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 5 - Automatic Undo Retention

Oracle Database 10g automatically tunes a parameter called the undo retention period.
The undo retention period indicates the amount of time that must pass before old
undo information—that is, undo information for committed transactions—can be
overwritten. The database collects usage statistics and tunes the undo retention period
based on these statistics and on undo tablespace size. Provided that automatic undo
management is enabled, the database automatically tunes the undo retention period as

  • For an AUTOEXTEND undo tablespace, the database tunes the undo retention

period to be slightly longer than the longest-running query, if space allows. In
addition, when there is adequate free space, the tuned retention period does not
go below the value of the UNDO_RETENTION initialization parameter.

  • For a fixed size undo tablespace, the database tunes for the maximum possible

undo retention. This means always providing the longest possible retention period
while avoiding out-of-space conditions and near out-of-space conditions in the
undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless
retention guarantee is enabled.

  • Automatic tuning of undo retention is not supported for LOBs. The tuned

retention value for LOB columns is set to the value of the UNDO_RETENTION

For fixed size and AUTOEXTEND undo tablespaces of equal size, depending on the
queries that you run, the tuning method used in fixed size tablespaces tends to
provide a longer retention period. This enables flashback operations to flash back
farther in time, and maximizes the amount of undo data available for long-running

Related Views

Monitor transaction and undo information with V$TRANSACTION and V$ROLLSTAT.
For automatic undo management, the information in V$ROLLSTAT reflects the
behaviors of the automatic undo management undo segments.
The V$UNDOSTAT view displays a histogram of statistical data to show how well the
system is working. You can see statistics such as undo consumption rate, transaction
concurrency, and lengths of queries run in the instance. Using this view, you can better
estimate the amount of undo space required for the current workload

Section 2 p16, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 6 - Locally Managed Tablespaces

In dictionary managed tablespaces, when a segment requires an extent larger than the
available extents, Oracle identifies and combines contiguous reclaimed extents to form
a larger one. This is called coalescing extents. Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

Section 2 p11, Section 3 p10, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 7 - The Data Dictionary

The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.
PL/SQL Program Units Description All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the database contains many of these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace.

Section 3 p6, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 8 - Transactions and Undo

When the first DML operation is run within a transaction, the transaction is bound
(assigned) to an undo segment (and therefore to a transaction table) in the current
undo tablespace. In rare circumstances, if the instance does not have a designated
undo tablespace, the transaction binds to the system undo segment.

Section 3 p7, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 9 - Assignment of Undo Tablespaces

In automatic undo management mode, each Oracle instance is assigned one (and only one) undo

You assign an undo tablespace to an instance in one of two ways:

  • At instance startup. You can specify the undo tablespace in the initialization file or

let the system choose an available undo tablespace.

  • While the instance is running. Use ALTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo tablespace. This method is

rarely used.

You can add more space to an undo tablespace by adding more datafiles to the undo
tablespace with the ALTER TABLESPACE statement.
You can have more than one undo tablespace and switch between them. Use the
Database Resource Manager to establish user quotas for undo tablespaces. You can
specify the retention period for undo information

Section 3 p7-8, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 10 - Space Management

Tablespaces allocate space in extents. Tablespaces can use two different methods to
keep track of their free and used space:

  • Locally managed tablespaces: Extent management by the tablespace
  • Dictionary managed tablespaces: Extent management by the data dictionary

When you create a tablespace, you choose one of these methods of space management.
Later, you can change the management method with the DBMS_SPACE_ADMIN PL/SQL package.

Section 3 p9, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 11 - Sort Space

You can manage space for sort operations more efficiently by designating one or more
temporary tablespaces exclusively for sorts. Doing so effectively eliminates
serialization of space management operations involved in the allocation and
deallocation of sort space. A single SQL operation can use more than one temporary
tablespace for sorting.

Sort segments are used when a segment is shared by multiple sort operations. One sort
segment exists for every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple
sorts that are too large to fit into memory. The sort segment of a given temporary
tablespace is created at the time of the first sort operation. The sort segment expands
by allocating extents until the segment size is equal to or greater than the total storage
demands of all of the active sorts running on that instance.

Section 3 p13, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 12 - Tablespace Repository

A tablespace repository is a collection of tablespace sets. Tablespace repositories are
built on file group repositories, but tablespace repositories only contain the files
required to move or copy tablespaces between databases. Different tablespace sets
may be stored in a tablespace repository, and different versions of a particular
tablespace set also may be stored. A version of a tablespace set in a tablespace
repository consists of the following files:

  • The Data Pump export dump file for the tablespace set
  • The Data Pump log file for the export
  • The datafiles that comprise the tablespace set

Section 3 p14, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 13 - Datafiles in 10G

The first time a tablespace’s datafiles are opened under Oracle Database with the
COMPATIBLE initialization parameter set to 10 or higher, each file identifies the
platform to which it belongs. These files have identical on disk formats for file header
blocks, which are used for file identification and verification. Read only and offline
files get the compatibility advanced after they are made read/write or are brought
online. This implies that tablespaces that are read only prior to Oracle Database 10g
must be made read/write at least once before they can use the cross platform
transportable feature.

Section 3 p15, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 14 - Locally Managed Temp Tablespaces

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which
are similar to ordinary datafiles, with the following exceptions:

  • Tempfiles are always set to NOLOGGING mode.
  • You cannot make a tempfile read only.
  • You cannot create a tempfile with the ALTER DATABASE statement.
  • Media recovery does not recognize tempfiles:
    • BACKUP CONTROLFILE does not generate any information for tempfiles.
    • CREATE CONTROLFILE cannot specify any information about tempfiles.
  • When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.

Section 3 p16-17, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 15 - Control File I

Among other things, a control file contains information such as:

  • The database name
  • The timestamp of database creation
  • The names and locations of associated datafiles and redo log files
  • Tablespace information
  • Datafile offline ranges
  • The log history
  • Archived log information
  • Backup set and backup piece information
  • Backup datafile and redo log information
  • Datafile copy information
  • The current log sequence number
  • Checkpoint information

Section 3 p17, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 16 - Control Files II

If you make a change to the physical structure of your database (using
ALTER DATABASE statements), then you should immediately make a backup of your
control file.
Control files also record information about checkpoints. Every three seconds, the
checkpoint process (CKPT) records information in the control file about the checkpoint
position in the redo log. This information is used during database recovery to tell
Oracle that all redo entries recorded before this point in the redo log group are not
necessary for database recovery; they were already written to the datafiles.

Section 3 p18, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 17 - Transaction Name I

You can name a transaction using the SET TRANSACTION … NAME statement before
you start the transaction. This makes it easier to monitor long-running transactions
and to resolve in-doubt distributed transactions.

Section 3 p19, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 18 - Statement Level Rollback

If at any time during a transaction, the execution of an SQL statement causes an error, all effects of the
statement are rolled back. The effect of the rollback is as if that statement had never
been run. This operation is a statement-level rollback.

An SQL statement that fails causes the loss only of any work it would have performed
itself. It does not cause the loss of any work that preceded it in the current transaction. If the
statement is a DDL statement, then the implicit commit that immediately preceded it is not undone.

Section 4 p3, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 19 - Resumable SQL

A statement runs in a resumable mode only when the client explicitly enables
resumable semantics for the session using the ALTER SESSION statement.
Resumable space allocation is suspended when one of the following conditions occur:

  • Out of space condition
  • Maximum extents reached condition
  • Space quota exceeded condition

Section 4 p3, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 20 - Transaction Management I

Before a transaction that modifies data is committed, the following has occurred:

  • Oracle has generated undo information. The undo information contains the old data values changed by the SQL statements of the transaction.
  • Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.
  • The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction is committed.

When a transaction is committed, the following occurs:

  • The internal transaction table for the associated undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

Note: The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process.

This writing takes place when it is most efficient for the database to do so. It can happen before the transaction commits or, alternatively, it can happen some time after the transaction commits.

  • The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file. It also writes the transaction’s SCN to the redo log file. This atomic event constitutes the commit of the transaction.
  • Oracle releases locks held on rows and tables.
  • Oracle marks the transaction complete.

Note The default behavior is for LGWR to write redo to the online redo log files synchronously and for transactions to wait for the redo to go to disk before returning a commit to the user. However, for lower transaction commit latency application developers can specify that redo be written asynchronously and that transactions do not need to wait for the redo to be on disk.

Section 4 p4-5, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 21 - Transaction Name II

You can name a transaction, using a simple and memorable text string. You can use transaction names to find a specific transaction in data dictionary views, such as V$TRANSACTION. Name a transaction using the SET TRANSACTION … NAME statement before you start the transaction. Transaction names do not have to be unique; different transactions can have the same transaction name at the same time by the same owner.

Note In previous releases, you could associate a comment with a transaction by using a commit comment. In a future release, commit comments will be deprecated.

Section 4 p6-7, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 22 - Autonomous Transactions

Autonomous transactions are independent transactions that can be called from within
another transaction. An autonomous transaction lets you leave the context of the
calling transaction, perform some SQL operations, commit or undo those operations,
and then return to the calling transaction’s context and continue with that transaction.
Once invoked, an autonomous transaction is totally independent of the main
transaction that called it. It does not see any of the uncommitted changes made by the
main transaction and does not share any locks or resources with the main transaction.
One autonomous transaction can call another. There are no limits, other than resource
limits, on how many levels of autonomous transactions can be called.
rolling back the main transaction to a savepoint taken before the beginning of an autonomous
transaction does not undo the autonomous transaction.

Section 4 p8, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 23 - Oracle Blocks I

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 if an update to an existing row causes the row to
outgrow its data block, then 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 an effect in I/O performance, because no extra I/O operation is
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. The Figure 5.3 shows the format of a row piece:


The row header precedes the data and contains information about:

  • Row pieces
  • Chaining (for chained row pieces only)
  • Columns in the row piece
  • Cluster keys (for clustered data only)

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.

N.B. Each row also uses 2 bytes in the data block header’s row directory.

Section 5 p4-5, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 24 - Row ID

The rowid identifies each row piece by its location or address. After they are assigned,
a given row piece retains its rowid until the corresponding row is deleted or exported
and imported using Oracle 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 DELETE.

Section 5 p6, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 25 - Column Order

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 a table has 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.

Section 5 p6, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 26 - Table Compression

Oracle's table compression feature compresses data by eliminating duplicate values in
a database block. Compressed data stored in a database block (also known as disk
page) is self-contained. That is, all the information needed to re-create the
uncompressed data in a block is available within that block. Duplicate values in all the
rows and columns in a block are stored once at the beginning of the block, in what is
called a symbol table for that block. All occurrences of such values are replaced with a
short reference to the symbol table.
With the exception of a symbol table at the beginning, compressed database blocks
look very much like regular database blocks. All database features and functions that
work on regular database blocks also work on compressed database blocks.

You can alter the compression attribute for a table (or a partition or
tablespace), and the change only applies to new data going into that table. As a result,
a single table or partition may contain some compressed blocks and some regular
blocks. This guarantees that data size will not increase as a result of compression; in
cases where compression could increase the size of a block, it is not applied to that


Compressed tables or partitions can be modified the same as other Oracle tables or
partitions. For example, data can be modified using INSERT, UPATE, and DELETE
statements. However, data modified without using bulk insertion or bulk loading
techniques is not compressed. Deleting compressed data is as fast as deleting
uncompressed data. Inserting new data is also as fast, because data is not compressed
in the case of conventional INSERT; it is compressed only doing bulk load. Updating
compressed data can be slower in some cases. For these reasons, compression is more
suitable for data warehousing applications than OLTP applications. Data should be
organized such that read only or infrequently changing portions of the data (for
example, historical data) is kept compressed.

Section 5 p6-7, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 27 - Row Migration

A row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block,
assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change. When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the
information for the row.

Section 2 p5, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 28 - Constraint Checking Flow

Default Value Insertion and Integrity Constraint Checking Integrity constraint checking occurs after the row with a default value is inserted. For example, in Figure 5–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 deptno column.

Section 5 p8, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 29 - Global Temporary Tables

The 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.

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 failure.

Section 5 p10, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 30 - External Tables

You cannot insert data into external tables, update records in them or truncate them; external tables are read only

Section 5 p12, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 31 - View Execution

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:

1. Merges the statement that references the view with the query that defines the view
1. Parses the merged statement in a shared SQL area
1. Executes the statement

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 get the benefit of
reduced memory use associated with shared SQL when you use views.

Only logical constraints, that is, constraints that are declarative and not enforced by Oracle, can be defined on views. The purpose of these constraints is not to enforce any business rules but to identify multidimensional data. The following constraints can be defined on views:

  • Primary key constraint
  • "Overview of Indexes" on page 5-21
  • Chapter 18, "Partitioned Tables and Indexes"
  • Oracle Database Data Warehousing Guide for information about materialized views in a data warehousing environment
  • Unique constraint
  • Referential Integrity constraint

Section 5 p14,p17,p18, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 32 - Updateable Views

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:

  • A set operator
  • A DISTINCT operator
  • An aggregate or analytic function
  • A collection expression in a SELECT list
  • A subquery in a SELECT list
  • Joins (with some exceptions)

Views that are not updatable can be modified using INSTEAD OF triggers.

Section 5 p15-16, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 33 - Materialized Views

Materialized views are similar to indexes in several ways:

  • They consume storage space.
  • They must be refreshed when the data in their master tables changes.
  • They improve the performance of SQL execution when they are used for query rewrites.
  • Their existence is transparent to SQL applications and users.

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 DELETE statement. 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.

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.

Section 5 p18, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 34 - Sequences

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.

N.B for Stupid Developers - If only they'ed learn…

If your application can never lose sequence numbers, then you cannot use Oracle sequences, and you may choose to store
sequence numbers in database tables. Be careful when implementing sequence generators using database tables. Even in a
single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of
locking the row that stores the sequence value

Section 5 p19,p20, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 35 - Indexes

You can create many indexes for a table as long as the combination of columns differs for each index. For example;

CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (job_id, last_name);

is valid, but

CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (last_name, job_id);

is not valid and will result in an Oracle error:

ORA-01408: such column list already indexed

Oracle provides several indexing schemes, which provide complementary performance functionality:

  • B-tree indexes
  • B-tree cluster indexes
  • Hash cluster indexes
  • Reverse key indexes
  • Bitmap indexes
  • Bitmap join indexes

Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

DEFINITION: A composite index (also called a concatenated index) is an index that you create on multiple columns in a table.

DEFINITION: Indexes and Keys, although the terms are often used interchangeably, indexes and keys are different. 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.

N.B A key value cannot exceed roughly half (minus some overhead) the available data space in a data block.

Section 5 p21-23, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 36 - Indexes and NULLS

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 NULL. 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.

Section 5 p23, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 37 - Function Based Indexes

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes 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

The expression cannot contain any aggregate functions, and it must be DETERMINISTIC.

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 INSERT and UPDATE statements, however,Oracle must still evaluate the function to process the statement.

For example, if you create the following index:

CREATE INDEX idx ON table_1 (a + b ** (c - 1), a, b);

then Oracle can use it when processing queries such as this:

SELECT a FROM table_1 WHERE a + b ** (c - 1) < 100;

To use a function-based index:

  • The table must be analyzed after the index is created.
  • The query must be guaranteed not to need any NULL values from the indexed expression, because NULL values are not stored in indexes.

A function-based index depends on any function that it is using. If the function or the specification of a package containing the function is redefined (or if the index owner’s EXECUTE privilege is revoked), then the following conditions hold:

  • The index is marked as DISABLED.
  • Queries on a DISABLED index fail if the optimizer chooses to use the index.
  • DML operations on a DISABLED index fail unless the index is also marked UNUSABLE and the initialization parameter SKIP_UNUSABLE_INDEXES is set to true. (The default)

Section 5 p23-25, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 37 - Index Block Structure

Space available for index data is the Oracle block size minus block overhead, entry overhead, rowid, and one length byte for each value indexed. 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.

Section 5 p26, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 38 - Btree Explained

Oracle uses B-trees to store indexes to speed up data access. With no indexes, you have to do a sequential scan on the data to find a value. For n rows, the average number of rows searched is n/2. This does not scale very well as data volumes increase. Consider an ordered list of the values divided into block-wide ranges (leaf blocks). The end points of the ranges along with pointers to the blocks can be stored in a search tree and a value in log(n) time for n entries could be found. This is the basic principle behind Oracle indexes.


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, one rowid exists 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.

The two kinds of blocks:

  • Branch blocks for searching
  • Leaf blocks that store the values

Branch Blocks Branch blocks store the following:

  • The minimum key prefix needed to make a branching decision between two keys
  • The pointer to the child block containing the key

If the blocks have n keys then they have n+1 pointers. The number of keys and pointers is limited by the block size.
Leaf Blocks All leaf blocks are at the same depth from the root branch block. Leaf blocks store the following:

  • The complete key value for every row
  • ROWIDs of the table rows

All key and ROWID pairs are linked to their left and right siblings. They are sorted by (key, ROWID).

The B-tree structure has the following advantages:

  • All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
  • B-tree indexes automatically stay balanced.
  • All blocks of the B-tree are three-quarters full on the average.
  • B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
  • Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
  • B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.

Section 5 p26-28, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 38 - Index Unique Scan

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.

Section 5 p28, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 39 - Index Range Scan

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 ROWIDs.

Section 5 p28, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 40 - Reverse key indexes

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 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.

N.B. Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Only fetch-by-key or full-index (table) scans can be performed.

Section 5 p30, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 41 - Bitmap Indexes

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.


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.

Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons.

Bitmapped indexes are only useful with equality queries, especially in combination with AND, OR, and NOT operators.

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

Bitmap indexes can 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 COUNT

Bitmap indexes on partitioned tables; The only restriction is that bitmap indexes must be local to the partitioned table, they cannot be global indexes

Section 5 p30-33, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Note 42 - Bitmap Example

Table 5–1 shows a portion of a company’s customer data.


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 ad retrieval.

Table 5–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 REGION.

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:

AND REGION IN (’central’,’west’);

Bitmap indexes can process this query with great efficiency by counting the number of ones in the resulting bitmap, as illustrated in Figure 5–8. To identify the specific customers who satisfy the criteria, the resulting bitmap can be used to access the table.

Section 5 p30-33, Oracle Database Concepts 10g Release 2, Oracle B14220-02, October 2005

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License