Architecture Oracle : Les tables


précédentsommairesuivant

1. Les tables standard

1.1. Création d'une table

Pour pouvoir créer une table dans votre schéma vous devez avoir le privilège système CREATE TABLE. Pour pouvoir créer une table dans un autre schéma vous devez avoir le privilège système CREATE ANY TABLE. De plus vous devez avoir un quota défini dans le tablespace où vous voulez stocker la table ou bien le privilège système UNLIMITED TABLESPACE.

Voici un exemple d'instruction de création d'une table :

exemple de création de table
Sélectionnez

CREATE TABLE	hr.emp
(
	empno	NUMBER(5)		PRIMARY KEY,
	ename	VARCHAR2(15)	NOT NULL,
	job	VARCHAR2(10),
	mgr	NUMBER(5),
	hiredate	DATE		DEFAULT sysdate,
	sal	NUMBER(7,2),
	com	NUMBER(7,2),
	deptno	NUMBER(3)		NOT NULL
		CONSTRAINT dept_fk REFERENCES hr.departments
		(department_id)
)
TABLESPACE	tbs1
STORAGE
(
	INITIAL		50K
	NEXT		50K
	MAXEXTENTS	10
	PCTINCREASE	25
) ;

La table emp est créée dans le schéma hr.
La colonne empno est définie comme étant la clé primaire.
La colonne ename est définie comme obligatoire.
La colonne hiredate reçoit par défaut la date du jour.
La colonne deptno est obligatoire et le département doit exister dans la table departments.

La table est créée dans le tablespace tbs1 avec ses informations de stockage soit :
un extent initial de 50 Ko, les extents ultérieurs de 50 Ko également, un maximum de 10 extents possible et un taux d'accroissement de 25% pour chaque nouvel extent créé.

1.1.1. Création d'une table temporaire

Il est possible de créer une table temporaire. l'aspect temporaire ne réside pas dans la structure même de la table puisque celle-ci demeure tant qu'elle n'est pas détruite (DROP TABLE), mais dans le temps durant lequel les informations persistent dans la table.
Une table temporaire est visible par toutes les sessions mais les données insérées ne sont visibles que par la session qui génère les ordres d'insertion. La persistance des données est relative à la clause définie au moment de la création selon 2 possibilités :

  • les données sont supprimées après chaque ordre COMMIT (ON COMMIT DELETE ROWS)
  • les données sont supprimées à la fermeture de la session (ON COMMIT PRESERVE ROWS)

Voici un exemple de création d'une table temporaire :

création d'une table temporaire
Sélectionnez

CREATE GLOBAL TEMPORARY TABLE tempo
(
	msg	VARCHAR2(256),
	deb	DATE,
	fin	DATE
) ON COMMIT PRESERVE ROWS ;

Il est possible de créer des indexes sur une table temporaire dont la persistance de contenu est identique à celle des données de la table.

1.1.2. Options de traitement parallèle

Lors d'une création de table basée sur une sous-requête ( CREATE ... AS SELECT), les deux parties de l'ordre CREATE et SELECT peuvent être traités en parallèle.

La partie CREATE peut être parallélisée si l'une des conditions suivante est vérifiée :

  • la clause PARALLEL est incluse dans l'ordre CREATE TABLE
  • vous avez préalablement spécifié l'instruction ALTER SESSION FORCE PARALLEL DLL

La partie SELECT peut être parallélisée si toutes les conditions suivantes sont vérifiées :

  • l'ordre SQL contient un indicateur (hint) PARALLEL ou PARALLEL_INDEX ou la partie CREATE contient une clause PARALLEL ou la (les) table(s) de référence ont été déclarées avec la clause PARALLEL
  • au moins une table spécifiée dans la partie SELECT requiert soit un full table scan, soit un index range scan distribué sur plusieurs partitions

Si vous parallélisez la création d'une table, tous les ordres de manipulation suivants seront également parallélisés si cela est possible.

exemple de création parallélisée d'une table
Sélectionnez

CREATE TABLE hr.emp2
PARALLEL
AS SELECT * FROM hr.emp ;

1.1.3. Collecte automatique de statistiques sur les tables

Le package PL/SQL DBMS_STATS permet de collecter, modifier, consulter, exporter et supprimer les statistiques relatives à vos tables.
Il est possible d'activer ce package dès la création (CREATE) ou la modification (ALTER) d'une table avec la clause MONITORING.
Utilisez la clause NOMONITORING de l'ordre ALTER TABLE pour désactiver cette fonction.

1.2. Modification une table

Vous pouvez modifier la structure d'une table à l'aide de l'instruction ALTER TABLE.
Pour cela, la table doit être contenue dans votre schéma ou vous devez posséder le privilège objet ALTER TABLE sur cette table ou le privilège système ALTER ANY TABLE.


L'ordre ALTER TABLE permet les modifications suivantes :

  • modification des caractéristiques physiques (PCTFREE, PCTUSED, INITRANS, MAXTRANS ainsi que les paramètres de stockage)
  • déplacement de la table vers un autre extent ou un autre tablespace
  • allocation explicite d'un nouvel extent ou désallocation de l'espace inutilisé
  • ajouter, supprimer(9i), renommer(9i) une ou plusieurs colonnes
  • modifier certaines caractéristiques d'une colonne (type, longueur, valeur par défaut ou contrainte NOT NULL)
  • modifier les attributs de logging de la table
  • modifier les attributs CACHE/NOCACHE de la table
  • ajouter, modifier ou supprimer les contraintes d'intégrités définies sur la table
  • activer ou désactiver les contraintes d'intégrités
  • modifier le degré de parallélisme de la table
  • activer ou désactiver la collecte des statistiques
  • renommer la table
  • ajouter ou modifier des caractéristiques des tables organisées en index
  • modifier les caractéristiques d'une table externe
  • ajouter ou modifier des colonnes de type LOB
  • ajouter ou modifier des types objets, tables imbriquées ou varrays

Remarque : Si une vue, une vue matérialisée, un trigger, un index de domaine, un index de fonction, une contrainte de contrôle (CHECK) une fonction, procédure ou package est lié à la table que vous modifiez, ces objets risquent d'être invalidés par l'opération.

1.2.1. Modification des attributs physiques d'une table

Lorsque vous modifiez les attributs PCTFREE et PCTUSED, les nouveaux réglages s'appliquent à tous les blocs utilisés par la table, y compris ceux déjà alloués.

Lorsque vous modifiez les paramètres de transaction INITRANS et MAXTRANS, les nouveaux réglages d' INITRANS s'appliquent uniquement aux nouveaux blocs alloués alors que ceux relatifs à MAXTRANS s'appliquent à tous les blocs.

Les paramètres INITIAL et MINEXTENTS ne peuvent pas être modifiés.
Tous les autres paramètres de stockage tels que NEXT ou PCTINCREASE n'affectent que les nouveaux extents. La taille d'un nouvel extent alloué dépend des valeurs courantes de NEXT et PCTINCREASE.

Modification des attributs physiques
Sélectionnez

ALTER TABLE employees 
   PCTFREE 30
   PCTUSED 60;

1.2.2. Déplacement d'une table vers un nouveau segment ou un autre tablespace

L'instruction ALTER TABLE ... MOVE permet le déplacement des données d'une table non partitionnée vers un nouveau segment ou un autre tablespace sur lequel vous possédez les privilèges suffisants. Cette instruction permet également de modifier tous les paramètres de stockage, y compris ceux qui ne sont pas modifiable par l'instruction ALTER TABLE.

L'exemple suivant déplace la table emp2 vers un nouveau segment avec de nouvelles spécifications de stockage

exemple de déplacement d'une table
Sélectionnez

ALTER TABLE hr.emp2 MOVE
	STORAGE (	INITIAL		20K
		NEXT		40K
		MINEXTENTS	2
		MAXEXTENTS	20
		PCTINCREASE	0
		) ;

Si la table contient des colonnes LOB, cette instruction peut être utilisée pour déplacer la table ainsi que ses colonnes LOB et leurs segments d'indexes associés.
(sans indication explicite, les colonnes LOB ne sont pas déplacées).

1.2.3. Allocation manuelle de stockage pour une table

Oracle alloue dynamiquement de nouveaux extents pour le segment de données d'une table selon le besoin.
Il est tout de même possible d'allouer un nouvel extent avec l'instruction ALTER TABLE ... ALLOCATE EXTENT.

Il est également possible de désallouer l'espace inutilisé avec la clause DEALLOCATE UNUSED de l'instruction ALTER TABLE.

1.2.4. Modification de la définition d'une colonne

L'instruction ALTER TABLE ... MODIFY permet de modifier la définition d'une colonne pré-existante, son type, sa taille, sa valeur par défaut ou l'une de ses contraintes.

Il est possible d'augmenter la taille d'une colonne et même de la diminuer à la condition que la nouvelle taille reste suffisante pour conserver intactes les données pré-existantes. Il est également possible de modifier le type d'une colonne sous réserve de la même condition.
Dans le cas d'une augmentation de taille d'une colonne de type CHAR, il convient de rappeler que cette opération risque de consommer du temps et de l'espace non négligeable, notament sur les tables à forte volumétrie, par l'ajout de tous les espaces nécessaire en fin de chaîne.

1.2.5. Ajout de nouvelles colonnes

Pour ajouter une ou plusieurs colonnes à une table, utiliser l'instruction ALTER TABLE ... ADD :

exemples d'ajout de colonne
Sélectionnez

ALTER TABLE hr.emp2 ADD
(
	bonus		NUMBER(7,2)
) ;

ALTER TABLE hr.emp2 ADD
(
	bonus		NUMBER(7,2),
	cp		NUMBER(7,2)
) ;

Remarque : Le nom d'une colonne doit être unique au sein d'une même table.
Comme dit précédemment, la modification d'une colonne peut invalider les objets qui en dépendent.
Dans le cas d'un renommage de colonne, Oracle met à jour automatiquement le dictionnaire de données pour conserver la validité des indexes de fonction ainsi que les contraintes de contrôle.

1.2.6. Suppression de colonnes (à partir d'Oracle 9i)

Pour supprimer une colonne d'une table, utiliser l'instruction ALTER TABLE ... DROP COLUMN :

suppression d'une colonne
Sélectionnez

ALTER TABLE hr.emp2 DROP COLUMN bonus ;

Pour supprimer plusieurs colonnes d'une table, utiliser l'instruction ALTER TABLE ... DROP (...) :

suppression de plusieurs colonnes
Sélectionnez

ALTER TABLE hr.emp2 DROP (bonus, cp) ;

A l'issue de ces commandes, les descriptions de colonnes ainsi que les données contenues sont supprimées de la table.

Marquage des colonnes inutilisées

La suppression immédiate de colonne(s) avec la clause DROP peut, sur une table de forte volumétrie, engendrer un surcoût de temps à l'exécution.

Dans ce cas, la suppression peut être effectuée en plusieurs étapes.
Les colonnes à suprimer sont d'abord marquées avec l'instruction ALTER TABLE ... SET UNUSED.
Les colonnes ainsi marquées ne sont plus affichées, n'apparaissent plus dans les vues du dictionnaire et leur nom n'est plus consigné (il est donc possible d'ajouter à la table une nouvelle colonne portant ce nom). Toutefois, les données de ces colonnes sont toujours présentes dans les blocs et l'espace correspondant n'est pas encore libéré.

marquage de colonnes inutilisées
Sélectionnez

ALTER TABLE hr.emp2 SET UNUSED (bonus, cp) ;

Plus tard, lorsque l'activité de la base de données est réduite, la suppression physique des données est réalisée avec l'instruction ALTER TABLE ... DROP UNUSED COLUMNS.

Les vues du dictionnaire USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS et DBA_UNUSED_COL_TABS peuvent être consultées (dans un traitement batch par exemple) pour connaitre la liste des tables contenant des colonnes inutilisées.

 
Sélectionnez

SELECT * FROM DBA_UNUSED_COL_TABS ;

OWNER                          TABLE_NAME                             COUNT
------------------------------ -------------------------------------- -----
HR                             EMP2                                       2

Ces vues indiquent le nombre de colonnes marquées pour suppression, mais pas leur nom.

L'instruction ALTER TABLE ... DROP UNUSED COLUMNS supprime physiquement les données du block et libère l'espace occupé. cette opération peut être lourde pour les segments de undo, c'est pourquoi la clause CHECKPOINT peut être ajoutée pour forcer un checkpoint après un nombre de lignes précisé.

suppression physique des colonnes inutilisées
Sélectionnez

ALTER TABLE hr.emp2 DROP UNUSED COLUMNS CHECKPOINT 300 ;

1.2.7. Redéfinition d'une table en ligne

Dans un système de haute disponibilité, il peut être nécessaire de redéfinir une table sans la rendre indisponible (en tout cas le moins longtemps possible).

Lorsqu'une table est redéfinie en ligne, elle reste accessible aux instructions du DML pendant presque toute la durée du processus. Elle ne reste verrouillée qu'un court laps de temps (dépendant de la volumétrie de la table et de la complexité de la redéfinition).

La redéfinition d'une table en ligne offre les possibilités suivantes :

  • modifier les paramètres de stockage
  • déplacer la table vers un autre tablespace mais dans le même schéma
  • ajouter le support du mode parallèle
  • ajouter ou supprimer le partitionnement
  • re-créer la table pour réduire la fragmentation
  • changer l'organisation "normale" d'une table en organisation index
  • ajouter et/ou supprimer une ou plusieurs colonnes


Le package PL/SQL DBMS_REDEFINITION

Le privilège d'exécution sur ce package est attribué au rôle EXECUTE_CATALOG_ROLE. De plus les privilèges suivants sont nécessaires :

  • CREATE ANY TABLE
  • ALTER ANY TABLE
  • DROP ANY TABLE
  • LOCK ANY TABLE
  • SELECT ANY TABLE

Il est clair que ces types de privilèges sont habituellement accordés aux utilisateurs ayant un rôle DBA.


Etapes de redéfinition d'une table en ligne

1. Choisir d'abord l'une des deux méthodes disponibles :

  • basée sur la clé primaire de la table (méthode par défaut et conseillée). Avec cette méthode, les définitions source et cible de la table doivent conserver la même clé primaire
  • basée sur le ROWID. Avec cette méthode, la table ne doit pas être organisée en index. De plus, une colonne cachée (M_ROW$$) est automatiquement ajoutée à la table redéfinie et doit être marquée comme inutilisée (UNUSED) puis supprimée (DROP UNUSED COLUMNS) après la fin de la procédure de redéfinition

2. Vérifier que la table peut effectivement être redéfinie en ligne à l'aide de la procédure DBMS_REDEFINITION.CAN_REDEF_TABLE() en spécifiant la méthode de redéfinition.

3. Créer une table intérimaire vide (dans le même schéma que la table a redéfinir) contenant les nouveaux attributs ( permettant de ne pas inclure certaines colonnes de la table source et d'ajouter de nouvelles colonnes).

Il est possible de paralléliser la redéfinition de la table en spécifiant un degré de parallélisme sur les deux tables (source et interimaire) et en s'assurant que l'exécution parallèle est activé pour la session.

4. Démarrer le processus de redéfinition par l'appel de la procédure DBMS_REDEFINITION.START_REDEF_TABLE() avec les arguments suivants :

  • le nom de la table a redéfinir
  • le nom de la table d'interim
  • la correspondance des colonnes (entre les définitions source et cible)
  • la méthode de redéfinition

Si la liste des correspondances de colonnes n'est pas fournie, Le package inclut toutes les colonnes sources. Si la méthode de redéfinition n'est pas indiquée, alors la méthode basée sur la clé primaire est utilisée.

5. Créer tous les objets dépendants sur la table intérimaire (triggers, indexes, privilèges et contraintes). Toutes les contraintes d'intégrité référentielle doivent être créées avec le statut désactivé (DISABLED). Tant que le processus de redéfinition n'est pas achevé, les triggers sur la table intérimaire ne sont pas exécutés.

Lorsque la redéfinition est entièrement achevée, les triggers, les contraintes et les droits créés sur la table d'intérim sont transférés sur la table redéfinie. Les contraintes d'intégrité référentielle créées avec le statut DISABLED sur la table d'intérim sont également transférées et automatiquement basculées avec un statut ENABLED.

6. Exécuter la procédure DBMS_REDEFINITION.FINISH_REDEF_TABLE() pour achever le processus de redéfinition.

Durant l'exécution de cette procédure, la table d'origine est verrouillée en mode exclusif pendant un court instant.

7. Si la méthode par ROWID a été utilisée, pensez à rendre inutilisée la colonne M_ROW$$ :

 
Sélectionnez

ALTER TABLE nom_table SET UNUSED (M_ROW$$)


Synchronisation intermédiaire

Pendant le processus de redéfinition (après START_REDEF_TABLE() et avant FINISH_REDEF_TABLE()), il est possible qu'un grand nombre d'ordres DML aient été exécutés sur la table origine. Il est recommandé de lancer périodiquement la synchronisation entre la table source et la table intérimaire par appel de la procédure DBMS_REDEFINITION.SYNC_INTERIM_TABLE(), permettant ainsi de réduire le temps nécessaire à l'exécution de la procédure FINISH_REDEF_TABLE().

Arrêt volontaire ou involontaire du processus et procédure de nettoyage

Dans le cas d'arrêt demandé ou causé par une erreur, utiliser la procédure DBMS_REDEFINITION.ABORT_REDEF_TABLE() pour nettoyer l'environement mis en place par le processus de redéfinition, et vous pouvez alors supprimer la table intérimaire ainsi que ses objets associés.

Restrictions relatives à la redéfinition en ligne :

  • Si la méthode de redéfinition est basée sur la clé primaire, les tables source et cible doivent garder les mêmes colonnes composant la clé primaire. Si la méthode est basée sur le ROWID, la table ne peut pas être organisée en index
  • Les tables supportant des vues matérialisées ou des logs de vues matérialisées ne peuvent pas être redéfinies en ligne
  • Les vues matérialisées et les tables de type Advanced Queuing ne peuvent pas être redéfinies en ligne
  • La partie overflow d'une table organisée en index ne peut pas être redéfinie en ligne
  • Les tables supportant des types utilisateurs (objets, REF, collections et tables imbriquées) ne peuvent pas être redéfinies en ligne
  • Les tables contenant des colonnes BFILE ne peuvent pas être redéfinies en ligne
  • Les tables contenant des colonnes LONG ne peuvent pas être redéfinies en ligne
  • La table devant être redéfinie ne doit pas appartenir à un cluster
  • Les tables des schémas SYS et SYSTEM ne peuvent pas être redéfinies en ligne
  • Les tables temporaires ne peuvent pas être redéfinies en ligne
  • Seules, les expressions simples peuvent être utilisées pour déterminer la correspondance des colonnes (les sous-requêtes sont interdites)
  • Si de nouvelles colonnes sont ajoutées à la table cible, elles ne peuvent pas être déclarées NOT NULL avant la fin du processus de redéfinition
  • Il ne doit exister aucune contrainte d'intégrité référentielle entre la table source et la table intérimaire
  • La redéfinition en ligne ne peut être éffectuée en mode NOLOGGING

1.3. Suppression de table

Pour pouvoir supprimer une table, celle-ci doit se trouver dans votre schéma ou vous devez posséder le privilège système DROP ANY TABLE.

Utilisez l'instruction DROP TABLE.

Exemple de suppression de la table emp2 du schéma hr :

 
Sélectionnez

DROP TABLE hr.emp2 ;


Si vous voulez supprimer une table supportant des contraintes d'intégrité référentielle, ajoutez la clause CASCADE CONSTRAINTS à l'instruction DROP TABLE :

 
Sélectionnez

DROP TABLE hr.emp2 CASCADE CONSTRAINTS ;



Remarque : La supression d'une table retire sa définition du dictionnaire de données
Tous les indexes et triggers associés sont également supprimés
Toutes les vues et procédures PL/SQL dépendant de la table supprimée deviennent invalides
Les synonymes créés sur la table persistent mais leur utilisation retourne une erreur
Tous les extents alloués à la table supprimée sont ajoutés à la liste des espaces libres du tablespace

1.4. Gestion des tables organisées en index

Une table standard contient des données stockées sans ordre particulier.
Dans une table organisée en index, les données sont stockées dans une structure de type B-tree, triées sur la clé primaire.

Une table organisée en index procure un accès rapide aux données car toutes les informations sont contenues dans la structure de type index (en effet les données n'appartenant pas à l'index ne sont pas stockées dans un espace différent).
Pour la même raison, l'espace de stockage nécessaire est moindre puisque les données contenues dans les colonnes indexées ne sont pas dupliquées (table et index).
Il n'est donc plus nécessaire de stocker dans l'index le ROWID pointant sur le reste des données non indexées comme dans une table standard.

Ce type d'organisation convient parfaitement pour les requêtes dont les termes sont basés exclusivement sur les éléments de la clé primaire.

Note : dans la suite de cette section, le terme table organisée en index sera indiqué sous sa forme compressée anglaise : IOT (Index-Organised Table).

1.4.1. Création d'une table organisée en index

L'instruction de création d'une IOT est toujours CREATE TABLE. Toutefois il est nécessaire de renseigner certaines informations supplémentaires :

  • ajout de la clause ORGANIZATION INDEX
  • spécification obligatoire d'une contrainte de clé primaire
  • en option une indication de stockage du débordement (OVERFLOW) permettant d'indiquer les colonnes qui seront stockées en dehors de la structure B-tree (dans un autre segment de données) ainsi qu'une clause INCLUDING pour indiquer quelles colonnes n'appartenant pas à la clé primaire seront stockées dans la zone d'overflow
  • un argument PCTTHRESHOLD pour définir le pourcentage d'espace réservé dans le bloc d'index. Chaque partie de la ligne qui excèdera ce pourcentage sera stockée dans la zone de débordement. En d'autres termes, le contenu d'une ligne peut être divisé en deux partie. une partie "préfixe" stockée dans la feuille d'index et une partie "suffixe" stockée dans la zone de débordement. L'entrée de l'index (partie préfixe) contient donc les éléments de la clé primaire plus les autres données à concurrence du pourcentage indiqué dans le paramètre PCTTHRESHOLD ainsi qu'un pointeur vers le reste de la ligne
exemple de création d'une table organisée en index
Sélectionnez

CREATE TABLE perso
(
	nom		VARCHAR2(30),
	prenom		VARCHAR2(20),
	age		NUMBER(3),
	infos		VARCHAR2(512),
	CONSTRAINT	pk_perso PRIMARY KEY (nom, prenom)
)
	ORGANIZATION INDEX
	TABLESPACE	tbs1
	PCTTHRESHOLD	20
	OVERFLOW		TABLESPACE tbs2 ;

Une table est créée avec 4 colonnes. les deux premières sont les éléments de la clé primaire.
La table (partie préfixe) est stockée dans le tablespace tbs1
Le pourcentage d'espace maximum occupé par les colonnes n'appartenant pas à la clé primaire et devant donc être déplacées dans la zone de débordement est de 20% de la taille d'un bloc
La zone de débordement est stockée dans le tablespace tbs2

Une IOT peut stocker des types objet :

 
Sélectionnez

CREATE OR REPLACE TYPE typ_nom AS OBJECT
(
	nom	VARCHAR2(30),
	prenom	VARCHAR2(20)
) ;

CREATE TABLE perso
(
	num	NUMBER(7)	PRIMARY KEY,
	id	typ_nom
)
	ORGANIZATION INDEX ;

Elle peut être elle-même de type objet :

 
Sélectionnez

CREATE TABLE perso OF typ_nom
(
	nom	PRIMARY KEY
)
	ORGANIZATION INDEX ;

Il est possible d'utiliser une sous-requête pour créer une IOT :

 
Sélectionnez

CREATE TABLE perso
(
	empno	PRIMARY KEY,
	ename,
	job
)
	ORGANIZATION INDEX PARALLEL (DEGREE 2)
	AS SELECT empno, ename, job FROM hr.emp ;


Utilisation de la clause OVERFLOW

Cette clause indique dans quel tablespace les colonnes n'appartenant pas à la clé sont stockées (celle qui excèdent le pourcentage indiqué, 20% dans l'exemple précédent).

Si une mise à jour d'une de ces colonnes réduit ou augmente la taille de la ligne, Oracle identifie quelle partie de la ligne ( préfixe ou suffixe) est impactée, et éventuellement, reconstruit une partie suffixe afin de conserver la loi des 20%.

Choix de la valeur de PCTTHRESHOLD

La valeur de ce pourcentage doit être calculée de sorte que la partie préfixe contienne les colonnes n'appartenant pas à la clé primaire dont les valeurs sont fréquemment recherchées.

Vous pouvez utiliser l'instruction ANALYZE TABLE ... LIST CHAINED ROWS pour déterminer quelles colonnes de votre IOT sont déplacées dans la zone de débordement.

Utilisation de la clause INCLUDING

Après avoir spécifié l'argument PCTTHRESHOLD, vous pouvez utiliser la clause INCLUDING pour contrôler quelles colonnes n'appartenant pas à la clé primaire doivent rester dans la partie préfixe. Oracle conservera (dans la limite de PCTTHRESHOLD) ces colonnes dans la feuille d'index. Toutes les colonnes suivant celle indiquée dans la clause INCLUDING seront stockée dans la zone de débordement.

Remarque : Oracle déplace toutes les colonnes appartenant à la clé primaire en tête de la table.

L'exemple suivant montre comment forcer le placement dans la zone de débordement de toutes les colonnes n'appartenant pas à la clé primaire :

 
Sélectionnez

CREATE TABLE perso
(
	nom		VARCHAR2(30),
	prenom		VARCHAR2(20),
	age		NUMBER(3),
	infos		VARCHAR2(512),
	CONSTRAINT	pk_perso PRIMARY KEY (nom, prenom)
)
	ORGANIZATION INDEX
	TABLESPACE	tbs1
	PCTTHRESHOLD	20
	INCLUDING		age
	OVERFLOW		TABLESPACE tbs2 ;

Où seules les colonnes jusqu'à la colonne age sont conservées dans la partie préfixe.

1.4.2. Utilisation de la compression de clé

Cette fonctionnalité est intéressante pour éliminer la redondance d'informations stockées dans la clé primaire.
Elle consiste à diviser la clé en deux parties. la première stocke l'information redondante et la deuxième celle qui ne l'est pas.

Prenons un exemple concret.

Soit la table suivante :

 
Sélectionnez

CREATE TABLE test
(
	a	NUMBER	PRIMARY KEY,
	b	NUMBER,
	c	NUMBER,
)
	ORGANIZATION INDEX COMPRESS ;

Et les valeurs insérées :

(1,2,3), (1,2,4), (1,2,6), (2,3,4), (2,4,5), (3,5,6)

Toutes les valeurs 1 et 2 de la clé primaire seront compressées, permettant de stocker plus de lignes dans la même feuille d'index.

Il est possible de fixer une valeur à l'argument COMPRESS (défaut : 2) afin de déterminer combien de colonnes de la clé primaire participent à la compression.

L'activation de la fonctionnalité de compression peut être effectuée suite à l'une des actions suivantes :

  • création de la table organisée en index
  • déplacement de la table organisée en index

La désactivation de la fonctionnalité de compression s'effectue avec l'instruction :

 
Sélectionnez

ALTER TABLE ... MOVE NOCOMPRESS ;

1.4.3. Modification d'une IOT

Utilisez l'instruction ALTER TABLE pour modifier la structure et les attributs de stockage pour la partie préfixe et la partie suffixe d'une IOT.

Tous les attributs spécifiés avant la clause OVERFLOW s'appliquent à la partie préfixe et tous les attributs spécifiés après cette clause s'appliquent à la zone de débordement.

Par exemple vous pouvez fixer le paramètre INITRANS de la partie préfixe à 4 et le même paramètre INITRANS de la partie suffixe à 6 :

 
Sélectionnez

ALTER TABLE perso INITRANS 4 OVERFLOW INITRANS 6 ;

Vous pouvez modifier la valeur des paramètres PCTTHRESHOLD et INCLUDING :

 
Sélectionnez

ALTER TABLE perso PCTTHRESHOLD 10 INCLUDING prenom ;

Pour les IOT créées sans zone de débordement, il est possible d'ajouter explicitement cette zone avec l'instruction :

 
Sélectionnez

ALTER TABLE perso ADD OVERFLOW TABLESPACE tbs3 ;

1.4.4. Recréation d'une IOT

Pour reconstruire une IOT, utilisez l'instruction :

ALTER TABLE ... MOVE ;

Pour reconstruire une IOT en ligne, ajouter la clause ONLINE :

 
Sélectionnez

ALTER TABLE perso MOVE ONLINE ;

Seule la partie préfixe est reconstruite. Si vous voulez reconstruire également la zone de débordement, il faut l'indiquer explicitement dans l'instruction :

 
Sélectionnez

ALTER TABLE perso MOVE ONLINE TABLESPACE tbs1
OVERFLOW TABLESPACE tbs2 ;

Reconstruction d'une table avec déplacement du segment LOB :

 
Sélectionnez

CREATE TABLE test_lob
(
	num		NUMBER(7)	PRIMARY KEY,
	col_lob	CLOB
)
	ORGANIZATON INDEX
	LOB (col_lob) STORE AS (TABLESPACE tbs3) ;

ALTER TABLE test_lob MOVE LOB (col_lob) STORE AS (TABLESPACE tbs4) ;

1.4.5. Analyse d'une IOT

Rappel : Oracle recommande d'utiliser les fonctions du package DBMS_STATS pour la collecte de statistiques à destination de l'optimiseur, réservant l'utilisation de l'instruction ANALYZE à la collecte de statistiques non destinées à l'optimiseur (validation de structure, liste des lignes chaînées).

 
Sélectionnez

ANALYZE TABLE perso COMPUTE STATISTICS ;

Cette instruction analyse à la fois la partie préfixe et la zone de débordement.

Les statistiques logiques sont consultables à travers les vues USER_TABLES, ALL_TABLES et DBA_TABLES.

Les statistiques physiques de la partie préfixe sont consultables à travers les vues USER_INDEXES, ALL_INDEXES et DBA_INDEXES (colonnes LAST_ANALYZED, BLEVEL, LEAF_BLOCKS, DISTINCTS_KEYS)

Les statistiques physiques de la zone de débordement sont consultables à travers les vues USER_TABLES, ALL_TABLES et DBA_TABLES (LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS) en précisant la recherche sur la partie overflow (WHERE IOT_TYPE='IOT_OVERFLOW').

1.4.6. Conversion d'une IOT en table standard (régulière)

Deux méthodes possibles :

  • utiliser l'outil d'exportation (EXPORT), créer une table standard de même structure et réaliser l'importation (IMPORT)
  • créer la nouvelle table avec une syntaxe CREATE TABLE ... AS SELECT

1.5. Gestion des tables externes

Depuis la version 9i, il est possible de gérer des tables externes.
Ces tables consistent en fichiers plats stockés hors de la base et sont interrogeables de la même manière que les tables stockées en base.

Il est possible d'interroger, joindre, trier les données d'une table externe, de créer des vues et des synonymes.

Il n'est pas possible d'utiliser les instruction du DML (INSERT, UPDATE et DELETE) ni de créer un index sur une table externe.

Le mécanisme des tables externes est à rapprocher de l'utilisation de l'outil Sql*Loader, avec l'avantage que les fichiers plats ne sont pas chargés dans une table standard mais gérés tel quels depuis le système de fichiers (avec toute la simplification de la gestion des interfaces que cela procure).

La définition de toutes les informations nécessaires à la gestion d'une table externe et réalisée dans l'instruction CREATE TABLE ... ORGANIZATION EXTERNAL.

Les privilèges système et objet nécessaire à la manipulation d'une table externe sont les suivants :

Privilèges système :

  • CREATE ANY TABLE
  • ALTER ANY TABLE
  • DROP ANY TABLE
  • SELECT ANY TABLE

Privilèges objet :

  • ALTER
  • SELECT

1.5.1. Création d'une table externe

Le ou les fichiers plats entrant dans la définition d'une table externe doivent résider dans un répertoire Oracle (DIRECTORY) préalablement défini et pour lequel vous avez le droit de lecture (READ).

Le mécanisme étant proche de celui utilisé par l'utilitaire Sql*Loader, l'écriture éventuelle d'un fichier log et d'un fichier de rejet requiert également le droit d'écriture (WRITE) sur un répertoire Oracle.

Exemple de création d'une table externe.

Soit le fichier plat test_table_externe.txt suivant :

10,Emile,DUBOUDIN, Charcutier
20,Jocelyne,POTAUFEU,Epicière
30,Harry,COVER,Maraîcher
40,Hans,BURGER,Restaurateur

 
Sélectionnez

CREATE TABLE employes_ext
(
	numero	NUMBER(7),
	nom	VARCHAR2(30),
	prenom	VARCHAR2(20),
	job	VARCHAR2(20)
)
	ORGANIZATION EXTERNAL
	(
	  TYPE ORACLE_LOADER
	  DEFAULT DIRECTORY	nom_directory_read
	  ACCESS PARAMETERS
	  (
		records delimited by newline
		badfile	 nom_directory_write:'test_table_externe.bad'
		logfile nom_directory_write:'test_table_externe.log'
		fields terminated by ','
		missing field values are null
		( numero, nom, prenom, job )
	  ) 
	  LOCATION ('test_table_externe.txt')
	)
	PARALLEL
	REJECT LIMIT UNLIMITED ;

SELECT * FROM employes_ext ;

1.5.2. Modification d'une table externe

L'instruction ALTER TABLE est utilisée avec les seules clauses possibles suivantes :

  • REJECT LIMIT
  • DEFAULT DIRECTORY
  • ACCESS PARAMETERS
  • LOCATION
  • PARALLEL
  • ADD COLUMN
  • MODIFY COLUMN
  • DROP COLUMN
  • RENAME TO

1.5.3. Suppression d'une table externe

Utilisez l'instruction DROP TABLE.

1.6. Consultation des informations relatives aux tables

Vue Description
DBA_TABLES
ALL_TABLES
USER_TABLES
Liste des tables relationnelles de la base
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
Liste des colonnes des tables, vues et clusters de la base
DBA_ALL_TABLES
ALL_ALL_TABLES
USER_ALL_TABLES
Liste des tables relationnelles et objets de la base
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
Liste des commentaires attachés aux tables
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS
Liste des commentaires attachés aux colonnes des tables
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
Liste des attributs attachés aux tables externes
DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
Liste des fichiers sources des tables externes
DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
Liste des histogrammes disponibles sur les tables et les vues
DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
Liste des statistiques et histogrammes collectés
DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
Liste des tables modifiées depuis la dernière collecte de statistiques et sur lesquelles le monitoring est positionné
DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
Liste des tables contenant des colonnes inutilisées suite à l'instruction ALTER TABLE ... SET UNUSED
DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS
Liste des tables contenant des colonnes partiellement supprimées (suite à une erreur ou une interruption utilisateur)

précédentsommairesuivant

Le guide Oracle par Orafrance, Helyos et SheikYerbouti