Document Sample
Chapter22 Powered By Docstoc
					      Oracle8 Clusters                                                     22
                                                                            C H A P T E R

                                                                           3      3      3        3
                                                                           In This Chapter
          Oracle8 provides an additional method for storing Tables and
          Indexes called clusters. An Oracle8 cluster is a Schema Object
                                                                           Introduction to
          containing a group of Tables which share one or more
                                                                           Oracle8 clusters
          common Columns (cluster Key), which are stored on the same
          data block. Once Tables are created in clusters, their storage
                                                                           Creating clusters,
          format is transparent to the end User. You can access these
                                                                           cluster Tables, and
          Tables like any other nonclustered Table. Cluster Tables
                                                                           cluster Indexes
          should be predominantly used together in queries.

          Tables and Indexes stored in a cluster have the following        Guidelines in
          immediate advantages:                                            creating clusters

             3 Disk I/O and access times are reduced for queries           Managing clusters
               involving joins of clustered Tables.
                                                                           Gathering statistics
             3 The cluster Key value in a cluster is only stored once,     on clusters
               regardless of how many Rows in the cluster Tables
               contain the Value. This condition can reduce the            3      3      3        3
               physical Table storage requirements of a cluster Schema

Example   Figure 22-1 illustrates the comparison of storing the STUDENT
          and DEPARTMENT Tables in a clustered and nonclustered
          format. The cluster Key is the D_NO. The right side of the
          diagram shows a typical, nonclustered format. The STUDENTS
          Table and the DEPARTMENT Tables each reside in separate data
          blocks. The left side of the diagram shows how clustered
          Tables are stored. The values in the two related tables are
          merged into a single table structure and sorted by the cluster
          Key (D_NO, D_NAME, ID). All merged data is stored together in
          one area in the database.
556   Chapter 22 3 Oracle8 Clusters

                                                         DEPARTMENT TABLE
                                                         D_NO   D_NAME
                                                          104        Acct
                                                          340        Engr

                    DEPARTMENT / STUDENT CLUSTER
                                                                                 STUDENTS TABLE
              104    Acct   22    John          Smith
                                                                       ID FIRST_NAME LAST_NAME D_NO
                     Acct   56     Lani        Kaupo
                                                                       11    Patricia     Butler   340
                     Acct   47    Fred         Savage
                                                                       22     John        Smith    104
              340    Engr   44    Amy          Johnson
                                                                       83    Laurel      Watson    340
                     Engr   11   Patricia       Butler
                                                                       44     Amy        Johnson   340
                     Engr   83    Laurel       Watson
                                                                       47     Fred       Savage    104
                                                                       56     Lani        Kaupo    104

                                                     DATA BLOCK

                                                                DATA BLOCK

                                            DATA BLOCK

           Figure 22-1: Cluster and noncluster Table formats

           Creating clusters, cluster Tables, and cluster Indexes
           The following Objects comprise a cluster:

              3 The cluster Schema Object
              3 The cluster Table
              3 The cluster Index

           Creating cluster Schema Objects
           To create a cluster in your Schema, you must have the CREATE CLUSTER system
           privilege. To create a cluster in another Schema, you must use the CREATE ANY
                                                                        Chapter 22 3 Clusters   557

            CLUSTER system privilege. Since the cluster requires storage space, you must also
            have a quota for the Tablespace intended to contain the cluster or have the
            UNLIMITED TABLESPACE system privilege.
Reference     CREATE CLUSTER
            Create a cluster by executing the SQL CREATE CLUSTER command with the
            SQL*Plus or Oracle SQL Worksheet Oracle8 tools. The following syntax creates a

              CREATE CLUSTER Cluster Name
              (Column_Name Datatype
               Column_Name Datatype,...)
              [SIZE                    integer]
              [PCTFREE                 integer]
              [PCTUSED                 integer]
              [INITRANS                integer]
              [MAXTRANS                integer]
              [TABLESPACE      Tablespace_Name]
              [STORAGE CLAUSE]

            A cluster can also be created using the Oracle8 Schema Manager. Figure 22-2
            introduces the Schema Manager Create Cluster dialog box. Enter general
            information about the cluster, such as the cluster’s name and the creation
            Tablespace location, in this dialog box.

            Figure 22-2: Create clusters using the
            Oracle8 Schema Manager.
558      Chapter 22 3 Oracle8 Clusters

              Figure 22-3 illustrates the addition of CREATE CLUSTER parameters via the Oracle8
              Schema Manager Create Cluster interface.

                                                      Figure 22-3: Specify cluster parameters
                                                      by clicking the Storage tab in the Cluster
                                                      Definition box.

              The following list describes the CREATE CLUSTER syntax parameters:

                 3 Cluster Name. The name of the cluster to be created.
                 3 Column_Name. The name of a Column in the cluster Key.
                 3 Datatype. The datatype of the cluster Key Column.
                 3 SIZE. The amount of byte space to store all rows with the same cluster Key
                 3 PCTFREE. The limit Oracle8 uses to determine when you can add additional
                   rows to a cluster’s data block.
                 3 PCTUSED. The space reserved in each cluster’s data blocks for future updates.
                 3 INITRANS. The initial number of concurrent update transactions allocated for
                   data blocks of the cluster. This value cannot be less than two or greater than
                 3 MAXTRANS. The maximum number of concurrent update transactions for any
                   given data block belonging to the cluster. This value cannot be less than the
                   value of INITRANS parameter or greater than 255. The default value is the
                   MAXTRANS value of the Tablespace containing the cluster.
                 3 TABLESPACE. The Tablespace in which the cluster is to be created.
                 3 STORAGE. The method of data blocks allocation to the cluster.

  See           For example, the following SQL statement uses the CREATE CLUSTER command
  Reference     to create the student_dept cluster:
                                                                     Chapter 22 3 Clusters    559

                  CREATE CLUSTER student_dept (dept_id INTEGER)
                  PCTUSED 80
                  PCTFREE 5
                  SIZE 600
                  TABLESPACE library
                  STORAGE ( INITIAL 200k
                                 NEXT 300k
                                 MINEXTENTS 2
                                 MAXEXTENTS 20
                                 PCTINCREASE 33);

       Creating clustered Tables
       To create a Table in a cluster, you must either have the CREATE TABLE or CREATE
       ANY TABLE system privilege. The Tablespace quota or the UNLIMITED TABLESPACE
       system privilege is not required to create a clustered Table. The Table is contained
       in a cluster that already has allocated space.

       A Table can be created in a cluster using the CREATE TABLE SQL command with the
       CLUSTER option. Execute this command with SQL*Plus or Oracle SQL Worksheet.
       Cluster Tables can also be created interactively using the Oracle8 Schema Manager.

Note   The Table name must be unique within the User’s Schema.

       For example, the following SQL statement creates the STUDENTS Table in the
       student_dept cluster:

             STUDENT_ID           INTEGER                NOT NULL,
             STUDENT_FIRST_NAME   VARCHAR2(25)           NULL,
             STUDENT_LAST_NAME    VARCHAR2(25)           NULL,
             STUDENT_DEPT_ID      INTEGER                NULL,
             STUDENT_ADDRESS      VARCHAR2(50)           NULL,
             STUDENT_CITY         VARCHAR2(25)           NULL,
             STUDENT_STATE        VARCHAR2(15)           NULL,
             STUDENT_ZIP          VARCHAR2(10)           NULL,
         CLUSTER student_dept (STUDENT_DEPT_ID);

       The following SQL statement creates the DEPARTMENT Table in the student_dept

             DEPT_ID              INTEGER                NOT NULL,
             DEPT_NAME            VARCHAR2(25)           NULL,
         ) CLUSTER student_dept (DEPT_ID);
560         Chapter 22 3 Oracle8 Clusters

   Note          A clustered Table can be created in a different Schema than the Schema containing
                 the cluster.

                 Creating cluster Indexes
                 A cluster Index must be created before you can load any data into a clustered
                 Table. To create a cluster Index, one of the following conditions must be true:

                    3 Your Schema contains the cluster and you have the CREATE INDEX system
                    3 You have the CREATE ANY INDEX system privilege.

                 In either case, you must also have a quota for the Tablespace intended to contain
                 the cluster Index or the UNLIMITED TABLESPACE system privilege.
  Reference        CREATE INDEX
                 Create a cluster Index in a cluster using the CREATE INDEX SQL command with the
                 CLUSTER option.

                 For example, the following SQL statement creates the student_dept_index for the
                 student_dept cluster:

                   CREATE INDEX student_dept_index
                   ON CLUSTER student_dept
                   INITRANS        2
                   MAXTRANS        5
                   PCTFREE        10
                   STORAGE (     INITIAL 50k
                                 NEXT 300k
                                 MINEXTENTS 2
                                 AXEXTENTS 10
                                 PCTINCREASE 33);

   Note          When estimating the size of cluster Indexes, remember the Index is on each cluster
                 Key, not the actual rows; therefore, each Key only appears once in the Index.

                 Guidelines in creating clusters
  Caution        In comparison with storing Tables separately with their own Indexes, clusters can
                 reduce the performance of SQL DML statements (INSERT, UPDATE, and DELETE).
                 This reduction occurs because multiple cluster Tables share the same data block.
                 Thus, Oracle8 may require more blocks to store a clustered Table than a
                 nonclustered Table. This storing method can increase the space required to hold the
                 clustered Tables and the number of blocks that it must traverse for a SQL query.
                                                            Chapter 22 3 Heading 1     561

The following sections discuss the following guidelines to help you determine
which tables you should cluster:

   3 Selecting candidate cluster Tables
   3 Identifying the cluster Key
   3 Optimizing the cluster Index
   3 Using the PCTFREE and PCTUSED parameters
   3 Using the SIZE parameter
   3 Using the TABLESPACE parameter

Selecting candidate cluster Tables
To identify candidate Tables that should be created in a cluster, look for Tables:

   1. Related via referential integrity Constraints
   2. Joined together in SELECT statements

Oracle8 stores all rows sharing the same cluster Key in the same data block. Thus,
clustering Tables based on Columns used in joins reduces the number of data
blocks you must scan to process the query — all rows needed for a join on a cluster
Key are in the same data block. This reduction improves the performance on join

Single Tables that you do not access via SQL joins can also be good candidates for
creation in a cluster. This creation is advantageous if a Table is queried
predominately by a particular Column. For example, if the STUDENTS Table is
queried department by department, the STUDENTS Table could be created in a
cluster where the cluster Key would be the dept_id.

Clusters do not affect application design because their format for storing Tables is
transparent to the end User. Regardless, only Tables predominately queried using
SQL SELECT statements that involve table joins should be created in clusters.
Therefore, application developers should be aware of Tables created in clusters to
monitor transactions types executed against the respective cluster Tables.

Identifying the cluster Key
The cluster Key is the Column or group of Columns that the cluster Tables have in
common. You specify the cluster Key Columns when creating the cluster and every
Table added to the cluster.

Every Table created in the cluster must have a Column that matches the size and
type of the Columns specified in the cluster Key. The actual Column names of each
Table do not have to match. The maximum number of Columns available in a
cluster Key is 16, with a maximum length of 239 bytes. Thus, cluster Keys cannot
include Columns that include the LONG or LONG RAW datatypes.
562         Chapter 22 3 Oracle8 Clusters

                 Cluster Keys should contain unique values allowing their respective Rows to fill
                 approximately one data block. If you choose cluster Keys incorrectly, only a few
                 rows per cluster Key are stored in a data block. This situation wastes space and
                 causes queries to scan unnecessary data blocks.

                 Data contained in cluster Tables can be inserted, updated, and deleted. The
                 placement of data depends on the cluster Key, however. Changing the cluster Key of
                 a row in an UPDATE operation may cause Oracle8 to physically relocate the row.
                 Therefore, frequently updated Columns are not good candidates for the cluster Key.

                 Optimizing the cluster Index
                 You must create a cluster Index before you can execute any SQL DML statements
                 against any clustered Tables. Thus, clustered Tables cannot be populated without a
                 cluster Index.

                 A cluster Index must be created on the cluster Key Columns after you have created
                 a cluster. Defined specifically for clusters, a cluster Index contains an entry for each
                 cluster Key value. Use the cluster Index to point to the data block that contains a
                 cluster row based on a cluster Key value.

                 You can place cluster Indexes in clusters that are not Indexed.

   Note          Oracle assesses a given row with a minimum of two I/Os, depending on the number
                 of levels traversed in the Index. Cluster Indexes differ from nonclustered Indexes in
                 the following ways:

                    3 They can contain Keys with a NULL value.
                    3 The cluster Indexes point to the first data block in the chain for a given
                      cluster Key value.
                    3 A cluster Index contains one entry per cluster Key value, rather than one
                      entry per cluster row.

  Caution        If you drop a cluster Index, data in the cluster remains but is inaccessible. Oracle8
                 only permits access to the cluster data after another cluster Index is created based
                 on the cluster Key values.

                 Suppose you want to move an Index to another Tablespace or alter its storage
                 requirements. These actions require you to drop and re-create a cluster Index. The
                 cluster’s data is not available after you drop the cluster Index, but becomes
                 available again after you re-create the cluster Index.

                 Using the PCTFREE and PCTUSED parameters
                 Use the PCTFREE and PCTUSED parameters specified in the CREATE CLUSTER
                 command to optimize space utilization. These parameters also optimize the
                 amount of space reserved for updates to the current rows in a data block of a
                 cluster’s data segment.
                                                                         Chapter 22 3 Clusters     563

 Note     PCTFREE and PCTUSED parameters set for Tables created in a cluster are ignored
          because cluster Tables automatically use the settings for the cluster in which they
          are created.

          Using the SIZE parameter
          The SIZE parameter in the CREATE CLUSTER command specifies the average
          amount of space required to store all rows for a cluster Key value.

          Use the SIZE parameter to optimize the number of cluster Keys that can fit in a
          cluster data block. This optimization maximizes the storage efficiency of Keys
          within a cluster.

          The maximum number of cluster Key values per data block is fixed by the SIZE
          parameter. Actually, Oracle8 does not reserve space for each cluster Key value or
          guarantee the number of cluster Keys you can assign to a block.

Example   For example, if each data block has 2100 bytes of available space and the specified
          cluster Key size is 600 bytes, each data block can potentially hold rows for three
          cluster Keys. If the SIZE parameter is greater than the amount of available space
          per data block, each data block contains Rows for only one cluster Key value.

          If the cluster Key rows grow and no longer fit in one data block, the cluster Key data
          rows flow onto other data blocks in the cluster. This overflow causes the data blocks
          to chain together. The cluster Index points to the beginning of the chain of the data
          blocks, each of which contains the cluster Key value and the associated rows. This
          chaining action speeds data access to all values within the given cluster Key.

          For example, if the SIZE parameter determines that four cluster Key values are
          allowed per data block, this limitation does not prevent rows for one cluster Key
          Value from taking up all available space in the block. If more rows exist for a given
          Key than can fit in a single block, the block is chained.

 Note     A cluster Key value is only stored once in a data block.

          Using the TABLESPACE parameter
          Use the TABLESPACE parameter in the CREATE CLUSTER or CREATE INDEX
          statements to identify the Tablespace in which you wish to store the new cluster or
          Index. Specifying a Tablespace allows the cluster and Index Schema Objects to be
          created in different Tablespaces. If a cluster and its respective Index are created on
          two different Tablespaces stored on different physical devices, you can retrieve the
          cluster data and Index data simultaneously.
564      Chapter 22 3 Oracle8 Clusters

              Managing cluster Schema Objects
              Managing a cluster involves the modification and deletion of the following Oracle8
              Schema Objects:

                 3 Clusters
                 3 Cluster Tables
                 3 Cluster Indexes
              You can manage cluster Schema Objects using the Oracle Schema Manager or by
              executing the code in the following section using SQL*Plus or Oracle SQL

              Altering clusters
              One of the following conditions must exist to alter a cluster: you must have the
              ALTER ANY CLUSTER system privilege or the cluster must exist in your Schema.
  Reference     ALTER CLUSTER
              To alter a cluster, use the ALTER CLUSTER command. The syntax follows:

                ALTER CLUSTER Cluster Name
                {PCTUSED integer
                |PCTFREE     integer

                |SIZE         integer

                |INITRANS       integer

                |MAXTRANS integer

                |STORAGE CLAUSE}

              Only the following cluster parameters can be altered using the ALTER CLUSTER

                 3 The PCTFREE and PCTUSED data block space usage parameters
                 3 The average cluster Key SIZE parameter
                 3 The INTRANS and MAXTRANS transaction entry settings
                 3 The storage parameters

              If you alter the PCTFREE and PCTUSED data block usage parameters or the cluster
              SIZE parameter, this alteration applies to all currently allocated and subsequently
              assigned data blocks used by the cluster.
                                                                   Chapter 22 3 Heading 1     565

Note   Oracle8 recognizes altering data blocks already allocated for the cluster on an as
       needed basis, not immediately!

       When you alter the INITRANS or MAXTRANS transaction entry parameters of a
       cluster, the new setting for INITRANS applies only to the data blocks subsequently
       allocated for a cluster. Alternately, a new MAXTRANS value applies to all presently
       and subsequently allocated blocks for a cluster.

       The storage parameters INITIAL and MINEXTENTS cannot be altered. All new
       settings for the other storage parameters only affect extents subsequently allocated
       for the cluster.

       For example, the following SQL statement alters the PCTFREE and PCTUSED
       parameters for the student_dept cluster:

         ALTER CLUSTER student_dept
         PCTFREE 25
         PCTUSED 65;

       Altering clustered Tables
       One of the following conditions must exist to alter a clustered Table: you must have
       the ALTER ANY TABLE system privilege or the clustered Table must exist in your

       Use the ALTER TABLE command to alter clustered Tables.

       Only the following SQL operations can be executed with the ALTER TABLE
       command for clustered Tables:

          3 Create or modify cluster Table Columns
          3 Create, drop, enable, or disable integrity Constraints or Triggers for a
            clustered Table

       You cannot use data block-related parameters, transaction entry parameters, or
       storage parameters in conjunction with the ALTER TABLE statement for a clustered
       Table. Oracle8 uses the parameters of the cluster to apply to all clustered Tables.

       Use the standard ALTER INDEX SQL command to alter cluster Indexes.

       Dropping clusters
       One of the following conditions must exist to drop a cluster: you must have the
       DROP ANY CLUSTER system privilege or the cluster must exist in your Schema. The
       following syntax drops a cluster:

         DROP CLUSTER Cluster Name
566      Chapter 22 3 Oracle8 Clusters

              For example, the following SQL statement drops the student_dept cluster:

                DROP CLUSTER student_dept;

              When a cluster is dropped, the following Schema Objects are also dropped:

                 3 Tables within the respective cluster
                 3 The corresponding cluster Index

              All extents belonging to both the cluster’s data segment and Index segment are
              returned to the containing Tablespace. They immediately become available for
              other segments within the Tablespace to occupy.

              Dropping clustered Tables
              One of the following conditions must exist to drop a clustered Table: your Schema
              must contain the cluster or you must have the DROP ANY TABLE system privilege.
              Clustered Tables can be dropped individually without affecting the actual cluster,
              other clustered Tables, or even cluster Indexes.

  Note        A clustered Table is dropped like a nonclustered Table by using the DROP TABLE

              For example, the following syntax drops the STUDENTS Table from the
              student_dept cluster:

                DROP TABLE students;

              While dropping a clustered Table, Oracle8 deletes each row individually. To avoid
              this time-consuming task, drop the entire cluster with the INCLUDING TABLES
              option. For example:

                DROP CLUSTER student_dept INCLUDING TABLES;

  Note        If you do not use the INCLUDING TABLES option when the cluster contains Tables,
              Oracle8 returns an error message to the end User.

              Dropping cluster Indexes
              A cluster Index can be dropped without affecting the cluster or its clustered Tables.
              Dropping a cluster Index immediately marks the cluster as inaccessible. To enable
              end Users to access the cluster, You must re-create the cluster Index. Cluster
              Indexes are dropped as part of a Procedure to rebuild the fragmented cluster

              To drop a cluster without Tables and its respective cluster Index, use the SQL
              command DROP CLUSTER. For example:

                DROP CLUSTER student_dept;
                                                              Chapter 22 3 Clusters     567

If one or more Tables in a cluster contain Primary or Unique Keys referenced by
Foreign Key Constraints of Tables outside the cluster, the cluster cannot be
dropped unless the dependent FOREIGN KEY Constraints are dropped first.

Remove FOREIGN KEY dependencies by using the following CASCADE CONSTRAINTS
option of the DROP CLUSTER command:


Gathering information on clusters
Use the following Data Dictionary views to gather statistics on clusters in your


You can also use the ANALYZE command to collect statistics about clusters. The
cluster must be in your own Schema or you must have the ANALYZE ANY system
privilege to use the ANALYZE command.

Use the ANALYZE command to:

   3 Collect statistics about clusters used by the Optimizer and store the results in
     the Oracle8 Data Dictionary.
   3 Validate the structure of the cluster Schema Object.
   3 Identify migrated and chained rows of a cluster into a Table local to your

The general syntax for using the ANALYZE command follows:






  |LIST Chained ROW INTO Table Name}

For example, the following statement computes statistics for the STUDENTS Table
and its respective Indexes in the cluster STUDENT_DEPT:
568   Chapter 22 3 Oracle8 Clusters

                    COMPUTE STATISTICS;

           View the actual results of this operation using the USER_CLUSTERS and
           USER_CLU_COLUMNS Data Dictionary views.

           Striving to optimize storage allocation for Tables, Oracle8 provides technique of
           clustering to the DBA . An Oracle8 cluster can be thought of as a container that
           stores Tables sharing the same Primary Key. Clustered Tables, are stored on the
           same data block, with their respective storage format transparent to the User.

           The following key points summarize this chapter:

              3 Oracle8 can cluster Tables and their respective Indexes.
              3 Two types of Table clustering techniques can be implemented: Clusters based
                on a cluster Key (Primary Key) and clusters based on a hash function.
              3 Cluster Keys are only stored once.
              3 Clustering, if implemented correctly, can reduce disk I/O for queries that
                involve complex joins.
              3 A cluster index must be created before any data can be loaded into any
                cluster Table.
              3 Only use hash clusters on Tables where the queries use equality operators
                and the Tables are relatively static in size.
              3 Upon creation, the storage parameters can be used to optimize the storage
                allocation and performance of the clusters.

                                         3       3        3

Shared By:
Rajesh lk.rajeshkumar Rajesh lk.rajeshkumar Senior Web Developer
About I am a IT Professional