Création d'une table à partir d'une requête

Version imprimable

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é:
La méthode la plus connue (Gourmand en utilisation du tablespace temporaire):
Code:

1 2 CREATE TABLE RESULTATS AS SELECT ...
Une autre méthode assez connue (Gourmand en utilisation du tablespace temporaire):
Code:

1 2 3 -- En admettant que la table a été crée au paravant: INSERT INTO RESULTATS SELECT ...
Par curseur interposé (Plus lent que les 2 prmières, mais n'utilise pas le tablespace temporaire):
Code:

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.
@+

24/10/2006, 18h19
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...
24/10/2006, 20h07
pedroleouf

Citation:

Envoyé par 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...

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.

@+
25/10/2006, 10h37
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 ?
25/10/2006, 10h41
orafrance

on peut faire :

Code:

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

Et

Code:

INSERT INTO AS SELECT

On peut optimiser le 3 avec les bulk collect aussi ;)
25/10/2006, 14h53
pedroleouf

Citation:

Envoyé par remi4444

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 ;)

@+
25/10/2006, 14h57
pedroleouf

Citation:

Envoyé par Fred_D

on peut faire :

Code:

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

Et

Code:

INSERT INTO AS SELECT

On peut optimiser le 3 avec les bulk collect aussi ;)

Le problème c'est que

Code:

INSERT INTO ...

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

Code:

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.

@+
25/10/2006, 15h01
orafrance

la doc PL/SQL est en ligne :arrow: http://oracle.developpez.com/guide
25/10/2006, 15h10
remi4444

Citation:

Envoyé par pedroleouf

Le problème c'est que

Code:

INSERT INTO ...

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

Code:

CREATE TABLE ... AS SELECT ...

Il manque quand meme un truc vachement important pour ne pas parler dans le vide, c'est ce qu'il y a dans ton select...

Citation:

Envoyé par remi4444

Il manque quand meme un truc vachement important pour ne pas parler dans le vide, c'est ce qu'il y a dans ton select...

Oui, alors le mieux c'est que je te donne un exemple du genre de requête que j'ai:

Code:

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
 
SELECT ROWNUM AS ROW_NUM, T.*
      FROM (SELECT TRIM(NUMPOL.NUM_POL) AS POL_NUM,
                   DECODE(VEH_POL.ID_CATR,
                          NULL,
                          POL_GAR.PNET_GARP,
                          GAR_FORMULES.PBAS_GPROD) AS PRIME_NETTE,
                   DECODE(VEH_POL.ID_CATR,
                          NULL,
                          POL_GAR.TTC_GARP,
                          GAR_FORMULES.TTC_GPROD) AS PRIME_TTC
              FROM POLICE POLICE
              JOIN NUMPOL NUMPOL ON (NUMPOL.ID_POL = POLICE.ID_POL)
              JOIN (SELECT MAX(LIENPOL.ID_LPOL) AS ID_LPOL,
                          LIENPOL.ID_POL AS ID_POL,
                          LIENPOL.ID_FPOL AS ID_FPOL
                     FROM LIENPOL
                    WHERE LIENPOL.CD_LIEN = 'FLOTTE'
                    GROUP BY LIENPOL.ID_POL, LIENPOL.ID_FPOL) VEH_LAST_LP ON (VEH_LAST_LP.ID_POL =
                                                                             POLICE.ID_POL)
              JOIN LIENPOL VEH_LPOL ON (VEH_LPOL.ID_LPOL = VEH_LAST_LP.ID_LPOL AND
                                       VEH_LPOL.ID_FPOL = VEH_LAST_LP.ID_FPOL AND
                                       VEH_LPOL.ID_POL = VEH_LAST_LP.ID_POL AND
                                       VEH_LPOL.CD_LIEN = 'FLOTTE')
              JOIN POLICE VEH_POL ON (VEH_POL.ID_POL = VEH_LPOL.ID_LPOL)
              LEFT OUTER JOIN GARPOL POL_GAR ON (POL_GAR.ID_POL = VEH_POL.ID_POL AND
                                                POL_GAR.CD_GAR = 'COURTAGE')
              LEFT OUTER JOIN CATEGRIS CATEGRIS ON (CATEGRIS.ID_CATR =
                                                   VEH_POL.ID_CATR)
              LEFT OUTER JOIN FORMULES GAR_FORMULES ON (GAR_FORMULES.CD_FORM =
                                                       CATEGRIS.FORM_CATR AND
                                                       GAR_FORMULES.CD_GAR =
                                                       'COURTAGE' AND
                                                       GAR_FORMULES.ID_POL =
                                                       POLICE.ID_POL)
              JOIN COMPAGNIE POL_CIE ON (POL_CIE.CD_CIE = NUMPOL.CD_CIE)
             WHERE POL_CIE.ID_PER = 6969) T

Cela peut ramener jusqu'à 250000 lignes :-)

Voilà, c'est horrible, mais je n'ai pas le choix, c'est le MPD du client qui est ainsi :(

Sinon toutes les requêtes sont de la sorte

Code:

1
2
3
 
SELECT ROWNUM AS ROW_NUM, T.*
      FROM (/* requête */) T

car par la suite j'utilise la ligne pour faire des calculs, enfin peut importe...

25/10/2006, 15h49
remi4444

De toutes façons ce genre de requête va consomer de l'espace temporaire car il y a des group by, des jointures sur des sous-requête en un calcul de rownum au dessus du tout... ce qui m'étonne c'est que la methode par curseur en consome moins... :koi:

Le fait de faire calculer un rownum dans une requête au dessus ne se justifie que si tu fait un ORDER BY dans ta sous requête. Dans l'exemple que tu as donné, tu peux tout aussi bien ramener le rownum au meme niveau que les 3 autres colonnes, ça économiserais du TEMP.
25/10/2006, 16h08
orafrance

il y a les hints APPEND et PARALLEL aussi... et la requête de SELECT prend combien de temps par rapport à l'INSERT ? T'as fait une trace ?

Quand je pense qu'on cherche une solution globale depuis le début alors que c'est un problème spécifique :roll:
25/10/2006, 16h10
remi4444

Citation:

Envoyé par Fred_D

Quand je pense qu'on cherche une solution globale depuis le début alors que c'est un problème spécifique :roll:

c'est la première fois que je vois ça ;):mouarf:
25/10/2006, 16h22
orafrance

quand je vois :

Citation:

JOIN (SELECT MAX(LIENPOL.ID_LPOL) AS ID_LPOL,
LIENPOL.ID_POL AS ID_POL,
LIENPOL.ID_FPOL AS ID_FPOL
FROM LIENPOL
WHERE LIENPOL.CD_LIEN = 'FLOTTE'
GROUP BY LIENPOL.ID_POL, LIENPOL.ID_FPOL) VEH_LAST_LP ON (VEH_LAST_LP.ID_POL =
POLICE.ID_POL)
JOIN LIENPOL VEH_LPOL ON (VEH_LPOL.ID_LPOL = VEH_LAST_LP.ID_LPOL AND
VEH_LPOL.ID_FPOL = VEH_LAST_LP.ID_FPOL AND
VEH_LPOL.ID_POL = VEH_LAST_LP.ID_POL AND
VEH_LPOL.CD_LIEN = 'FLOTTE')

je pense immédiatement fonction analytique... malheureusement, n'étant pas familier de l'ANSI j'ai du mal à voir comment le mettre en oeuvre :'(

rémi :arrow: c'est sûr :mouarf:

Citation:

Envoyé par remi4444

Dans l'exemple que tu as donné, tu peux tout aussi bien ramener le rownum au meme niveau que les 3 autres colonnes, ça économiserais du TEMP.

Non, car il arrivera souvent que j'ai un order by dans la requête, et à ce moment, j'aurai plus le bon ordre dans ma table de destination:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 /* * - ROWNUM à l'extérieur: * 1 | Alfred * 2 | Jean * 3 | Robert * * ROWNUM à l'intérieur (exemple possible): * 2 | Alfred * 3 | Jean * 1 | Robert */

25/10/2006, 18h07
pedroleouf

Vous verriez l'état de la base de données du client... :aie:
Après on dit que le client est roi, mais là vu les requêtes de fou que je suis obligé de faire pour récupérer des infos si basiques, le client va être roi mais devra patienter durant l'éxécution des requêtes...

Le pb avec la requête d'exemple, c'est qu'en gros et brièvement le schéma de la BDD pour cette requête est le suivant:

Assuré <=> TL <=> Police de la flotte <=> TLB => Police du vehicule <=> TLB <=> Vehicule

TL: Table de liaison
TLB: Table de liaison bizare (MAX..) :P

Enfin... vous comprenez mon malheur quand on m'a demandé de faire un requêteur simple à utiliser pour un utilisateur lambda (enfin je dirai bêta...) qui devra avoir une durée de compilation de requête limitée à 30 secondes car

Citation:

ah non, il ne faut pas toucher au paramètres du serveur ni à ceux de la connection

selon les instructions du client...
25/10/2006, 18h18
nuke_y

Monte un DWH avec rapatriement des données à intervalles réguliers...