Optimisation de requête SQL (Oracle)

Alors comment optimiser ses requêtes pour optimiser les temps de traitement (Oracle) ?

Tout d'abord il faut savoir que le SGBD utilise un plan d'exécution pour effectuer les instructions demandées.

- Comment fait-il ? Il calcul un plan d'exécution optimisé suivant des algorithmes en fonction des informations qu'il possède, notamment de ses statistiques.

L'exécution de la requête se fait de la plus profonde à l'extérieur (la sous sous sous requête avant la requête affichant le résultat).

- Que va t'il regarder ? Tout d'abord il va regarder les différents index existants sur les tables/objets utilisés dans la requête notamment les clés primaires (index unique) ; à partir des méta-données & statistiques (après un analyse "compute statistic" par exemple). Il va alors déterminer son plan d'exécution en fonction de la volumétrie (index ou full scan) etc.

N'hésitez pas à vous faire conseiller de votre DBA (Administrateur de la base de données), qui vous aidera à savoir s'il convient de partitionner, de vous aider à choisir vos tablesspaces suivant les données physiques et volumétrie de vos tables.

Coût de la requête : le "cost"
Le Cost est un indicateur intéressant, il s'affiche lorsque l'on demande au SGBD de nous montrer son plan d'exécution.

Ce qui est le plus coûteux : La jointure

Optimisation :

• Critères sélectif à la fin de la requête (exécutés en premier) 
• Il convient donc de mettre en fin de requête tous les critères de sélection afin de réduire le nombre de ligne à joindre (et non l'inverse).

• select champ1, champ2 from table1 t1, table2 t2 where t1.pk = t2.pk and t1.champ1 in ('toto','titi');

• Il convient également de projeter uniquement les colonnes nécessaires (ramener 10 champs dont 8 ne seront pas utilisés est un volume de données inutile).
• Table la moins volumineuse (référence) à gauche
• Eviter les select... in(select...) utiliser select... exists(select...)
• Utilisez des fonctions analytiques plutôt que des sous select (peut changer exponentiellement le temps d'exécution)
• Lancer l'analyse de la table régulièrement (en full ou % suivant la volumétrie). Cela peut changer le plan d’exécution en fonction des données récupérées
• Créer des index sur les critères de jointure (create [unique] index...)
• Utiliser des identifiants techniques optimise la sélection des relations complexes (table dont la clé primaire et composite et de types hétérogènes (caractère,numérique...))
• Partitionner la table dans la mesure du possible/besoin (la partition est vue comme une table à part et permet donc de réduire fortement la volumétrie) : select... from table1 partition()
• Utilisation de hints : forcer le plan d’exécution (pour les experts : souvent l'analyse / rebuild ou création d'index / refonte de la requête suffit au SGBD de choisir le bon algorithme).

Produit Cartésien : multiplication des lignes de données car aucun lien n'est trouvé entre 2 tables

Quelques références de site :

• Choix dans les requêtes et optimisation physique
• Tuning Sql - Oracle
• Requête analytique - Oracle Analytic Functions

Share |

Particularités Oracle

--renvoi 'PAS NULL'
select case when NULL = NULL then 'NULL' else 'PAS NULL' end from dual;
--renvoi 'NULL'
select case when NULL IS NULL then 'NULL' else 'PAS NULL' end from dual;

--première condition de vérifiée les autres ne sont pas regardées
select
case
when 1 > 0 then 1
when 1 > 0 and 0 = 0 then 0
else null
end X
from dual;
--résultat : 1 et non 0 !

--intervention de null dans une fonction d'agrégation min,max => aucune incidence
select max(a) from
(
select null a from dual
union
select 5 a from dual
union
select 3 a from dual
);
--résultat : 5, c’est ok

--null absorbant sur la somme (opération d'aggrégation)
select sum(null + 1) from dual;
--résultat : null et non 1 !

--transformation des valeurs nulles
select sum(nvl(null,0) + 1) from dual;
--résultat : 1 comme attendu

--ne remonte pas les lignes dont champ est à NULL
select champ from dual where champ <> '1';
select champ from dual where nvl(champ,'x') <> '1'; --ou champ <> '1' or champ is null;

Test universel ?

Pour savoir s'il n'y a pas de donnée, dans tout language de programmation qui se respecte on teste la longueur de la chaîne "trimée"
on teste donc si la chaine est nulle ou vide en même temps !
Mais sou Oracle Null est absorbant...
On testera donc... la nullité de la valeur qu'elle soit à blanc ou à null => non renseigné.


select trim(nvl(null, ' ')) from dual;

select nvl(null, ' ') from dual;

-- Length(null) => NULL !!
select length(null) from dual;

Doc Functions : ss64 - gladir - techonthenet

Share |