Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.2 Basic Data Structures

This section describes the three basic Oracle data structures: tables, views, and
indexes.

4.2.1 Tables

The
table
is the basic data structure used in a relational database. A table is
a collection of rows. Each
row
in a table contains one or more
columns.
If you're unfamiliar with relational databases, you
can map a table to the concept of a file or database in a
nonrelational database, just as you can map a row to the concept of a
record in a nonrelational database.

With the Enterprise Editions of
Oracle8 and beyond, you can purchase an
option called Partitioning that, as the name implies, allows you to
partition tables and indexes, which are described later in this
chapter. Partitioning a data structure means that you can divide the
information in the structure between multiple physical storage areas.
A partitioned data structure is divided based on column values in the
table. You can partition tables based on the range of column values
in the table, the result of a hash function (which returns a value
based on a calculation performed on the values in one or more
columns), or a combination of the two. With
Oracle9i you can also use a list of values to
define a partition, which can be particularly useful in a data
warehouse environment.

Oracle is smart enough to take advantage of partitions to improve
performance in two ways:

Oracle won't bother to access partitions that
won't contain any data to satisfy the query.

If all the data in a partition satisfies a part of the WHERE clause
for the query, Oracle simply selects all the rows for the partition
without bothering to evaluate the clause for each row.

Partitioning tables can also be useful in a data warehouse, in which
data can be partitioned based on the time period it spans.

Equally important is the fact that partitioning substantially reduces
the scope of maintenance operations and increases the availability of
your data. You can perform all maintenance operations, such as
backup, recovery, and loading, on a single partition. This
flexibility makes it possible to handle extremely large data
structures while still performing those maintenance operations in a
reasonable amount of time. In addition, if you must recover one
partition in a table for some reason, the other partitions in the
table can remain online during the recovery operation.

If you've been working with other databases that
don't offer the same type of partitioning, you may
have tried to implement a similar functionality by dividing a table
into several separate tables and then using a UNION SQL statement to
view the data in several tables at once. Partitioned tables give you
all the advantages of having several identical tables joined by a
UNION statement without the complexity that implementation requires.

To maximize the benefits of partitioning, it sometimes makes sense to
partition a table and an index identically so that both the table
partition and the index partition map to the same set of rows. You
can automatically implement this type of partitioning, which is
called
equipartitioning,
by specifying an index for a partitioned table as a LOCAL index.

For more details about the structure and limitations associated with
partitioned tables, refer to your Oracle documentation.

As of Oracle9i, you can define
external tables. As its name implies, the
data for an external table is stored outside the database, typically
in a flat file. The external table is read-only; you cannot update
the data it contains. The external table is good for loading and
unloading data to files from a database, among other purposes.

4.2.2 Views

A
view
is an Oracle data structure constructed with a SQL statement. The SQL
statement is stored in the database. When you use a view in a query,
the stored query is executed and the base table data is returned to
the user. Views do not contain data, but represent ways to look at
the base table data in the way the query specifies.

You can use a view for several purposes:

To simplify access to data stored in multiple tables

To implement specific security for the data in a table (e.g., by
creating a view that includes a WHERE clause that limits the data you
can access through the view)

Since Oracle9i, you can use
fine-grained access
control to accomplish the
same purpose. Fine-grained access control gives you the ability to
automatically limit data access based on the value of data in a row.

To isolate an application from the specific structure of the
underlying tables

A view is built on a collection of base tables, which
can be either actual tables in an Oracle database or other views. If
you modify any of the base tables for a view so that they no longer
can be used for a view, that view itself can no longer be used.

In general, you can write to the columns of only one underlying base
table of a view in a single SQL statement. There are additional
restrictions for INSERT, UPDATE, and DELETE operations, and there are
certain SQL clauses that prevent you from updating any of the data in
a view.

You can write to a non-updateable view by using an INSTEAD OF
trigger, which is described later in this chapter.

Oracle8i introduced
materialized views.
Materialized views can hold presummarized data, which provides
significant performance improvements in a data warehouse scenario.
Materialized views are described in more detail in Chapter 9.

4.2.3 Indexes

An index is
a data structure that speeds up access to particular rows in a
database. An index is associated with a particular table and contains
the data from one or more columns in the table.

The basic SQL syntax for creating an index
is shown in this example:

CREATE INDEX emp_idx1 ON emp (ename, job);

in which emp_idx1 is the name of the index,
emp is the table on which the index is created,
and ename and job are the
column values that make up the index.

The Oracle database server automatically modifies the values in the
index when the values in the corresponding columns are modified.
Because the index contains less data than the complete row in the
table and because indexes are stored in a special structure that
makes them faster to read, it takes fewer I/O operations to retrieve
the data in them. Selecting rows based on an index value can be
faster than selecting rows based on values in the table rows. In
addition, most indexes are stored in sorted order (either ascending
or descending, depending on the declaration made when you created the
index). Because of this storage scheme, selecting rows based on a
range of values or returning rows in sorted order is much faster when
the range or sort order is contained in the presorted indexes.

In addition to the data for an
index, an index entry stores the
ROWID for its
associated row. The ROWID is the fastest way to retrieve any row in a
database, so the subsequent retrieval of a database row is performed
in the most optimal way.

An index can be either unique (which means that no two rows in the
table or view can have the same index value) or nonunique. If the
column or columns on which an index is based contain
NULL values, the row
isn't included in an index.

An index in Oracle is the physical structure
used within the database. A
key is a term for a
logical entity, typically the value stored within the index. In most
places in the Oracle documentation, the two terms are used
interchangeably, with the notable exception of the foreign key
constraint, which is discussed later in this chapter.

Four different types of index structures, which are
described in the following sections, are used in Oracle: standard
B*-tree indexes; reverse key indexes; bitmap indexes; and a new type
of index, the function-based index, which was introduced in
Oracle8i. Oracle also gives you the ability to
cluster the data in the tables, which can improve performance. This
is described later, in Section 4.3.3.

4.2.3.1 B*-tree indexes

The B*-tree index is the
default index used in Oracle. It gets its name from its resemblance
to an inverted tree, as shown in Figure 4-1.

Figure 4-1. A B*-tree index

The B*-tree index is composed of one or more levels of
branch blocks and a single level of leaf
blocks. The branch blocks contain information about the range of
values contained in the next level of branch blocks. The number of
branch levels between the root and leaf blocks is called the
depth
of the index. The leaf blocks contain the actual index
values and the ROWID for the associated row.

The B*-tree index structure doesn't contain many
blocks at the higher levels of branch blocks, so it takes relatively
few I/O operations to read quite far down the B*-tree index
structure. All leaf blocks are at the same depth in the index, so all
retrievals require essentially the same amount of I/O to get to the
index entry, which evens out the performance of the index.

Oracle allows you to create index organized
tables (IOTs), in which the leaf blocks store the entire
row of data rather than only the ROWID that points to the associated
row. Index organized tables reduce the amount of space needed to
store an index and a table by eliminating the need to store the ROWID
in the leaf page. But index organized tables cannot use a
UNIQUE constraint or be stored in a
cluster. In addition, index organized
tables don't support distribution, replication, and
partitioning (covered in greater detail in other chapters), although
IOTs can be used with Oracle Streams for capturing and applying
changes with Oracle Database 10g.

There have been a number of enhancements to index organized tables as
of Oracle9i,
including a lifting of the restriction against the use of bitmap
indexes as secondary indexes for an IOT and the ability to create,
rebuild, or coalesce secondary indexes on an IOT. Oracle Database
10g continues this trend by allowing list
partitioning for index organized tables, as well as providing other
enhancements.

4.2.3.2 Reverse key indexes

Reverse key
indexes, as their name implies, automatically reverse the
order of the bytes in the key value stored in the index. If the value
in a row is "ABCD", the value for
the reverse key index for that row is
"DCBA".

To understand the need for a reverse key index, you have to review
some basic facts about the standard B*-tree index. First and
foremost, the depth of the B*-tree is determined by the number of
entries in the leaf nodes. The greater the depth of the B*-tree, the
more levels of branch nodes there are and the more I/O is required to
locate and access the appropriate leaf node.

The index illustrated in Figure 4-1 is a nice, well-behaved,
alphabetic-based index. It's balanced, with an even
distribution of entries across the width of the leaf pages. But some
values commonly used for an index are not so well behaved.
Incremental values, such as ascending sequence numbers or
increasingly later date values, are always added to the right side of
the index, which is the home of higher and higher values. In
addition, any deletions from the index have a tendency to be skewed
toward the left side as older rows are deleted. The net effect of
these practices is that over time the index turns into an unbalanced
B*-tree, where the left side of the index is more sparsely populated
than the leaf nodes on the right side. This unbalanced growth has the
overall effect of increasing the depth of the B*-tree structure due
to the number of entries on the right side of the index. The effects
described here also apply to the values that are automatically
decremented, except that the left side of the B*-tree will end up
holding more entries.

You can solve this problem by periodically dropping and recreating
the index. However, you can also solve it by using the reverse value
index, which reverses the order of the value of the index. This
reversal causes the index entries to be more evenly distributed over
the width of the leaf nodes. For example, rather than having the
values 234, 235, and 236 be added to the maximum side of the index,
they are translated to the values 432, 532, and 632 for storage and
then translated back when the values are retrieved. These values are
more evenly spread throughout the leaf nodes.

The overall result of the reverse index is to correct the imbalance
caused by continually adding increasing values to a standard B*-tree
index. For more information about reverse key indexes and where to
use them, refer to your Oracle documentation.

4.2.3.3 Bitmap indexes

In a standard B*-tree index, the ROWIDs
are stored in the leaf blocks of the index. In a bitmap index, each
bit in the index represents a ROWID. If a particular row contains a
particular value, the bit for that row is "turned
on" in the bitmap for that value. A mapping function
converts the bit into its corresponding ROWID. Unlike other index
types, bitmap indexes include entries for NULL values.

You can store a bitmap index in much less space than a standard
B*-tree index if there aren't many values in the
index. Figure 4-2 shows an illustration of how a bitmap index is
stored. Figure 9-3 in Chapter 9 shows how a bitmap index is used in a
selection condition.

Figure 4-2. Bitmap index

The functionality provided by bitmap indexes is especially important
in data warehousing applications in which each dimension of the
warehouse contains many repeating values and queries typically
require the interaction of several different dimensions. For more
about data warehousing, see Chapter 9.

4.2.3.4 Function-based indexes

Function-based indexes were new in
Oracle8i. A
function-based index is just like a standard B*-tree or bitmap index,
except that you can base the index on the result of a SQL function,
rather than just on the value of a column or columns.

Prior to Oracle8i, if you wanted to select on
the result of a function, Oracle retrieved every row in the database,
executed the function, and then accepted or rejected each row. With
function-based indexes you can simply use the index for selection,
without having to execute the function on every row, every time.

For example, without a function-based index, if you wanted to perform
a case-insensitive selection of data you would have to use the UPPER
function in the WHERE clause, which would retrieve every candidate
row and execute the function. With a function-based index based on
the UPPER function, you can select directly from the index.

With Oracle Database 10g, you can perform case- or accent-insensitive queries; such queries provide another way to solve this problem.

This capability becomes even more valuable when you consider that you
can create your own functions in an Oracle database. You can create a
very sophisticated function and then create an index based on the
function, which can dramatically affect the performance of queries
that require the function.