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é?