J'ai cette requête sur la table des lignes de commande. C'est une table assez grande. J'essaie de faire expédier la quantité par article au cours des 365 derniers jours. La requête fonctionne, mais est très lente à renvoyer les résultats. Dois-je utiliser un index basé sur une fonction pour cela? J'ai lu un peu à leur sujet, mais je n'ai pas beaucoup travaillé avec eux.
Comment puis-je accélérer cette requête?
select OOL.INVENTORY_ITEM_ID
,SUM(nvl(OOL.shipped_QUANTITY,0)) shipped_QUANTITY_Last_365
from oe_order_lines_all OOL
where ool.actual_shipment_date>=trunc(sysdate)-365
and cancelled_flag='N'
and fulfilled_flag='Y'
group by ool.inventory_item_id;
Expliquer le plan:
p>
Les statistiques sont à jour, nous nous réunissons une fois par semaine.
La requête prend plus de 30 minutes pour se terminer.
MISE À JOUR p >
Après avoir ajouté cet index:
Le plan d'explication montre que la requête utilise maintenant l'index:
La requête s'exécute plus rapidement mais pas "vite". Terminer en 6 minutes environ.
UPDATE2
J'ai créé un index de couverture comme suggéré par Matthew et Gordon:
La requête se termine maintenant en moins d'une seconde.
Expliquez le plan:
Je me demande toujours pourquoi ou si un index basé sur des fonctions aurait également été une solution viable, mais je n'ai pas le temps de jouer avec.
3 Réponses :
Pour cette requête:
select OOL.INVENTORY_ITEM_ID,
SUM(OOL.shipped_QUANTITY) as shipped_QUANTITY_Last_365
from oe_order_lines_all OOL
where ool.actual_shipment_date >= trunc(sysdate) - 365 and
cancelled_flag = 'N' and
fulfilled_flag = 'Y'
group by ool.inventory_item_id;
Je recommanderais de commencer par un index sur oe_order_lines_all (cancelled_flag, completed_flag, actual_shipment_date) . Cela devrait permettre d'identifier les lignes.
Vous pouvez également ajouter les colonnes supplémentaires Inventory_item_id et Quantity_shipped à l'index. >
Merci. J'essaye ça ... Index prend une éternité à créer.
En règle générale, l'utilisation d'un index qui accède à un pourcentage "significatif" des lignes de votre table est plus lente qu'une analyse complète de la table. Selon votre système, «significatif» peut être aussi bas que 5% ou 10%.
Alors, réfléchissez à vos données pendant une minute ...
OE_ORDER_LINES_ALL sont annulées? (Espérons qu'il n'y en ait pas beaucoup ...) Mettez tout cela ensemble et votre requête devra probablement lire au moins 10% des lignes de votre tableau. C'est très proche du seuil où un index va être pire qu'un scan complet de la table (ou, du moins pas beaucoup mieux qu'un).
Maintenant, si vous avez besoin d'exécuter beaucoup cette requête, vous avez quelques options.
OE_ORDER_LINES_ALL pour le mois en cours. Vous pouvez améliorer les performances d'un index, même s'il accède à un pourcentage significatif des lignes de la table, en lui faisant inclure toutes les informations requises par la requête, ce qui permet à Oracle d'éviter tout accès à la table.
CREATE INDEX idx1 ON OE_ORDER_LINES_ALL
( actual_shipment_date,
cancelled_flag,
fulfilled_flag,
inventory_item_id,
shipped_quantity ) ONLINE;
Avec un index comme celui-là, Oracle peut satisfaire la requête en lisant simplement l'index (ce qui est plus rapide car il est beaucoup plus petit que la table).
p>
Pourquoi un index basé sur une fonction n'est-il pas recommandé?
@alexherm Parce que vous n'avez pas de conditions de clause WHERE qui se comparent à une fonction prenant des colonnes de OE_ORDER_LINES en entrée.
Récapitulons les faits:
a) Vous accédez à environ 300K lignes de votre table (voir cardinalité dans la 3ème ligne du plan d'exécution)
b) vous utilisez le FULL TABLE SCAN pour obtenir les données
c) la requête est très lente
La première chose est de vérifier pourquoi le FULL TABLE SCAN est si lent - si la table est extrêmement grande (vérifiez le < code> BYTES dans user_segments ), vous devez optimiser l'accès à vos données.
Mais rappelez-vous que aucun index ne vous aidera à obtenir 300 000 lignes à partir de 30 millions de lignes au total .
L'accès à un index à 300 000 lignes peut prendre 1/4 d'heure, voire plus, si l'index n'est pas très utilisé et qu'une grande partie est sur le disque.
Ce dont vous avez besoin, c'est d'un partitionnement - dans votre cas, un partitionnement par plage le actual_shipment_date - pour la taille de vos données sur une base mensuelle ou annuelle.
Cela éliminera le besoin d'analyser les anciennes données ( élagage de partition ) et rendra la requête beaucoup plus efficace.
Autre possibilité - si le nombre de lignes est petit, mais que la taille de la table est très grande - vous devez réorganiser la table pour obtenir une meilleure durée d'analyse complète.
Il s'agit d'une table standard pour Oracle EBS, la réorganisation de la table n'est donc pas une option. Je ne suis pas sûr non plus du partitionnement de la plage, j'ai le sentiment que quelque chose comme ça peut se briser lorsqu'un correctif est installé.
La table contient environ 10 millions d'enregistrements. Avec l'index de couverture, la requête est effectuée en moins d'une seconde.
@alexherm Faites un peu attention au timing optimiste (<1 seconde). Un effet secondaire de la lecture à partir d'un index de couverture est que les blocs d'index que vous lisez sont lus au milieu du cache LRU (alors que les blocs lus via FTS sont lus en ajoutant la fin la moins récemment utilisée, où ils vieillissent immédiatement). Cela signifie que vous pouvez bénéficier de hits de cache qui pourraient être irréalistes en production si cette requête n'est exécutée que rarement.
J'ai vérifié cela en ouvrant une nouvelle session et en exécutant la requête. Il s'est également terminé en moins d'une seconde. Je suppose qu'un meilleur test sera après la prochaine collecte de statistiques.
J'ai déplacé l'index en production et y ai exécuté la requête, où elle n'avait pas été exécutée du tout depuis quelques jours. Également fait en moins d'une seconde.
@alexherm Combien de lignes renvoient cette requête: select count (*) from oe_order_lines_all OOL where ool.actual_shipment_date> = trunc (sysdate) -365 and cancelled_flag = 'N' and completed_flag = 'Y'
@Marmie - 352 989 lignes
Quel est le plan explicatif? Quels sont les index disponibles? Vos statistiques sont-elles à jour?
Découvrez ici comment publier le plan d'exécution complet sous forme de texte.