Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. The cluster key is the column or columns by which the tables are usually joined in a query.
When a query is fired that joins these 2 tables by Cluster Key, the joined rows would be fetched with a single IO operation.
A cluster is a data structure that improves retrieval performance.
WITHOUT CLUSTERING
SQL> CREATE TABLE dept
2 (
3 deptno NUMBER(2,0),
4 dname VARCHAR2(14),
5 loc VARCHAR2(13),
6 CONSTRAINT pk_dept PRIMARY KEY (deptno)
7 );
Table created.
SQL> CREATE TABLE emp
2 (
3 empno NUMBER(4,0),
4 ename VARCHAR2(10),
5 job VARCHAR2(9),
6 mgr NUMBER(4,0),
7 hiredate DATE,
8 sal NUMBER(7,2),
9 comm NUMBER(7,2),
10 deptno NUMBER(2,0),
11 CONSTRAINT pk_emp PRIMARY KEY (empno),
12 CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
13 );
Table created.
--After Inserting Data.
/*
DATABASE OBJECT NUMBER ::: AAAVh0
RELATIVE FILE NUMBER ::: AAH
BLOCK NUMBER ::: AAAT+F
ROW NUMBER ::: AAA - AAN
*/
SQL> SELECT ROWID,e.* FROM emp e;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
AAAVh0AAHAAAT+FAAA 7839 KING PRESIDENT 17-NOV-81 5000 10
AAAVh0AAHAAAT+FAAB 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
AAAVh0AAHAAAT+FAAC 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
AAAVh0AAHAAAT+FAAD 7566 JONES MANAGER 7839 02-APR-81 2975 20
AAAVh0AAHAAAT+FAAE 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
AAAVh0AAHAAAT+FAAF 7902 FORD ANALYST 7566 03-DEC-81 3000 20
AAAVh0AAHAAAT+FAAG 7369 SMITH CLERK 7902 17-DEC-80 800 20
AAAVh0AAHAAAT+FAAH 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
AAAVh0AAHAAAT+FAAI 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
AAAVh0AAHAAAT+FAAJ 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
AAAVh0AAHAAAT+FAAK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
AAAVh0AAHAAAT+FAAL 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
AAAVh0AAHAAAT+FAAM 7900 JAMES CLERK 7698 03-DEC-81 950 30
AAAVh0AAHAAAT+FAAN 7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
/*
DATABASE OBJECT NUMBER ::: AAAVhy
RELATIVE FILE NUMBER ::: AAH
BLOCK NUMBER ::: AAAT71
ROW NUMBER ::: AAA - AAD
*/
SQL> SELECT ROWID,d.* FROM dept d;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVhyAAHAAAT71AAA 10 ACCOUNTING NEW YORK
AAAVhyAAHAAAT71AAB 20 RESEARCH DALLAS
AAAVhyAAHAAAT71AAC 30 SALES CHICAGO
AAAVhyAAHAAAT71AAD 40 OPERATIONS BOSTON
--The BLOCK NUMBER is different for EMP & DEPT tables.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('COURSE','EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('COURSE','DEPT');
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno;
ENAME LOC
---------- -------------
CLARK NEW YORK
MILLER NEW YORK
KING NEW YORK
FORD DALLAS
SCOTT DALLAS
JONES DALLAS
SMITH DALLAS
ADAMS DALLAS
WARD CHICAGO
MARTIN CHICAGO
TURNER CHICAGO
JAMES CHICAGO
ALLEN CHICAGO
BLAKE CHICAGO
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
SQL> DROP TABLE emp PURGE;
Table dropped.
SQL> DROP TABLE dept PURGE;
Table dropped.WITH CLUSTERING
SQL> CREATE CLUSTER empdept (deptno NUMBER(2,0));
Cluster created.
SQL> SELECT cluster_name,cluster_type FROM user_clusters WHERE cluster_name = 'EMPDEPT';
CLUSTER_NAME CLUSTER_TYPE
-------------- -------------
EMPDEPT INDEX
SQL> CREATE INDEX empdept_indx ON CLUSTER empdept;
Index created.
SQL> SELECT index_type,table_name,table_type,uniqueness FROM user_indexes WHERE index_name = 'EMPDEPT_INDX';
INDEX_TYPE TABLE_NAME TABLE_TYPE UNIQUENESS
----------- ----------- ------------ ------------
CLUSTER EMPDEPT CLUSTER UNIQUE
SQL> CREATE TABLE dept
2 (
3 deptno NUMBER(2,0),
4 dname VARCHAR2(14),
5 loc VARCHAR2(13),
6 CONSTRAINT pk_dept PRIMARY KEY (deptno)
7 )
8 CLUSTER empdept(deptno);
Table created.
SQL> CREATE TABLE emp
2 (
3 empno NUMBER(4,0),
4 ename VARCHAR2(10),
5 job VARCHAR2(9),
6 mgr NUMBER(4,0),
7 hiredate DATE,
8 sal NUMBER(7,2),
9 comm NUMBER(7,2),
10 deptno NUMBER(2,0),
11 CONSTRAINT pk_emp PRIMARY KEY (empno),
12 CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
13 )
14 CLUSTER empdept(deptno);
Table created.
SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'EMP';
no rows selected
SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'DEPT';
no rows selected
--After Inserting Data.
/*
DATABASE OBJECT NUMBER ::: AAAVh2
RELATIVE FILE NUMBER ::: AAH
BLOCK NUMBER ::: AAAByF (Deptno = 10)
ROW NUMBER :::
AAA
AAB
AAC
BLOCK NUMBER ::: AAAByG (Deptno = 20)
ROW NUMBER :::
AAA
AAB
AAC
AAD
AAE
BLOCK NUMBER ::: AAAByH (Deptno = 30)
ROW NUMBER :::
AAA
AAB
AAC
AAD
AAE
AAF
*/
SQL> SELECT ROWID,e.* FROM emp e;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
AAAVh2AAHAAAByFAAA 7839 KING PRESIDENT 17-NOV-81 5000 10
AAAVh2AAHAAAByFAAB 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
AAAVh2AAHAAAByFAAC 7934 MILLER CLERK 7782 23-JAN-82 1300 10
AAAVh2AAHAAAByGAAA 7566 JONES MANAGER 7839 02-APR-81 2975 20
AAAVh2AAHAAAByGAAB 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
AAAVh2AAHAAAByGAAC 7902 FORD ANALYST 7566 03-DEC-81 3000 20
AAAVh2AAHAAAByGAAD 7369 SMITH CLERK 7902 17-DEC-80 800 20
AAAVh2AAHAAAByGAAE 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
AAAVh2AAHAAAByHAAA 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
AAAVh2AAHAAAByHAAB 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
AAAVh2AAHAAAByHAAC 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
AAAVh2AAHAAAByHAAD 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
AAAVh2AAHAAAByHAAE 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
AAAVh2AAHAAAByHAAF 7900 JAMES CLERK 7698 03-DEC-81 950 30
14 rows selected.
/*
DATABASE OBJECT NUMBER ::: AAAVh2
RELATIVE FILE NUMBER ::: AAH
BLOCK NUMBER ::: AAAByF (Deptno = 10)
ROW NUMBER ::: AAA
BLOCK NUMBER ::: AAAByG (Deptno = 20)
ROW NUMBER ::: AAA
BLOCK NUMBER ::: AAAByH (Deptno = 30)
ROW NUMBER ::: AAA
BLOCK NUMBER ::: AAAByD (Deptno = 40)
*/
SQL> SELECT ROWID,d.* FROM dept d;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVh2AAHAAAByDAAA 40 OPERATIONS BOSTON
AAAVh2AAHAAAByFAAA 10 ACCOUNTING NEW YORK
AAAVh2AAHAAAByGAAA 20 RESEARCH DALLAS
AAAVh2AAHAAAByHAAA 30 SALES CHICAGO
--The BLOCK NUMBER is same for each departments in EMP & DEPT tables.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('COURSE','EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('COURSE','DEPT');
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno;
ENAME LOC
---------- -------------
KING NEW YORK
CLARK NEW YORK
MILLER NEW YORK
JONES DALLAS
SCOTT DALLAS
FORD DALLAS
SMITH DALLAS
ADAMS DALLAS
BLAKE CHICAGO
ALLEN CHICAGO
WARD CHICAGO
MARTIN CHICAGO
TURNER CHICAGO
JAMES CHICAGO
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1635080117
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 280 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS CLUSTER| EMP | 4 | 36 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMPDEPT_INDX | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- this is an adaptive plan
SQL> SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno AND e.deptno = 10;
ENAME LOC
---------- -------------
KING NEW YORK
CLARK NEW YORK
MILLER NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 1162885468
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 100 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS CLUSTER | EMP | 5 | 45 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10)
SQL> SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno AND d.deptno = 10;
ENAME LOC
---------- -------------
KING NEW YORK
CLARK NEW YORK
MILLER NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 1049931286
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 100 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | EMP | 5 | 45 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | EMPDEPT_INDX | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
5 - access("E"."DEPTNO"=10)
SQL> SELECT e.ename,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno AND d.deptno = 40;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1049931286
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 40 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | EMP | 2 | 18 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | EMPDEPT_INDX | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=40)
5 - access("E"."DEPTNO"=40)
SQL> DROP CLUSTER empdept INCLUDING TABLES CASCADE CONSTRAINTS;
Cluster dropped.
SQL> SELECT COUNT(*) FROM user_clusters WHERE cluster_name = 'EMPDEPT';
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM user_indexes WHERE index_name = 'EMPDEPT_INDX';
COUNT(*)
----------
0
SQL> SELECT * FROM emp;
SELECT * FROM emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM dept;
SELECT * FROM dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
