J'ai une requête que je souhaite optimiser. Voici la requête:
Cost 5102095 Time 00:03:20
Voici les résultats du plan explicatif avec un coût énorme:
SELECT "c"."NETSW_ACQEREF" AS "BANK",
count("c"."NETSW_ACQEREF") AS "QTY",
sum("c"."TRAN_AMNT") / 100 AS "AMOUNT",
count(distinct "c"."TERM_ID") as "terminals"
FROM "CSCLWH"."CLWH_COMMON_DATA" "c"
WHERE ("c"."TRAN_DATE" between 20201101 AND 20201111)
AND ("TRAN_TYPE" IN
('00', '01', '10', '12', '19', '20', '26', '29', '50', '51', '52'))
AND ("RESP_CODE" IN ('0', '00', '000', '400'))
AND ("MTI" IN ('1100', '1200', '1240', '1400', '1420'))
GROUP BY "c"."NETSW_ACQEREF"
ORDER BY "BANK"
il a une date de 3 millions de lignes que j'ai créé groupe par index mais c'est moins utile. Pouvez-vous s'il vous plaît me montrer un moyen de réduire les coûts?
3 Réponses :
Les opérations d'agrégation COUNT et SUM ne peuvent pas être beaucoup optimisées, et il n'y a pas non plus de clause HAVING , donc votre meilleur pari ici serait probablement d'ajouter un index multi-colonnes couvrant toute la clause WHERE :
CREATE INDEX idx ON "CSCLWH"."CLWH_COMMON_DATA" (TRAN_DATE, TRAN_TYPE, RESP_CODE, MTI);
Cet index, s'il est utilisé, permettrait au moins à Oracle de supprimer de nombreux enregistrements ne correspondant pas au filtre where. L'ordre exact des colonnes utilisées dans l'index dépendrait de la cardinalité des données dans chaque colonne. En règle générale, vous souhaitez placer en premier les colonnes qui sont plus restrictives, en plaçant les colonnes moins restrictives en dernier.
merci je serai créer mais je veux optimiser cette requête Comment l'optimiser?
À part l'ajout de l'index ci-dessus, vous ne pouvez pas faire grand-chose. L' ajout de l'index est l' optimisation de la requête.
Par exemple, pouvez-vous me donner quelques exemples. J'ai créé un index pour des colonnes uniques en fait c'était inutile
can you give me some examples ... Je pensais que ma réponse était un exemple. Si vous ne pouvez pas investir quelques instants de votre temps pour au moins essayer ma réponse, je ne sais pas ce que je peux faire d'autre ici ...
La première question à poser à l'OP devrait être: "Combien de lignes y a-t-il au total dans le tableau et combien passent le filtre WHERE". J'ai un fort soupçon que de nombreuses lignes le font; si c'est le cas, l'indexation n'aidera pas. Plus probablement, l'agrégation et le tri prennent la majeure partie du temps. La lecture de la table est O (n) (n = cardinalité); le groupement et le tri sont O (n log n), et «n» semble être juste assez grand pour que le regroupement et le tri constituent le goulot d'étranglement probable.
Je peux voir deux sources potentielles de lenteur dans votre requête. Vous pouvez exécuter quelques tests pour voir lequel est le pire. Il existe un moyen simple de résoudre l'un d'entre eux; Je ne pense pas que vous puissiez faire grand-chose pour l'autre.
Vous n'avez pas seulement le group by agrégation au niveau global de la requête; vous avez également un compte ( distinct {quelque chose}). Ce compte distinct est une agrégation imbriquée qui est coûteuse. Que se passe-t-il si vous supprimez le mot «distinct» ici? Signification, comment le temps d'exécution change-t-il? (Bien sûr, cela ne vous donnera pas le résultat dont vous avez besoin; mais cela vous dira à quel point le «distinct» est cher.)
Malheureusement, si CELA est le plus gros goulot d'étranglement, vous ne pouvez rien y faire.
L'autre source de lenteur est la clause ORDER BY à la fin de la requête. Un peu de contexte: il existe essentiellement deux façons de TRIER PAR. La première consiste à ordonner les expressions que vous «regroupez»; l'autre est de les hacher. Dans l'ancien temps, Oracle utilisait le «tri» groupé par - ce qui coûte cher. Comme effet secondaire, les résultats étaient classés par les expressions GROUP BY même sans clause ORDER BY explicite; c'est ainsi que les développeurs ont acquis de très mauvaises habitudes.
À un moment donné, Oracle a "appris" que le "hachage" groupé était plus rapide. Cependant, ils sont tombés dans un piège: lorsque vous avez GROUP BY suivi de ORDER BY les mêmes expressions, Oracle pensait (à tort dans la plupart des cas) qu'ils pouvaient gagner du temps en faisant les deux en un seul coup en utilisant simplement l'ancien groupe de "tri" par . C'est très inutile lorsque 3 millions de lignes dans l'entrée résultent peut-être de 300 groupes. Mieux vaut hacher le groupement pour les 3 millions de lignes, puis passer à l'étape (supplémentaire, mais triviale) de tri des 300 lignes de sortie. Pourquoi Oracle est si stupide de ne pas voir ça, je ne sais pas - c'est juste comme ça.
Ce problème, cependant, a une solution très simple. Vous pouvez forcer le groupe de hachage avec l' use_hash_aggregation . (Tout d'abord, vous pouvez simplement supprimer la clause ORDER BY de votre requête pour voir si c'est le problème; si vous ne voyez aucune amélioration, l'ajout de l'indice sur l'agrégation de hachage ne vous aidera pas.)
Je ne sais pas lequel des deux problèmes que j'ai décrits est le plus grave. Et si c'est le «groupe de tri» (le seul sur lequel vous pouvez faire quelque chose), ne vous attendez pas à des miracles. Vous pouvez voir le temps d'exécution passer de 3 minutes et 20 secondes à 2 minutes ou 2 minutes et 30 secondes ou autre; pas un ordre de grandeur d'amélioration.
Pensez également à suggérer de placer un index sur TRAN_DATE , car il est probable que la table couvre plusieurs dates, alors que la requête en question ne cible que deux de ces dates.
@TimBiegeleisen - Plus important encore, d'après la façon dont la requête est écrite, il semble que les "dates" soient stockées sous forme de nombres. Si tel est le cas, même avec un index, l'optimiseur tirera de fausses conclusions. Dans tous les cas, même sans index, le temps nécessaire pour tout lire dans la table ne croît que linéairement avec la cardinalité, alors que l'agrégation et le tri sont pires que cela. Je commencerais par ceux-ci, même si la tran_date des problèmes concernant tran_date peut également s'avérer importante.
@TimBiegeleisen - il me semble que la requête cible 11 jours, du 1er novembre au 11 novembre. Il est toujours possible qu'un index puisse aider, surtout si le type de données était DATE.
Tran_date est varchar2 (8) et j'utilise group by dans la requête afin de créer des index simples et uniques (créer un index IDX1 CLWH_COMMON_DATA (tran_date)) index il est inutile 01.11.2020 et 12.11.2020. Je veux optimiser avec une sous-requête mais je ne peux pas le faire
@ Ma'rufJavliyev - OK, alors (1) pourquoi comparez-vous les dates à des nombres dans votre requête, et non à des chaînes? (2) Plus important encore, pouvez-vous changer le type de données dans votre table réelle? L'indexation sous forme de chaînes pour des choses censées être des dates sera inutile. (3) Avez-vous essayé ce que j'ai suggéré, et si vous l'avez fait, qu'en avez-vous appris? A savoir: exécuter la requête sans le ORDER BY final. Est-ce que ça marche plus vite? Exécutez la requête avec count (terminaux) au lieu de count (terminaux distincts). Est-ce que ça marche plus vite?
Je me demande si deux niveaux d'agrégation avec des index appropriés pourraient aider:
SELECT bank, SUM(qty) as qty, SUM(amount) as amount,
count(*) as terminals
FROM (SELECT "c"."NETSW_ACQEREF" AS bank, "c"."TERM_ID",
count(*) AS qty,
sum("c"."TRAN_AMNT") / 100 AS "AMOUNT",
FROM "CSCLWH"."CLWH_COMMON_DATA" "c"
WHERE "c"."TRAN_DATE" between 20201101 AND 20201111 AND
"TRAN_TYPE" IN ('00', '01', '10', '12', '19', '20', '26', '29', '50', '51', '52') AND
"RESP_CODE" IN ('0', '00', '000', '400') AND
"MTI" IN ('1100', '1200', '1240', '1400', '1420')
GROUP BY "c"."NETSW_ACQEREF", "c"."TERM_ID"
) c
GROUP BY bank
ORDER BY BANK;
Cela suppose que tran_type , resp_code et MTI sont toutes des chaînes. S'il s'agit de nombres, modifiez les comparaisons pour utiliser des nombres.
Ensuite, vous voulez un index pour la clause WHERE . On ne sait pas très bien quelles sont les meilleures possibilités, mais quelque chose comme (tran_date, mti, tran_type, resp_code) - celles-ci devraient d'abord être les plus sélectives.
Pourquoi comparez-vous la date de transaction à NUMBERS (20201101 et 20201111)? La colonne de date de transaction est-elle de type NUMBER? Cela peut être un problème important. Même si vous avez un index sur cette colonne, si le type de données est incorrect, l'index n'aidera pas beaucoup - même si cela aiderait beaucoup autrement (si le type de données était DATE).