Sélection de MIN et MAX De la Table est plus lent que prévu
J'ai une table MYTABLE
avec une colonne de date SDATE
qui est la clé primaire de la table et a un index unique sur elle.
Lorsque j'exécute cette requête:
SELECT MIN(SDATE) FROM MYTABLE
il donne la réponse instantanément. Le même phénomène se produit pour:
SELECT MAX(SDATE) FROM MYTABLE
Mais, si j'ai une requête à la fois ensemble:
SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE
il prend beaucoup plus de temps à s'exécuter. J'ai analysé les plans et trouvé lorsque l'un des min ou max est interrogé, il utilise des INDEX FULL SCAN(MIN/MAX), mais quand les deux sont interrogés dans le même temps, il fait un FULL TABLE SCAN.
pourquoi?
Données De Test:
version 11g
create table MYTABLE
(
SDATE DATE not null,
CELL VARCHAR2(10),
data NUMBER
)
tablespace CHIPS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table MYTABLE
add constraint PK_SDATE primary key (SDATE)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
De la table de chargement:
declare
i integer;
begin
for i in 0 .. 100000 loop
insert into MYTABLE(sdate, cell, data)
values(sysdate - i/24, 'T' || i, i);
commit;
end loop;
end;
Recueillir des statistiques:
begin
dbms_stats.gather_table_stats(tabname => 'MYTABLE', ownname => 'SYS');
end;
Plan 1:
Plan2:
- Le nombre de lignes dans la table? Comment les frais sont les statistiques?
- Ma table a presque 100000 lignes et les stats sont frais; vous pouvez facilement re-générer le problème par la création d'un tableau simple avec seulement un ou deux colonnes et de voir les résultats pour vous-même.
- quels sont les coûts pour les requêtes? pouvez-vous poster les plans? Je pense que l'index est très fragmenté.
- tablespace SYSTEM? veuillez essayer un autre.
- C'est mon petit environnement de test donc je n'ai pas le SYSTÈME de soins de tablespace. mais de toute façon la même chose qui se passe sur la production de la table.
- Merci de ne pas prendre de mauvaises habitudes: (1) Ne pas créer des objets dans le schéma SYS, jamais. (2) ne s'engagent pas dans une boucle.
- Je vais prendre vos conseils!!! Merci. 🙂
Vous devez vous connecter pour publier un commentaire.
L'Index Scan Complet seulement un côté de l'index. Quand vous faites
vous demandez à visiter, des 2 côtés. Par conséquent, si vous souhaitez à la fois le minimum et le maximum de la valeur de la colonne, un Indice de l'Analyse Complète n'est pas viable.
Plus détaillée analyser vous pouvez trouver ici.
SELECT MIN(SDATE), MIN(SDATE) FROM MYTABLE
aussi est différent! Veuillez voir mon post mis à jour.SELECT MIN(SDATE), MIN(SDATE) FROM MYTABLE
est en quelque sorte provoquant Oracle de ne pas détecter l'optimisation que pour lesSELECT MIN(SDATE) FROM MYTABLE
. Cependant, dans mon test (11gR2) l'optimisation fonctionne très bien, même avec clauses redondantes dans le select.L'expliquent les plans sont différents: un seul
MIN
ouMAX
va produire unINDEX FULL SCAN (MIN/MAX)
alors que quand les deux sont présents, vous obtiendrez unINDEX FULL SCAN
ou unRAPIDE ANALYSE d'INDEX
.Pour comprendre la différence, nous devons nous pencher pour une description d'un
INDEX COMPLET SCAN
:En d'autres termes, si l'index est sur un
VARCHAR2
champ, Oracle va chercher le premier bloc de l'index qui contiendrait par exemple toutes les entrées qui commencent par la lettre "A" et le lire bloc par bloc, toutes les entrées par ordre alphabétique jusqu'à ce que la dernière entrée ("A" à "Z"). Oracle peut traiter de cette façon parce que les entrées sont triées dans un arbre binaire de l'index.Quand vous voyez
INDEX FULL SCAN (MIN/MAX)
en expliquer le plan, qui est le résultat d'une optimisation qui utilise le fait que, depuis les entrées sont triées, vous pouvez vous arrêter après avoir lu la première, si vous êtes uniquement intéressé par laMIN
. Si vous êtes intéressé dans leMAX
seulement, Oracle peut utiliser le même chemin d'accès, mais cette fois en commençant par la dernière entrée et de lecture en arrière à partir de "Z" à "Une".Dès maintenant, un
FULL INDEX SCAN
a une seule direction (vers l'avant ou vers l'arrière) et ne peut pas commencer à partir de deux extrémités simultanément, c'est pourquoi, lorsque vous demandez à la fois le min et le max, vous bénéficiez d'une moins efficace méthode d'accès.Comme suggéré par d'autres réponses, si la requête a besoin critique de l'efficacité, vous pouvez exécuter votre propre optimisation par recherche pour le min et le max dans les deux requêtes distinctes.
Essayez de ne pas sélectionner les deux bords de l'indice dans une requête ,
Accédant à la requête d'une manière différente comme ceci :
sera la cause de l'optimiseur pour accéder à l'index dans INDEX_FULL_SCAN(MIN/MAX) dans les boucles imbriquées (dans notre cas , deux fois).
Je dois dire que je ne vois pas le même comportement dans 11.2
Si j'ai mis un cas de test comme suit et mis à jour à partir de 10k à 1m de lignes en réponse à Vincent commentaire
Ensuite, l'exécution de vos requêtes-je obtenir presque identiques à la recherche expliquent les plans (notez les différents types d'INDEX FULL SCAN)
Pour citer un à la réponse précédente de la mine:
Moins qu'il y a quelque chose que vous n'êtes pas poster dans la question, ma réponse serait que vous n'avez pas recueilli des statistiques sur ce tableau, vous n'avez pas a recueillis avec un haut assez estimation pour cent ou que vous avez utilisé
analyse
, qui pas aider l'Optimiseur Basé sur les Coûts, contrairement àdbms_stats.gather_table_stats
.Des extraits de la documentation sur
analyze
:INDEX FULL SCAN (MIN/MAX)
va s'arrêter après la première ligne récupérée (colonne rows=1) alors que laINDEX FULL SCAN
permettra de lire tous les index de blocs (colonne rows=10000) c'est 10000 fois plus de travail ! (à peu près 🙂INDEX FULL SCAN
qui va arrêter le scan complet après la première entrée trouvée pour répondre à la condition, dans ce cas, la première ligne. Je peux voir comment le nom peut être trompeur 🙂