Programmation SQL

Optimiser des requêtes SQL

SQL, un Langage basé sur l’Algèbre relationnel, non procédural, fermé, puissant, ensembliste.

_Union, intersection, différence,

_Sélection, projection, jointure, division.

Requêtes complexes : Jointure ; Jointure externe ; Union ; Différence ; Jointure Opération permet de combiner des informations provenant de plusieurs tables.

 Condition de jointure entre les différentes tables

select t1.c1, t1.c2, t2.c5 from table1 t1,table2 t2 wheret1.c1= t2.c1 (+)andt1.c2= t2.c2 (+)

Remarques : vérifier le plan d’exécution de la requête, car la table (+) risque d’être accédée en TABLE ACCESS FULL, ce qui est pénalisant.

Union, Différence : Combiner des lignes provenant de deux interrogations. La même liste SELECT. Résultat sans doublons (sauf UNION ALL). La clause ORDER BY doit faire référence à la position des colonnes

Première aperçu de SQL

 Les différents langages

DML : Data Manipulation Langage

Utilisé pour Select, Insert, Update, Delete, Merge, Lock, Explain Plan

DDL : Data Définition Langage - Utilisé pour définir, modifier, supprimer des objets ou des privilèges.

Utilisé par Create, Alter, Drop, Rename, Truncate, Audit, NoAudit, Comment

Les mots clés :

SELECT, DISTINCT, FROM, WHERE, ORDER BY, HAVING, UNION, UNION ALL, INTERSECT, MINUS ...

 

Les opérateurs mathématiques et ordre de préférence :
Pour les opérateurs suivants -, +, * et /, Oracle les évaluent dans un ordre de préférence. L'ordre de prise en compte des opérateur par le noyau Oracle est le suivant :

*, /, +, -

Les alias de colonne : Le nom de la colonne Alias est défini après le nom de la colonne avec un espace ou en utilisant le mot réservé:

"AS"Select Name Nom, ... Select Name as Nom, ...

Unicité de résultat : Le mot clé utilisé est Distinct - Unicité pour l'ensemble des éléments sélectionnés.

Select Distinct Name, Age from Person;Unité de Name+Age

 

La table Dual :

Select Sysdate from Dual

Dual est une table virtuelle utilisable par tous les utilisateurs et qui ne possède qu'une seule colonne. La table Dual est utilisée pour sélectionner des valeurs system ou évaluer une expression.

 

 Les Opérateurs :

 

Equalité = inégralité != ^= plus petit que < plus grand que > plus petit ou égal <= plus grand ou égal >=

L' Opérateur liste : ANY - SOME sont des opérateurs pour comparer toutes les valeurs d'une liste ou sous requête.

ANY - SOME doivent être précédés de :

=, !=, , = Select ... toto_id <= ANY (10, 15 ,20)

=, !=, ,= Select ... toto_id >= ALL (10, 15, 20)

- L'opérateur NOT est utilisé pour inverser le résultat. Retourne TRUE si l'opérant est faux, FALSE si l'opérant est TRUE, et NULL si l'opérant est NULL.

- L'opérateur AND retourne vrai si les deux opérant sont vrais. Retourne FALSE si un opérant est FALSE,Null si les deux opérants sont nulls.

- L'opérateur OR retourne TRUE si un opérateur est vrai, FALSE si les deux sont FALSE; sinon NULL.

Les Autres Opérateurs :

IN, NOT IN , Ces opérateurs sont utilisés pour tester l'existence ou non dans une liste ou sous requête.

BETWEEN A and B , teste une valeur entre deux bornes

EXISTS , cet opérateur est toujours suivi d'une sous requête. EXISTE retourne TRUE si la sous- requête retourne au moins un enregistrement.

IS NULL, IS NOT NULL , recherche si une valeur est nulle ou non nulle. Cet opérateur ne marche pas avec = ou !=

LIKE , permet de sélectionner des enregistrements en fonction d'une chaîne de caractère.

'CA%' tous les CA....'CA\_%' ESCAPE '\' tous les ...CA...
 

 Trier les enregistrements

ORDER BY : sont les mots clés pour sélectionner des enregistrements dans un ordre défini ASC ou DESC. Le tri est toujours ASC par défaut si aucun formalisme n'est n'existe. Les champs utilisés dans l'instruction Order by doivent être présents dans la sélection.

ASC : tri ascendant

DESC : tri descendant

 Positionnement des valeurs Null dans le tri :

Par default les valeurs Null sont toujours positionnées à la fin. Les mots clés NULL FIRST permettent de positionner les champs de la colonne triée en premier. Les mots clés NULL LAST permettent de placer les champs Null à la fin.

ASC : les valeurs NULL sont à la fin ASC NULL FIRST : les valeurs NULL sont au début ASC NULL LAST : les valeurs NULL sont à la fin DESC : les valeurs NULL sont au debut DESC NULL FIRST : les valeurs NULL sont au debut DESC NULL LAST : les valeurs NULL sont à la fin

 

l'Evolution SQL 8i, 9i : Pour éviter le IF THEN ELSE nous pouvons à partir de la version SQL 9i utiliser la notion de :

SELECT CASE CASE WHEN THEN ELSE END

 Exemple :

Select Nom,Prenom,Age, Case Age WHEN < 18 THEN 'Enfant' WHEN >= 18 THEN 'Adulte' ELSE 'Erreur' END

Utiliser  SQL Plus - Commande dans SQL-PLUS :

LIST, APPEND, CHANGE, INPUT, DEL, CLEAR BUFFER Start, @ : Lancement d'un programme SAVE, EDIT, GET : Utilisation de fichier script LIST : commande LIST ou L, les paramètres sont n et m. LIST m n (L m n) : liste les lignes depuis la ligne m à n LIST LAST (L LAST) : liste la dernière ligne APPEND : commande APPEND ou A, ajoute du texte à la fin de la ligne sélectionnée A Where toto 'T' APPEND Where toto 'T' CHANGE : Commande CHANGE /old/new (C /old/new ), modifie une valeur par une autre, si le valeur de new est manquante elle sera supprimée. INPUT : Commande INPUT texte (I texte), ajoute une ligne de texte DEL : commande CLEAR BUFFER (CL BUFF) supprime les lignes du buffer START: commande DEL, DEL m n supprime de la ligne m à n, DEL n * supprime de la ligne n à la fin. DEL LAST : supprime la dernière ligne. commande START fichier.sql (@ fichier.sql) :  permet de lancer un fichier sous sql Plus SAVE : commande SAVE fichier, permet de sauvegarder le buffer dans un fichier SAVE fichier APPEND : commande SAVE fichier APPEND, permet d'ajouter le buffer à la fin du fichier. EDIT : commande EDIT fichier, permet d'éditer le fichier dans un programme par défaut (note.exe) GET : SPOOL dans un fichier SPOOL : commande SPOOL fichier, permet d'afficher le résultat des requêtes dans le fichier SPOOL OFF : arrête le transfert de données vers le fichier. SPOOL OUT : arrête le transfert et le redirige vers l'imprimante par défaut.

 

Les Commentaires : les mots clés

REMARKS, REM, --, /* (début), */ (fin)

 

 Modification des paramètres de SQL Plus :

Show All : Pour voir tous les paramètres de Sql Plus

Show PAGESIZE LINSIZE : Pour voir un ou plusieurs paramètres

 Modifier les paramètres :

SET - SET PAGESIZE 1500  - SET TIME ON1500 - LINESIZE 100

 

SHOW : Visualiser les erreurs dans SQL Plus

commande Show errors, Show err

 

 Création d'une requête interactive :

La commande & devant une valeur permet d'effectuer un Accept avant le lancement de la requête. Pour avoir plusieurs Accept utiliser && devant la variable. Pour initialiser ces variables :

UNDEFINE var1 var2 ... au début du script

 

 Groupe de fonction

AVG () moyenne

Count() nombre

SUM () totalMAX () maximumMIN () minimumFIRST () premier LAST () dernier. L'ensemble des fonctions de groupe est positionnées  dans le SELECT

SQLPLUS user/password@chaine_de_connexion : Connexion à SQL Plus connect user/password@chaine_de_connexion pour se connecter (dans sqlplus ) disconnect pour se deconnecter ( dans sqlplus ) exit pour de deconnecter et sortir (dans sqlplus )

 Les Jointures

NATURAL JOIN : jointure entre deux tables, quand la jointure est basées sur des champs ayant la même structure et le même nom.

exemple :

Select * from Person NATURAL JOIN Famille==> Select * from Person, Famille where Person.Nom=Famille.Nom

JOIN USING : jointure entre tables avec des noms identiques mais pas le même DataType.

exemple :

Select * from Person JOIN Famille USING (Nom) ==> Select * from Person, Famille where Person.Nom=Famille.Nom

JOIN ON : jointure entre tables avec des noms différents

exemple :

Select * from Person P JOIN Famille F ON P.Nom = F.Nom ==> Select * from Person, Famille where Person.Nom=Famille.Nom

Jointures Cartésiennes : à éviter full scan, le résultat est le nombre de libre de la 1° table * nombre de libre de la 2° table.

(T1=1000 lignes T2=500 lignes, résultat = 500 000 lignes)

exemple :

Select * from Person, Famille

Jointures externes classiques :

exemple :

Select * From Person,Famillewhere Person.nom = Famille.nom (+)and Famille.age(+) > 10;

Jointures externe "LEFT OUTER JOIN" :

exemple :

Select * From Person P LEFT OUTER JOIN Famille F P.Nom =F.Nom;

Peut être écrit de cette manière suivante ==> Select * From Person P, Famille F Where F.Nom(+) = P.Nom

Jointures externes "RIGHT OUTER JOIN" :

exemple :

Select * From Famille F RIGHT OUTER JOIN Person PON P.Nom =F.Nom;

Peut être écrit de la manière suivante ==> Select * From Person P, Famille F Where P.Nom = F.Nom(+)

 L’Utilisation d'opérateur entre des requêtes combinées :

UNION, UNION ALL, INTERSECT, MINUS

UNION : retourne tous les enregistrements uniques sélectionnés par les requêtes

UNION ALL : retourne tous les enregistrements sélectionnés par les requêtes

INTERSECT : retourne les enregistrements sélectionnés par les requêtes

MINUS : retourne les enregistrements uniques de la 1° requête moins ceux sélectionnés par la seconde requête.

 

---

Plus d'articles...

TKPROF

Fonction du PL primaire