Discussion :

[Lagnio]

Bonjour,

Je dois ré-écrire une requete un peu longuette... (704 lignes de pure bonheur)

Dans cette requete j'ai 10 sous-requetes, ma question est la suivante, ne serait-il pas plus facile a maintenir si en place de chaque sous-requete je mettais une petite fonction retournant une table.

Ou serait-il préférable de passer par des vues (lorsqu'elles ne sont pas paramétrées)?

Idem, au niveau du select, j'ai des joli case énorme, donc ne vaut-il pas mieux les remplacer eux aussi??

Merci pour vos conseils!

[al1_24]

Et pourquoi pas utiliser une expression de table commune (Common Table Expression) ou plusieurs si nécessaire ?

[JeanYvette]

Je rajouterais, outre ce qu'à dit al_1 sur les CTE (les CTE, c'est la vie, n'oubliez jamais ça) qu'Oracle fonctionne mieux en curseur qu'en ensembliste (je l'ai revu il y a peu à mes dépends...) et que en fonction de comment ta/tes requêtes sont faites, parfois, les disséquer pour en sortir des curseur ?

A voir comment le code est fait quoi, sans, on ne peut que faire des hypothéses (bon d'un côté, lire 700 lignes, ça vend pas forcement du rêve)

Après, le mieux serait peut être aussi de dissequer/prendre les requêtes une a une, voir le plan d'exécution si y'en a une ou plusieurs qui ne sont pas opti...

Bisous bisous

[vanagreg]

Je rajouterais, outre ce qu'à dit al_1 sur les CTE (les CTE, c'est la vie, n'oubliez jamais ça) qu'Oracle fonctionne mieux en curseur qu'en ensembliste (je l'ai revu il y a peu à mes dépends...)
Bisous bisous

Bonjour,

Tu as un exemple pour comparer parce que c'est le contraire en fait? Un curseur qui fait du ligne à ligne sera plus lent qu'une requête qui traite tout d'un coup.

[vanagreg]

Bonjour,

Ce n'est pas parce qu'une requête est longue qu'elle est problématique. Pourquoi dois-tu la réécrire? Pose-t-elle des problèmes de perf?
Pour commencer il faudrait que tu montres la requête en question.

[Lagnio]

Merci pour vos réponses.

Envoyé par JeanYvette

Après, le mieux serait peut être aussi de dissequer/prendre les requêtes une a une, voir le plan d'exécution si y'en a une ou plusieurs qui ne sont pas opti...

La je peux confirmer que c'est bien le cas, les sous-requetes en double, les 2 similaires mais sur des tables différentes, bon j'en passes...

en exemple de sous-requete j'ai :

Code :

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

1
2
3
4
5
6
select ac.num_cpt "A", round(sum(balance)/max(valuation),2) "B"
 from  compte c,  propriete p
where c.num_prop=p.num_prop
and   c.type = 'M'
and   c.period='31-dec-19'
group by c.num_prop

et je l'ai en double puisque j'ai besoin de la meme info pour le mois de novembre

de plus, toute les tables utilisés dans la sous-requetes sont présentes dans la requete principale.

Les sous-reauetes sont jointes par le numéro de compte a la requete principale.

mon but est juste que quand je partirai, mon remplacant ne s'arrache pas les cheveux a essayer de comprendre ce que j'ai bien pu fabriquer!

Voila, voilou!

[JeanYvette]

Bonjour,

Tu as un exemple pour comparer parce que c'est le contraire en fait? Un curseur qui fait du ligne à ligne sera plus lent qu'une requête qui traite tout d'un coup.

Un exemple purement personnel. J'avais une requête assez importante, qui utilisez des CTE et des index. Elle était lente, mais lente... (Bon après je soupçonne aussi la base car la même requête mettait, en 30 minutes d'écart, 10 secondes une fois contre 380 une autres...)
J'ai fait la même requête, en terme d'algo, en curseur, ca tourne super bien. Ca me sidère parce que je suis du même avis que toi, mais j'ai eu plusieurs cas qui se sont passés comme ça...

Après, et c'est un fait, Oracle gère mieux les curseurs que d'autres SGBD...




Sinon, pour en revenir au problème actuel. Une requête en double doit clairement être mise dans une CTE (la petite clause WITH, au cas où ce nom ne te parle pas)
Ensuite, deux sous-requête similaires mais sur des tables différentes, bon beh tant pis j'ai envie de dire

[vanagreg]

de plus, toute les tables utilisés dans la sous-requetes sont présentes dans la requete principale.
Les sous-reauetes sont jointes par le numéro de compte a la requete principale.

Dans ce cas tu certainement mutualiser avec un bloc WITH, des CASE et des fonctions analytiques.

[JeanYvette]

Une question bête mais qui pourrait avoir un potentiel intérêt à la fin.

Tu saurais nous retranscrire un algo "fonctionnel" de la requête du genre :
Je vais chercher dans ma table A les données qui sont...
Je les couples avec la table C quand...

Et le but final ?

Des fois, ce travail peut mettre en évidence le fait que la requête pourrait être fractionné, que certains points ne sont peut-être pas nécessaires ou des trucs comme ça, sait-on jamais

[Lagnio]

Envoyé par vanagreg

Dans ce cas tu certainement mutualiser avec un bloc WITH, des CASE et des fonctions analytiques.

Fonction analytique?? tu peux me donner un exemple parceque je suis pas sur de ce dont tu parles.

Merci

[JeanYvette]

Une question bête mais qui pourrait avoir un potentiel intérêt à la fin.

Tu saurais nous retranscrire un algo "fonctionnel" de la requête du genre :
Je vais chercher dans ma table A les données qui sont...
Je les couples avec la table C quand...

Et le but final ?

Des fois, ce travail peut mettre en évidence le fait que la requête pourrait être fractionné, que certains points ne sont peut-être pas nécessaires ou des trucs comme ça, sait-on jamais



Pour les fonctions analytics, il s'agit de fonctions faites par Oracle, en tapant "Oracle analytic function" sur google tu trouveras la liste. Dedans tu as "LEAD", "LAG" par exemple

[vanagreg]

Par exemple pour ta sous-requête, tu peux récupérer les autres montants en utilisant un CASE dans le SUM, puis si tu utilises les fonctions SUM et MAX dans leur forme analytique, tu peux sélectionner d'autres colonnes sans nécessité de GROUP BY.
Par exemple:

Code :

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

1
2
3
4
5
6
7
select c.*,
       round(sum(case when period = '30-nov-19' then balance else 0 end) over (partition by c.num_cpt) /max(valuation) over (partition by c.num_cpt) ,2) "B_nov_19",
       round(sum(case when period = '31-dec-19' then balance else 0 end) over (partition by c.num_cpt) /max(valuation) over (partition by c.num_cpt) ,2) "B_dec_19"
 from  compte c,  propriete p
where c.num_prop=p.num_prop
and   c.type = 'M'
and   c.period in ('30-nov-19', '31-dec-19');

[pachot]

J'ai fait la même requête, en terme d'algo, en curseur, ca tourne super bien. Ca me sidère parce que je suis du même avis que toi, mais j'ai eu plusieurs cas qui se sont passés comme ça...

En requête SQL, Oracle a de nombreux algorithmes possibles: plusieurs méthodes de jointures, transformations pour descendre les prédicats au plus tôt,...
En language procédural, tu n'en a qu'un: boucles imbriquées. Sauf si tu commence à construire des tables de hachage dans des collections mais alors ça devient compliqué.

Donc, si c'es t plus rapide en language procédural, c'est que le plan d'exécution de la requête SQL était très mauvais. Vérifier les statistiques (systèmes, histogrammes, column group,...) sera un meilleur investissement que de tout ré-écrire en procédural. Parce que durable (lorsque le volume augmentera) et global (pour toutes nouvelles requêtes similaires). Et plus stable parce que passer de 10 secondes à 380 c'est un gros problème de plan d'exécution qui change ou qui lit trop de données qui sont aléatoirement en cache.

C'est vrai qu'une requête de 700 lignes ça peut être illisible pour le développeur et complexe pour l'optimiseur. Séparer en sous-requêtes et/ou CTE avec des commentaires clairs pour décrire chaque bloc de requête le rend plus lisible pour nous. Rajouter des hints materialize, no_merge, no_push_pred peut le rendre plus simple pour l'optimiseur. Mais uniquement si nécessaire. Peut-être qu'un niveau de dynamic sampling plus élevé sera suffisant pour que l'optimiseur trouve le bon plan d'exécution.

[JeanYvette]

Donc, si c'es t plus rapide en language procédural, c'est que le plan d'exécution de la requête SQL était très mauvais. Vérifier les statistiques (systèmes, histogrammes, column group,...) sera un meilleur investissement que de tout ré-écrire en procédural. Parce que durable (lorsque le volume augmentera) et global (pour toutes nouvelles requêtes similaires). Et plus stable parce que passer de 10 secondes à 380 c'est un gros problème de plan d'exécution qui change ou qui lit trop de données qui sont aléatoirement en cache.

La table sur laquelle j'ai l'habitude de travailler pour ce cas décrit est anormalement lente, les statistiques étaient à jour, le plan d'exécution était ok mais l’enchaînement d'accession aux tables moins. Et pour ce cas précis, l'ensembliste ne pouvait pas m'aider car trop de contraintes fonctionnelles, beaucoup beaucoup trop.. J'aurais peut-être dû rentrer plus dans les détails que dire que parfois les curseurs sont juste mieux, pardon

Pour les CTE, je te rejoins entièrement, malheureusement, certains n'en n'ont jamais vu et je ne compte pas le nombre de fois où j'ai dû expliquer ce que c'est "Ce truc WITH en début de requête là"


Juste une petite question, pour les hints dont tu parles. On m'a dit il a peu que "les utiliser c'est bien, mais c'est à double tranchant". Le problème est que la personne qui me l'a dit n'a pas su m'expliquer pourquoi... Tu dirais quoi sur ce sujet ?

[SQLpro]

....
Juste une petite question, pour les hints dont tu parles. On m'a dit il a peu que "les utiliser c'est bien, mais c'est à double tranchant". Le problème est que la personne qui me l'a dit n'a pas su m'expliquer pourquoi... Tu dirais quoi sur ce sujet ?

Un plan de requête n'est pas quelque chose de stable. Il peut changer en fonction de :

• la volumétrie (une jointure de 1 000 lignes par 10 ne sera pas traité avec le même algorithme qu'une jointure de 1 000 000 de lignes par 1 000 000 de lignes)
• la distribution/dispersion des données (les clients de Paris, Londres ou New York sont généralement plus nombreux que ceux de Trifouillis les Argonzettes)

En fixant le problème avec un hint, vous obligez toutes les requêtes similaires (aux paramètres près...) d'utiliser la même plan de requête qui conviendra bien à certaines et peuvent s'avérer épouvantables pour d'autres...

A +

[Lagnio]

Merci à tous pour vos réponses qui m’ont énormément aidé.

JeanYvette
J’ai recréer un algo fonctionnel et j’ai pu détermine toutes les tables/champs inutiles, les redondants et j’ai rajoute des CTE quand j’avais besoin de données « externe ». Le passage par l’algo est super efficace et je sais maintenant comment démonter les requêtes de mon collègue facilement !! (Trop contente, même lui ne comprends pas ce qu’il a fait)

Vanagreg : Merci pour l’exemple de code, très utile et explicite, j’ai pu le mettre en place aussi et mon boss pense que je suis une vraie pro maintenant !!

Pachot : merci pour ces explications mais malheureusement, mon niveau actuel de connaissance sur Oracle n’est pas si pousser. Je dois aller suivre quelques formations chez Oracle mais euh, mon boss veut que je commence par la certification DBA donc pour le plan d’exécution si vous avez des liens ou cela est expliqué je suis preneuse (ps : en français se serait génial car même si mon anglais est bon, je peine a comprendre de nouveau concept dans cette langue !)

Encore une fois, merci à tous !! et a très vite sur l’un des forums oracle !