Oracle Clusters Table Index

Search the Reference Library pages:

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

-----