Oracle Clusters

Oracle Clusters Version 11.2.0.3

General Information

Note: The word cluster in Oracle bears not relationship to its use in SQL Server and other RDBMS products.

Data Dictionary Objects

ALL_CLUSTERS	DBA_CLUSTERS	TAB$
ALL_CLU_COLUMNS	DBA_CLU_COLUMNS	USER_CLUSTERS
ALL_CLUSTER_HASH_EXPRESSIONS	DBA_CLUSTER_HASH_EXPRESSIONS	USER_CLU_COLUMNS
ALL_ALL_TABLES	DBA_ALL_TABLES	USER_CLUSTER_HASH_EXPRESSIONS
CLU$	OBJ$	USER_ALL_TABLES

System Privileges

ALTER ANY CLUSTER	CREATE CLUSTER	DROP ANY CLUSTER
CREATE ANY CLUSTER

Clustering By Hash

Data is co-located within a single block based on a hash key and a hash function.

Clustering By Index

Data is co-located within a single block based on a common column index.

Cluster Size Calculation

Size of the primary key of the parent + ((size of the primary key of the children) * # of children) - ((size of the primary key of the parent) * # of children).

For example:
If the parent is the employee table where the primary key is ssn (VARCHAR2(9)) and the child is table is pay where the primary key is ssn (VARCHAR2(9)) and date of payment (date datatype which is 7 bytes) ... on average if there are 300 children records then 9 + (300 * (9+7)) - (300 * 9) or 9 + (300 * 7) = 2,109.

Create Cluster by Hash

Single Table Hash Cluster

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type>)
PCTFREE <integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
SINGLE TABLE
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

conn uwclass/uwclass

CREATE CLUSTER sthc_si (srvr_id NUMBER(10))
SIZE 1024 SINGLE TABLE HASHKEYS 11
TABLESPACE uwdata;

set linesize 121

SELECT cluster_name, tablespace_name, key_size, hashkeys, single_table
FROM user_clusters;

CREATE TABLE si_hash
CLUSTER sthc_si (srvr_id) AS
SELECT *
FROM serv_inst;

SELECT table_name, cluster_name, tablespace_name
FROM user_tables;

set long 1000000

SELECT dbms_metadata.get_ddl('CLUSTER', 'STHC_SI')
FROM dual;

EXPLAIN PLAN FOR
SELECT srvr_id, COUNT(*)
FROM serv_inst
WHERE srvr_id = 503
GROUP BY srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT srvr_id, COUNT(*)
FROM si_hash
WHERE srvr_id = 503
GROUP BY srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

Multi-Table Hash Cluster

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE <integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

conn uwclass/uwclass

CREATE CLUSTER hcl_srvr_id (
si_clustercol NUMBER(10))
PCTFREE 0
TABLESPACE uwdata
HASHKEYS 141
ROWDEPENDENCIES;

col object_name format a30

SELECT object_name, object_type
FROM user_objects
ORDER BY object_type;

-- hashkeys must be a prime number. If it is not Oracle will choose the next prime number higher than the value you entered.

col tablespace_name format a15

SELECT cluster_name, cluster_type, tablespace_name, hashkeys, dependencies
FROM user_clusters;

CREATE TABLE cservers (
srvr_id    NUMBER(10),
network_id NUMBER(10),
status     VARCHAR2(1),
latitude   FLOAT(20),
longitude FLOAT(20),
netaddress VARCHAR2(15))
CLUSTER hcl_srvr_id (srvr_id);

CREATE TABLE cserv_inst (
siid          NUMBER(10),
si_status     VARCHAR2(15),
type          VARCHAR2(5),
installstatus VARCHAR2(1),
location_code NUMBER(10),
custacct_id   VARCHAR2(10),
srvr_id       NUMBER(10),
ws_id         NUMBER(10))
CLUSTER hcl_srvr_id (srvr_id);

col table_name format a25
col cluster_owner format a20
col cluster_name format a20
col clu_column_name format a20
col tab_column_name format a20

SELECT table_name, cluster_name, tablespace_name
FROM user_tables;

SELECT *
FROM user_clu_columns;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT srvr_id
FROM cservers s
WHERE EXISTS (
SELECT srvr_id
FROM cserv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

INSERT INTO cservers
SELECT * FROM servers;

COMMIT;

SELECT ora_rowscn, srvr_id, latitude, longitude
FROM cservers
WHERE srvr_id < 11;

UPDATE cservers
SET srvr_id = 999
WHERE srvr_id < 11
AND rownum = 1;

COMMIT;

col ora_rowscn format 999999999999999

SELECT ora_rowscn, srvr_id, latitude, longitude
FROM cservers
WHERE (srvr_id NOT BETWEEN 11 AND 900)
ORDER BY 2;

Hash Cluster with Hash Expression

CREATE CLUSTER <cluster_name> (
column_name> <data_type>, <column_name> <data_type>
HASHKEYS <integer>
HASH IS <hash_expression>);

conn uwclass/uwclass

CREATE CLUSTER cl_address
(postal_code NUMBER, country_id VARCHAR2(2))
HASHKEYS 16
HASH IS MOD(postal_code + country_id, 101);

SELECT cluster_name, tablespace_name, hashkeys,
degree, single_table
FROM user_clusters;

desc user_cluster_hash_expressions

set long 100000

SELECT cluster_name, hash_expression
FROM user_cluster_hash_expressions;

Create Cluster by Index

Create Index Cluster

CREATE CLUSTER <schema_name>. <cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE <integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

conn uwclass/uwclass

CREATE CLUSTER sc_srvr_id (
srvr_id NUMBER(10))
SIZE 1024;

desc user_clusters

set linesize 121

SELECT cluster_name, tablespace_name, hashkeys, degree, single_table
FROM user_clusters;

CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;

SELECT index_name, index_type, tablespace_name
FROM user_indexes;

CREATE TABLE cservers (
srvr_id    NUMBER(10),
network_id NUMBER(10),
status     VARCHAR2(1),
latitude   FLOAT(20),
longitude FLOAT(20),
netaddress VARCHAR2(15))
CLUSTER sc_srvr_id (srvr_id);

CREATE TABLE cserv_inst (
siid          NUMBER(10),
si_status     VARCHAR2(15),
type          VARCHAR2(5),
installstatus VARCHAR2(1),
location_code NUMBER(10),
custacct_id   VARCHAR2(10),
srvr_id       NUMBER(10),
ws_id         NUMBER(10))
CLUSTER sc_srvr_id (srvr_id);

SELECT table_name, cluster_name, tablespace_name
FROM user_tables;

-- load tables with data: Click Here

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT srvr_id
FROM cservers s
WHERE EXISTS (
SELECT srvr_id
FROM cserv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

Create Sorted Hash Cluster

Sorted Hash Cluster Demo 1

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE <integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T> -- combined length of all rows of a given hash key
TABLESPACE <tablespace_name>
INDEX
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

conn uwclass/uwclass

CREATE CLUSTER sorted_hc (
program_id NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT)
TABLESPACE uwdata
HASHKEYS 9
SIZE 750
HASH IS program_id;

SELECT cluster_name, tablespace_name, hashkeys
FROM user_clusters;

CREATE TABLE shc_airplane (
program_id  NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT,
customer_id VARCHAR2(3),
order_dt    DATE)
CLUSTER sorted_hc (program_id, line_id, delivery_dt);

CREATE TABLE reg_airplane (
program_id  NUMBER(3),
line_id     NUMBER(10),
delivery_dt DATE,
customer_id VARCHAR2(3),
order_dt    DATE)
TABLESPACE uwdata;

DECLARE
pid     shc_airplane.program_id%TYPE;
datemod NUMBER(10,5);
BEGIN
pid := 777;
FOR i IN 1..999
LOOP
    SELECT DECODE(pid, 737, 747, 747, 757,
    757, 767, 767, 777, 777, 737)
    INTO pid
    FROM dual;

    SELECT ROUND((EXTRACT(SECOND FROM SYSTIMESTAMP) *
    1000), -2) / 100
    INTO datemod
    FROM dual;

    INSERT INTO shc_airplane
    (program_id, line_id, delivery_dt, order_dt)
    VALUES
    (pid, i, SYSDATE+datemod, SYSDATE-datemod);

    INSERT INTO reg_airplane
    (program_id, line_id, delivery_dt, order_dt)
    VALUES
    (pid, i, SYSDATE+datemod, SYSDATE-datemod);
END LOOP;
COMMIT;
END;
/

SELECT program_id, COUNT(*)
FROM reg_airplane
GROUP BY program_id;

SELECT program_id, COUNT(*)
FROM shc_airplane
GROUP BY program_id;

SELECT *
FROM reg_airplane
WHERE rownum < 11;

SELECT *
FROM shc_airplane
WHERE rownum < 11;

exec dbms_stats.gather_table_stats(USER, 'REG_AIRPLANE');

exec dbms_stats.gather_table_stats(USER, 'SHC_AIRPLANE');

EXPLAIN PLAN
SET statement_id = 'reg' FOR
SELECT program_id
FROM reg_airplane
WHERE program_id = 757
ORDER BY line_id, delivery_dt;

EXPLAIN PLAN
SET statement_id = 'shc' FOR
SELECT program_id
FROM shc_airplane
WHERE program_id = 757
ORDER BY line_id, delivery_dt;

SELECT * FROM table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','reg','ALL'));

SELECT * FROM table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','shc','ALL'));

EXPLAIN PLAN
SET statement_id = 'cpu' FOR
SELECT program_id
FROM shc_airplane
WHERE program_id = 757
ORDER BY delivery_dt, line_id;

SELECT * FROM table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','cpu','ALL'));

Sorted Hash Cluster Demo 2

conn sh/sh

GRANT select ON sales TO uwclass;

conn uwclass/uwclass

CREATE CLUSTER sorted_hc (
prod_id NUMBER,
time_id DATE SORT)
TABLESPACE users
HASHKEYS 97
HASH IS prod_id;

CREATE TABLE shc_sales
CLUSTER sorted_hc (prod_id, time_id) AS
SELECT *
FROM sh. sales;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);

EXPLAIN PLAN
SET statement_id = 'reg'
FOR SELECT prod_id
FROM sh.sales
WHERE prod_id = 12292
ORDER BY time_id;

EXPLAIN PLAN
SET statement_id = 'shc'
FOR SELECT prod_id
FROM shc_sales
WHERE prod_id = 12292
ORDER BY time_id;

set linesize 121
set pagesize 40

SELECT * FROM table(dbms_xplan.display('PLAN_TABLE','reg','ALL'));

SELECT * FROM table(dbms_xplan.display('PLAN_TABLE','shc','ALL'));

Sorted Hash Cluster related bug (8438978) reported by Morgan

This should be fixed in the most recent releases but verify with your version.

SQL> CREATE CLUSTER sorted_hc (
program_id NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT)
TABLESPACE uwdata
HASHKEYS 11
HASH IS program_id;

Cluster created.

SQL> select object_name, object_type from user_objects order by 2,1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SORTED_HC                      CLUSTER <== the sorted hash cluster
BIX_SERV_INST_LOCATION_CODE    INDEX
BIX_SERV_INST_WS_ID            INDEX
IX_PROGRAM_ID                  INDEX
PK_SERVERS                     INDEX
PK_SERV_INST                   INDEX
PK_ZIP_CODE                    INDEX
SYS_IQ0000072193$$             INDEX   <== the hash cluster's index
AIRPLANES                      TABLE
POSTAL_CODE                    TABLE
SERVERS                        TABLE
SERV_INST                      TABLE

12 rows selected.

SQL> select table_name from user_indexes
2 where index_name = 'SYS_IQ0000072193$$';

TABLE_NAME
------------------------------
SORTED_HC <== relationship verified

SQL> drop index SYS_IQ0000072193$$; <== drop the cluster's index

Index dropped. <== this should be blocked

-- verify the index is truly gone ... it is
SQL> select object_name, object_type from user_objects order by 2,1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SORTED_HC                      CLUSTER <== the sorted hash cluster
BIX_SERV_INST_LOCATION_CODE    INDEX
BIX_SERV_INST_WS_ID            INDEX
IX_PROGRAM_ID                  INDEX
PK_SERVERS                     INDEX
PK_SERV_INST                   INDEX
PK_ZIP_CODE                    INDEX
AIRPLANES                      TABLE
POSTAL_CODE                    TABLE
SERVERS                        TABLE
SERV_INST                      TABLE

11 rows selected.

-- create a table in the cluster after index drop
SQL> CREATE TABLE shc_airplane (
program_id NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT,
customer_id VARCHAR2(3),
order_dt    DATE)
CLUSTER sorted_hc (program_id, line_id, delivery_dt);

Table created.

-- try to insert into the sorted hash cluster's table
SQL> insert into shc_airplane values (1, 1, SYSDATE, 'ABC', SYSDATE);
insert into shc_airplane values (1, 1, SYSDATE, 'ABC', SYSDATE)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_1], [], [], [], [], [], [], [], [], [], [], []

SQL>
-- this is repeatable in both 11.1.0.7 and the 11.2.0.0.1 Beta and following the error

SQL> SELECT * FROM v$version;

-- returns no response or error
/* Note: When creating a normal multi-table cluster by index Oracle does the following if the index does not exist: */

SQL> insert into cservers select * from servers;
insert into cservers select * from servers
*
ERROR at line 1:
ORA-02032: clustered tables cannot be used before the cluster index is built

Alter Cluster

INITRANS

ALTER CLUSTER <cluster_name> INITRANS <integer>;

ALTER CLUSTER icl_person INITRANS 2;

MAXTRANS

ALTER CLUSTER <cluster_name> MAXTRANS <integer>;

ALTER CLUSTER icl_person MAXTRANS 254;

PCTFREE

ALTER CLUSTER <cluster_name> PCTFREE <integer>;

ALTER CLUSTER icl_person PCTFREE 15;

PCTUSED

ALTER CLUSTER <cluster_name> PCTUSED <integer>;

ALTER CLUSTER icl_person PCTUSED 85;

Drop Cluster

Drop an empty cluster

DROP CLUSTER <cluster_name>;

DROP CLUSTER sthc_si;

Drop a cluster with its tables

DROP CLUSTER <cluster_name> INCLUDING TABLES;

DROP CLUSTER sthc_si INCLUDING TABLES;