Databases Reference
In-Depth Information
In this section, we will take a closer look at one of the most common types of
structure in almost every data model—the lookup table.
Lookup tables are tables where the data change is slow, meaning that the data
hardly (if ever after the initial load) changes. The data is inserted once, but is
queried very frequently. These tables are often used as
List of Values
(
LOV
) in your
application. Because these tables are queried so frequently, it is important to make
the actual query as eficient as possible. Tables like these are excellent candidates to
be implemented as single-table hash clusters, provided that the size of the table is
primarily static and the lookup is done with an equality query. Another option is to
use an index-organized table; think of it as a combination of an index and table in
one structure. This will be discussed later in this chapter.
Single-table hash clusters
Detailed explanation about single-table hash clusters can be found in the Oracle
documentation—
Oracle database concepts, 11g Release 2 (11.2)
—at
http://docs.
oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CNCPT88831
.
"A single-table hash cluster is an optimized version of a hash cluster that supports
only one table at a time. A one-to-one mapping exists between hash keys and rows.
A single-table hash cluster can be beneicial when users require rapid access to a
table by primary key. For example, users often look up an employee record in the
employees table by employee_id."
A lookup table its this description like a glove.
Normally a database block stores data for exactly one table. A cluster allows you to
store data from more than one table on a block. However this is not the case with a
single-table hash cluster. As you might have guessed from the name, only the data of
a single table is stored in the cluster. The irst thing we need before we can create a
single-table hash cluster, is the cluster itself:
SQL> create cluster lookup_c
2 (id number)
3 single table
4 hashkeys 50000
5 size 50
6 /
Cluster created.
