Discussion :

[pedroleouf]

Bonjour.

Je cherche la meilleure méthode pour créer une table à partir d'une requête. La requête en question ramène de très volumineux résultats. La table où seront stoqués les résultats aura son propre tablespace.

Voici les méthodes que j'ai trouvé:

1. La méthode la plus connue (Gourmand en utilisation du tablespace temporaire):
   

   Code :

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

   1
   2
    
   CREATE TABLE RESULTATS AS SELECT ...

2. Une autre méthode assez connue (Gourmand en utilisation du tablespace temporaire):
   

   Code :

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

   1
   2
   3
    
   -- En admettant que la table a été crée au paravant:
   INSERT INTO RESULTATS SELECT ...

3. Par curseur interposé (Plus lent que les 2 prmières, mais n'utilise pas le tablespace temporaire):
   

   Code :

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

   1
   2
   3
   4
   5
   6
    
   -- On admettra aussi que la table a déjà été crée
   -- On pourra tout aussi bien faire un WHILE en utilisant FETCH pour parcourir le curseur
   FOR C IN (SELECT...) LOOP
       INSERT INTO RESULTATS VALUES (C.COL1, C.COL2, ...);
   END LOOP;

Bon voilà ce que j'ai trouvé. Evidemment je pourrais déclarer une vue, mais celà ne m'intéresse pas, sauf si il est possible de faire une vue fixe, c'est à dire sans qu'elle ne prenne en compte les modifications futures dans les tables concernées par la requête.

Y aurait-il d'autres méthodes plus rapides? Le problème est qu'il ne faut pas que le tablespace temporaire soit utilisé, ou qu'il soit très peu utilisé, comme dans la 3ème méthode.

Merci d'avance.
@+

[remi4444]

A mon avis (je me trompe peut etre) c'est plus le select que le "create table machin as" qui est gourmant en tablespace temporaire, si tu as un gros tri, un group-by, une union, un distinct etc... dans ton select, là ça va effectivement consomer du temp...

[pedroleouf]

A mon avis (je me trompe peut etre) c'est plus le select que le "create table machin as" qui est gourmant en tablespace temporaire, si tu as un gros tri, un group-by, une union, un distinct etc... dans ton select, là ça va effectivement consomer du temp...

En fait je ne pense pas que ce soit le create qui bouffe du tablespace temporaire, car quand je crée la table en décrivant les colonne grace à l'ouverture d'un curseur (DBMS_SQL.OPEN_CURSOR, DBMS_SQL.PARSE, DBMS_SQL.DESCRIBE_COLUMNS2), celà prend moins d'une seconde et n'utilise pas de tablespace. Ensuite en replissant cette table ligne à ligne grace à la boucle LOOP de la 3ème méthode, celà met du temps, mais n'utilise pas tout ou presque pas de tablespace temporaire.
Ce que je pense, c'est que lors de l'utilisation de la syntaxe de la 1ère et 2ème syntaxe, Oracle doit mettre tout le résultat de la requête dans l'espace temporaire avant de le basculer dans la table. Alors qu'en utilisant le parcours ligne à ligne du curseur, il y a tout au plus une seule ligne qui se retrouve dans l'espace temporaire en même temps.

Bon sinon je suis en train de me renseigner et de faire des test de performances sur l'utilisation des vue matérialisées.
Dès que j'aurai le temps, je ferais des tests avec mesure du temps sur toutes les méthodes que j'aurai trouvé et publierai un petit tableau comparatif si ça peut servir à quelqu'un.

@+

[remi4444]

Une vue matérialisée, ce n'est jamais qu'une table qui se rempli avec une requête, c'est à dire l'équivalent de "SELECT * FROM MACHIN INTO BIDULE". Le gros avantage est que, sous certaines conditions, tu peux faire du rafraichissement différentiel mais il faudra forcément en passer au départ par un remplissage complet.

Sinon, j'ai cru comprendre que d'après oracle c'était le "CREATE TABLE TRUC AS ..." qui était le plus éfficace car il faisait du "DIRECT LOAD" un peu comme SQL*LOADER.

PS: Qu'est-ce qui te fait dire qu'une méthode prends plus d'espace temporaire qu'une autre, tu as des messages d'erreurs de dépassement de tablespace TEMP ?

[orafrance]

on peut faire :

Code :

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

CREATE TABLE ... AS SELECT ... WHERE ROWNUM = 0

Et

Code :

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

INSERT INTO AS SELECT

On peut optimiser le 3 avec les bulk collect aussi

[pedroleouf]

on peut faire :

Code :

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

CREATE TABLE ... AS SELECT ... WHERE ROWNUM = 0

Et

Code :

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

INSERT INTO AS SELECT

On peut optimiser le 3 avec les bulk collect aussi

Le problème c'est que

Code :

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

INSERT INTO ...

comme je l'ai dit plus haut, est aussi gourmand en tablespace temporaire que le simple

Code :

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

CREATE TABLE ... AS SELECT ...

Sinon pourrais-tu être plus explicite sur les bulk collect (avec un petit exemple), il me semblait que celà obligeait la création d'autant de tableau mémoire qu'il y a de colonne dans la requête. Merci d'avance.

@+

[pedroleouf]

PS: Qu'est-ce qui te fait dire qu'une méthode prends plus d'espace temporaire qu'une autre, tu as des messages d'erreurs de dépassement de tablespace TEMP ?

Je regarde l'utilisation du tablespace en temps réel tout simplement

@+