Oracle requête à l'aide de "like" sur indexées nombre de colonnes, de mauvaises performances
Sur Requête 1 un full table scan est effectué même si l'id est une colonne indexée. Requête 2 permet d'obtenir le même résultat, mais beaucoup plus rapide. Si la Requête 1 est exécuté au retour d'une colonne indexée puis elle se retourne rapidement, mais si non indexées colonnes sont retournées ou la totalité de la ligne est alors la requête prend plus de temps.
Dans la Requête 3 il court vite, mais la colonne "code" est un VARCHAR2(10) à la place d'un NOMBRE(12) et est indexée de la même façon que 'id'.
Pourquoi Requête 1 pas ramasser ce qu'il devrait utiliser l'index? Est-il quelque chose qui devrait être modifié pour permettre indexées nombre de colonnes à effectuer plus rapidement?
[Requête 1]
select a1.*
from people a1
where a1.id like '119%'
and rownum < 5
Expliquer Le Plan D'
INSTRUCTION SELECT ALL_ROWS
Coût: 67 Octets: 2,592 Cardinalité: 4
2 LE COMTE STOPKEY
1 ACCÈS à la TABLE COMPLÈTE de la TABLE des gens
Coût: 67 Octets: 3,240 Cardinalité: 5
[Requête 2]
select a1.*
from people a1, people a2
where a1.id = a2.id
and a2.id like '119%'
and rownum < 5
Expliquer Le Plan D'
INSTRUCTION SELECT ALL_ROWS
Prix: 11 Octets: 2,620 Cardinalité: 4
5 LE COMTE STOPKEY
4 TABLE ACCESS BY INDEX ROWID TABLE les gens
Coût: 3 Octets: 648 Cardinalité: 1
3 BOUCLES IMBRIQUÉES
Coût: 11 Octets: 2,620 Cardinalité: 4
1 INDEX RAPIDE de l'ANALYSE COMPLÈTE de l'INDICE de people_IDX3
Coût: 2 Octets: 54,796 Cardinalité: 7,828
2 INDEX RANGE SCAN d'INDEX people_IDX3
Coût: 2 Cardinalité: 1
[Requête 3]
select a1.*
from people a1
where a1.code like '119%'
and rownum < 5
Expliquer Le Plan D'
INSTRUCTION SELECT ALL_ROWS
Coût: 6 Octets: 1,296 Cardinalité: 2
3 COUNT STOPKEY
2 TABLE ACCESS BY INDEX ROWID TABLE les gens
Coût: 6 Octets: 1,296 Cardinalité: 2
1 INDEX RANGE SCAN d'INDEX people_IDX4
Coût: 3 Cardinalité: 2
Votre accueil j'aime la lecture agréable mise en forme moi-même.
Vous devriez envisager d'utiliser la DBMS_XPLAN.Méthode d'AFFICHAGE de la récupération du plan d'expliquer vraiment. C'est beaucoup plus utile et montre l'application de prédicats et se joint à différents stades de l'exécution.
OriginalL'auteur James Collins | 2009-11-04
Vous devez vous connecter pour publier un commentaire.
COMME le pattern matching condition s'attend à voir les types de caractères comme à la fois du côté gauche et du côté droit opérandes. Lorsqu'il rencontre un certain NOMBRE, il convertit implicitement à char. Votre Requête 1 est essentiellement silencieusement réécrit:
Qui se passe dans votre cas, et ce qui est mauvais pour 2 raisons:
A1.ID
colonne.De la contourner, vous devez effectuer l'une des opérations suivantes:
Créer un la fonction d'index de base sur
A1.ID
colonne:CREATE INDEX people_idx5 ON people (TO_CHAR(id));
Si vous avez besoin de correspondre à des enregistrements sur les 3 premiers caractères de la colonne ID, créez une colonne de type numérique contenant uniquement ces 3 personnages et d'utiliser une plaine = opérateur.
Créer un séparé colonne
ID_CHAR
de typeVARCHAR2
et de le remplir avecTO_CHAR(id)
. De l'Index et de l'utiliser au lieu deID
dans votreWHERE
condition.Bien sûr, si vous choisissez de créer une colonne supplémentaire fondés sur la colonne ID, vous avez besoin de garder ces 2 synchronisé.Vous pouvez le faire en batch que d'une seule mise à JOUR, ou dans le cadre d'un déclencheur de mise à JOUR ou d'ajouter cette colonne pour l'INSERTION et la mise à JOUR des instructions dans votre code.
Merci c'était une explication complète et j'ai apprécié figurant dans votre liste de solutions. La fonction d'index de base semble fonctionner bien.
OriginalL'auteur Sergey Stadnik
Est une fonction de chaîne, ainsi qu'un index numérique ne peut être utilisé aussi facilement. Dans l'index numérique, vous aurez 119,120,130,..,1191,1192,1193...,11921,11922... etc. C'est toutes les lignes commençant avec le " 119 " ne sera pas au même endroit, de sorte que la totalité de l'index a à lire (d'où la RAPIDE ANALYSE COMPLÈTE). Dans une demeure de caractère en fonction de l'indice qu'ils seront ensemble (par exemple '119','1191','11911','120',...) donc, une meilleure ANALYSE de PLAGE peut être utilisé.
Si vous avez été à la recherche pour les valeurs d'id dans une plage particulière (par exemple, 119000 à 119999) alors préciser que le prédicat (id entre 119000 et 119999).
OriginalL'auteur Gary Myers
Optimiseur a décidé qu'il est plus rapide de faire un scan de table, probablement en raison du faible nombre d'enregistrements réels.
Aussi, vous devriez savoir que la non-correspondance exacte est toujours façon de pire que d'exact. Si votre où était "a1.id='123456'", il serait probablement utiliser les index. Mais là encore, même indice prend deux lectures (d'abord trouver un record dans l'index, puis lire le bloc de la table) et pour les très petits tableaux, il pourrait décider pour l'analyse de la table.
En raison de la ROWNUM prédicat, la requête ne sera pas forcément analyse complète de l'ensemble de la table. il s'arrête lorsqu'il trouve le nombre approprié de lignes.
Expliquer le plan de la requête 1 montre qu'il existe un full table scan avec le numéro de rangée.
OriginalL'auteur user188658
Essayez de placer un indice dans l'une de vos requêtes pour les forcer à utiliser l'indice désiré et ensuite, vérifiez votre plan: il se pourrait que (en raison de l'inclinaison ou de quoi que ce soit) le optimzer ne prendre l'index en compte, mais décide de l'utiliser en raison du coût perçu.
Nous avons essayé d'utiliser un indicateur qui fait la force de l'index pour être utilisé et a abouti à un résultat rapide, cependant nous n'allons pas être en mesure d'utiliser des astuces de notre frontend. Requête 3 montre que, lorsque la même chose est effectué sur une VARCHAR2 colonne fonctionne très bien, sans les conseils. Je voudrais trouver une solution qui ne nécessite pas de conseils. Un changement à la table devrait être possible (sauf pour changer le type de colonne).
OriginalL'auteur davek
La
LIKE
mot-clé indique SQL que vous faites une expression régulière. Vous ne devez jamais utiliser des expressions régulières dans SQL ou dans toute bibliothèque de programmation jusqu'à ce que vous avez coché la chaîne de fonctions disponibles pour voir si la requête peut être exprimée simplement avec eux. Dans ce cas, vous pouvez changer ce un est égale à condition par la comparaison de la sous-chaîne de caractères comprenant les 3 premiers caractères du code. Dans Oracle, ce serait:Votre requête à l'aide de la substr effectue un full table scan. Nous utilisons comme pas regexp_like.
La fonction de base des index sont prises en charge dans l'édition standard depuis 10g si je ne me trompe pas. substr + fonction de l'indice est probablement la meilleure solution.
COMME n'est PAS une expression régulière de la fonction. La ferme est REGEXP_INSTR. Et une sous-chaîne n'est pas plus susceptible d'utiliser un index de un COMME.
LIKE
n'est pas une expression régulière correspondant, il est évident que [pattern matching][1]. - Vous confus avec [REGEXP_LIKE][2] [1]: download.oracle.com/docs/cd/B19306_01/server.102/b14200/... [2]: download.oracle.com/docs/cd/B19306_01/server.102/b14200/...OriginalL'auteur Michael Dillon