Clusters

Clustering can boost the performance on Pin queries because rows that are joined are stored together. This way the operating system doesn't have to search for the data over a wide area, thus reducing data retrieval time. To cluster tables, you must own the tables you are going to cluster together.

Clustering works by doing the same thing on disk that a view or a join can do no your terminal screen. I takes related data, even from separate tables, and puts, them all in the same place.

To be clustered, a group of tables must share a column with the same type, length and meaning. Such column is called a cluster column or column key. An example, in our sample tables is the DEPTNO column in the EMP and DEPTS tables: in both tables, this column is a NUMBER column of default

length, and contains department numbers.

The cluster column need not have the same name in each table in a cluster, although if often does.

Clustering a group of tables has the following effect :

The rows from all of the tables that have the same value in their cluster columns are stored in the same disk page (or pages, if they don't fit in one page).

Each distinct value that appears in the cluster columns is stored in the database only once. This saves space and retrieval time.

SQL*Plus creates an index on the cluster column(s) defined in the CREATE CLUSTER command This index is called the cluster index.

ORACLE uses it to improve the performance of operations that require searching a table on a cluster column, just as it would use an ordinary index.

For example, if EMP and DEPTS were to be clustered on their respective DEPTNO columns, the rows of EMT for LANGER, KAUL, KAR, MUNSHI, etc. and the row of DEPTS for DESIGN would be stored in the same disk page(s), because they all have the value 2 in the cluster column. The value 2 would be stored only one, although it occurs six times in the two cluster columns.

Clustering affects only the way your data is physically stored on disk. It has on effect on the logical appearance of your data when you retrieve or display them. All the usual SQL*Plus commands will work as normal.
It's possible to have more than one cluster column In a single cluster. As a rule of thumb, you can establish cluster columns in much the same way, and for much the same reasons, as you would establish indexes on the same columns.

The benefits of clustering tables on several sets of cluster columns are even more effeicient Joins and more efficient use of disk storage.

The first step in creating clustered tables is to create a cluster; then you must create the tables and specify that they are to be members of the cluster.

You can create a cluster with the CREATE CLUSTER command.

The syntax of the command isS :

CREATE CLUSTER cluster (column specifications, column specifications,....);

The cluster is assigned the name cluster. Each column gives a cluster column a name by which it known in the cluster itself. This name need not be the same as the name of the cluster column 11, any of the clustered tables, although it's often practical to keep it the same as all of them. The specification describes the type and width of the respective column.

To create a cluster FMP DEPTS, with a cluster column named DEPTNO of type "number" and default width, enter:

SQL > CREATE CLUSTER EMP DEPTS
2 (DEPTNUM NUMBER(2));
Cluster created.

This creates a cluster (a space Is set aside, as it would be for a table) with nothing in it. The use of DEPTNUM for the cluster key is irrelevant; you will never use it again.

Now that the cluster has been created, you can install any number of tables in it. The table must have cluster column(s) matching the columns described in the CREATE CLUSTER command, and matching specifications.

You cannot move existing table into a cluster. You can create new tables though, including tables with the same contents as existing tables.
In the second. form, query returns a set of rows which are inserted in the newly created table. The query also determines the specifications of. the table's rows. The number of columns returned by the query must be the same as the number of columns named in parenthesis after the table name.

We will now add two tables to the EMP DEPTS cluster we just created, using the second form of the
CREATE CLUSTER command described above. The tables are CL DEPTS and CL EMP, which are
structurally identical to the DEPTS and EMP tables respectively.

SQL > CREATE TABLE CL EMP
2 CLUSTER EMP DEPTS(DEPTNO)
3 AS SELECT*FROM DEPTS;

Table created.

SQL > CREATE TABLE CL EMP
2 CLUSTER EMP DEPTS(DEPTNO)
3 AS SELECT11FROM EMP;

Table created

Note that since the cluster column has to be NOT NULL, if the DEPTNO column in the EMP and DEPTS tables did not have the NOT NULL specification, then the column would have to be made NOT NULL using the ALTER TABLE coqimand and then the tables could be added to the cluster.

It is sometimes useful to cluster a single table. The benefit of more efficient join operations of course doesn't apply here, but the disk storage is utilized more efficiently. If a table contains many rows with the same value in the cluster column, clustering can yield a substantial reduction in disk storage requirements. On the other hand, if a table contains many values in the cluster column, and only a few rows have i, repeated values, clustering can actually increase storage requirements.

If you wish to determine whether clustering a particular table is worthwhile, you can do so by asking your DBA to query the data dictionary's STORAGE table to observe the effect on storage requirements before and after clustering.

Removing Clustered table

You can use the DROP command to get rid of a clustered table,just as you can wipe out its conventional counterparts. if, however you wish to retain an unclustered version of the table, then you go through the following steps:
Determine what indexes are defined on the original table. Print their definitions.

Create a new table outside the cluster with the same columns as the table you want to "remove", and copy the original table's data to it. Then drop the original table (SQL *Plus will drop the table's indexes automatically).

3. Rename the new table so that its name is the same as the original table.

4. Recreate any indexes you wish to retain

Since we know that there aren't any indexes on the CL EMP table, we don't have to do the first step. We first create a copy of the clustered table outside the cluster.

SQL > CREATE TABLE UNCL EMP
2 AS (SELECT*FROM CL EMP);

Table created.

Drop the clustered table, just like any ordinary table

SQL > DROP TABLE CL EMP;

Table dropped

Rename the copy:

SQL > RENAME UNCL EMP TO CL EMP;

Rename succeeded.

To view information on the clusters that you have created, query the table CLUSTERS in the data dictionary. (check if you have access to this table). This table contains a list of active and their component tables.

SQL > SELECT CLNAME, TNAME FROM CLUSTER;

In a similar fashion you could find out what indexes have been defined on your tables by querying the INDEXES table:

If you wish to remove the cluster entirely from the database, then you have to first drop all the tables that are members of the cluster. Once that is done, you can drop the cluster using the DROP CLUSTER command.
The syntax of the command is:

DROP CLUSTER cluster;

because the sequence number are automatically generated and can be use to coordinate keys across multiple. tables.

You can try the same by first dropping the CL DEPTS, table and then dropping the EMP DEPTS cluster.

Domain Name Search