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