Datasheet

Tables
Tables in Oracle perform the same function as they do in SQL Server— they contain information about
your business.
Keys
Keys in Oracle perform the same function as they do in SQL Server — they uniquely identify each row of
data in a table.
Indexes
Indexes in Oracle perform the same function as they do in SQL Server — they provide efficient access to
the data in your tables. However, Oracle contains many different kinds of indexes, as outlined in this
section.
B*Tree indexes contain four subtypes of indexes: The Index Organized Table index performs the same
function as the clustered index in SQL Server, which is to sort and store the data in the table by the
primary key.
The B*Tree cluster index, or index clustered table, stores blocks of data from multiple tables prejoined on the
keys. This enables you to select data using a clustered key (a primary and foreign key, for example) and
from the block that contains the rows related to that clustered key.
The reverse key index stores the keys in an index with the key value in reverse order, and is primarily used
on keys that contain sequential numbers. For example, suppose your primary keys started with a
sequential number of 1000. The next primary key would be 1001, and then 1002, and so on. The reverse
key index stores the primary keys in the index as 0001, 1001, and 2001. This allows the index keys to be
inserted into the index spread out over multiple blocks, thereby increasing the efficiency of your index.
The descending index enables you to store the primary key for a table in the index in descending
order. This is particularly useful when most of the data selected from a table is selected in descending
order.
Bitmap indexes use a single index entry to point to many data rows in a single table. This type of index
is particularly useful when indexing columns that contain simple values. For example, if a column
contains a value of 0 or 1 or a value of Y or N, this index can use a single index entry to point to all rows
of data that contain the specified value in your query.
A function-based index stores the computed results of a function in the index. A function is a subroutine
that can be used to encapsulate SQL statements that are repetitively executed and that return a result.
For example, the
MAX function returns the maximum value in a column. Using a function-based index on
tables that rarely change can increase the performance of your queries.
The domain index is a user-defined index that you build yourself. You can then tell Oracle about the index
and the query optimizer will decide whether to use the index in your queries. This type of index is for
advanced users; in particular, database administrators.
The interMedia Text index enables the searching of keywords in large text fields. This type of index is
useful for specialized applications such as search engines that need to search huge amounts of text for
keywords entered by the user.
9
Databases
04_58894x ch01.qxd 10/13/05 5:54 PM Page 9