Oracle 9I : Évolutions et Optimisations

II-A-1. Nouveaux paramètres dynamiques▲

II-A-2. Statistiques supplémentaires dans :▲

• Sa taille peut changer dynamiquement pendant le fonctionnement d'une instance (par l'intermédiaire du Buffer Cache et de la Shared Pool Size).
• Nouvelle unité d'allocation : le Granule.
  C'est une unité d'allocation mémoire contiguë et qui dépend de la taille de la SGA :
  => Si SGA_MAX_SIZE < 128 Mo, taille granule = 4Mo ;
  => Si SGA_MAX_SIZE >= 128 Mo, taille granule = 16Mo ;
  => taille de la SGA exprimée en granules et en contient au moins trois (Fixed SGA (dont Redo Buffer), buffer Cache, Share Pool).
• L'allocation des granules est montrée par la vue V$BUFFER_POOL.
• La Shared Pool peut être retaillée dynamiquement avec la commande :

ALTER SYSTEM
SET SHARED_POOL_SIZE = 128 M;

Cette taille doit être un multiple du granule et doit être inférieure à SGA_MAX_SIZE.

Le Buffer Cache peut être retaillé dynamiquement avec la commande :

ALTER SYSTEM
SET DB_CACHE_SIZE= 64 M;

Cette taille doit être un multiple du granule et doit être inférieure à
SGA_MAX_SIZE. Elle doit être différente de 0.

Nouveaux paramètres : DB_CACHE_SIZE, DB_KEEP_CACH_SIZE, et DB_RECYCLE_CACHE_SIZE.
Paramètres obsolètes et bientôt supprimés : DB_BLOCK_BUFFER, BUFFER_POOL_KEEP et BUFFER_POOL_RECYCLE.

Statistiques qui prédisent le comportement de la base lorsque l'on modifie la taille du cache. Est activé en utilisant la commande ALTER SYSTEM sur le paramètre DB_CACHE_ADVICE (ON, OFF ou READY). Les résultats sont consultables à l'aide de la vue V$DB_CACHE_ADVICE.

N. B. Les paramètres LOG_BUFFER, LARGE_POOL_SIZE et JAVA_POOL_SIZE restent statiques en 9i.

Fichier géré par le SGBD, sert pour les control files, les datafiles et les redo logs :

• sert principalement pour les bases de test ;
• crée un nom unique pour chaque fichier et se charge de les supprimer automatiquement en cas de suppression logique ;

• les répertoires physiques sont gérés par deux paramètres dynamiques :

  • DB_CREATE_FILE_DEST : utilisé pour les Data Files(Fichiers de données),
  • DB_CREATE_ONLINE_LOG_DEST_n (1 <= n <= 5) : utilisé pour les controls files et les redo logs.

Ex. : la commande suivante peut être lancée pour définir le premier paramètre :

ALTER SYSTEM SET db_create_file_dest = ‘/oracle/dbs/files'

Si seul le premier paramètre est positionné, alors tous les fichiers (control, données et redo) seront localisés dans le même répertoire.

Nommage des fichiers gérés par OMF (voir note Métalink n° 159888.1) :
Oracle Version 9.0.1.1 : ora_<nomdutablespace>_8caracteresaleatoires.dbf (même chose pour .log) ;
Oracle Version 9.0.1.2 : o1_mf_<nomdutablespace>_8caracteresaleatoires_.dbf (même chose pour .log) ;

OMF reconnaît ses fichiers par le préfixe et l'extension, la syntaxe a changé à partir de la 9.0.1.2, car « ora_ » était beaucoup utilisé avant OMF.

Pour créer des control files OMF :

• il faut définir un ou les deux paramètres dynamiques ;

• ne pas spécifier le paramètre CONTROL_FILES lors de la création de la base :

  • un control file est créé dans chacun des DB_CREATE_ONLINE_LOG_DEST_n,
  • ou dans DB_CREATE_FILE_DEST sinon,
  • les fichiers sont affichés dans le fichier d'alerte ;
• si SPFILE est défini, les control files générés sont automatiquement inclus dans ce fichier binaire.

Pour créer des redo logs OMF :

1. Définir les paramètres DB_CREATE_ONLINE_LOG_DEST, les groupes seront créés dans chacun des DB_CREATE_ONLINE_LOG_DEST_1 et DB_CREATE_ONLINE_LOG_DEST_2 ;
2. Chaque groupe aura deux membres ;
3. Chaque fichier a comme taille par défaut : 100 Mo.

Pour ajouter un groupe :

ALTER DATABASE ADD LOGFILE ;

Pour supprimer un groupe :

LTER DATABASE DROP LOGFILE GROUP 3 ;

Les fichiers journaux archivés ne peuvent être de type OMF.
Pour créer des tablespaces de type OMF :

• DB_CREATE_FILE_DEST doit être défini ;
• utiliser la syntaxe :

CREATE TABLESPACE omf_2 ;

• le fichier de données associé est créé dans DB_CREATE_FILE_DEST ;
• la clause DATAFILE n'est pas obligatoire ;
• la taille par défaut est de 100 Mo ;
• le tablespace est créé en AUTOEXTENT UNLIMITED ;
• à la suppression du tablespace, le fichier associé est supprimé automatiquement.

On peut lancer aussi la commande :

CREATE TABLESPACE omf_2 
    DATAFILE SIZE 500M ;

N. B. Les tablespaces sont créés automatiquement en EXTENT MANAGEMENT LOCAL en 9i. Pour utiliser le dictionnaire de données, il faut spécifier EXTENT MANAGEMENT DICTIONARY.

Pour ajouter un fichier de données à un tablespace OMF :

ALTER TABLESPACE TBS1 ADD DATAFILE;

Pour supprimer un tablespace et ses fichiers de données non OMF :

DROP TABLESPACE TBS1
    INCLUDING CONTENTS AND DATAFILES;

En 9i, possibilité de définir un tablespace temporaire par défaut autre que SYSTEM.

1. À la création de la base avec la clause :

    

   Sélectionnez

   DEFAULT TEMPORARY TABLESPACE tmp1
       TEMPFILE '/oracle/dbs/temp01.dbf' SIZE 100M

2. Avec la commande :

ALTER DATABASE db1
    DEFAULT TEMPORARY TABLESPACE tmp1.

Nouveau paramètre d'initialisation : UNDO_MANAGEMENT
Deux valeurs :

• AUTO : géré par l'instance (sur un seul tablespace de type UNDO_TABLESPACE).
  nécessite d'avoir les paramètres suivants :

  • UNDO_MANAGEMENT = AUTO,
  • UNDO_TABLESPACE = UNDO1.

(Le fonctionnement des rollbacks segments de SYSTEM est identique aux versions antérieures et un rollback segment est créé automatiquement dans le tablespace SYSTEM.) ;

• MANUAL : géré par le DBA. (Identique à la gestion des rollbacks segments avant la 9i.)
  Nécessite d'avoir les paramètres suivants :

  • UNDO_MANAGEMENT=MANUAL ;
  • CREATE TABLESPACE RBS ;
  • CREATE ROLLBACK SEGMENT rbs1 STORAGE (…) ;
  • ROLLBACK_SEGMENTS= (rbs1,rbs2).

Création d'un undo tablespace :

• à la création de la base, avec la clause supplémentaire :

   

  Sélectionnez

  UNDO TABLESPACE undotbs1
      DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

  (si au démarrage d'une instance, UNDO_MANAGEMENT=AUTO et qu'il n'y a pas de clause UNDO_TABLESPACE, il en crée un par défaut, de nom SYS_UNDOTBS, en AUTOEXTEND ON, et dans un fichier de 10 Mo).

• après la création de la base avec :

CREATE UNDO TABLESPACE
    DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

(Ce tablespace ne peut servir que pour stocker les données d'annulation).

Modification d'un undo tablespace :

ALTER TABLESPACE undotbs1
    ADD DATAFILE …
    RENAME …
    DATAFILE [ONLINE, OFFLINE]
    BEGIN BACKUP
    END BACKUP

Suppression d'un undo tablespace :

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

le undo tablespace utilisé (un à la fois) peut être modifié à l'aide de la commande :

ALTER SYSTEM
    SET UNDO_TABLESPACE=tbsundo2;

En résumé, la gestion des segments d'annulation peut se faire avec les nouveaux paramètres :

• UNDO_MANAGEMENT : MANUAL ou AUTO ;
• UNDO_TABLESPACE : Nom du tablespace d'annulation ;
• UNDO_SUPPRESS_ERRORS : TRUE OU FALSE (par défaut).
  => si positionné à TRUE, supprime les messages d'erreurs affichés, quand on est en mode AUTO et qu'une opération manuelle est exécutée (telle que ALTER ROLLBACK SEGMENT ou ALTER SESSION SET TRANSACTION USE ROLLBACK SEGMENT) ;
• UNDO_RETENTION: Durée (par défaut 30 s) pendant laquelle la donnée modifiée est gardée= Flashback.

Les informations d'utilisation des segments UNDO (auto ou manuel) sont stockées dans la vue V$UNDOSTAT.

Le DB_BLOCK_SIZE utilisé à la création de la base (et servant pour la création du tablespace SYSTEM), ne peut être changé sans recréer la base et sert comme taille par défaut lors de la création d'un tablespace.
Néanmoins, pour chaque tablespace créé manuellement, la taille du bloc est maintenant paramétrable :

CREATE TABLESPACE TBS1
    DATAFILE '/oracle/dbs/tbs01.dbf' SIZE 10 M
    BLOCKSIZE 4096;

(2 Ko <= BLOCKSIZE <= 32Ko).

N. B.

• Toutes les partitions d'un objet partitionné doivent être réparties dans des tablespaces de tailles de bloc identiques.
• Tous les tablespaces temporaires doivent utiliser la taille de bloc standard.

Pour gérer ces tailles différentes de bloc, des paramètres (dynamiques) de taille de buffer doivent être configurés :

• DB_CACHE_SIZE : taille réservée aux blocs standards de la base ;
• DB_nK_CACHE_SIZE : Taille allouée aux différents blocs non standards, par défaut à 0 (n=2, 4, 8,16 et 32), excluant celle des blocs standard.

Uniquement pour les blocs standard, deux paramètres peuvent être utilisés (ils ne sont pas inclus dans DB_CACHE_SIZE) :

• DB_KEEP_CACHE_SIZE (au lieu de BUFFER_POOL_KEEP auparavant) : garde les tables en mémoire plus longtemps ;
• DB_RECYCLE_CACHE_SIZE (au lieu de BUFFER_POOL_RECYCLE auparavant) : inverse du précédent.

Espaces libres et occupés sont décrits dans des bitmaps, et permet de savoir combien de blocs sont libres dans chaque segment.
Stockés dans des bitmapped blocs (BMB).
Permet d'améliorer le processus d'insertion.

Un tablespace géré avec des bitmaps doit être de la forme :

CREATE TABLESPACE tbs1
    DATAFILE '/oracle/dbs/tbs1.dbf'
    SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT (tablespace géré localement).
    SPACE MANAGEMENT AUTO. (Gestion automatique).

Les vues DBA_TABLESPACES, DBA_TABLES et DBA_SEGMENTS ont été modifiées pour stocker ces nouvelles informations.

Définit le nombre de sessions simultanées actives par groupe de consommateurs (de ressources).
=> géré par plusieurs paramètres d'initialisation (positionnés par défaut à UNLIMITED) :

• ACTIVE_SESSION_POOL_P1 : nombre de sessions actives qui établissent le seuil d'un groupe de consommateurs ;
• QUEUEING_P1 : temps d'attente maximum (en s) pour une session en file d'attente, après cette durée, la session est annulée avec une erreur.

Temps maximum d'exécution estimé (par défaut à UNLIMITED) :

• MAX_ESTIMATED_EXEC_TIME : temps maximal estimé autorisé pour une transaction avant son exécution. Si la transaction dépasse ce seuil, elle n'est pas exécutée.

Changement automatique de groupe de consommateurs :
Trois directives sont possibles (par défaut, les deux premières sont à UNLIMITED, le 3^e à FALSE) :

• SWITCH_GROUP : groupe sur lequel on a commuté ;
• SWITCH_TIME : temps (en s) durant laquelle une session doit être exécutée avant de changer de groupe ;
• SWITCH_ESTIMATE : Si positionné à TRUE, le temps d'exécution est utilisé pour décider quand une opération va être switchée, et ce, peut-être avant qu'elle commence.

Si les deux derniers paramètres sont positionnés, la session est commutée automatiquement.

Nouveau paramètre de directive de plan de ressource :

• UNDO_POOL (Kb), par défaut à UNLIMITED, restreint les opérations de type DML, et correspond au quota d'annulation par groupe de consommateurs (après ce quota, aucun ordre DML n'est autorisé).

Est créé ou modifié par les procédures suivantes :

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan', 
    GROUP_OR_SUBPLAN => 'support',
    COMMENT =>  'OLTP sessions',
    UNDO_POOL => 10000);

et

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan', 
    GROUP_OR_SUBPLAN => 'support',
    UNDO_POOL => 25000);

Les vues suivantes ont été adaptées : V$SESSION, V$MYSESSION, V$RSRC_CONSUMER_GROUP et DBA_RSRC_PLANS.

• Ajout de trois colonnes dans la table PLAN_TABLE :

  • cpu_cost : cout CPU estimé de l'opération ;
  • io_cost : cout accès disque estimé de l'opération ;
  • temp_space : espace temporaire estimé par l'opération.
• Estimation tient compte de l'utilisation réseau et CPU, des effets du cache (sur les jointures) et de la préextraction (prefetching) (rapatrie plusieurs blocs feuilles d'un index en un seul accès disque).

• Nouvelle valeur pour le paramètre estimate_percent : AUTO_SAMPLE_SIZE
  => estime la taille de l'échantillon nécessaire pour obtenir les meilleurs gains de performances.

• Nouvelles options pour le paramètre size :

  • REPEAT : répète la forme de l'histogramme déjà existant ;
  • AUTO : histogramme basé sur la distribution des données et l'utilisation de la colonne par l'application ;
  • SKEWONLY : histogramme basé uniquement sur la distribution des données => intéressant pour la première « collecte » de statistiques (sinon AUTO).

Ex :

execute DBMS_STATS.GATHER_SCHEMA_STATS(…..
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt   => 'for all columns size AUTO')

• Nouvelles procédures :

  • DBMS_STATS.GATHER_SYSTEM_STATS : récupère des statistiques système durant un intervalle de temps défini ;
  • DBMS_STATS.SET_SYSTEM_STATS: Spécifie certains paramètres ;
  • DBMS_STATS.GET_SYSTEM_STATS: Récupère certains paramètres.

Ex :

execute DBMS_STATS.GATHER_SYSTEM_STATS(interval => 120, stattab=>'messtats', statid=>'app')

Avec :

• intervalle en minutes ;
• stattab : nom de la table où seront stockées les stats ;
• statid : identifiant des stats générées.

(Avant de lancer cette commande, il faut activer une tâche (job) avec : ALTER SYSTEM SET job_queue_process=1, si la commande est lancée à l'aide d'un job.)

OPTIMIZER_MODE (paramètre d'initialisation) ou OPTIMIZER_GOAL (à l'intérieur d'une session) peuvent prendre ces nouvelles valeurs :

• FIRST_ROWS_1 ;
• FIRST_ROWS_10 ;
• FIRST_ROWS_100 ;
• FIRST_ROWS_1000.

Ex :

ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_100;

L'indicateur FIRST_ROWS (à l'intérieur d'une commande SQL) peut prendre n'importe quelle valeur.

Ex. :

Select /*+  FIRST_ROWS (25)  */

Les plans d'exécution stockés dans les tables réservées du schéma « outln », peuvent être édités puis publiés vers les autres utilisateurs après optimisation du (des) plan(s) en question.

Les différentes parties modifiables sont :

• l'ordre des jointures ;
• méthodes des jointures ;
• méthodes d'accès (full scan ou par index) ;
• méthodes de distribution (parallel query) ;
• réécriture des sous-requêtes et vues.

En plus des plans publics qui sont stockés dans le schéma outln et ne sont pas modifiables, 9i ajoute les plans privés propres à une session et stockés sur son propre schéma d'exécution. Ce plan n'est visible par les autres sessions que s'il est sauvegardé en tant que « public ».
Les plans publics sont utilisés quand le paramètre USE_STORED_OUTLINES vaut TRUE.
Les plans privés sont utilisés quand le paramètre USE_PRIVATE_OUTLINES vaut TRUE.

Pour utiliser la commande CREATE OUTLINE FROM, il est nécessaire de posséder :

• le privilège SELECT_ANY_CATALOG ;
• le privilège CREATE ANY_OUTLINE.

La commande DBMS_OUTLN_EDIT.create_edit_tables (valable depuis la 8i) :

• crée les tables nécessaires dans le schéma en cours pour dupliquer et éditer les plans stockés (<=> exécution du script utledtol.sql)
• nécessite d'avoir le privilège EXECUTE sur le package DBMS_OUTLN_EDIT.

La colonne outline_sid à été ajoutée à V$SQL : session oracle à partir de laquelle le plan a été rapatrié.

Pour contrôler l'utilisation des plans privés, le paramètre de session peut être modifié :

ALTER SESSION SET USE_PRIVATE_OUTLINES = [TRUE|FALSE|nom_categorie]

• TRUE : active les plans privés dans la catégorie DEFAULT ;
• FALSE : désactive les plans privés ;
• nom_categorie : Active les plans privés dans la catégorie nom_categorie.

La commande qui crée les plans stockés est la suivante :

CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE [nom_outline]
    [FROM  [PUBLIC | PRIVATE] nom_outline_source]
    [FOR CATEGORY nom_catégorie] [ON statement]

Plans d'exécution non recalculés à chaque fois : les valeurs littérales sont remplacées par des variables de substitution (et différents plans possibles avec différentes valeurs).
Trois valeurs possibles pour le paramètre dynamique CURSOR_SHARING :

• FORCE : pas de remplacement ;
• EXACT (par défaut) : partage pour toutes les valeurs ;
• SIMILAR (nouveauté 9i) : l'optimiseur examine l'ordre SQL avant de décider de recalculer (ou pas) le plan.

Les plans d'exécution sont maintenant stockés en cache et consultables avec la vue V$SQL_PLAN.
Pour activer (/désactiver) le monitoring sur un index :

ALTER INDEX mon_index MONITORING USAGE;
ALTER INDEX mon_index NOMONITORING USAGE;

Ces infos sont stockées dans la vue V$OBJECT_USAGE. (Purgés à chacun des ces ordres sur l'index suivi.)

Utilisation d'un index même si la 1^re colonne n'est pas spécifiée (sauf pour les reverses ou bitmap index) = skip scanning.
Bitmap Join Index : Index bitmap créé à partir de jointures.

CREATE BITMAP INDEX mon_index_bitmap
ON sales (c.region)
FROM sales s, customers c
Where c.cust_id = s.cust_id

=> sera utilise dans l'ordre suivant :

SELECT SUM(s.cost)
FROM sales s, customers c   
WHERE s.cust_id = c.cust_id
AND c.region ='Est';

=> permet d'utiliser seulement une table (sales) et l'index bitmap.

Restrictions :

• limitation au parallel DML sur la table fille uniquement (pas sur les tables de référence) ;
• les tables concernées peuvent être mises à jour une à la fois seulement ;
• les tables ne peuvent apparaître plus d'une fois dans la requête ;
• les tables ne peuvent pas être organisées en index ou être temporaires ;
• les colonnes citées dans l'index n'appartiennent qu'aux tables maîtres (ou de référence) ;
• la colonne de jointure sur la table maître ne peut être qu' une clé primaire ou unique ;
• si la clé primaire est une clé composée, toutes les colonnes doivent apparaître dans la jointure.

Ex. :

CREATE TABLE CLIENTS
    (CLIENT_ID        VARCHAR2(7),
    CLIENT_ADRESS       VARCHAR2(32),
    CLIENT_DEPT        VARCHAR2(2))
     PARTITION BY LIST (CLIENT_REGION)
     STORAGE (INITIAL 100K NEXT 10K PCTINCREASE 0) TABLESPACE tbs_cli
    (PARTITION region_nord
      VALUES ('62','75','92','60','45','44')
      STORAGE(INITIAL 25K, NEXT 5K) TABLESPACE tbs_cli1
      PARTITION region_sud
      VALUES ('86','87','33','13','69','84'));

=> les instructions de stockage pour la table servent d'information par défaut pour les partitions (sauf si celles-ci sont spécifiées au niveau de la partition, (STORAGE(INITIAL 25K, NEXT 5K) TABLESPACE tbs_cli1)).
=> si une valeur insérée au niveau de la table n'appartient pas aux listes, une erreur Oracle sera générée.
=> on peut, dans un select, faire une recherche à l'aide d'une égalité, d'une liste de valeurs (opérateur IN) ou d'un opérateur de comparaison (<=, <, >, >= …).

Ajout d'une partition : les valeurs ajoutées ne doivent pas exister dans les listes déjà créées.

ALTER TABLE CLIENT
    ADD PARTITION region_autre
    VALUES ('20','97');

Si le nom de la partition n'est pas spécifié, celui-ci sera généré automatiquement et commencera par SYS_P###.

Fusion (Merge) de deux partitions :

ALTER TABLE CLIENTS
    MERGE PARTITIONS nord_est, nord_ouest
    INTO PARTITION region_nord;

=> Les instructions de stockage pour la table servent d'information par défaut pour la partition de fusion (sauf si celles-ci sont spécifiées au niveau de la partition).
Les index (locaux et globaux) deviennent « Unusable ».

Ajout de valeur à une partition :

ALTER TABLE CLIENTS
    MODIFY PARTITION region_sud
    ADD VALUES ('09','31');

Suppression de valeur d'une partition (et non pas d'une partition) :

ALTER TABLE CLIENT
    MODIFY PARTITION region_sud
    DROP VALUES ('87');

=> il ne doit pas exister de ligne en table pour la valeur supprimée (DELETE obligatoire dans le cas inverse).
=> ne supprime pas toutes les valeurs de la partition.

Éclatement (Split) de partition en deux :

ALTER TABLE CLIENTS
    SPLIT PARTITION region_nord
    VALUES ('44','45','92')
    INTO (PARTITION region_nord_ouest
    TABLESPACE tbs_cli2
    PARTITION region_nord_est) ;

=> Les trois valeurs spécifiées ci-dessous remplissent la première sous-partition créée, les autres valeurs remplissant la seconde sous-partition.
=> Les index globaux deviennent « unusable ».

Restrictions :

• la valeur Null n'est pas acceptée dans les listes ;
• partition sur plusieurs colonnes non acceptées ;
• la liste des valeurs ne peut dépasser 4 Ko ;
• chaque liste doit comporter au moins une valeur.

En 8i, les index globaux sur une table partagée sur plusieurs partitions deviennent 'unusable' si un ordre DDL est exécuté sur la table portant l'index.
En 9i, ceux-ci peuvent rester utilisables si la clause suivante est ajoutée :

ALTER TABLE matable
    DROP SUBPARTITION tab1
    UPDATE GLOBAL INDEXES
    PARALLEL (DEGREE 4);

=> Valables pour les ordres ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE et COALESCE.
=> ne fonctionne pas sur les tables organisées en index.

En 8i, les chargements directs en parallèle (après le high water mark) ne se faisaient qu'avec un processus esclave par partition.
En 9i, plusieurs processus esclaves peuvent être utilisés par partition, ceci améliorant les temps de chargement. L'indicateur (hint) à utiliser est le suivant :
/*+ PARALLEL DEGREE n */ (avec n = nombre de processus /partition).

XI-A-1. Produit cartésien de deux tables (Cross Joins)▲

Select e.ename,
    d.deptno
From emp e CROSS JOIN
    dept d;

XI-A-2. Jointures « Naturelles »▲

Select e.ename,
    deptno
From emp e NATURAL JOIN
    dept d;

XI-A-3. Équijointures▲

Select e.ename,
    deptno
From emp e JOIN
    dept d USING (deptno)
Where d.dept_name = 'PARIS';

XI-A-4. Jointures avec clause « ON »▲

Select e.ename,
    deptno
From emp e JOIN
    dept d ON (e.deptno = d.deptno) JOIN
    region r ON (d.regionno = r.regionno)
Where r.region_name = 'EUROPE';

XI-A-5. Jointures externes▲

Select e.ename,
    deptno
From emp e {LEFT|RIGHT|FULL} OUTER JOIN
    dept d ON (e.deptno = d.deptno)
Where d.dept_name = 'PARIS';

XI-B-1. Fonction EXTRACT▲

EXTRACT (YEAR FROM madate)

XI-B-2. CASE simple : similaire au DECODE▲

Select e.ename , (CASE salary
    WHEN 10000 THEN 'EMPLOYE'
    WHEN 15000 THEN 'CADRE'
    ELSE 'BIG BOSS' END) status
From Emp e;

XI-B-3. CASE de recherche▲

Select e.ename, (CASE 
    WHEN salary < 10000 THEN 'EMPLOYE'
    WHEN salary > 15000 THEN 'CADRE'
    ELSE 'BIG BOSS' END) status
From Emp e;

XI-B-4. NULLIF▲

IF expr1 = expr2 Then 
    return null
ELSE
    return expr1;
End If;

XI-B-5. COALESCE▲

COALESCE(expr1,expr2,expr3,...,exprn)

Ramène une ligne avec une seule valeur

Select t.libelle_type_client ,
    (Select count (*)
    From Client c
    Where c.typ_cli = t.typ_cli) Total
From Type_Client T;

=> peut être utilisé partout sauf dans les clauses GROUP BY ou condition WHEN des triggers.

XI-D-1. GROUPING SETS▲

select time_id, support_id , item_id,
    sum( montant) as cumul
From sales
Group By GROUPING SETS
    ((time_id, support_id , item_id),
    (time_id, support_id), 
    (time_id, item_id))

XI-D-2. Colonnes composites▲

• TIMESTAMP : format de date incluant jusqu'au milliardième de seconde.
• TIMESTAMP WITH TIME ZONE : TIMESTAMP + décalage en heures de la zone.
• TIMESTAMP WITH LOCAL TIME ZONE : TIMESTAMP + décalage en heures et minutes de la zone intégrée dans la date affichée.
• INTERVAL YEAR TO MONTH : stocke le nombre d'années et de mois d'une période.
• INTERVAL DAY TO SECOND : stocke le nombre de jours, heures, minutes, secondes et fractions de seconde d'une période.

• Paramètre statique de base TIME_ZONE :

  • peut avoir comme valeur soit un nombre d'heures et de minutes, soit un nom de zone ;
  • peut être spécifié à la création de la base ou après avec un ALTER DATABASE.
• Heures d'été / d'hiver : géré automatiquement par la base pour TIMESTAMP WITH TIME ZONE et TIMESTAMP WITH LOCAL TIME ZONE (pas pour TIMESTAMP).

La vue V$TIMEZONE_NAMES décrit tous les fuseaux horaires.

• Seul utilisable pour le national character set.
• En UTF-8 (1 à 4 octets) ou UTF-16 (2 * 2 octets).

Nouveaux paramètres d'initialisation pour pouvoir compiler en C :

• PLSQL_NATIVE_LIBRARY_DIR : répertoire de stockage des objets créés par le compilateur ;
• PLSQL_NATIVE_MAKE_UTILITY : nom de l'utilitaire Make ;
• PLSQL_NATIVE_MAKE_FILE_NAME : chemin complet du Make File ;
• PLSQL_NATIVE_C_COMPILER : chemin complet du compilateur C (prend par défaut celui du système d'exploitation) ;
• PLSQL_NATIVE_LINKER : chemin complet de l'éditeur de liens (linker) (sous UNIX) ;
• PLSQL_COMPILER_FLAG : 'INTERPRETED' ou 'NATIVE' (peut être modifié au cours de la session).

• Héritage complet pour les types SQL.
• Combinaisons multiples possibles entre les collections, tables imbriquées (Nested tables) et VARRAY.
• Type : FINAL ou NOT FINAL (signifie qu'il y a encore des fils dessous ).
• Pouvoir de substitution: une colonne d'un type peut contenir des instances de son type ou de n'importe quel de ses sous-types.
• Vues hiérarchiques : du même genre que pour les types et sous-types.

• Privilèges système :

  • UNDER ANY TYPE : permet de créer des sous-types sous des types non finaux ;
  • UNDER ANY VIEW : permet de créer des sous-vues ;
  • WITH HIERARCHY OPTION : Donne (par un GRANT) un privilège objet sur tous ses fils.
• SQL*Loader, Import et Export supportent ces nouvelles fonctionnalités.

• Possibilité d'évolution des Types d'Objet :

  • avant 9i, seul l'ajout de méthodes était autorisé ;

    • une modification de l'objet est répercutée sur les objets le référençant ;
    • les attributs et méthodes peuvent être ajoutés, supprimés ou modifiés ;
    • les types SQL peuvent être modifiés sans être recréés.

Pour améliorer le temps de restauration, Oracle ne restaure que les blocs en mémoire qui n'ont pas été écrits sur le disque (il balaye pour cela deux fois les redo logs).
Un nouveau paramètre dynamique FAST_START_MTTR_TARGET détermine le temps maximal (en secondes : de 0 à 3600) que prendra une restauration (défini par le DBA).
Le changement de ce paramètre induit automatiquement le changement de deux autres :

• FAST_START_IO_TARGET : nombre de blocs de données à récupérer ;
• LOG_CHECKPOINT_INTERVAL : intervalle de temps maximum pour limiter le nombre d'enregistrements de redo logs à lire.
  
  (Ces deux paramètres ne peuvent pas être modifiés manuellement.)

La vue V$INSTANCE_RECOVERY permet de suivre les points de synchronisation (checkpoints) et leurs impacts sur le temps de restauration.

De nouveaux types d'index peuvent être reconstruits en ligne :

• index à clé inversée ;
• index basé sur une fonction ;
• index à clé compressée sur les tables normales ;
• index à clé compressée sur les tables organisées en index.

Une table organisée en index peut être reconstruite en ligne :

ALTER TABLE matable MOVE ONLINE TABLESPACE data1 OVERFLOW TABLESPACE data1;

Pour améliorer les performances d'un index basé sur un IOT, il stocke chaque entrée de l'index dans la DBA (Data Block Address). Si beaucoup de mises à jour sont intervenues, cette DBA n'est alors plus à jour, pour la reconstruire, il faut lancer en ligne l'ordre suivant :

ALTER INDEX monindex UPDATE BLOCK REFERENCES;

Une table peut être réorganisée en ligne si celle-ci contient une clé primaire (Oracle utilise des vues matérialisées pour cela).
Étapes pour réorganiser une table :

• vérifier que la table est réorganisable en ligne avec DBMS_REDEFINITION.Can_Redef_Table ;
• créer la table d'intérim qui servira à stocker les mises à jour effectuées lors de la redéfinition ;
• réorganiser la table avec DBMS_REDEFINITION.Start_Redef_Table ;
• créer les objets et triggers rattachés à la table d'intérim ;
• finir la réorganisation avec DBMS_REDEFINITION.Finish_Redef_Table.

Pour synchroniser la table initiale et celle d'intérim, on appelle la procédure :

DBMS_REDEFINITION.Sync_Interim_Table.

Pour arrêter le traitement en cas d'erreur, on appelle la procédure :

DBMS_REDEFINITION.Abort_Redef_Table.

Limitations :

• colonnes de clés primaires non modifiables ;
• tables des schémas SYS ou SYSTEM non modifiables ;
• colonnes de type LONG ou FILE non modifiables ;
• réorganisation dans le même schéma ;
• nouvelles colonnes ne peuvent être déclarées NOT NULL qu'après la réorganisation.

• Permet de créer une vue de la base à une date située dans le passé.
• Peut démarrer à partir d'une date ou d'un numéro SCN.
• Seules les transactions commitées jusqu'à ce moment sont visibles.
• Valable au niveau de la session.
• Se base sur les tablespaces d'annulation : plus on veut regarder loin dans le temps, plus ceux-ci doivent être importants.
• Utilise le package DBMS_FLASHBACK.
• Pour spécifier le temps de rétention :

ALTER SYSTEM SET UNDO_RETENTION= tempsensecondes;

• Se lance avec la commande :

DBMS_FLASHBACK.Enable_At_Time('30/08/2002 0:51:42');

(il prend le N° SCN le plus proche de la date spécifiée, dans un intervalle de 5 minutes).

• Se termine avec la commande :

DBMS_FLASHBACK.disable.

ou en fermant la session.

• ne s'applique qu'aux ordres DML.

Complément d'information

Vous pouvez également consulter l'article Oracle FlashBack par Helyos