Discussion :

[ilalaina]

Bonjour à tous.

J'essaie de générer l'ordre d'insertion de données dans les tables appartenant à un utilisateur Oracle donné, en respectant les dépendances (clés étrangères).
J'ai donc créé la procédure suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CREATE OR REPLACE PROCEDURE hierarchie_fk(p_owner dba_tables.owner%TYPE) IS
 
  -- Cette procédure affiche l'hiérarchie des contraintes de clés étrangère 
  -- des tables d'un schéma Oracle donné
  -- Utile pour générer l'ordre d'insertion ou de suppression de données
  -- L'insertion doit se faire par ordre croissant de l'hierarchie obtenu
  -- Pour la suppression, c'est l'inverse
 
  /*create table hierarchie_fk_1
  (
    NOM        VARCHAR2(64),
    HIERARCHIE NUMBER
  )*/
 
  TYPE t_t_name IS TABLE OF dba_tables.table_name%TYPE;
  v_t_name_1 t_t_name;
  TYPE t_hierarchie IS TABLE OF hierarchie_fk_1.hierarchie%TYPE;
  v_hierarchie hierarchie_fk_1.hierarchie%TYPE;
 
  PROCEDURE reference(p_t_name     dba_tables.table_name%TYPE,
                      p_hierarchie NUMBER) IS
    v_t_name_2 t_t_name;
 
  BEGIN
 
    SELECT DISTINCT B1.TABLE_NAME BULK COLLECT
      INTO v_t_name_2
      FROM ALL_CONSTRAINTS B1, ALL_CONS_COLUMNS C1
     WHERE B1.R_Constraint_Name = C1.Constraint_name
       AND B1.R_Owner = C1.Owner
       AND B1.CONSTRAINT_TYPE = 'R'
       AND C1.Table_name = p_t_name
       AND C1.owner = upper(p_owner);
    IF v_t_name_2.COUNT > 0 THEN
      SELECT hierarchie
        INTO v_hierarchie
        FROM hierarchie_fk_1
       WHERE nom = p_t_name;
      -- On ne met pas à jour la valeur de hiérarchie si il est déjà supérieur à p_hierarchie actuel
      IF v_hierarchie < p_hierarchie THEN
        UPDATE hierarchie_fk_1
           SET hierarchie = p_hierarchie
         WHERE nom = p_t_name;
        COMMIT;
      END IF;
      -- Appel récursif de la procédure pour chaque nom de table obtenu
      FOR i IN v_t_name_2.FIRST .. v_t_name_2.LAST LOOP
        reference(v_t_name_2(i), p_hierarchie + 1);
      END LOOP;
    END IF;
 
  END reference;
 
BEGIN
 
  EXECUTE IMMEDIATE 'truncate TABLE hierarchie_fk_1';
  -- Liste de toutes les tables du schéma p_owner
  SELECT DISTINCT table_name BULK COLLECT
    INTO v_t_name_1
    FROM dba_tables
   WHERE owner = upper(p_owner);
  IF v_t_name_1.COUNT <> 0 THEN
    FOR i IN v_t_name_1.FIRST .. v_t_name_1.LAST LOOP
      -- Initialisation de la table hierarchie_fk_1
      INSERT INTO hierarchie_fk_1 VALUES (v_t_name_1(i), 0);
      COMMIT;
    END LOOP;
    FOR i IN v_t_name_1.FIRST .. v_t_name_1.LAST LOOP
      reference(v_t_name_1(i), 1);
    END LOOP;
  END IF;
 
END hierarchie_fk;

Mon problème c'est que le code bouffe trop de CPU (100%).
Quelqu'un aurait-il une idée pour l'améliorer, ou bien existe t-il d'autres moyens ou outils pour réaliser ce que j'essaie de faire?

Pou information :

• Oracle 8, Windows 2000 SP2, CPU 2.4 GHz, RAM 2Go.
• L'utilisateur avec lequel j'ai effectué le test possède environ 200 tables.

Merci d'avance.

[orafrance]

remplace les tableaux par des tables temporaires (TEMPORARY TABLE) ça devrait aller nettement mieux

[ilalaina]

remplace les tableaux par des tables temporaires (TEMPORARY TABLE) ça devrait aller nettement mieux

Voulez-vous dire remplacer v_t_name_1 et v_t_name_2 par des tables temporaires?

[orafrance]

oui ou mieux, utiliser des curseurs

[ilalaina]

J'ai utilisé des curseurs mais ça n'a pas changé grand chose, le CPU est toujours à 100%.
En ce qui concerne les tables temporaires je ne vois pas trop comment adapter mon code, pourriez-vous m'éclairer un peu?
Merci.

[ilalaina]

Je crois qu'un DBA a souvent besoin d'une requête de ce genre, je ne comprends pas pourquoi ce n'est pas incorporé dans les différents outils d'administration. Ou es-ce moi qui n'ai pas bien cherché?

[orafrance]

Il donne quoi le code maintenant ?

[ilalaina]

Il donne quoi le code maintenant ?

Le code a dépassé le nombre limite de curseur ouvert pas session.
(alors que j'ai augmenté la valeur du paramètre open_cursors de 1 000 à 10 000).
En fait mon but c'est de faire le portage d'une base Oracle vers Postgres, avec l'outil Ora2Pg qui ne gère pas l'ordre d'insertion des données, donc je dois lister à la main l'ordre des tables dans le fichier de configuration.
Mais j'ai trouvé une alternative. L'export des données avec l'outil PL/SQL Developer met automatiquement en ordre la désactivation - et l'activation des clés étrangères. Si jamais mon code ne marche pas je pense que je vais exploiter cet outil.
En tout cas merci pour vos réponses.
Seulement je voudrais avoir vos avis : quel est le moyen le plus approprié d'écrire des requêtes qui contiennent beaucoup de boucles, question utilisation de CPU, de mémoire, et rapidité.
Merci.

[orafrance]

j'aimerais bien le voir

Seulement je voudrais avoir vos avis : quel est le moyen le plus approprié d'écrire des requêtes qui contiennent beaucoup de boucles, question utilisation de CPU, de mémoire, et rapidité.
Merci.

une requête SQL bien écrite ou à défaut des curseurs qu'il faut penser à fermer