Aller au contenu
  • Société
    • Qui sommes-nous
    • Nos valeurs
    • Nos partenaires
    • Entreprise citoyenne
    • Régions
  • Services
    • Expertise
    • Formation
    • Développement
    • Migration
    • Infogérance
  • Join the team
  • Actualités
  • Blog
  • Formations
  • Rugb’Easyteam
  • Contact
Menu
  • Société
    • Qui sommes-nous
    • Nos valeurs
    • Nos partenaires
    • Entreprise citoyenne
    • Régions
  • Services
    • Expertise
    • Formation
    • Développement
    • Migration
    • Infogérance
  • Join the team
  • Actualités
  • Blog
  • Formations
  • Rugb’Easyteam
  • Contact
Inscrivez-vous à la newsletter

Inscrivez-vous à la newsletter

Abonnez-vous maintenant et nous vous tiendrons au courant.
Nous respectons votre vie privée. Vous pouvez vous désabonner à tout moment.

Bienvenue sur le Blog d'EASYTEAM (ex ArKZoYd)

  • Accueil
  • Actualités
  • Cloud
  • Infrastructure
  • Data
  • Intégration
  • Dev / DevOps
  • SAM / FinOps
Menu
  • Accueil
  • Actualités
  • Cloud
  • Infrastructure
  • Data
  • Intégration
  • Dev / DevOps
  • SAM / FinOps
  • le 04/02/2019
  • Laurent Gallet
  • Données, Oracle

Validation de contraintes NOT NULL en parallèle

Partager sur linkedin
LinkedIn 0
Partager sur twitter
Twitter
Partager sur facebook
Facebook 0
Partager sur google
Google+ 0

Sur de tables à forte volumétrie, valider ou revalider des contraintes de clés étrangères peut être une opération extrêmement longue  si elle n’est pas exécutée en parallèle.

On peut également mettre ces contraintes en mode RELY si le mode d’alimentation le permet afin d’éviter d’avoir à revalider les contraintes après des opérations de chargement en parallèle par exemple.

Comme l’explique d’ailleurs ici Christian Antognini (foreign-keys-and-library-cache-locks), la bonne pratique pour diminuer le temps de verrouillage d’une table pendant ces opérations est de procéder en 2 étapes :

  1. ALTER TABLE … ENABLE NOVALIDATE CONSTRAINT …
  2. ALTER TABLE … ENABLE VALIDATE CONSTRAINT …

La 1ère étape est immédiate, la seconde peut être parallélisée !

Ceci nous amène au sujet connexe que voulais présenter ici : la modification en parallèle de contraintes NOT NULL

Sur une table de plus de 6 milliards de lignes, j’ai voulu ajouter une contrainte NOT NULL sur les 2 colonnes ID1 et ID2 simultanément.

L’ordre à exécuter est très simple : ALTER TABLE ma_table MODIFY (id1 NOT NULL, id2  NOT NULL);

Malheureusement, l’ordre s’est exécuté en 90 mn sans le moindre parallélisme, ce qui est beaucoup trop long dans un contexte de production ou de MEP.

J’ai donc cherché à paralléliser cette commande, mais ma base étant en version 11gR2, ce ne fut pas si évident que je l’avais imaginé.

 

En version 12c

Les choses sont très simples, il suffit de positionner au préalable du parallélisme sur la table : ALTER TABLE ma_table PARALLEL 8

A noter à ce sujet, que forcer le parallélisme DML et DDL au niveau de la session n’est pas suffisant pour que ça fonctionne, la note MOS 2264469.1 : Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null)  l’explique :

il faut uniquement utiliser ALTER TABLE … PARALLEL X

et ne pas utiliser :

ALTER SESSION FORCE PARALLEL DML PARALLEL 8
ou
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
ni
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8

 

Et en version 11gR2 alors ?

Tout cela est très bien, mais quand la base est en version 11gR2 comme la mienne, que faut-il faire ?

Après de multiples tentatives et en repartant de la bonne pratique présentée au début, j’ai fini part déterminer les ordres qui fonctionnent et conduisent à la parallélisation de l’ordre de check de la contrainte. Il faut donc procéder en 2 étapes :

1/ Ajout des contraintes check NOT NULL nommées en NOVALIDATE sur les 2 colonnes

Le mot « nommées » ci-dessus est important car c’est en nommant les contraintes C1 et C2 que les colonnes ID1 et ID2 passent à NULLABLE dans le dictionnaire Oracle. Ce n’est pas le cas avec un simple ADD CONSTRAINT.

Trouver la syntaxe ci-dessous m’a pris un peu de temps…

1
2
ALTER TABLE ma_table MODIFY ID1 constraint  C1 NOT NULL NOVALIDATE
ALTER TABLE ma_table MODIFY ID2 constraint  C2 NOT NULL NOVALIDATE

L’opération est immédiate.

2/ Validation des contraintes en parallèle avec positionnement du parallélisme DDL et au niveau de la table

1
2
ALTER TABLE ma_table  PARALLEL 8;
ALTER SESSION FORCE PARALLEL DDL PARALLEL  8;

Puis :

1
2
ALTER TABLE ma_table MODIFY CONSTRAINT C1 VALIDATE;
ALTER TABLE ma_table MODIFY CONSTRAINT C2 VALIDATE;

On n’oublie pas d’enlever le parallélisme sur la table…

1
ALTER TABLE ma_table NOPARALLEL;

3/ Résultat

La durée d’exécution entièrement concentrée sur l’étape 2 passe à 9 mn  au lieu de 90 mn  avec un parallélisme de 8  (pour 6Mds de lignes) !

L’ordre SQL sous-jacent exécuté en parallèle pour valider la contraint NOT NULL est :

1
select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "ma_table" A where not ( "ID1" IS NOT NULL)

Vérification du résultat final :

1
2
3
4
5
SQL> desc ma_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NOT NULL VARCHAR2(20)
ID2  NOT NULL DATE

Le gain en temps de traitement valait bien la peine de se pencher sur ce cas !

 

Laurent Gallet
Laurent Gallet
Voir tous ses articles
Partager sur linkedin
LinkedIn 0
Partager sur twitter
Twitter
Partager sur facebook
Facebook 0
Partager sur google
Google+ 0

Laisser un commentaire Annuler la réponse

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Les derniers articles

  • Forcer le DBID et le DBName d’une base Oracle 18/02/2019
  • Oracle 12c Administration – Déplacement d’un datafile online 15/02/2019
  • Les avantages d’une communication interne efficace 13/02/2019
  • GraphQL : encapsulation dans HTTP 11/02/2019
  • Directives de conception d’une API RESTful 08/02/2019

Les derniers commentaires

  • pga dans webMethods ESB : Framework Publish-Subscribe avec routage dynamique
  • SMAIL JR dans Oracle Cloud Gen 2 , (1) les premiers pas
  • thierry gascard dans Nouvelle Fonctionnalité – Oracle Data Guard Broker 12c
  • Jacek dans Emuler la compression HCC avec la VM ZFS
  • Oracle Cloud Gen 2 , (1) les premiers pas - EASYTEAM dans La plongée maîtrisée vers le IaaS Oracle
Espace Membres
Mot de passe perdu?
EASYTEAM

Tour Nova, 71 Boulevard National,
92250 La Garenne-Colombes
Tél. 0800 40 60 40
contact@easyteam.fr

Facebook Linkedin Twitter
Navigation
  • Accueil
  • Qui sommes-nous
  • Entreprise citoyenne
  • Nos valeurs
  • Régions
  • Partenaires
  • Contact
  • Support
Menu
  • Accueil
  • Qui sommes-nous
  • Entreprise citoyenne
  • Nos valeurs
  • Régions
  • Partenaires
  • Contact
  • Support
Services
  • Développement
  • Migration
  • Infogérance
  • Expertise
  • Formation
Menu
  • Développement
  • Migration
  • Infogérance
  • Expertise
  • Formation
Blog
  • Cloud
  • Infrastructures
  • Data
  • Intégration
  • Dev / DevOps
  • SAM / FinOps
  • Applications
Menu
  • Cloud
  • Infrastructures
  • Data
  • Intégration
  • Dev / DevOps
  • SAM / FinOps
  • Applications
Copyright 2018 - EASYTEAM, Tous droits réservés
Mentions légales
Politique de confidentialité​