Requête avec jointure en comparaison [Résolu]

[GoLDoZ]

(re)Bonjour.

J'ai un souci de performances sur cette requête assez simple:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH table1 AS (
SELECT 1 AS col1, 150 AS col2 FROM dual union ALL
SELECT 2 AS col1, 250 AS col2 FROM dual union ALL
SELECT 3 AS col1, 350 AS col2 FROM dual union ALL
SELECT 4 AS col1, 450 AS col2 FROM dual union ALL
SELECT 5 AS col1, 550 AS col2 FROM dual union ALL
SELECT 6 AS col1, 650 AS col2 FROM dual),
table2 AS (
SELECT 1 AS col1, 100 AS borneinf, 199 AS bornesup FROM dual union ALL
SELECT 2 AS col1, 200 AS borneinf, 299 AS bornesup FROM dual union ALL
SELECT 3 AS col1, 300 AS borneinf, 399 AS bornesup FROM dual union ALL
SELECT 4 AS col1, 400 AS borneinf, 499 AS bornesup FROM dual)
SELECT t1.*, t2.col4
  FROM table1 t1, table2 t2
WHERE t1.col2 >= t2.borneinf
   AND  t1.col2 <= t2.bornesup;

J'ai des index sur les éléments de comparaison (t1.col2, t2.borneinf et t2.bornesup) qui sont des NUMBER.

t1 fait 64 000 lignes, et t2 fait 134 000 lignes. L'insertion de ce select dans une table vide prend 2h...... alors que t1 contiendra 900 000 lignes.

L'explain plan est en pièce joint. C'est le MERGE qui coince...

Une précision: dans la table t2, chaque intervalle [borneinf; bornesup] n'est qu'en un seul exemplaire, et les intervalles ne se chevauchent pas. Il faudrait que pour chaque ligne, la comparaison s'arrête dès qu'on en a trouvé un (il peut n'y avoir aucun intervalle).

Merci pour votre aide!

[spdev666]

Bonjour,

Est ce que l'exécution du select seul (sans insert) prend autant de temps ? (je suppose que non)
Si non, quel est la structure de la table à remplir ? comporte t elle des indexes, des clés étrangères ?
Si oui, essayez de désactiver les index (et si besoin les clé étrangères) le temps de l'insertion, ca devrait limiter le travail de la base durant l'insert.

[Rams7s]

Si c'est pour mettre dans une table vide, c'est plus rapide de faire un create table qu'un gros insert.

[GoLDoZ]

Le SELECT ne prend pas autant de temps (SqlDeveloper me ramène les 50 premières lignes en 11s) mais c'est déjà trop.
La table cible n'a ni index, ni clé primaire (je l'ai virée avant l'insertion), juste quelques contraintes not null. Les stats sont calculées sur les 3 tables.

Il y a un vrai problème sur le SELECT, car le MERGE me fait visiter 21 millions de lignes...

[GoLDoZ]

Envoyé par Rams7s

Si c'est pour mettre dans une table vide, c'est plus rapide de faire un create table qu'un gros insert.

Oui mais non!
Cette table sera vidée et remplie tous les jours, et j'ai deux insert successifs à faire.

[GoLDoZ]

Mon DBA sèche aussi. Personne n'a une idée?

Il doit forcément y avoir un truc pour éviter qu'une simple comparaison ne provoque pas un produit cartésien...

[GoLDoZ]

RESOLU!

Le DBA (c'est mon pote!!!!) a trouvé la soluce en cherchant sur ORAFAQ: Tuning "BETWEEN" Queries.
Au chapitre "Range Table Single-row Lookup" de la page.

Le truc: on remplace

Code :

1
2
3
4
SELECT *
FROM   orders o
,      delivery_method m
WHERE  o.order_amt BETWEEN m.lower_bound AND m.upper_bound

par:

Code :

1
2
3
4
5
6
7
8
9
SELECT /*+ordered use_nl(m)*/ *
FROM   orders o
,      delivery_method m
WHERE  o.order_amt BETWEEN m.lower_bound and m.upper_bound
AND    m.upper_bound =  (
       SELECT min(m1.upper_bound)
       FROM   delivery_method m1
       WHERE  m1.upper_bound >= o.order_amt
)

La clause ajoutée sur la bornesup restreint le champ d'action et le forçage (obligatoire!) de l'index fait le reste.

Je suis passé de 2 heures à ...... 2 secondes!!!

A garder précieusement pour l'utilisation des BETWEEN!

[skuatamad]

Comment est (sont) créé l'index sur table2 ?
Un index composite sur (borneinf,bornesup) ou 2 index séparés ? Si l'index est composite, est il unique ? ça pourrait donner des infos à l'optimiseur.
Les stats sont elles à jour et y a t il des histogrammes sur les colonnes borneinf et bornesup ?
Analyser l'index est aussi une bonne chose.
Quelle est la version d'oracle ?

[spdev666]

En fait, ce n est pas vraiment un produit cartesien : 64*134 ~ 8500 >> 21, mais c'est quand meme une combinatoire de possibilités

Une solution de contournement serait de traiter les lignes de table1 par paquets de n lignes triées. Ca permettrait de limiter les combinaisons

[mnitu]

Ajoutez plutôt un petit jeu d’essai avec les "create table" et index etc. plus la requête et n’oubliez pas de préciser la version d’Oracle.

[mnitu]

Envoyé par GoLDoZ

RESOLU!

Le DBA (c'est mon pote!!!!) a trouvé la soluce en cherchant sur ORAFAQ: Tuning "BETWEEN" Queries.
...

Avez vous remarqué à la fin de l'article que le Sort-Merge Join se comporte mieux ?