SQL

Ce chapitre présente le SQL (Structured Query Language) et la façon dont Oracle AI Database traite les instructions SQL.

Introduction à SQL

SQL (prononcé sequel) est le langage informatique déclaratif de haut niveau basé sur un ensemble avec lequel tous les programmes et utilisateurs accèdent aux données d'une base de données Oracle.

Bien que certains outils et applications Oracle masquent l'utilisation du langage SQL, toutes les tâches de base de données sont exécutées à l'aide du langage SQL. Toute autre méthode d'accès aux données contourne la sécurité intégrée à Oracle AI Database et compromet potentiellement la sécurité et l'intégrité des données.

SQL fournit une interface vers une base de données relationnelle telle qu'Oracle AI Database. Le langage SQL unifie des tâches telles que les suivantes dans un langage cohérent :

SQL peut être utilisé de manière interactive, ce qui signifie que les instructions sont entrées manuellement dans un programme. Les instructions SQL peuvent également être intégrées à un programme écrit dans un langage différent, tel que C ou Java.

Voir aussi :

Accès aux données SQL

Il existe deux grandes familles de langages informatiques : les langages de déclaration qui ne sont pas de procédure et décrivent ce qui doit être fait, et les langages de procédure tels que C++ et Java qui décrivent comment les choses doivent être faites.

Le langage SQL est déclaratif, c'est-à-dire que les utilisateurs indiquent le résultat qu'ils veulent et non comment le dériver. Par exemple, l'instruction suivante interroge les enregistrements des employés dont le nom commence par K :

La base de données effectue le travail de génération d'une procédure permettant de parcourir les données et d'extraire les résultats demandés. La nature déclarative du langage SQL permet d'utiliser des données au niveau logique. Vous devez vous préoccuper des détails d'implémentation uniquement lorsque vous manipulez les données.

SELECT   last_name, first_name
FROM     hr.employees
WHERE    last_name LIKE 'K%'
ORDER BY last_name, first_name;

La base de données extrait toutes les lignes répondant à la condition WHERE, également appelée prédicat, en une seule étape. La base de données peut transmettre ces lignes en tant qu'unité à l'utilisateur, à une autre instruction SQL ou à une application. L'application n'a pas besoin de traiter les lignes une par une, et le développeur n'a pas non plus besoin de savoir comment les lignes sont stockées ou extraites physiquement.

Toutes les instructions SQL utilisent l'optimiseur, un composant de la base de données qui détermine le moyen le plus efficace d'accéder aux données demandées. Oracle Database prend également en charge les techniques permettant à l'optimiseur d'améliorer ses performances.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour obtenir des informations détaillées sur les instructions SQL et d'autres parties du langage SQL (telles que les opérateurs, les fonctions et les modèles de format).

Normes SQL

Oracle s'efforce de suivre les normes acceptées par le secteur et participe activement aux comités des normes SQL.

Les comités reconnus par l'industrie sont l'Institut national américain de normalisation (ANSI) et l'Organisation internationale de normalisation (ISO). L'ANSI et l'ISO/IEC ont accepté le langage SQL comme langage standard pour les bases de données relationnelles.

La norme SQL est composée de dix parties. Une partie (SQL/RPR:2012) est nouvelle en 2012. Cinq autres parties ont été révisées en 2011. Pour les quatre autres parties, la version 2008 reste en place. Oracle SQL inclut de nombreuses extensions du langage SQL standard ANSI/ISO, et les outils et applications Oracle AI Database fournissent des instructions supplémentaires. Les outils SQL*Plus, SQL Developer et Oracle Enterprise Manager vous permettent d'exécuter n'importe quelle instruction SQL standard ANSI/ISO sur une base de données Oracle, ainsi que toutes les instructions ou fonctions supplémentaires disponibles pour ces outils.

Voir aussi :

Présentation des instructions SQL

Toutes les opérations effectuées sur les informations d'une base de données Oracle sont exécutées à l'aide d'instructions SQL. Une instruction SQL est un programme ou une instruction informatique composé d'identificateurs, de paramètres, de variables, de noms, de types de données et de mots réservés SQL. Remarque : les mots réservés SQL ont une signification particulière dans SQL et ne doivent pas être utilisés à d'autres fins. Par exemple, SELECT et UPDATE sont des mots réservés et ne doivent pas être utilisés comme noms de table.

Une instruction SQL doit être équivalente à une phrase SQL complète, par exemple :

SELECT last_name, department_id FROM employees

Oracle AI Database exécute uniquement des instructions SQL complètes. Un fragment tel que le suivant génère une erreur indiquant qu'un texte supplémentaire est requis :

SELECT last_name;

Les instructions SQL Oracle sont divisées en plusieurs catégories :

Instructions DDL (Data Definition Language - langage de définition des données)

Les instructions de langage de définition de données (DLL) définissent, modifient et suppriment les objets de schéma.

Le langage DDL permet de modifier les attributs d'un objet sans modifier les applications qui y accèdent. Par exemple, vous pouvez ajouter une colonne à une table à laquelle accède une application de ressources humaines sans réécrire l'application. Vous pouvez également utiliser des instructions LDD pour modifier la structure des objets pendant que les utilisateurs de base de données effectuent des tâches dans la base.

Plus précisément, les instructions LDD permettent d'effectuer les opérations suivantes :

Exemple 10-1 Instructions LDD

L'exemple suivant utilise des instructions LDD pour créer la table plants, puis une instruction LMD pour insérer deux lignes dans la table. L'exemple utilise ensuite le langage DDL pour modifier la structure de la table, accorder et révoquer des privilèges de lecture sur cette table à un utilisateur, puis supprimer la table.

CREATE TABLE plants
    ( plant_id    NUMBER PRIMARY KEY,
      common_name VARCHAR2(15) );

INSERT INTO plants VALUES (1, 'African Violet'); # DML statement

INSERT INTO plants VALUES (2, 'Amaryllis'); # DML statement

ALTER TABLE plants ADD
    ( latin_name VARCHAR2(40) );

GRANT READ ON plants TO scott;

REVOKE READ ON plants FROM scott;

DROP TABLE plants;

Une instruction COMMIT implicite se produit immédiatement avant que la base de données n'exécute une instruction LDD et une instruction COMMIT ou ROLLBACK se produit immédiatement après. Dans l'exemple précédent, deux instructions INSERT sont suivies d'une instruction ALTER TABLE, de sorte que la base de données valide les deux instructions INSERT. Si l'instruction ALTER TABLE réussit, la base de données valide cette instruction. Sinon, la base de données annule cette instruction. Dans les deux cas, les deux instructions INSERT ont déjà été validées.

Voir aussi :

Instructions DML (Data Manipulation Language - langage de manipulation des données)

Les instructions de langage de manipulation de données (DML) interrogent ou manipulent des données dans des objets de schéma existants.

Alors que les instructions LDD modifient la structure de la base de données, les instructions LMD interrogent ou modifient le contenu. Par exemple, ALTER TABLE modifie la structure d'une table, tandis que INSERT ajoute une ou plusieurs lignes à la table.

Les instructions LMD sont les instructions SQL les plus utilisées et vous permettent d'effectuer les opérations suivantes :

L'exemple suivant utilise une instruction LMD pour interroger la table employees. L'exemple utilise le langage LMD pour insérer une ligne dans employees, mettre à jour cette ligne, puis la supprimer :

SELECT * FROM employees;

INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
  VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);

UPDATE employees SET salary=9100 WHERE employee_id=1234;

DELETE FROM employees WHERE employee_id=1234;

Un ensemble d'instructions DML qui forme une unité logique de travail est appelé une transaction. Par exemple, une transaction de transfert d'argent peut impliquer trois opérations distinctes : réduire le solde du compte épargne, augmenter le solde du compte courant et enregistrer le transfert dans une table d'historique des comptes. Contrairement aux instructions LDD, les instructions LMD ne valident pas implicitement la transaction en cours.

Voir aussi :

Instructions SELECT

Une requête est une opération qui extrait des données d'une table ou d'une vue.

SELECT est la seule instruction SQL que vous pouvez utiliser pour interroger des données. L'ensemble de données extraites de l'exécution d'une instruction SELECT est appelé ensemble de résultats.

Le tableau suivant présente un mot-clé requis et trois mots-clés couramment trouvés dans une instruction SELECT. La table associe également les fonctionnalités d'une instruction SELECT aux mots-clés.

Tableau 1 Mots-clés dans une instruction SQL

Mot-clé Obligatoire ? Description Capacité
SELECT Oui

Indique les colonnes à afficher dans le résultat. La projection produit un sous-ensemble des colonnes de la table.

Une expression est une combinaison de valeurs, d'opérateurs et de fonctions SQL qui aboutit à une valeur. La liste des expressions qui apparaît après le mot-clé SELECT et avant la clause FROM est appelée liste de sélection.

Projection
FROM Non Indique les tables ou les vues à partir desquelles les données doivent être extraites. Jointure
WHERE Non Spécifie une condition pour filtrer les lignes, produisant un sous-ensemble des lignes de la table. Une condition indique une combinaison d'expressions et d'opérateurs (booléens) logiques, et renvoie la valeur TRUE, FALSE ou UNKNOWN. Sélection
ORDER BY Non Indique l'ordre dans lequel les lignes doivent être affichées.  

Voir aussi : Référence du langage SQL d'Oracle AI Database pour la syntaxe et la sémantique SELECT

Jointures

Une jointure est une requête qui combine des lignes de plusieurs tables, vues ou vues matérialisées.

L'exemple suivant joint les tables employees et departments (clause FROM), sélectionne uniquement les lignes qui répondent aux critères indiqués (clause WHERE) et utilise la projection pour extraire les données de deux colonnes (SELECT). L'exemple de sortie suit l'instruction SQL.

SELECT email, department_name
FROM   employees
JOIN   departments
ON     employees.department_id = departments.department_id
WHERE  employee_id IN (100,103)
ORDER BY email;

EMAIL                     DEPARTMENT_NAME
------------------------- ------------------------------
AHUNOLD                   IT
SKING                     Executive

Le graphique suivant représente les opérations de projection et de sélection dans la jointure illustrée dans la requête précédente.

Figure 1 : Projection et sélection

Description de l'image cncpt290.gif

La plupart des jointures ont au moins une condition de jointure, dans la clause FROM ou dans la clause WHERE, qui compare deux colonnes, chacune provenant d'une table différente. La base de données combine des paires de lignes contenant chacune une ligne de chaque table, pour lesquelles la condition de jointure est évaluée à TRUE. L'optimiseur détermine l'ordre dans lequel la base de données joint les tables en fonction des conditions de jointure, des index et des statistiques disponibles pour ces tables.

Les types de jointure sont les suivants :

Voir aussi :

Sous-requêtes

Une sous-interrogation est une instruction SELECT imbriquée dans une autre instruction SQL. Les sous-interrogations sont utiles lorsque vous devez exécuter plusieurs interrogations pour résoudre un seul problème.

Chaque partie de requête d'une instruction est appelée bloc de requête. Dans l'interrogation suivante, la sous-interrogation entre parenthèses est le bloc d'interrogation interne :

SELECT first_name, last_name
FROM   employees
WHERE  department_id
IN     ( SELECT department_id
         FROM departments
         WHERE location_id = 1800 );

L'instruction interne SELECT extrait les ID des services dont l'ID d'emplacement est 1800. Ces codes service sont requis par le bloc d'interrogation externe, qui extrait les noms des employés des services dont les codes ont été fournis par la sous-interrogation.

La structure de l'instruction SQL n'oblige pas la base de données à exécuter d'abord l'interrogation interne. Par exemple, la base de données peut réécrire l'interrogation entière en tant que jointure de employees et departments, de sorte que la sous-interrogation ne s'exécute jamais seule. Autre exemple, la fonctionnalité Virtual Private Database (VPD) peut restreindre l'interrogation des employés à l'aide d'une clause WHERE, de sorte que la base de données interroge d'abord les employés, puis obtient les ID de service. L'optimiseur détermine la meilleure séquence d'étapes pour extraire les lignes demandées.

Voir aussi : Guide de sécurité Oracle AI Database pour en savoir plus sur VPD

Instructions de contrôle de transaction

Les instructions de contrôle de transaction gèrent les modifications apportées par les instructions LMD et regroupent les instructions LMD en transactions.

Ces instructions permettent d'effectuer les opérations suivantes :

L'exemple suivant démarre une transaction nommée Update salaries. L'exemple de la diapositive ci-dessus crée un point de sauvegarde, met à jour le salaire d'un employé, puis annule la transaction jusqu'au point de sauvegarde. L'exemple met à jour le salaire avec une valeur différente et valide.

SET TRANSACTION NAME 'Update salaries';

SAVEPOINT before_salary_update;

UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML

ROLLBACK TO SAVEPOINT before_salary_update;

UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML

COMMIT COMMENT 'Updated salaries';

Voir aussi :

Instructions de contrôle de session

Les instructions de contrôle de session gèrent dynamiquement les propriétés d'une session utilisateur.

Une session est une entité logique de la mémoire de l'instance de base de données qui représente l'état d'une connexion utilisateur en cours à une base de données. Une session dure à partir du moment où l'utilisateur est authentifié par la base de données jusqu'à ce qu'il se déconnecte ou quitte l'application de base de données.

Les instructions de contrôle de session permettent d'effectuer les opérations suivantes :

L'instruction suivante modifie dynamiquement le format de date par défaut de votre session en 'YYYY MM DD-HH24:MI:SS' :

ALTER SESSION
   SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

Les instructions de contrôle de session ne valident pas implicitement la transaction en cours.

Voir aussi :

Instruction de contrôle système

Une instruction de contrôle système modifie les propriétés de l'instance de base de données.

La seule instruction de contrôle système est ALTER SYSTEM. Il vous permet de modifier des paramètres tels que le nombre minimal de serveurs partagés, de mettre fin à une session et d'effectuer d'autres tâches au niveau du système.

Exemples d'instructions de contrôle système :

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM KILL SESSION '39, 23';

L'instruction ALTER SYSTEM ne valide pas implicitement la transaction en cours.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour la syntaxe et la sémantique ALTER SYSTEM

Instructions SQL intégrées

Les instructions SQL intégrées intègrent des instructions LDD, LMD et de contrôle des transactions dans un programme en langage procédural.

Les instructions intégrées sont utilisées avec les précompilateurs Oracle. Le langage SQL intégré est une approche pour intégrer le langage SQL dans vos applications de langage procédural. Une autre approche consiste à utiliser une API procédurale telle que la connectivité ODBC (Open Database Connectivity) ou JDBC (Java Database Connectivity).

Les instructions SQL intégrées permettent d'effectuer les opérations suivantes :

Voir aussi :

Présentation de l'optimiseur

Pour comprendre comment Oracle AI Database traite les instructions SQL, il est nécessaire de comprendre la partie de la base de données appelée optimiseur (également appelé optimiseur d'interrogation ou optimiseur basé sur les coûts). Toutes les instructions SQL utilisent l'optimiseur pour déterminer le moyen le plus efficace d'accéder aux données indiquées.

Utilisation de l'optimiseur

L'optimiseur génère des plans d'exécution décrivant les méthodes d'exécution possibles.

L'optimiseur détermine le plan d'exécution le plus efficace en tenant compte de plusieurs sources d'informations. Par exemple, l'optimiseur prend en compte les conditions d'interrogation, les chemins d'accès disponibles, les statistiques collectées pour le système et les conseils.

Pour exécuter une instruction LMD, Oracle AI Database peut être amené à effectuer de nombreuses étapes. Chacune d'elles extrait physiquement des lignes de données de la base ou les prépare de manière à ce que l'utilisateur émettant l'instruction. Les étapes que la base de données utilise pour exécuter une instruction ont une incidence considérable sur la rapidité d'exécution de l'instruction. Plusieurs méthodes de traitement d'une instruction LMD sont souvent possibles. Par exemple, l'ordre d'accès aux tables ou aux index peut varier.

Lorsque l'optimiseur détermine le meilleur plan d'exécution pour une instruction SQL, il effectue les opérations suivantes :

L'optimiseur génère la plupart des méthodes possibles de traitement d'une interrogation et affecte un coût à chaque étape du plan d'exécution généré. Le plan dont le coût est le plus bas est choisi comme plan de requête à exécuter.

Remarque : vous pouvez obtenir un plan d'exécution pour une instruction SQL sans exécuter le plan. Seul un plan d'exécution que la base de données utilise réellement pour exécuter une interrogation est correctement appelé plan d'interrogation.

Vous pouvez influencer les choix de l'optimiseur en définissant l'objectif de l'optimiseur et en collectant des statistiques représentatives pour l'optimiseur. Par exemple, vous pouvez définir l'objectif de l'optimiseur sur l'un des éléments suivants :

Une application interactive utilisateur final classique bénéficierait de l'optimisation du temps de réponse initial, tandis qu'une application non interactive en mode batch bénéficierait de l'optimisation du débit total.

Voir aussi :

Composants d'optimiseur

L'optimiseur contient trois composants principaux : le transformateur, l'estimateur et le générateur de plans.

Le diagramme suivant représente les composants :

Figure 2 : Composants de l'optimiseur

Description de l'image cncpt287.gif

L'entrée de l'optimiseur est une interrogation analysée. L'optimiseur effectue les opérations suivantes :

  1. L'optimiseur reçoit l'interrogation analysée et génère un ensemble de plans potentiels pour l'instruction SQL en fonction des chemins d'accès et des conseils disponibles.

  2. L'optimiseur estime le coût de chaque plan en fonction des statistiques du dictionnaire de données. Le coût est une valeur estimée, proportionnelle à l'utilisation des ressources prévue, nécessaire pour exécuter l'instruction avec un plan particulier.

  3. L'optimiseur compare les coûts des plans et choisit le plan le moins coûteux, appelé plan d'interrogation, à transmettre au générateur de row sources.

Voir aussi :

Transformateur de requêtes

Le transformateur d'interrogation détermine s'il est utile de modifier la forme de l'interrogation afin que l'optimiseur puisse générer un meilleur plan d'exécution. L'entrée du transformateur d'interrogations est une interrogation analysée, que l'optimiseur représente sous la forme d'un ensemble de blocs d'interrogation.

Voir aussi : Réécriture de requête

Estimateur

L'estimateur détermine le coût global d'un plan d'exécution donné.

L'estimateur génère trois types de mesures différents pour atteindre cet objectif :

Si des statistiques sont disponibles, l'estimateur les utilise pour calculer les mesures. Les statistiques améliorent le degré de précision des mesures.

Générateur de plan

Le générateur de plans teste différents plans pour une requête soumise. L'optimiseur choisit le plan dont le coût est le plus bas.

Pour chaque sous-interrogation imbriquée et vue non fusionnée, l'optimiseur génère un sous-plan. L'optimiseur représente chaque sous-plan sous la forme d'un bloc de requête distinct. Le générateur de plans explore différents plans pour un bloc d'interrogation en essayant différents chemins d'accès, méthodes de jointure et ordres de jointure.

La fonction d'optimisation adaptative des requêtes modifie les plans en fonction des statistiques collectées lors de l'exécution des instructions. Tous les mécanismes adaptatifs peuvent exécuter un plan final pour une instruction qui diffère du plan par défaut. L'optimisation adaptative utilise des plans dynamiques, qui choisissent parmi les sous-plans lors de l'exécution de l'instruction, ou la réoptimisation, qui modifie un plan lors des exécutions après l'exécution en cours.

Voir aussi :

Chemins d'accès

Un chemin d'accès est la technique utilisée par une requête pour extraire des lignes.

Par exemple, une requête qui utilise un index a un chemin d'accès différent d'une requête qui ne le fait pas. En général, les chemins d'accès aux index sont les meilleurs pour les instructions qui extraient un petit sous-ensemble de lignes de table. Les balayages complets sont plus efficaces pour accéder à une grande partie d'une table.

La base de données peut utiliser plusieurs chemins d'accès différents pour extraire des données d'une table. Voici une liste représentative :

L'optimiseur choisit un chemin d'accès en fonction des chemins d'accès disponibles pour l'instruction et du coût estimé de l'utilisation de chaque chemin d'accès ou combinaison de chemins.

Voir aussi : Oracle AI Database - Introduction au réglage des performances et Guide de réglage SQL Oracle AI Database pour en savoir plus sur les chemins d'accès

Statistiques de l'optimiseur

Les statistiques de l'optimiseur sont un ensemble de données qui décrivent les détails de la base de données et des objets qu'elle contient. Les statistiques offrent une image statistiquement correcte du stockage et de la répartition des données, qui est utilisable par l'optimiseur lors de l'évaluation des chemins d'accès.

Elles se composent des éléments suivants :

Oracle AI Database collecte automatiquement les statistiques destinées à l'optimiseur sur tous les objets de base de données et les tient à jour en tant que tâche de maintenance automatisée. Vous pouvez également collecter des statistiques manuellement à l'aide du package DBMS_STATS. Ce package PL/SQL peut modifier, afficher, exporter, importer et supprimer des statistiques. Remarque : les statistiques destinées à l'optimiseur sont créées à des fins d'optimisation des requêtes et stockées dans le dictionnaire de données. Ne confondez pas ces statistiques avec les statistiques de performances visibles via des vues dynamiques des performances.

Optimizer Statistics Advisor est un logiciel de diagnostic intégré qui analyse la façon dont vous collectez actuellement des statistiques, l'efficacité des travaux de collecte de statistiques existants et la qualité des statistiques collectées. Elle tient à jour des règles qui reflètent les meilleures pratiques Oracle en fonction de l'ensemble actuel de fonctionnalités. De cette façon, la fonction de conseil fournit toujours les recommandations les plus récentes pour la collecte de statistiques.

Voir aussi :

Conseils de l'optimiseur

Un conseil est un commentaire dans une instruction SQL qui sert d'instruction à l'optimiseur.

Parfois, le concepteur d'applications, qui dispose de plus d'informations sur les données d'une application particulière que celles dont dispose l'optimiseur, peut choisir un moyen plus efficace d'exécuter une instruction SQL. Le concepteur d'applications peut utiliser des conseils dans les instructions SQL pour indiquer comment l'instruction doit être exécutée. Les exemples suivants illustrent l'utilisation des conseils.

Exemple 10-2 : plan d'exécution pour SELECT avec conseil FIRST_ROWS

Supposons que votre application interactive exécute une requête qui renvoie 50 lignes. Cette application extrait initialement uniquement les 25 premières lignes de la requête à présenter à l'utilisateur final. Vous souhaitez que l'optimiseur génère un plan qui obtient les 25 premiers enregistrements le plus rapidement possible afin que l'utilisateur ne soit pas obligé d'attendre. Vous pouvez utiliser un conseil pour transmettre cette instruction à l'optimiseur, comme indiqué dans l'instruction SELECT et dans la sortie AUTOTRACE de l'exemple suivant :

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes
------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   | 26   | 182
|  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         | 26   | 182
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      |
------------------------------------------------------------------------

Dans cet exemple, le plan d'exécution indique que l'optimiseur choisit un index sur la colonne employees.department_id pour rechercher les 25 premières lignes de employees dont l'ID de département est supérieur à 50. L'optimiseur utilise l'ID de ligne extrait de l'index pour extraire l'enregistrement de la table employees et le renvoyer au client. La récupération du premier enregistrement est généralement quasi instantanée.

Exemple 10-3 : plan d'exécution pour SELECT sans conseil

Supposons que vous exécutez la même instruction, mais sans le conseil de l'optimiseur :

SELECT employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cos
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                   | 50   | 350   |
|* 1 |  VIEW                  | index$_join$_001  | 50   | 350   |
|* 2 |   HASH JOIN            |                   |      |       |
|* 3 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX | 50   | 350   |
|  4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK     | 50   | 350   |

Dans ce cas, le plan d'exécution joint deux index pour renvoyer les enregistrements demandés le plus rapidement possible. Plutôt que de passer de manière répétée d'un index à une autre, comme dans l'Example 10-2, l'optimiseur choisit un balayage de plage de EMP_DEPARTMENT_IX pour rechercher toutes les lignes dont l'ID de département est supérieur à 50 et placer ces lignes dans une table de hachage. L'optimiseur choisit ensuite de lire l'index EMP_EMP_ID_PK. Pour chaque ligne de cet index, il sonde la table de hachage pour trouver l'ID de département.

Dans ce cas, la base de données ne peut pas renvoyer la première ligne au client tant que l'analyse de plage d'index de EMP_DEPARTMENT_IX n'est pas terminée. Ainsi, ce plan généré prendrait plus de temps pour renvoyer le premier enregistrement. Contrairement au plan de l'Example 10-2, qui accède à la table par rowid d'index, le plan utilise des E/S multiblocs, ce qui entraîne des lectures volumineuses. Les lectures permettent de renvoyer plus rapidement la dernière ligne de l'ensemble de résultats.

Voir aussi : Guide de réglage SQL Oracle AI Database pour apprendre à utiliser les conseils de l'optimiseur

Présentation du traitement SQL

Cette section explique comment Oracle AI Database traite les instructions SQL. Plus précisément, cette section explique comment la base de données traite les instructions LDD pour créer des objets, les instructions LMD pour modifier des données et les interrogations pour extraire des données.

Etapes de traitement SQL

Les étapes générales du traitement SQL sont l'analyse, l'optimisation, la génération de row sources et l'exécution. Selon l'instruction, la base de données peut omettre certaines de ces étapes.

La figure suivante représente les étapes générales :

Figure 3 : Etapes de traitement SQL

Description de l'image cncpt250.gif

Analyse SQL

La première étape du traitement SQL est l'analyse SQL. Cette étape consiste à séparer les morceaux d'une instruction SQL en une structure des données pouvant être traitée par d'autres routines.

Lorsqu'une application émet une instruction SQL, elle envoie un appel d'analyse à la base de données pour préparer l'instruction en vue de son exécution. L'appel d'analyse ouvre ou crée un curseur, qui est un descripteur pour la zone SQL privée propre à la session qui contient une instruction SQL analysée et d'autres informations de traitement. Le curseur et la zone SQL privée se trouvent dans la PGA.

Au cours de l'appel d'analyse, la base de données effectue les vérifications suivantes :

Les vérifications précédentes identifient les erreurs détectées avant l'exécution de l'instruction. Certaines erreurs ne peuvent pas être détectées par l'analyse. Par exemple, la base de données peut rencontrer un verrou mortel ou des erreurs de conversion de données uniquement lors de l'exécution de l'instruction.

La base de données tente d'atténuer automatiquement les erreurs pour les instructions SQL qui échouent avec une erreur ORA-00600 pendant la phase d'analyse. Une erreur ORA-00600 est grave. Elle indique qu'un processus a rencontré une condition inattendue de bas niveau. Lorsqu'une instruction SQL échoue avec cette erreur pendant la phase d'analyse, la réduction automatique des erreurs l'interrompt et tente de résoudre la condition. Si une résolution est trouvée, la base de données génère un patch SQL afin d'ajuster le plan d'exécution SQL. Si ce patch permet à l'analyse de se terminer correctement, l'erreur ORA-00600 n'est pas générée. Aucune exception n'est détectée par l'application.

Voir aussi : Verrous et interblocages A propos de l'atténuation automatique des erreurs SQL

Optimisation SQL

L'optimisation des requêtes est le processus qui consiste à choisir le moyen le plus efficace d'exécuter une instruction SQL.

La base de données optimise les requêtes en fonction des statistiques recueillies sur les données auxquelles les utilisateurs accèdent. L'optimiseur utilise le nombre de lignes, la taille de l'ensemble de données et d'autres facteurs pour générer des plans d'exécution possibles, en attribuant un coût numérique à chaque plan. La base de données a recours au plan présentant le coût le plus bas.

La base de données doit effectuer une analyse complète au moins une fois pour chaque instruction LMD unique et effectue une optimisation au cours de cette analyse. Le langage DDL n'est jamais optimisé, sauf s'il inclut un composant LMD tel qu'une sous-interrogation nécessitant une optimisation.

Voir aussi :

Génération de sources de ligne SQL

Le générateur de sources de ligne est le logiciel qui reçoit le plan d'exécution optimal de l'optimiseur et produit un plan itératif, appelé plan de requête, utilisable par le reste de la base de données.

Le plan de requête prend la forme d'une combinaison d'étapes. Chaque étape renvoie un ensemble de lignes. Les lignes de cet ensemble sont utilisées à l'étape suivante ou, à la dernière étape, renvoyées à l'application à l'origine de l'instruction SQL.

Un row source est un ensemble de lignes renvoyé par une étape du plan d'exécution, ainsi qu'une structure de contrôle qui peut traiter les lignes de manière itérative. Le row source peut être une table, une vue ou le résultat d'une opération de jointure ou de regroupement.

Exécution SQL

Pendant l'exécution, le moteur SQL exécute chaque source des lignes dans l'arborescence produite par le générateur de sources de lignes. Il s'agit de la seule étape obligatoire du traitement LMD.

Lors de l'exécution, si les données ne sont pas en mémoire, la base lit les données du disque dans la mémoire. La base de données élimine également tous les verrous et verrous internes nécessaires pour garantir l'intégrité des données et consigne toutes les modifications apportées pendant l'exécution SQL. La dernière étape du traitement d'une instruction SQL consiste à fermer le curseur.

Si la base de données est configurée pour utiliser le stockage en colonnes en mémoire (emplacement de stockage des colonnes IM), elle achemine les requêtes de manière transparente vers l'emplacement de stockage des colonnes IM, si possible, vers le disque et le cache de tampons de la base de données. Une requête unique peut également utiliser le stockage de colonnes IM, le disque et le cache de tampons. Par exemple, une requête peut joindre deux tables, dont une seule est mise en cache dans la banque de colonnes IM.

Voir aussi :

Différences entre le traitement LMD et LDD

Oracle AI Database traite les instructions DDL différemment des instructions DML.

Par exemple, lorsque vous créez une table, la base de données n'optimise pas l'instruction CREATE TABLE. A la place, Oracle AI Database analyse l'instruction DDL et exécute la commande.

Contrairement aux instructions LDD, la plupart des instructions LMD comportent un composant d'interrogation. Dans une interrogation, l'exécution d'un curseur place la ligne générée par l'interrogation dans l'ensemble de résultats.

La base de données peut extraire des lignes d'ensemble de résultats une ligne à la fois ou en groupes. Lors de l'extraction, la base de données sélectionne les lignes et, si l'interrogation le demande, trie les lignes. Chaque extraction successive extrait une autre ligne du résultat jusqu'à ce que la dernière ligne ait été extraite.

Voir aussi : Guide de développement Oracle AI Database pour en savoir plus sur le traitement des instructions DDL, le contrôle des transactions et d'autres types d'instruction