Was sind Cluster?
Diese Frage erwartet man vielleicht nicht in einen Blog von jemandem, der Kurse zum Real Application Cluster gibt. Aber der RAC ist mit dieser vielleicht etwas provozierenden Frage nicht gemeint. Gemeint ist die Möglichkeit, Daten von inhaltlich zusammenhängenden Tabellen gemeinsam – geclustert – abzulegen. Dieses Feature von Oracle gibt es schon seit vielen Versionen; wird aber selten genutzt und ist daher eher unbekannt. Das Feature, das natürlich auch einige Vorteile bietet, unterliegt aber auch einigen Einschränkungen. Und so kommt es immer wieder zu der Frage: “Was sind Cluster?” wenn es z.B. zum Befehl “ALTER TABLE .. SHRINK” heißt, dass diser Befehl für Tabellen im Cluster nicht erlaubt ist.
Zum Einstieg hilft ein Blick in den Oracle Concepts Guide: “A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables.”
Also: mehrere Tabellen mit gemeinsamen Spalten werden gemeinsam, d.h. in den gleichen Datenblöcken, gespeichert.
Bedingung dafür ist, dass es eine oder mehrere gemeinsame Spalten gibt, den sog. “Cluster Key”. Dieser “Cluster Key” ist erforderlich, wenn wir im ersten Schritt den Cluster anlegen:
SQL> create cluster cluster_emp_dept 2 ( 3 deptno number(2) 4 ) 5 tablespace users; Cluster created.
Zusätzlich muss es einen Index auf den Cluster-Key geben (daher auch die Bezeichnung “Index-Clustered-Tables”). Also:
SQL> create index index_emp_dept 2 on 3 cluster cluster_emp_dept 4 tablespace users; Index created.
Im nächsten Schritt können wir die Tabellen im Cluster anlegen:
SQL> create table dept_cluster
(deptno number(2) constraint pk_dept_cluster primary key,
dname varchar2(14) ,
loc varchar2(13) )
cluster cluster_emp_dept(deptno);
Table created.
SQL> create table emp_cluster
(empno number(4) constraint pk_emp_cluster primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno_cluster references dept_cluster)
cluster cluster_emp_dept(deptno);
Table created.
Jetzt die Demo-Daten einfügen. Damit man den Vorteil des Clusters – die gemeinsame Speicherung – am besten nutzen kann, werden die Daten abteilungsweise eingefügt. (Damit hätten wir auch direkt einen Nachteil dieser Cluster: für Tabellen mit häufigen Änderungen ist diese Speichervariante nicht geeignet).
SQL> insert into dept_cluster select * from dept where deptno=10; 1 row created. SQL> insert into emp_cluster select * from emp where deptno=10; 3 rows created. SQL> insert into dept_cluster select * from dept where deptno=20; 1 row created. SQL> insert into emp_cluster select * from emp where deptno=20; 5 rows created. SQL> insert into dept_cluster select * from dept where deptno=30; 1 row created. SQL> insert into emp_cluster select * from emp where deptno=30; 6 rows created. SQL> insert into dept_cluster select * from dept where deptno=40; 1 row created. SQL> insert into emp_cluster select * from emp where deptno=40; 0 rows created. SQL> commit; Commit complete.
Vorteil der “Index Clustered Tables” soll sein, dass sie bei entsprechenden SELECTs über beide Tabellen schneller sind als “normale” Tabellen. Also vergleichen wir mal:
Zuerst die klassische Variante:
SQL> set autotrace on
SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname
2 from emp e,dept d
3* where e.deptno = d.deptno;
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7369 SMITH 800 20 RESEARCH
7499 ALLEN 1600 30 SALES
7521 WARD 1250 30 SALES
7566 JONES 2975 20 RESEARCH
7654 MARTIN 1250 30 SALES
7698 BLAKE 2850 30 SALES
7782 CLARK 2964.5 10 ACCOUNTING
7788 SCOTT 3000 20 RESEARCH
7839 KING 6050 10 ACCOUNTING
7844 TURNER 1500 30 SALES
7876 ADAMS 1100 20 RESEARCH
7900 JAMES 950 30 SALES
7902 FORD 3000 20 RESEARCH
7934 MILLER 1573 10 ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 420 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
1054 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
Dann die Cluster-Variante:
SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp_cluster e,dept_cluster d
where e.deptno = d.deptno;
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7782 CLARK 2964.5 10 ACCOUNTING
7839 KING 6050 10 ACCOUNTING
7934 MILLER 1573 10 ACCOUNTING
7369 SMITH 800 20 RESEARCH
7566 JONES 2975 20 RESEARCH
7788 SCOTT 3000 20 RESEARCH
7876 ADAMS 1100 20 RESEARCH
7902 FORD 3000 20 RESEARCH
7499 ALLEN 1600 30 SALES
7521 WARD 1250 30 SALES
7654 MARTIN 1250 30 SALES
7698 BLAKE 2850 30 SALES
7844 TURNER 1500 30 SALES
7900 JAMES 950 30 SALES
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1698751118
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 420 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT_CLUSTER | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS CLUSTER| EMP_CLUSTER | 4 | 68 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | INDEX_EMP_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads 0 redo size 974 bytes sent via SQL*Net to client 384 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
Und wenn man die Statistiken beider Selects vergleicht, dann sind die Cluster-Tabellen I/O-mäßig wirklich effizienter: 14 “consistent gets” gegenüber 20 consistent gets bei den ungeclusterten Tabellen. Dem entgegen stehen höhere CPU-Kosten (6 vs. 3). Hier muss man also abwägen und – wenn man Clustering in Betracht zieht – mit größeren Datenmengen und applikationsnahen Abfragen testen. Und natürlich muss man auch die Einschränkungen von “Index-Clustered-Tables” bedenken:
- TRUNCATE TABLE ist nicht mehr möglich
- ALTER TABLE SHRINK SPACE ist nicht mehr möglich
- kein “FLASHBACK” möglich
- Full-Table-Scans dauern längern (da auch Informationen aus der jeweils anderen Tabelle gelesen werden müssen)
- für Tabellen die häufig aktualisiert werden sind Cluster auch nicht geeignet
Bleiben also als Vorteile:
- weniger Platzbedarf
- weniger I/O
- Vorteile wenn alle Tabellen überwiegend gemeinsam abgefragt werden
Weitere Pro- und Contra-Argumente sind im Performance Tuning Guide aufgeführt.
Die Praxis zeigt aber, dass die Einschränkungen meist stärker gewichtet werden als die Vorteile, außer im Oracle-Data Dictionary werden “Index-Clustered-Tables” in der Oracle-Welt praktisch garnicht eingesetzt.
Neben den Indexed-Clustern gibt es auch “Hash-Cluster”, die im Prinzip ähnlich funktionieren. Anstelle des Index-Keys tritt hier ein Hash-Wert über die Cluster-Spalte. Einzelheiten dazu gibt es im Concepts Guide.