Uit de oude doos: clusters

De Oracle database gaat al een paar jaar mee en steeds komen er nieuwe technieken en features naar voren. De oude zijn nog niet afgeschreven, maar worden vaak vergeten.  Zo kwam recent bij een klant een cluster ter sprake. Nee niet een cluster in de zin van RAC of van een VMWare cluster. Maar in in de zin van “Tables en Clusters”. Hier bleek maar weer eens hoe in een beperkte omgeving (bijvoorbeeld Oracle Standard Edition), waar veel features en opties niet beschikbaar zijn, zo een oude structuur zijn voordeel kan hebben.

Laten we dit aan de hand van een eenvoudig voorbeeld bekijken.
We nemen een simpele tabel met 3 kolommen, een primary key, data kolom en tekst veld:

create table t1
as
with generator as (
        select rownum id from dual 
        connect by level <= 1e4  
)
select
        rownum                          id,
        round(dbms_random.value(0,50)) v1,
        lpad('x',100,'x')               padding
from generator v1 where rownum <= 1e3;

alter table t1 add  constraint t1pk primary key (id);

create index v1_idx on t1(v1);

exec dbms_stats.gather_table_stats('SYS','T1'); 

Hiermee hebben we een table met 1000  rijen en het veld v1 heeft random een waarde tussen de 1 en 50.

Als we nu kijken naar het (edited) executieplan van de query

 
select * from t1 where v1=42

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    20 |  2160 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

We  zien hier dat er (ondanks een primary key index en een index op v1) een full table scan nodig is.  Als we echter zeker zouden weten dat alle rijen van een zekere ‘v1’ waarde bij elkaar zouden staan is er minder werk nodig.  Dit is exact wat een cluster kan doen.

Een tabel in een cluster krijgen vereist echter wel wat voorwerk. Eerst moet je  het cluster en de bijbehorende cluster index maken.  Pas daarmee kan je de table aanmaken.  Dat ziet er dan als volgt uit:

create cluster t2cluster (id number);

create index clu_t2_idx  on cluster t2cluster;
 
create table t2  cluster t2cluster(v1)
as
with generator as (
        select 
                rownum id
        from dual 
        connect by level <= 1e4  
)
select
        rownum                          id,
        round(dbms_random.value(0,50)) v1,
        lpad('x',100,'x')               padding
from
        generator       v1
where
        rownum <= 1e3;
        
alter table t2 add  constraint t2pk primary key (id);

create index t2_v1_idx on t2(v1);

exec dbms_stats.gather_table_stats('SYS','T2');        

We zien hier dat we achtereenvolgens de cluster en cluster index creëren en bij de create table door middel van een cluster clause expliciet aangeven dat de tabel binnen het cluster aangemaakt moet worden. Als we dan nu naar het bijbehorende executieplan (edited) kijken zien we:

select * from t2 where v1=42
 
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS CLUSTER| T2         |    16 |  1728 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN  | CLU_T2_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

De kosten zijn slechts 33%  van de niet cluster aanpak. Sterker nog in een volgend experiment waar dezelfde tabel een 1 miljoen rijen heeft zijn de kosten 44 in het eerste scenario tegen een ongewijzigde 2 in scenario 2.
Een cluster plaatst rijen met dezelfde waarde voor de ‘cluster sleutel’ , in ons voorbeeld de kolom v1, fysiek bij elkaar. Deze rijen worden dus geclusterd.  Hier zit wat administratie omheen, zoals de index op het cluster.  Voordeel is echter wel dat de optimizer weet dat bijbehorende rijen bij elkaar staan. Dit is in essentie een eigenschap die clusters gemeen hebben met partitioned tables. 
Het voordeel van clusters is dat deze ook in Oracle Standard Edition licentie vrij te gebruiken zijn.  Echter er zijn ook een aantal functionele beperkingen en randvoorwaarden. Zo worden clusters minder efficiënt als het aantal rijen per cluster waarde heel erg verschilt. Idealiter is het aantal rijen per waarde niet te hoog en redelijk stabiel. Hiermee blijkt in ieder geval dat oude structuren nog steeds kunnen bijdragen aan een top performance.

We hopen dat we je met dit artikel, ” Uit de oude doos: clusters”, op weg bent geholpen. Mocht je hulp nodig hebben of je hebt vragen n.a.v. dit artikel, laat het ons dan weten.

Gerelateerd aan dit artikel: lees ook Oracle 19 New Features