Est substr ou COMME plus rapide dans Oracle?

En supposant que le rendement maximal est le but, je voudrais idéalement choisir SUBSTR(my_field,1,6) et de créer une fonction d'index de base à l'appui de la requête.

CREATE INDEX my_substr_idx
    ON my_table( substr( my_field,1,6 ) );

Comme l'autre, SUBSTR(my_field,1,6) ne serait pas en mesure d'utiliser un index normal sur MY_FIELD. La COMME version peut utiliser l'index, mais l'optimiseur estimations de cardinalité dans ce cas sont en général assez pauvres, de sorte qu'il est tout à fait probable, soit ne pas utiliser un index quand il serait utile d'utiliser un index lors de l'analyse d'une table serait préférable. L'indexation de l'expression réelle donnera à l'optimiseur de beaucoup plus d'informations pour travailler avec de sorte qu'il est beaucoup plus susceptibles de choisir l'index correctement. Quelqu'un de plus malin que je suis peut être en mesure de vous suggérer une façon d'utiliser les statistiques sur les colonnes virtuelles en 11g pour donner l'optimiseur de meilleures informations pour la COMME requête.

Si 6 est une variable (c'est à dire parfois, vous souhaitez rechercher les 6 premiers caractères et parfois souhaitez rechercher un numéro différent), vous ne serez probablement pas en mesure de venir avec une fonction d'index de base à l'appui de cette requête. Dans ce cas, vous êtes probablement mieux avec les aléas de l'optimiseur de décisions avec la même formulation.

Si vous avez un index sur my_field, alors, COMME peut être plus rapide. Faire vos propres repères.

Il n'y a vraiment deux questions se posent ici:

1. Pour laquelle Oracle produire le plus précis de cardinalité et de l'estimation des coûts?
2. La méthode est plus souple en termes de potentiel de méthodes d'accès?

Cela peut varier en fonction de la version, mais les deux sont assez facile à tester, et de cette façon vous êtes sûr que vous avez le meilleur de l'information pour votre version et vos données.

Exécuter des plans d'exécution pour les deux requêtes à l'aide de ...

explain plan for 
select ... from ... where my_field LIKE 'search%';

select * from table(dbms_xplan.display);

et

explain plan for 
select ... from ... where substr(my_field,1,6) = 'search';

select * from table(dbms_xplan.display);

Vous pouvez voir une différence sur le plan de l'exécution, en fonction de la présence d'index, etc., mais aussi de comparer les estimations de cardinalité avec le résultat que vous obtenez à partir de:

select count(*) from ... where my_field LIKE 'search%';

L'une des deux méthodes peuvent être beaucoup plus précis que les autres.

Si aucun des deux n'est très précis et cette requête est prévue pour un montant non négligeable de temps alors envisager l'utilisation d'échantillonnage dynamique à l'amélioration de l'estimation, car avec la mauvaise estimation de cardinalité l'optimiseur peut choisir un sous-optimale de la méthode d'accès de toute façon.

explain plan for 
select /*+ dynamic_sampling(4) */ ... from ... where substr(my_field,1,6) = 'search';

select * from table(dbms_xplan.display);

Autant que l'indice de l'utilisation, les deux méthodes peuvent utiliser un indice basé sur la méthode d'accès. Le prédicat LIKE est probablement plus l'indice sympathique et pourrait utiliser une analyse de plage ou une rapide analyse d'index. La méthode SUBSTR pouvez certainement utiliser la rapide analyse d'index, mais si l'optimiseur de prendre en considération un éventail d'analyse est le mieux testé sur votre propre version -- mon souvenir est qu'il ne est pas, mais qui est-à-dire que substr(my_column,1,n) ne sera pas reconnu comme un cas particulier, si pas maintenant, alors dans le futur?