Requête avec intervalle et comptage [Résolu]

[GoLDoZ]

Bonjour.

Je souhaite optimiser une requête qui me pose des problèmes de performances.

Le principe: je souhaite sélectionner les lignes d'une table t1, dont une colonne est présente dans un intervalle (entre 2 colonnes) de t2, mais les données ramenées ne sont pas les mêmes selon le nombre d'occurrences trouvées dans t2.
J'ai décomposé cela en 2 requêtes.

Quand il y a 0 ou plusieurs intervalles dans t2:

Code :

1
2
3
4
5
6
7
8
9
SELECT t1.col1, t1.col2, t1.col3
  FROM table1 t1
 WHERE NOT EXISTS
     (SELECT COUNT(*), t2.borneinf, t2.bornesup
        FROM table2 t2
      WHERE t1.col4 >= t2.borneinf
          AND t1.col4 <= t2.bornesup
       GROUP BY t2.borneinf, t2.bornesup
      HAVING COUNT(*) = 1)

Quand il y a effectivement un seul intervalle, ça se complique car j'ai besoin de récupérer certaines données de t2:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t1.col1, t1.col2, t2.col1
  FROM table1 t1,
          table2 t2
 WHERE t1.col4 >= t2.borneinf
     AND t1.col4 <= t2.bornesup
     AND EXISTS
     (SELECT COUNT(*), t2.borneinf, t2.bornesup
        FROM table2 t2
      WHERE t1.col4 >= t2.borneinf
          AND t1.col4 <= t2.bornesup
       GROUP BY t2.borneinf, t2.bornesup
      HAVING COUNT(*) = 1)

La table t1 fait 900 000 lignes, la table t2 en fait 130 000.
Il y a des index sur t1.col4, t2.borneinf et t2.bornesup (qui sont des number), les stats sont calculées. Oracle 10g.
Quand je lance la procédure, je fais un "INSERT ...SELECT..." avec les SELECT ci-dessus, en /*+ APPEND */.

Je suis persuadé qu'il y a un moyen d'écrire ça autrement mais je sèche (et je suis très à la bourre dans les dev...).

Si vous avez une idée, merci d'avance, vous allez me sauver!!!

[Waldar]

Un petit jeu de test et ce sera parfait !

[GoLDoZ]

Bonjour Waldar.

Comme je ne peux pas faire de copier coller (car je bosse sous une VM ), je t'en fais un à la main:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH table1 AS (
SELECT 1 AS col1, 1 AS col2, 1 AS col3, 150 AS col4 FROM dual union ALL
SELECT 1 AS col1, 2 AS col2, 1 AS col3, 250 AS col4 FROM dual union ALL
SELECT 1 AS col1, 3 AS col2, 1 AS col3, 350 AS col4 FROM dual union ALL
SELECT 1 AS col1, 4 AS col2, 1 AS col3, 450 AS col4 FROM dual union ALL
SELECT 1 AS col1, 5 AS col2, 1 AS col3, 550 AS col4 FROM dual union ALL
SELECT 1 AS col1, 6 AS col2, 1 AS col3, 650 AS col4 FROM dual),
table2 AS (
SELECT 1 AS col1, 100 AS borneinf, 199 AS bornesup FROM dual union ALL
SELECT 2 AS col1, 100 AS borneinf, 199 AS bornesup FROM dual union ALL
SELECT 3 AS col1, 200 AS borneinf, 299 AS bornesup FROM dual union ALL
SELECT 4 AS col1, 300 AS borneinf, 499 AS bornesup FROM dual union ALL
SELECT 5 AS col1, 300 AS borneinf, 499 AS bornesup FROM dual union ALL
SELECT 6 AS col1, 500 AS borneinf, 599 AS bornesup FROM dual)

Je suis en train de regarder une autre solution fonctionnelle, éventuellement à faire 2 tables selon le nombre d'intervalles. Je suis en train de me faire confirmer (ou non) si les intervalles peuvent être à cheval ou s'il y a une continuité stricte (ce qui ne devrait pas changer grand chose dans la requête en fin de compte).

Edit: les col1, col2 et col3 de table1 n'ont pas d'importance en soi, col2 suffira pour identifier les lignes.

Edit2: la table table2 est déjà une extraction d'une autre table. L'idée que j'ai - dans le cas où les requêtes seraient trop lourdes - est de séparer cette table en 2 tables, avec d'un côté les lignes avec un intervalle en un seul exemplaire, de l'autre les lignes avec intervalles en doublons. Ca éviterait de faire le count(*) group by, il ne resterait que la comparaison.

[GoLDoZ]

MA solution fonctionnelle a l'air pas mal et rapide. Dans ma table t2, je ne garde que les lignes avec les intervalles en un seul exemplaire car je n'ai pas besoin des données que dans le cas ou il y a un seul intervalle.

Ca se résumera donc à cela:

Quand il y a 0 ou plusieurs intervalles dans t2:

Code :

1
2
3
4
5
6
7
SELECT t1.col1, t1.col2, t1.col3
  FROM table1 t1
 WHERE NOT EXISTS
     (SELECT 1
        FROM table2 t2
      WHERE t1.col4 >= t2.borneinf
          AND t1.col4 <= t2.bornesup)

Quand il y a un seul intervalle:

Code :

1
2
3
4
5
SELECT t1.col1, t1.col2, t2.col1
  FROM table1 t1,
          table2 t2
 WHERE t1.col4 >= t2.borneinf
     AND t1.col4 <= t2.bornesup

MAIS (il y a toujours un mais) j'ai un autre problème, à mon avis plus simple: la construction de la table t2.

Au départ, ma table2 est définie ainsi:

Code :

1
2
3
4
CREATE TABLE table2 AS
SELECT col1 AS borneinf, col2, col3... col12 AS bornesup
  FROM table3
 WHERE  (conditions compliquées avec table3 appelée plusieurs fois)......

Il faut que je retire de cette table les lignes avec des intervalles en double:

Code :

1
2
3
4
5
6
7
8
DELETE FROM table2
 WHERE (borneinf, bornesup) IN
     (SELECT borneinf, bornesup FROM
           (SELECT COUNT(*), borneinf, bornesup
               FROM table2
            GROUP BY borneinf, bornesup
            HAVING COUNT(*) <>1)
     )

Mon problème: je veux créer la table et "faire le DELETE" en un seul coup, donc créer la table en filtrant sur les intervalles uniques.

Une idée?

[Waldar]

Beaucoup d'informations, au final ce n'est plus très clair de ce que vous voulez faire !

Entre la requête multi intervalle, les create tables vous m'avez perdu !

Une petite synthèse ?

[GoLDoZ]

Effectivement, trop d'infos tue l'info, d'autant que ma vision a évolué depuis.

Au final, mon problème se résume à cela:
je fais deux actions (un CREATE et un DELETE) pour avoir les données souhaitées dans une table, j'aimerais directement créer la table en une seule fois sans avoir à faire de DELETE.

Action 1:

Code :

1
2
3
4
CREATE TABLE table2 AS
SELECT col1 AS borneinf, col2, col3... col12 AS bornesup
  FROM table3
WHERE condition

Action 2:

Code :

1
2
3
4
5
6
7
8
DELETE FROM table2
 WHERE (borneinf, bornesup) IN
     (SELECT borneinf, bornesup FROM
           (SELECT COUNT(*), borneinf, bornesup
               FROM table2
            GROUP BY borneinf, bornesup
            HAVING COUNT(*) <>1)
     )

Je souhaite intégrer l'action 2 dans le CREATE, donc filtrer directement les lignes sur l'unicité du couple (borneinf, bornesup) à la création.
Ca m'échappe...

Merci d'avance.

[Waldar]

Donc tous les intervalles présents plusieurs fois ne sont pas à insérer (même pas en un exemplaire) ?

Quelque chose dans ce goût-là je dirai :

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE table2 AS
WITH SR AS
(
SELECT col1, col2, col3... col12,
       count(*) over(partition BY col1, col12) AS nb_borne
  FROM table3
 WHERE condition
)
SELECT col1 AS borneinf, col2, col3... col12 AS bornesup
  FROM SR
 WHERE nb_borne = 1;

[GoLDoZ]

Ah la la, je ne suis pas encore à l'aise avec les PARTITION BY...

Et je ne pensais pas qu'on pouvait utiliser un WITH dans un CREATE.

Effectivement, on ne prend pas les intervalles multiples car on n'a pas besoin de leurs données.
Je teste ça et je te dis quoi.

[GoLDoZ]

Ca fonctionne.
C'est déjà ça de réglé. J'ai un autre souci mais c'est un autre sujet.

Merci Waldar.