Previous | Table of Contents | Next

Page 798

it is said to be hot. The same holds true for active columns within a table, which are also called hot. A warm table or column is one that is simply less active, relative to a hot component. Thus, cold components are those that are infrequently accessed. You might also see the nomenclature Low/Medium/High activity used. In summary, in addition to typing your application, classify your tables as the following:

H High activity, or hot
M Medium activity, or warm
L Low activity, or cold

Although a simple classification, this will help immensely with your physical layout and performance tuning. For example, high DML activity tables will cause high fragmentation. If stored on their own, separate tablespaces or disks, this isolates them from causing unnecessary fragmentation of lower activity tables. When your classification job is done, follow these simple guidelines for tablespace partitioning and table/index placement:

Clustering provides an optional storage method for storing frequently joined tables.

Clustering

A cluster is a special type of tablespace in which parent-child, or master-detail, types of hierarchical relationships may be physically nested. For example, if an employee can work in only one

Page 799

department, it is a logical, one-to-many relationship from a DEPARTMENTS table to an EMPLOYEES table. The EMPLOYEES table has a foreign key, DEPTID, back to the DEPARTMENTS table primary key, DEPTID. You can create a cluster like so:

SQL> CREATE CLUSTER DEPTS_EMPS (DEPTID NUMBER (9))
        2> SIZE 256
     3> TABLESPACE DATAn
         4> STORAGE (...);


SQL> CREATE TABLE DEPARTMENTS
        2> (DEPTID NUMBER(9) PRIMARY KEY, ...)
       3> CLUSTER DEPTS_EMPS (DEPTID);


SQL> CREATE TABLE EMPLOYEES
        2> (EMPID NUMBER (9) PRIMARY KEY, ...
       3> , DEPTID NUMBER (9) REFERENCES DEPARTMENTS)
        4> CLUSTER DEPTS_EMPS (DEPTID);

The optional SIZE argument specifies how many bytes are expected to be exhausted by the cluster key (in this case, DEPTID) and all its associated rows. Table 32.1 is a textual representation of how the DEPTS_EMPS cluster is physically organized:

Table 32.1The DEPTS_EMPS Cluster

The DEPARTMENTS and EMPLOYEES tables before clustering:

DEPTID DEPTNAME etc.
1 PERSONNEL
2 ACCOUNTING

EMPLOYEES (before clustering):

EMPID EMPNAME DEPTID etc.
1 William Day 1
2 James Hutch 1
4 Ely Jones 1
4 Ely Jones 1
5 Tom Edwards 2

Page 800

The DEPARTMENTS and EMPLOYEES tables after clustering:

DEPTID DEPTNAME etc.
EMPLOYEES
EMPID EMPNAME etc.
1 PERSONNEL
1 William Day
2 James Hutch
4 Ely Jones
2 ACCOUNTING
4 Ely Jones
5 Tom Edwards

The etc. refers to other nonkey columns in the DEPARTMENTS and EMPLOYEES tables. As you can see, the EMPLOYEES table is physically nested within the DEPARTMENTS table so that whenever these two tables are accessed by a join on DEPTID, the data is already organized in that fashion and much more readily available than would ordinarily be the case with two nonclustered tables. Use ordinary indexes (B*Tree structures) for most cases, in particular those involving any substantial range retrievals (bounded or not). However, if your application queries are almost always point queries (exact matches, equality comparisons), you may want to use hash indexing.

A hash index is one that takes as input a column value, and using a specialized internal hash function, computes an output that is the physical address of that row (in effect, ROWID). If a column is of a NUMBER datatype, is uniformly distributed, and is not composite, you may choose to use it, rather than the internal hash function, to create the hash index. For example, if you re-create the DEPTS_EMPS cluster using a hashed index, the syntax would be

SQL> CREATE CLUSTER DEPTS_EMPS (DEPTID NUMBER (9))
        2> SIZE 256
       3> HASH IS DEPTID HASHKEYS 29
       3> TABLESPACE DATAn
          4> STORAGE (...);

The HASH IS option tells Oracle to use the DEPTID column instead of the internal hash function. The HASHKEYS argument tells how many hash buckets to create to hold the hash cluster key index (DEPTID) output values. This argument should be set equal to the number of distinct cluster key values, rounded up to the next highest prime number. In this case, suppose you have 25 departments (DEPTIDs). You round up to 29, the next highest prime number. For further information on how to size and manage hash clusters and indexes, please refer to the Oracle Server Administrator's Guide.

Previous | Table of Contents | Next