Cette documentation définit de manière exhaustive les normes pour :

• La conception du modèle de données
• La conception des tables
• La conception des tables partitionnées
• La conception des index
• La conception d'autres objets de base de données
• Les normes d'accès SQL
• Les normes d'encodage et de commentaires

Elle détaille également les points clés et méthodes courantes pour l'optimisation SQL, servant de référence technique pour la planification, la conception, le développement et la maintenance des bases de données relationnelles.

L'objectif est de guider la conception et le développement des bases de données relationnelles, en démontrant que la performance et la qualité sont des éléments issus de la conception elle-même.

Principes de modélisation des bases de données

Pour les projets impliquant une base de données, il est essentiel de construire un modèle logique. Ce diagramme sert de base de communication entre les membres de l'équipe concernant la base de données et doit suivre des normes strictes (tables, clés primaires, clés étrangères, relations).

Pour les modèles comptant plus de 20 tables, la participation d'un DBA est requise pour la conception et la validation finale.

Pour les systèmes OLTP, une approche normalisée est recommandée. Pour les systèmes OLAP, une approche orientée problème avec des granularités multiples est privilégiée.

Il est conseillé d'utiliser des outils de conception standardisés tels que PowerDesigner ou ERWin.

Relations entre entités

Toutes les relations logiques métier doivent être converties en l'une des trois relations fondamentales des bases de données relationnelles (1:1, 1:N, N:M). Pour les relations impliquant trois entités ou plus, la participation d'un DBA est nécessaire.

La relation entre deux entités A et B peut être déterminée par deux questions :

• Un A peut correspondre à combien de B ?
• Un B peut correspondre à combien de A ?

Principes d'implémentation :

• 1:1 : La clé primaire de l'une des tables est ajoutée comme clé étrangère dans l'autre table.
• 1:N : La clé primaire de la table "1" est ajoutée comme clé étrangère dans la table "N".
• N:M : Une table de relation est créée. Sa clé primaire est une clé composée des clés primaires des tables concernées, qui sont également des clés étrangères.

Normes de normalisation (1NF)

Les modèles des systèmes OLTP doivent respecter la troisième forme normale (3NF). Pour les modèles de plus de 20 tables, la participation d'un DBA est requise.

Exigence de la 1NF : Une colonne est la plus petite unité d'accès, possède une nature atomique et ne peut être subdivisée.

Exemple de violation courante : stocker plusieurs valeurs dans une seule colonne sous forme de chaîne séparée par des virgules. La conception correcte utilise deux tables (une table d'employés et une table de compétences) liées par une relation N:M via une table de relation.

Normes de conception des tables

Normes de nommage

Règle de nommage pour les tables : TBL_MODULE_TABLE_SUFFIX en majuscules. TBL pour les tables générales, TMP pour les temporaires, etc.

Règle de nommage pour les colonnes : nom_colonne en minuscules. Le suffixe indique le type de donnée (ex: _date pour les dates, _num pour les quantités).

Normes de conception physique

Espace de table : Chaque table doit être créée dans un espace de table spécifique et non par défaut.

Clé primaire : Sauf pour les tables temporaires ou de journal, chaque table doit avoir une clé primaire. Pour les tables de plus de 5 millions de lignes, créez d'abord un index unique avant d'ajouter la contrainte de clé primaire.

Clé étrangère : Les clés étrangères doivent être ajoutées pour garantir l'intégrité des données. Pour les performances, un index doit être créé sur les colonnes de clé étrangère.

Champs volumineux (BLOB, CLOB) : Ils doivent être stockés dans une table séparée et liés par clé étrangère, ou leur chemin d'accès doit être stocké dans la table principale.

NULL : La possibilité d'être NULL doit être explicitement spécifiée lors de la création.

Commentaires : Chaque table et chaque colonne doivent avoir un commentaire expliquant leur signification.

Normes pour les tables partitionnées

Lorsque le volume de données dépasse 5 millions de lignes, envisagez une table partitionnée. Au-delà de 50 millions de lignes, la participation d'un DBA est requise.

Avantages : Améliore les performances de requête, l'isolation des données et simplifie la maintainance.

Types de partitionnement recommandés :

• RANGE : Pour les accès basés sur une plage de valeurs (ex: dates).
• LIST : Pour les accès basés sur des valeurs discrètes (ex: régions).
• HASH : Pour une distribution uniforme lorsque les critères ci-dessus ne s'appliquent pas.

Les partitions doivent être stockées dans des espaces de table distincts. Préférez les index locaux pour faciliter la maintenance des partitions.

Normes de conception des index

Les index sont essentiels pour la performance, mais ils ajoutent une surcharge en écriture.

Nommage : TYPE_TABLE_SUFFIX en majuscules (ex: IDX_ pour un index normal, UK_ pour un index unique).

Principes de conception :

• Créez des index sur les colonnes fréquemment utilisées dans les clauses WHERE, JOIN et ORDER BY.
• Évitez les index redondants. Un index composite sur (A, B, C) rend un index sur (A) inutile.
• Pour les index composites, placez la colonne la plus sélective en premier.
• Évitez les index sur les colonnes à faible sélectivité (ex: genre).
• Pour les tables volumineuses, créez d'abord un index unique, puis ajoutez la contrainte de clé primaire.
• Utilisez ALTER INDEX REBUILD pour reconstruire les index, jamais DROP puis CREATE.

Normes d'accès SQL

Écriture de requêtes efficaces

Évitez SELECT * : Listez explicitement les colonnes nécessaires.

Utilisez des variables liées : Pour améliorer les performances et réduire le parsing dur.

Préférez EXISTS à IN pour les sous-requêtes, surtout avec de grands ensembles de résultats.

Utilisez UNION ALL au lieu de UNION lorsque les doublons ne sont pas un problème.

Évitez les fonctions sur les colonnes indexées dans les clauses WHERE. Par exemple, utilisez WHERE date_col = TO_DATE('2024-01-01', 'YYYY-MM-DD') au lieu de WHERE TO_CHAR(date_col, 'YYYY-MM-DD') = '2024-01-01'.

Exemple d'optimisation de requête

Avant (inefficace) :

SELECT * FROM commandes 
WHERE TO_CHAR(date_commande, 'YYYY') = '2023'
AND client_id IN (SELECT id FROM clients WHERE region = 'Paris');

Après (efficace) :

SELECT commande_id, montant, statut 
FROM commandes c
WHERE c.date_commande >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
  AND c.date_commande < TO_DATE('2024-01-01', 'YYYY-MM-DD')
  AND EXISTS (SELECT 1 FROM clients cl WHERE cl.id = c.client_id AND cl.region = 'Paris');

Optimisation PL/SQL

L'optimisation PL/SQL vise à réduire l'utilisation des ressources (CPU, mémoire, E/S).

Principes clés :

• Réduisez les requêtes en boucle : Collectez les données nécessaires en une seule requête avant la boucle de traitement.
• Utilisez le traitement par lots : Pour les opérations DML sur de grands volumes, utilisez FORALL ou INSERT /*+ APPEND */ ... SELECT.
• Validez les transactions périodiquement : Pour libérer les ressources et réduire la contention des verrous, validez (COMMIT) toutes les N lignes traitées.
• Analysez les plans d'exécution : Utilisez EXPLAIN PLAN pour vérifier que les index sont utilisés efficacement.

Exemple de traitement par lots

Avant (inefficace) :

FOR rec IN (SELECT id, montant FROM ventes_a_traiter) LOOP
  UPDATE ventes SET traite = 'O' WHERE id = rec.id;
  -- Autres opérations
  COMMIT;
END LOOP;

Après (efficace) :

TYPE t_ids IS TABLE OF ventes.id%TYPE;
l_ids t_ids;

CURSOR c_ventes IS SELECT id FROM ventes_a_traiter;
BEGIN
  OPEN c_ventes;
  LOOP
    FETCH c_ventes BULK COLLECT INTO l_ids LIMIT 1000;
    EXIT WHEN l_ids.COUNT = 0;
    
    FORALL i IN 1..l_ids.COUNT
      UPDATE ventes SET traite = 'O' WHERE id = l_ids(i);
    
    COMMIT;
  END LOOP;
  CLOSE c_ventes;
END;

Normes d'encodage et de commentaires

Formatage : Utilisez une indentation cohérente (2 espaces). Placez chaque clause (SELECT, FROM, WHERE) sur une nouvelle ligne.

Commentaires : Chaque bloc de code (procédure, fonction, déclencheur) doit avoir un en-tête commenté décrivant sa fonction, ses paramètres, son auteur et l'historique des modifications.

Lisibilité : Utilisez des alias de table significatifs et évitez les abréviations obscures.

Exemple de formatage :

-- Récupérer les commandes récentes pour un client spécifique
SELECT o.id_commande,
       o.date_commande,
       o.montant_total,
       c.nom_client
  FROM commandes o
  JOIN clients c ON o.client_id = c.id
 WHERE c.email = 'client@example.com'
   AND o.date_commande >= SYSDATE - 30
 ORDER BY o.date_commande DESC;