Was sind Cluster?

21. Januar 2013 Aus Von Markus Flechtner

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.