Discussion :

[carden752]

Bonjour,

J'ai un package qui me retourne une table d'identifiant.
Je souhaite faire une jointure sur ceux-ci avec l'union de deux tables.

Code :

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

1
2
3
 
select  * from tmp, (select id from tiers union select id from acteur) acttiers where acttiers.id in
(select * from table(pack_tmp.ACTTIER((select id from tmp tmp2 where tmp2.id=tmp.id)))).

Mon problème dépend des paramètres passés dans mon package

En effet, si je met directement un nombre, la requête est immédiate.

Code :

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

1
2
select  * from tmp, (select id from tiers union select id from acteur) acttiers where acttiers.id in
(select * from table(pack_tmp.ACTTIER((1300))))

Si j'écris la valeur de tmp.id en lui mettant un filtre sur la valeur, c'est hyper long

Code :

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

1
2
select  * from tmp, (select id from tiers union select id from acteur) acttiers where tmp.id=1300 and acttiers.id in
(select * from table(pack_tmp.ACTTIER((tmp.id))))

Si je passe par un appel à une table intermédiaire, cela reste long

Code :

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

1
2
select  * from tmp, (select id from tiers union select id from acteur) acttiers where  tmp.id=1300 and acttiers.id in
(select * from table(pack_tmp.ACTTIER((select id from tmp tmp2 where tmp2.id=tmp.id))))

Est-ce que quelqu'un peut m'expliquer ce que je fais mal et comment optimiser pour pouvoir utiliser la 2ème ou 3ème écriture car bien évidemment, je souhaite interroger sur plusieurs enregistrements de tmp.

merci d'avance pour vos éclaircissements.

[mnitu]

D'abord j'ai formaté une de vos requêtes cela permet toujours de mieux voir

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select  * 
  from tmp, 
       (select id 
          from tiers 
          union 
          select id 
          from acteur
       ) acttiers 
 where tmp.id=1300 
   and acttiers.id In (select * 
                         from table(pack_tmp.ACTTIER(tmp.id))
                      )

Si vous l'analysez vous pouvez vous apercevoir que vous est en train de faire un produit cartésien: vous avez deux relation tmp et acttier pour lesquels vous avez ajouté des filtre tmp.id et acttiers.id mais vous n'avez pas de condition de jointure.

De plus la table tmp n'annonce rien de bon probablement juste un table de travail pour laquelle certainement l'optimiseur n'a pas de statistiques à jour. A cela s'ajoute la sous-requête utilisant le package pack_tmp pour laquelle vous avez déjà compris l'optimiseur n'a pas de tout des statistiques. Donc vous avez le bon cocktail pour une problème d'optimisation: requête mal écrite et manque des informations pour l'optimiseur.

Dans ce type de requête vous pouvez optimiser via

1. réécriture la requête en évitant les produits cartésien
2. ajouter le hint SQL dynamic_sampling avec un niveau 4 ou 5 pour la table tmp
3. ajouter des informations sur la cardinalité de la sous-requête utilisant le package via le hint SQL cardinality (j'ai vaguement dans la tête des autres méthodes on verra plus tard)

Bref quand je vois ce type de requêtes j'ai des grosses doutes que la table tmp justifie son existence mais bon acceptons la!

A ma compréhension vous voulez faire une jointure entre la zone id de la table tmp et la zone id de la vue acttiers mais en transformant l'id de la table tmp via le package pack_tmp. Est-ce bien ça ?
Pourriez-vous ajouter un jeu d'essai ou des explications ?

[carden752]

Bonjour,

J'avais renommé la table tmp qui n'est pas une table de travail mais une table de la base donc effectivement, la dessus pas de souci, j'ai bien l'utilisation d'un index.

Ensuite, je dois récupérer des données de deux tables par des sous-requêtes sur des éléments différents tiers.id et acteur.id servant de lien, le but étant de pouvoir réaliser une extraction de tous les tiers et acteurs renseignés. Evidemment, je l'avais simplifié car je ne récupère que les enregistrements nécessaires des tables tiers et acteurs (1000 environ).

Non, je veux faire la jointure sur le retour du package ACTTIER qui renvoie une table d'identifiants(d'identifiants tiers ou acteurs spécifiques calculés à partir de l'id de la table tmp) et l'id de la table acttier.

Cela me permettrait de récupérer pour chaque enregistrement de tmp, un lot d'informations acteur et tiers pour ensuite faire une requête de regroupement dessus.


Les plans d'exécution d'Oracle sont les suivants mais les requêtes sont anormalement longues

Pour la première forme (pack_tmp.ACTTIER(tmp.id))

Operation Optimizer Cost Cardinality Bytes
SELECT STATEMENT FIRST_ROWS 167588 61 15250
NESTED LOOPS(SEMI) 167588 61 15250
NESTED LOOPS 6 6119 1517512
TABLE ACCESS(BY INDEX ROWID) TMP ANALYZED 1 1 235
INDEX(UNIQUE SCAN) IDX_TMP ANALYZED 1 1
VIEW 5 6119 79547
SORT(UNIQUE) 5 6119 36714
UNION-ALL
INDEX(FULL SCAN) IDX_TIERS ANALYZED 2 5926 35556
INDEX(FULL SCAN) IDX_ACTEUR ANALYZED 1 193 1158
COLLECTION ITERATOR(PICKLER FETCH) PACK_TMP.ACTTIER 27 82 164





Pour la 2ème forme (select id from tmp tmp2 where tmp2.id=tmp.id)
Operation Optimizer Cost Cardinality Bytes
SELECT STATEMENT FIRST_ROWS 8 6119 1517512
FILTER
NESTED LOOPS 6 6119 1517512
TABLE ACCESS(BY INDEX ROWID) TMP ANALYZED 1 1 235
INDEX(UNIQUE SCAN) IDX_TMP ANALYZED 1 1
VIEW 5 6119 79547
SORT(UNIQUE) 5 6119 36714
UNION-ALL
INDEX(FULL SCAN) IDX_TIERS ANALYZED 2 5926 35556
INDEX(FULL SCAN) IDX_ACTEUR ANALYZED 1 193 1158
COLLECTION ITERATOR(PICKLER FETCH) PACK_TMP. ACTTIER 2 1 2

[Waldar]

Pour le plan d'exécution, comme ceci il est très difficile à lire.
Exécutez votre requête de la façon suivante pour le plan estimé :

Code :

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

1
2
3
4
5
6
set linesize 250;
 
explain plan for
select ...;
 
SELECT * FROM TABLE(dbms_xplan.display);

Ou comme ceci pour le plan réel (nécessite l'exécution de la requête) :

Code :

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

1
2
3
4
5
6
set linesize 250;
 
select /*+ gather_plan_statistics */  *
  from ...;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

Le résultat est du bon texte déjà mis en page.

[mnitu]

Dans ces plans vous n'aurez pas la bonne vision de ce qui se passe réellement à l'exécution de la requête parce que le plan d'exécution des éventuels requêtes issues de la procédure packagée pack_tmp.acttier ne seront pas présentées. Je pense que dans ce cas vous avez besoin d'effectuer une trace SQL étendue pour retrouver l'ensembles des requêtes impliquées.

[mnitu]

Et je pense que votre requête devrait écrite de cette manière

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
 
Select v.*, a.*
  From (
        Select t.*, p.ActTie_Id
          From tmp t,
               (Select Column_Value As ActTie_Id
                  From pack_tmp.ActTier(tmp.id)
               ) 
      ) v,
      (select id 
          from tiers 
          union 
          select id 
          from acteur
      ) a
  Where v.actTie_Id = a.id
    And v.id = 1300
/