tester l'existence d'une table + merge via dblink

[marieo]

Bonjour à tous,

J'ai une instance de base Oracle de production qui contient toutes mes données de production (schéma PRODUCTION) et une instance de base infocentre qui contient tous mes indicateurs calculés et historisés (schéma INFOCENTRE). Cette dernière a un dblink vers la base de production DBPROD_LNK.

Pour calculer les indicateurs à partir des données de production puis les mettre à jour dans l'infocentre, je passe par une table temporaire TB_TEMP avant de stocker le résutat définitif dans la table TB_RESULT.

Voici le code correspondant:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
-- 1. création d'une table temporaire pour utiliser le merge (db_link interdit)
DROP TABLE infocentre.tb_temp;
CREATE TABLE infocentre.tb_temp AS 
   SELECT * FROM infocentre.tb_result WHERE 1=0;
 
-- 2. ajout des indicateurs dans la table temporaire
INSERT INTO infocentre.tb_temp (annee,mois,indic1,indic2,datecalcul) SELECT annee,mois,sum(valeur1),count(valeur2),sysdate
FROM production.tb_donnees@dbprod_lnk
GROUP BY annee,mois; 
 
-- 3. mise à jour des indicateurs à partir de la table temp
MERGE INTO infocentre.tb_result TbResult
 USING (SELECT annee, mois, indic1, indic2, datecalcul     
   FROM infocentre.tb_temp) TbTemp 
 ON (TbResult.annee = TbTemp.annee AND TbResult.mois = TbTemp.mois)
WHEN MATCHED THEN
 UPDATE SET TbResult.indic1 = TbTemp.indic1, 
   TbResult.indic2 = TbTemp.indic2, 
   TbResult.sysdate = TbTemp.sysdate;
 
-- 4. suppression de la table temporaire (db_link interdit le merge...)
DROP TABLE infocentre.tb_temp;

La 1ère requête me permet de tester l'existence préalable de la table temporaire à créer (précaution si l'exécution précédente du script a planté).
Elle me renvoie une erreur car normalement cette table n'existe pas déjà dans le schéma INFOCENTRE.

Mes questions sont les suivantes :
1. comment faire pour tester l'existence préalable d'une table et soit la créer si elle n'existe pas soit faire un truncate si elle existe avec la même structure soit encore faire un drop si elle existe mais avec une structure différente ?

2. avez-vous une autre solution pour faire un merge entre des instances Oracle différentes ?

Merci beaucoup à tous pour votre aide précieuse,
MarieO

[Waldar]

Quelques étrangetés quand même.
Pourquoi DROP / CREATE table à chaque fois ?
La structure de votre table de production change souvent ?

Dans votre MERGE vous faites une sous-requête, mais cette dernière n'est pas nécessaire.
Vous avez une sortie en UPDATE mais pas en INSERT, comment gérez-vous les nouveaux mois ?

Sinon je viens de tester sur une 11g, le MERGE avec un dblink fonctionne.

[marieo]

Il arrive que la structure de la table change, c'est pourquoi j'ai fait un DROP suivi d'un CREATE...

Par ailleurs, afin de simplier le code donné en exemple, je n'ai pas repris l'intégralité de la requête telle qu'elle est écrite dans le script sql.
Ceci explique la sous-requête qui ne semble pas nécessaire ainsi que l'absence de UPDATE pour les lignes nouvelles qui ont une autre origine que la requête utilisée dans le merge.

Pour information, je suis en 10gR2. C'est peut-être pour cela que le merge ne fonctionne pas...

Si quelqu'un a une solution (ou des solutions) à proposer, je suis preneuse.

MarieO

[mnitu]

Envoyé par marieo

...
Pour information, je suis en 10gR2. C'est peut-être pour cela que le merge ne fonctionne pas...

...

The bug was fixed in 11.0, and backported to 10.2.0.2 and 10.2.0.3.

Mettez aussi le message d'anomalie.

[marieo]

The bug was fixed in 11.0, and backported to 10.2.0.2 and 10.2.0.3.

Merci pour cette information, je vais aller demander à notre DBA...

Par contre, cela ne répond pas à ma question sur comment tester l'existence d'une table...


MarieO

[Waldar]

Il suffit d'interroger la vue SYS.ALL_TABLES.

[marieo]

Je ne sais pas interroger sys.all_tables ...
Comment faudrait-il écrire le bloc pl/sql pour avoir ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
 si infocentre.tb_temp n existe pas alors
   CREATE TABLE infocentre.tb_temp AS 
   SELECT * FROM infocentre.tb_result WHERE 1=0;
 sinon 
   si structure de infocentre.tb_temp = structure de infocentre.tb_result alors
       supprimer les enregistrements de infocente.tb_temp
   sinon
     CREATE TABLE infocentre.tb_temp AS 
       SELECT * FROM infocentre.tb_result WHERE 1=0;
   fin de si
 fin de si

[nuke_y]

Tu fais une procédure stockée:

si infocentre.tb_temp n existe pas alors

-> tu testes si résultat de cette requête est 0 :

Code :

SELECT count(*) FROM SYS.ALL_TABLES WHERE TABLE_NAME = 'TB_TEMP'

Pour

CREATE TABLE infocentre.tb_temp AS
SELECT * FROM infocentre.tb_result WHERE 1=0;

-> Un EXECUTE IMMEDIATE de ça ira très bien.

Pour

si structure de infocentre.tb_temp = structure de infocentre.tb_result

-> s'il y a une différence de structure, une requête du genre, à compléter avec les critères nécessaires devrais renvoyer un nombre > 0 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
count(*)
FROM
( SELECT * FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MATABLE') "source" 
full OUTER JOIN ( SELECT * FROM SYS.ALL_TAB_COLUMNS@DBLINK WHERE TABLE_NAME = 'MATABLE') "cible"
ON ("source".COLUMN_NAME = "cible".COLUMN_NAME
AND "source".DATA_TYPE = "cible".DATA_TYPE
AND ( nvl("source".DATA_LENGTH, -1) = nvl("cible".DATA_LENGTH, -1) )
AND ( nvl("source".DATA_PRECISION, -1) = nvl("cible".DATA_PRECISION, -1) )
AND ( nvl("source".DATA_SCALE, -1) = nvl("cible".DATA_SCALE, -1) )
AND ( "source".NULLABLE = "cible".NULLABLE )
)
WHERE
"source".TABLE_NAME IS NULL OR "cible".TABLE_NAME IS NULL

Pour

supprimer les enregistrements de infocente.tb_temp

-> EXECUTE IMMEDIATE d'un truncate

Pour

CREATE TABLE infocentre.tb_temp AS
SELECT * FROM infocentre.tb_result WHERE 1=0;

-> Un EXECUTE IMMEDIATE de ça ira très bien.

Je considère que tu sais te débrouiller en PL/SQL, si non je prendrais le temps demain de développer plus ma réponse (ou d'autres plus compétents le feront).
A noter que je n'ai pas d'Oracle sous la main à la seconde donc j'ai pu faire des erreurs de syntaxe ou des oublis (je sais c'est une mauvaise pratique que de donner un code dont on est pas sûr à 100%) mais tu as au moins des pistes. Demain au boulot je contrôle mon code et je corrige ça éventuellement.


Quelques remarques:

- Tu prends en compte le fait que ta table temporaire puisse changer de structure mais pas ta table finale. Que se passe-t-il si cela se produit ? Si c'est parce que les champs que tu utilises dans ta table finale ne changent jamais dans ta table source (et donc jamais dans la temporaire), pourquoi ne pas construire une table temporaire avec seulement les champs de ta table finale? Seul cas particulier: si tu veux conserver dans ta table temporaire une trace des données détaillées complètes qui ont servi à créer le résultat agrégé de ta table finale, dans ce cas effectivement...

- Personnellement, dans un DWH, le drop de tables temporaires ne me choque pas particulièrement (certains ETL/ELT font ça, Genio par exemple, ODI aussi il me semble). Par contre ce qui m'étonne ce sont tes raisons: dans le cas d'un ETL/ELT il n'y a pas assez d'intelligence dans le produit pour éviter ce genre de comportement mais tu devrais pouvoir l'éviter toi, cf ma remarque du dessus.

EDIT:
Voila le code de la requête est correct

[Waldar]

Envoyé par nuke_y

Personnellement, dans un DWH, le drop de tables temporaires ne me choque pas particulièrement (certains ETL/ELT font ça, Genio par exemple, ODI aussi il me semble)

Certes, mais dans le cas de Génio il faut cocher l'option "create temporary table at the beginning of a module or process execution".
Si je vois cette option cochée, ça me donne une idée sur le modeste niveau du développeur et de ma petite expérience ça c'est toujours vérifié. Maintenant, on doit bien trouver quelques cas où ça doit avoir une utilité.

[nuke_y]

@Waldar: Oui mais par exemple Oracle DI créé des tables temporaires pour exécuter ses KM, et c'est assez difficilement contournable puisqu'il faudrait modifier les KM soit même.
Il y a des gens qui préfèrent utiliser à fond les possibilités de l'outil quitte à perdre le contrôle sur ce qu'il fait, et sur certains outils pourquoi pas.

Cependant je suis d'accord avec toi: un développeur qui développe tout lui même n'a pas à dropper ses tables temporaires, si le travail est bien fait.

[remi4444]

Il y a peut etre aussi des habitudes prise sur d'autres SGBD.
Sur Sybase par exemple, la création de tables temporaires est monaie courrante, très simple, et meme très souvent la meilleurs solution pour résoudre des pb de perf sur des requêtes complexes, et au final l'optim de requête s'avère plus simple que sur Oracle. D'ailleurs, Oracle a repris un peu le concept avec la clause "WITH".

[Waldar]

Le WITH c'est pour se conformer à la norme SQL:2003, et tous les WITH ne sont pas forcément matérialisés dans une table temporaire.

Je dirai plutôt que Sybase utilise des tables temporaires car l'optimiseur SQL est très faible comparé au CBO d'Oracle Database

[remi4444]

Envoyé par Waldar

Le WITH c'est pour se conformer à la norme SQL:2003, et tous les WITH ne sont pas forcément matérialisés dans une table temporaire.

Je dirai plutôt que Sybase utilise des tables temporaires car l'optimiseur SQL est très faible comparé au CBO d'Oracle Database

c'est un autre débat, l'implémention technique des tables temporaire sybase et des with oracle. Je voulais juste dire que quand on utilise un "With" à des fin d'optim, on fait exactement la même démarche que dans Sybase (et MS-SQLserver surement, mais je connais moins)

ça c'est sur que le l'optimiseur sybase est moins "puissant" que celui d'oracle. Mais d'expérience, je peux t'assurer que c'est plus facile d'optimiser Sybase parceque précisément, l'optimiseur étant bien moins complexe, il te réserves beaucoup moins de mauvaises surprises!

Etant donné que dans la complexité, il s'en sort moins bien, on ne tergiverse plus, on décortique la requête en plusieurs parties à l'aide justement des tables temporaires crées et effacées à la volée dans une transaction. Ca oblige le développeur à garder la maitrise et la compréhension de ses accès aux données.

L'optimiseur oracle quant à lui fait peur dans la mesure ou ça peut donner l'impression du: "faite n'importe quoi, je m'occupe de tout" et quand, malgré tout les performance sont mauvaises, il est bien plus difficile de rattraper le truc, et ça demande bien plus de connaissance et d'expérience que pour Sybase.

[Waldar]

Oui je comprends votre raisonnement : il est plus simple de décomposer un problème complexe en n problèmes simples.

[mnitu]

Envoyé par remi4444

...
Mais d'expérience, je peux t'assurer que c'est plus facile d'optimiser Sybase parceque précisément, l'optimiseur étant bien moins complexe, il te réserves beaucoup moins de mauvaises surprises!
....

En fait ce que vous dit est qu'il est plus simple d'optimiser ce qu'on connait mieux.
L’ancien optimiseur d’Oracle basé sur des règles c’était pareil : plus simple à comprendre bien moins de choses à vérifier mais au bout de ligne l’optimisation n’était pas forcement plus simple. D’ou la décomposition des requêtes en N requêtes « simple », l’utilisation des tables de travail, la dénormalisation, etc.