[2008R2] Liaison Oracle - Requête SQL [Résolu]

[cgair]

Bonjour à tous,

J'ai un petit soucis sous sql server 2008R2 pour faire une requête sous un un serveur oracle.

Ma requête est faite sous toad, et fonctionne.

Mon but serai de créer une table contenant le résultat de ma requête sous sql server.

Déjà, j'essaye de faire la requête sélection ...

Code :

1
2
3
 
SELECT * FROM 
OPENQUERY(SEVEURoRACLE, "Requête sql oracle") ;

Le soucis, c'est qu'il me sort un problème de longueur de requête ... Celle ci faisant 720 caractères ...

Qui aurai une idée ?

Merci d'avance !

CG

[7gyY9w1ZY6ySRgPeaefZ]

Et avec cette syntaxe ?

Code :

1
2
SELECT * FROM 
OPENQUERY( "MACHINE_ORACLE", 'select * from dual')

[cgair]

Bonjour !

J'y avait penser ... Seul soucis, c'est la taille de la table dans oracle ... plus de 250Gb ...

En plus, cette table oracle possède une architecture en "arbre" je crois. Sachant que dans ma requete oracle, je fais un tri ...

Voici la requête :

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
SELECT 
    DI1.DEM_ID,
    DI1.DI_VALUE AS CLIENT,
    DI2.DI_VALUE AS STATUT,
    DI3.DI_VALUE AS CONTRAT,
    DI4.DI_VALUE AS ID
 
FROM
( 
    (ORACLE.DEMANDES DI1 
        JOIN
    ORACLE.DEMANDES DI2
        ON
            DI1.DEM_ID = DI2.DEM_ID)
        JOIN
    ORACLE.DEMANDES DI3
        ON
            DI1.DEM_ID = DI3.DEM_ID)
        JOIN
            ORACLE.DEMANDES DI4
         ON DI1.DEM_ID = DI4.DEM_ID
WHERE 
 
        DI1.DI_PARAM = '/client' AND 
        DI1.DI_VALUE LIKE 'XXX%' AND
        DI2.DI_PARAM = '/status' AND
        DI3.DI_PARAM = '/id_commande' AND
        DI4.DI_PARAM = '/IDGEN';

Par exemple, pour un même ID, on a plusieurs paramètres avec différentes valeurs. Donc j'ai 4 liaisons sur cette table, avec donc trois jointures. Via toad, j'ai une exécution en un peu plus de 2 minutes ...

[Waldar]

Commencez déjà par améliorer votre requête, utilisez la méthode décrite dans ce sujet :
http://www.developpez.net/forums/d10...l/#post5959473

Si c'est un problème de longueur de requête, vous pouvez toujours créer une vue sur Oracle et faire un select sur celle-ci depuis SQL-Server.

[aieeeuuuuu]

Votre requete vous renvoie une seule ligne par DEM_ID ?

que donne celle-ci (qui est "Waldar certified" EDIT : lequel a posté entretemps...) niveau perf ?

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT 
    DEM_ID,
    MAX(
        CASE 
            WHEN  
                DI_PARAM = '/client' 
                AND DI_VALUE LIKE 'XXX%' 
            THEN DI_VALUE 
        END 
    ) AS CLIENT,
    MAX(CASE WHEN DI_PARAM = '/status' THEN DI_VALUE END) AS STATUT,
    MAX(CASE WHEN DI_PARAM =  '/id_commande' THEN DI_VALUE END) AS CONTRAT,
    MAX(CASE WHEN DI_PARAM =  '/IDGEN' THEN DI_VALUE END) AS ID
FROM
    ORACLE.DEMANDES 
WHERE 
     DI_PARAM IN ('/client', '/status','/id_commande', '/IDGEN')
GROUP BY DEM_ID

[Waldar]

Bien vu aieeeuuuuu

[cgair]

Merci pour la requête oracle, c'est de l'optimisation.

Effectivement la requête est trop longue ... Le hic c'est que je ne suis pas admin de la base oracle, et demander une vue est chose longue, j'ai déjà essayer et abandonné vu les procédures qu'on me demande... C'est pour ça que je voulais utiliser la méthode serveur lié.

[cgair]

J'ai modifier la requête comme dit plus haut, ça fait 10 mn qu'elle tourne

Je vais me faire tapper sur les doigts pour l'occupation serveur

[iberserk]

J'ai modifier la requête comme dit plus haut, ça fait 10 mn qu'elle tourne

Je vais me faire tapper sur les doigts pour l'occupation serveur

Je suis quand même surpris de ne voir aucun 'vrai' prédicat dans votre WHERE... vous devez remonter tous les ID de la table de 2.5GO?

[cgair]

En fait, la condition DI1.DI_VALUE LIKE 'XXX%' permet de filtrer 95% des lignes ...

[iberserk]

En fait, la condition DI1.DI_VALUE LIKE 'XXX%' permet de filtrer 95% des lignes ...

Oui mais SQL SERVER le sait il?
J'entends par là regardez le plan d'execution estimé et regardez si le nbr de ligne qu'il s'attend à retourner est correct...

[cgair]

Le soucis est justement la dessus, je n'arrive pas à l'exécuter via le serveur lié ...

La seule méthode que j'ai trouver c'est comme suit sous sql-server ...

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
SELECT 
    DI1.DEM_ID,
    DI1.DI_VALUE AS CLIENT,
    DI2.DI_VALUE AS STATUT,
    DI3.DI_VALUE AS CONTRAT,
    DI4.DI_VALUE AS ID
 
FROM
( 
    (OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI1 
        JOIN
    OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI2
        ON
            DI1.DEM_ID = DI2.DEM_ID)
        JOIN
    OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI3
        ON
            DI1.DEM_ID = DI3.DEM_ID)
        JOIN
            OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI4
         ON DI1.DEM_ID = DI4.DEM_ID
WHERE 
 
        DI1.DI_PARAM = '/contrat/commande_client' AND
        DI1.DI_VALUE LIKE 'XXX%' AND
        DI2.DI_PARAM = '/line/status' AND
        DI2.DI_VALUE = 'A' AND
        DI3.DI_PARAM = '/id' AND
        DI3.DI_VALUE LIKE 'L%'
        DI4.DI_PARAM = '/contrat/id_service_administratif';

Mais pour le plan d'exécution, comme je fais aucune condition sur les OPENQUERY, il veut me sortir les quelques 200 000 000 de ligne que la table contient ...

[Waldar]

Envoyé par cgair

En fait, la condition DI1.DI_VALUE LIKE 'XXX%' permet de filtrer 95% des lignes ...

Pouvez-vous publier un petit jeu de données ?
Ce filtre est effectivement essentiel, mais il faut s'assurer qu'avec la réécriture on peut le mettre dans le WHERE avec les autres filtres.

Plus fonctionnellement, ce filtre vous ne l'avez mis en place que pour le paramètre "/client". Est-il également valide pour les autres paramètres ?

[cgair]

Actuellement, pour un dem_id on a :

DEM_ID DI_PARAM DI_VALUE
014242 /line/statut A
014242 /id L0754126
014242 /contrat/commande_client XXX01564891

Et ainsi de suite.

L'objectif étant d'avoir les infos sur une ligne pour un même dem_id.

Sinon, je voudrai savoir avec une variable, comment la mettre dans openquery ?

C'est à dire, imaginons la requête suivante :

Code :

1
2
3
4
5
6
7
8
 
SELECT 
     ID, 
     COMMANDE
FROM
     DEMANDES
WHERE
     COMMANDE LIKE 'XXX%';

Le soucis c'est que la requête que l'on place en argument dans openquery doit être sous la forme string.

J'ai testé :

Code :

1
2
3
4
5
 
DECLARE @rq
@rq = 'SELECT ID, COMMANDE FROM DEMANDES WHERE COMMANDE LIKE ' &char(39) &'XXX%' &char(39) &';' ;
 
SELECT * FROM OPENQUERY(ORACLE, @rq);

Mais il ne reconnais pas @rq ... Ne peut on pas faire un cast ?

merci d'avance ...

[Waldar]

Ok, par rapport à vos données il vaut compléter la requête que vous a proposé aieeeuuuuu :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
  SELECT D1.DEM_ID
       , MAX(CASE D1.DI_PARAM WHEN '/client'      THEN D1.DI_VALUE END) AS CLIENT
       , MAX(CASE D1.DI_PARAM WHEN '/status'      THEN D1.DI_VALUE END) AS STATUT
       , MAX(CASE D1.DI_PARAM WHEN '/id_commande' THEN D1.DI_VALUE END) AS CONTRAT
       , MAX(CASE D1.DI_PARAM WHEN '/IDGEN'       THEN D1.DI_VALUE END) AS ID
    FROM ORACLE.DEMANDES D1
         INNER JOIN ORACLE.DEMANDES D2
           ON D2.DEM_ID = D1.DEM_ID
   WHERE D1.DI_PARAM IN ('/client', '/status','/id_commande', '/IDGEN')
     AND D2.DI_PARAM = '/client'
     AND D2.DI_VALUE LIKE 'XXX%'
GROUP BY D1.DEM_ID;

J'ai hésité entre la jointure et le exists, ça peut valoir le coup d'évaluer les deux, je la poste aussi, mais mon petit doigt m'oriente quand même sur la jointure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
  SELECT D1.DEM_ID
       , MAX(CASE D1.DI_PARAM WHEN '/client'      THEN D1.DI_VALUE END) AS CLIENT
       , MAX(CASE D1.DI_PARAM WHEN '/status'      THEN D1.DI_VALUE END) AS STATUT
       , MAX(CASE D1.DI_PARAM WHEN '/id_commande' THEN D1.DI_VALUE END) AS CONTRAT
       , MAX(CASE D1.DI_PARAM WHEN '/IDGEN'       THEN D1.DI_VALUE END) AS ID
    FROM ORACLE.DEMANDES D1
   WHERE D1.DI_PARAM IN ('/client', '/status','/id_commande', '/IDGEN')
     AND EXISTS (SELECT NULL
                   FROM ORACLE.DEMANDES D2
                  WHERE D2.DI_PARAM = '/client'
                    AND D2.DI_VALUE LIKE 'XXX%'
                    AND D2.DEM_ID = D1.DEM_ID)
GROUP BY D1.DEM_ID;

Par contre vos critères ont bougé entre le début et la fin, je vous laisse adapter si besoin.
Une de ces requêtes doit aller plus vite que votre requête initiale.
Testez-la directement sous TOAD.

[cgair]

Pour la première méthode 1 j'ai 7mn30 de traitement sous toad. Celle ci s'exécutant la nuit le weekend, c'est largement bon.

Maintenant reste à déterminer comment l'implémenter dans sql server...

Thx !

[Waldar]

Oui enfin si votre requête précédente faisait deux minutes, ça ne va pas dans le bon sens.
Il faudrait tester les deux requêtes bout à bout pour avoir des conditions d'exécution relativement similaires.

[iberserk]

Oui enfin si votre requête précédente faisait deux minutes, ça ne va pas dans le bon sens.

Affichez les plans d'execution...

Pour la première méthode 1 j'ai 7mn30 de traitement sous toad. Celle ci s'exécutant la nuit le weekend, c'est largement bon.

Maintenant reste à déterminer comment l'implémenter dans sql server...

Thx !

Après je suppose que vous stockez le résultat côté SQL SERVER est-ce vraiment utile de remettre 'en colonne' ces données côté ORACLE?

A voir si vous ne pourriez pas importer les données en lignes puis faire le traitement côté SQL SERVER sur des/une table(s) nettement plus petite(s).

[aieeeuuuuu]

@Waldar, soit je ne comprends pas ta requête, soit je la comprend, et il y a une erreur car il me semble que le but de ta jointure était de traiter "à part" le cas "client" qui doit subir un filtre supplémentaire non ?!?

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
  SELECT D1.DEM_ID
       , MAX(D2.DI_VALUE) AS CLIENT
       , MAX(CASE D1.DI_PARAM WHEN '/status'      THEN D1.DI_VALUE END) AS STATUT
       , MAX(CASE D1.DI_PARAM WHEN '/id_commande' THEN D1.DI_VALUE END) AS CONTRAT
       , MAX(CASE D1.DI_PARAM WHEN '/IDGEN'       THEN D1.DI_VALUE END) AS ID
    FROM ORACLE.DEMANDES D1
         INNER JOIN ORACLE.DEMANDES D2
           ON D2.DEM_ID = D1.DEM_ID
   WHERE D1.DI_PARAM IN ( '/status','/id_commande', '/IDGEN')
     AND D2.DI_PARAM = '/client'
     AND D2.DI_VALUE LIKE 'XXX%'
GROUP BY D1.DEM_ID;

[cgair]

Bonjour,

Alors voici le plan d'exécution que sqlserver me dit :



J'ai tenter une exécution cette nuit, au bout de 16heures d'exécution...

J'ai pas réussi à mettre en place les conditions, je vais tenter de le faire là. Voici la requête qui au bout de 16h, n'a rien donné (tourner toujours...)

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
38
39
40
41
 
CREATE TABLE DSLACT(
	DEM_ID INT, 
	CMDE_CLIENT VARCHAR(20), 
	STAT_LIGNE VARCHAR(20), 
	CODE_CONTRAT VARCHAR(20), 
	MASTERID VARCHAR(20)
	)
SELECT 
    DI1.DEM_ID,
    DI1.DI_VALUE AS CMDE_CLIENT,
    DI2.DI_VALUE AS STAT_LIGNE,
    DI3.DI_VALUE AS CODE_CONTRAT,
    DI4.DI_VALUE AS MASTERID
 
FROM
( 
    OPENQUERY(BASEDEPILOTAGE,'SELECT [NOM SERVICE] as MASTERID FROM SuiviQuot_PROD_etat;') BP 
    JOIN
		(((OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI1 
			JOIN
			OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI2
			ON
            DI1.DEM_ID = DI2.DEM_ID)
			JOIN
				OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI3
				ON
				DI1.DEM_ID = DI3.DEM_ID)
				JOIN
					OPENQUERY(ORACLE, 'SELECT * FROM ORACLE.DEMANDES') DI4
					ON DI1.DEM_ID = DI4.DEM_ID)
	ON BP.MASTERID = DI4.DI_VALUE)
WHERE 
(
        DI1.DI_PARAM = '/contrat/client' AND
        DI1.DI_VALUE LIKE 'XXX%' AND
        DI2.DI_PARAM = '/ligne/stat' AND
        DI2.DI_VALUE = 'A' AND
        DI3.DI_PARAM = '/idGEN' AND
        DI3.DI_VALUE LIKE 'L%' AND
        DI4.DI_PARAM = '/contrat/id_administratif');

J'essaye de modifier la requête et vous tiens au courant !