Oracle Clusters
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Note: The word cluster in Oracle bears not relationship to its use in SQL Server and other RDBMS products.
Data Dictionary Objects
ALL_CLUSTERS CDB_CLUSTER_HASH_EXPRESSIONS OBJ$
ALL_CLU_COLUMNS CDB_CLU_COLUMNS TAB$
ALL_CLUSTER_HASH_EXPRESSIONS DBA_ALL_TABLES USER_CLUSTERS
ALL_ALL_TABLES DBA_CLUSTERS USER_CLU_COLUMNS
CLU$ DBA_CLUSTER_HASH_EXPRESSIONS USER_CLUSTER_HASH_EXPRESSIONS
CDB_ALL_TABLES DBA_CLU_COLUMNS USER_ALL_TABLES
CDB_CLUSTERS    
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. The phrase "HASH INDEX" refers to these objects.
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@pdbdev

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@pdbdev

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@pdbdev

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@pdbdev

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@pdbdev

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@pdborcl

GRANT select ON sales TO uwclass;

conn uwclass/uwclass@pdbdev

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'));
The Sorted Hash Cluster related  bug (8438978) reported by Morgan has been fixed as of 11.2.0.3 as shown here 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$$; <== attempt to drop the cluster's index
                *
ERROR at line 1:
ORA-43814: DROP INDEX is invalid for hash cluster index
 
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;

Related Topics
DBMS_REPAIR
Indexes
Tables
Tablespaces
Truncate

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved