CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Clusters
Version 11.1
 
General Information
Note: The word cluster in Oracle bears not relationship to its use in SQL Server and other RDBMS products.
Data Dictionary Objects
clu$    
DBA ALL USER
dba_clusters all_clusters user_clusters
dba_clu_columns all_clu_columns user_clu_columns
dba_cluster_hash _expressions all_cluster_hash _expressions user_cluster_hash _expressions
dba_all_tables all_all_tables user_all_tables
System Privileges

alter any cluster

create any cluster create cluster drop 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.
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 Single-Table 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>;
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);
 
Create Multi-Table Cluster by Hash

Multi-Table Cluster by Hash
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>;
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;
 
Create Multi-Table Cluster by Index

Multi-Table Cluster by Index
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>;
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);
 
Sorted Hash Cluster

Sorted 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
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;
CREATE CLUSTER sorted_hc (
program_id  NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT)
TABLESPACE uwdata
HASHKEYS 9
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'));
 
Additional Hash Cluster Options
Create Hash Cluster With Hash Expression CREATE CLUSTER <cluster_name> (
column_name> <data_type>, <column_name> <data_type>
HASHKEYS <integer>
HASH IN <hash_expression>);
CREATE CLUSTER cl_address
(postal_code NUMBER, country_id VARCHAR(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;
 
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 10;
PCTUSED ALTER CLUSTER <cluster_name>
PCTUSED <integer>
ALTER CLUSTER icl_person
PCTUSED 80;
 
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
Indexes
Tables
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [69 users online]    © 2010 psoug.org