Introduction

Une base de données contient principalement des tables mais également des objets connexes aux tables comme les index, les vues, les triggers, les séquences mais aussi des objets plus exotiques comme des procédures stockées, des directories ou des clusters de tables.
Nous allons les passer en revue.

On a ci-après un aperçu des différents types d’objets que l’on peut trouver dans une base gràce au navigateur d’objets (bien pratique) de SQLDeveloper:

Les Tables

Les tables sont les objets principaux contenant les données utilisateur de la base. Ce sont des segments physiques.
Elles sont composées de colonnes ayant un nom, un type et souvent une longueur maximale.

exemple de création de TABLE

SQL> CREATE TABLE EMP 
(EMPNO NUMBER(4) NOT NULL, 
ENAME VARCHAR2(10), 
JOB VARCHAR2(9), 
MGR NUMBER(4), 
HIREDATE DATE, 
SAL NUMBER(7, 2), 
COMM NUMBER(7, 2), 
DEPTNO NUMBER(2));

SQL> describe EMP
Name      Null?    Type
--------- -------- ------------
EMPNO     NOT NULL NUMBER(4)
ENAME              VARCHAR(10)
JOB                VARCHAR(9)
MGR                NUMBER(4)
HIREDATE           DATE
SAL                NUMBER(7,2)
COMM               NUMBER(7,2)
DEPTNO             NUMBER(2)

On peut vérifier les caractéristiques de la table dans le dictionnaire par une commande SQL (ici des détails techniques extraits de la VUE USER_TABLES:

SQL> SELECT table_name, tablespace_name,
     status, last_analyzed
     FROM user_tables
     WHERE table_name='EMP';
TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED
---------- --------------- ------ -------------
EMP        USERS           VALID  25/05/2014

ou avec des outils graphiques comme Enterprise Manager ou SQLDeveloper :

vue de l’écran TABLE dans la page SCHEMAS de Enterprise Manager

vue de la table EMP dans SQLDeveloper (description + contenu)

META DONNEES SUR LES TABLES

Les tables sont documentées dans le dictionnaire principalement dans les vues USER_TABLES, ALL_TABLES et DBA_TABLES.
Respectivement la vue de MES tables, des tables sur lesquelles j’ai des droits et de toutes les tables.
Pour information la ‘vraie’ table des tables s’appelle SYS.TAB$.

Quizz: Que fait ce SELECT?

SQL> SELECT table_name FROM all_tables
  MINUS
SELECT table_name FROM user_tables;
TABLE_NAME
------------------------------
AUC
AUDIT_ACTIONS
DEF$_TEMP$LOB
DR$NUMBER_SEQUENCE
DUAL
HELP

Contraintes d’intégrité

Il est plus que souhaitable (car c’est une caractéristique fondamentale du modèle relationnel mais surtout pour avoir dse données fiabilisées dans les tables) que les tables présentent des contraintes d’intégrité.
A minima on aura une clé primaire (PRIMARY KEY), souvent une clé étrangère (FOREIGN KEY), et parfois une contrainte  d’unicité (UNIQUE), ou personnalisée (CHECK).
exemple de création de table avec un certain nombre de contraintes:

SQL> CREATE TABLE mini_emp 
(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT upper_ename 
CHECK (ename = UPPER(ename)),
hiredate DATE DEFAULT SYSDATE,
deptno NUMBER(2) CONSTRAINT fk_deptno 
REFERENCES scott.dept(deptno) )

Stockage physique des tables

Les tables sont des segments physiques, c’est à dire qu’elles consomment  de la place disque dans les fichiers oracle. Concrètement les fichiers ne sont pas visibles par le développeur mais seulement l’espace de stockage logique correspondant appelé TABLESPACE (voir chapitre correspondant).
Une TABLE occupe donc de l’espace dans un TABLESPACE, c’est un ensemble plus ou moins contigus de blocs disques.
Les blocs contigus sont regroupés en EXTENTs et l’ensemble des EXTENTS contigus ou non constitue le SEGMENT PHYSIQUE de la TABLE.

remarque: Tous les objets dans la base ne sont pas des segments physiques, pensez aux synonymes ou aux vues par exemple…

L’emplacement et la façon dont est exactement stockée une TABLE peut se préciser lors de la création de la table par une clause SQL particulière, la clause STORAGE:

remarque : Il peut être judicieux de préciser la clause STORAGE au niveau de chaque table plutôt qu’au niveau du tablespace, les volumes pouvant être très différents d’une table à l’autre….

CREATE TABLE divisions_entreprise
(div_no NUMBER(2), 
div_name VARCHAR2(14), 
location VARCHAR2(13) ) 
STORAGE ( INITIAL 100K NEXT 50K 
MINEXTENTS 1 MAXEXTENTS 50 
PCTINCREASE 5);

remarque: la clause STORAGE peut aller assez loin, il ne faut pas en abuser. On se reportera au manuel SQL de référence pour plus d’informations. Voici un petit aperçu de son arbre syntaxique complet pour s’en persuader..:

exemple d’implantation d’un segment sur disque. On a ici un segment de 96K, composé de 2 EXTENTS non contigus de 24K et 72K, chacun composé d’un certain nombre de blocs de 2K.
remarque: 2K sur cet exemple, est la taille d’un ‘bloc oracle’. Cette taille est un paramètre d’initialisation de la base et peut différer de la taille réelle du bloc disque.

Les clusters

Généralités

Les clusters est une alternative au stockage standard des tables. Ce sont des segments spéciaux qui contiennent une ou plusieurs tables fusionnées, suivant en général la colonne de jointure.
On peut les voir comme des partitionnements (cas d’une seule table) ou comme des jointures physiques (cas de 2 tables ou plus).

Chaque partition est identifiée par une clé de cluster, et les blocs des différentes tables correspondant à cette clé sont regroupés dans cette même partition.
On peut voir cela sur le schéma suivant ou sont représentées les tables EMP et DEPT organisées ou non en cluster sur le no de département (commun aux 2 tables):

Cette structure est par définition efficace:

• en consultation plutot qu’en mise à jour,  lors de jointures (moins de blocs et moins d’éparpillement de blocs)
• en terme de stockage, en évitant la redondance du stockage de la clé de cluster.

Il est symétriquement pénalisant d’utiliser des clusters si les tables sont accédées plutôt séparément.

Clusters et metadonnées

Les metadonnées correspondant aux clusters sont principalement dans les tables DBA_TABLES et DBA_CLUSTERS.

Il est à noter que du fait de l’efficacité des clusters dans des contextes bien précis, un certain nombre de tables du dictionnaire sont elles même organisées en cluster.
On le voit ci-après:

SQL> SELECT count(*) FROM dba_clusters;
  COUNT(*)
----------
        10

Le cluster C_OBJ# qui décrit les objets les plus populaires d’Oracle, contient 16 tables !!

SQL> SELECT  table_name
     FROM dba_tables
     WHERE cluster_name ='C_OBJ#';
TABLE_NAME
------------------------------
TAB$
IND$
COL$
CLU$
LOB$
COLTYPE$
SUBCOLTYPE$
ATTRCOL$
VIEWTRCOL$
TYPE_MISC$
NTAB$
LIBRARY$
REFCON$
OPQTYPE$
ICOLDEP$
ICOL$

16 rows selected.

création et gestion d’un cluster

Concrètement il faut d’abord créer le CLUSTER puis ensuite la ou les tables qui partageront ce CLUSTER.
Chaque bloc du CLUSTER pourra contenir par défaut 1 clé de CLUSTER et les lignes associées. Si il y a  trop peu ou trop de lignes dans un bloc on peut faire varier la taille d’une ‘partition’ du CLUSTER frace au paramètre SIZE.

remarque: le paramètre SIZE n’est pas une limite physique à ne pas dépasser mais une estimation. Si toutes les lignes ne tiennent pas dans la partition, des blocs supplémentaires pourront être chaînés aux premiers.

L’adressage des blocs dans le CLUSTER peut se faire de 2 manières:

• par un INDEX créé explicitement (ou par défaut si on ne précise rien)
• par une clé de Hashage (HASH KEY)

INDEX CLUSTER

Un cluster doit être indexé sur sa clé avant d’y insérer des lignes.
Ceci ne préjuge pas des index qu’on pourrait créer sur les tables sous-jacentes…

exemple de création minimaliste d’un cluster.
On ne précisera pas ici de clause STORAGE ou SIZE pour simplifier.

SQL> create cluster c (i int);
Cluster created.

SQL> create table t1 (c char(1), i int)
   cluster c(i);
Table created.

SQL> create table t2 (c char(1), i int)
    cluster c(i);
Table created.

SQL> insert into t1 values ('A',1);
            *
ERROR at line 1:
ORA-02032: clustered tables cannot be used 
before the cluster index is built

-- une fois l'index créé on insère...
SQL> create index ic on CLUSTER c;
Index created
SQL> insert into t1 values ('A',1);
1 row created.
SQL> insert into t2  values ('B',2);
1 row created.

-- on peut aussi créer un index sur T1...
SQL> CREATE INDEX it1  ON t1(i);
Index created.

remarque: il n’est pas possible de déplacer directement des tables existantes dans un CLUSTER.
Néanmoins, On pourrait par exemple utiliser les étapes suivantes,

• créer le CLUSTER
• recuperer le DDL et les INSERT des tables d’origine (par un outil genre SQLdeveloper par exemple)
• récuperer le DDL des index éventuels
• renommer les tables origines
• créer les tables dans le CLUSTER
• créer l’index du cluster
• y insérer les lignes
• créer les index d’origine éventuels

HASH CLUSTER

Dans ce deuxième cas c’est en fait un calcul de HASH qui adressera les blocs et non pas une structure physique annexe comme dans le cas de l’index, ce qui ne consommera pas a priori d’entrée-sortie

Voici un exemple de création de HASH CLUSTER à une seule table:

SQL> CREATE CLUSTER trial_cluster (trialno NUMBER(5,0))
TABLESPACE users
STORAGE (INITIAL 250K     NEXT 50K
MINEXTENTS 1     MAXEXTENTS 3
PCTINCREASE 0)
HASH IS trialno HASHKEYS 150;

SQL> CREATE TABLE trial (
trialno NUMBER(5,0) PRIMARY KEY,
...)
CLUSTER trial_cluster (trialno);

La valeur de HASHKEYS (150) spécifie le nombre maximum de valeurs uniques que peut délivrer la fonction de hashage.
Il est possible de préciser sa propre fonction de hashage (personnalise).
En l’absence de clause  HASH IS Oracle utilise une fonction de hashage interne.

Les index

Ce sont des accélérateurs, liés aux tables mais externes aux tables.
On peut créer autant d’index qu’on veut sur une table (mais il ne faut pas en abuser…).
Si une table contient une clé primaire, un index est automatiquement créé par Oracle.
Voir le chapitre correspondant pour plus de détails.