Является ли substr или LIKE быстрее в Oracle?

Ответ 1

Предполагая, что максимальная производительность является целью, я бы идеально выбрал SUBSTR(my_field,1,6) и создал индекс на основе функций для поддержки запроса.

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

Как отмечают другие, SUBSTR(my_field,1,6) не сможет использовать регулярный индекс на MY_FIELD. Версия LIKE может использовать индекс, но оценки мощности оптимизатора в этом случае, как правило, довольно плохие, поэтому вполне вероятно, что он не будет использовать индекс, когда это было бы полезно, или использовать индекс, когда было бы предпочтительным сканирование таблицы. Индексирование фактического выражения даст оптимизатору гораздо больше информации для работы, так что гораздо вероятнее правильно выбрать индекс. Кто-то умнее, чем я, может предложить способ использовать статистику по виртуальным столбцам в 11g, чтобы дать оптимизатору лучшую информацию для запроса LIKE.

Если 6 - переменная (т.е. вы иногда хотите искать первые 6 символов и иногда хотите искать другой номер), вы, вероятно, не сможете придумать функциональный индекс для поддержки этого запроса. В этом случае вам, вероятно, будет лучше с капризами решений оптимизатора с формулировкой LIKE.

Ответ 2

Из двух предоставленных опций, безусловно, LIKE. Метод подстроки должен быть выполнен для всех строк таблицы. Использование LIKE позволит использовать индексы.

Чтобы проверить мой ответ, просто просмотрите результаты. Это должно быть ясно, как день.

Ответ 3

Если у вас есть индекс на my_field, тогда LIKE может быть быстрее. Сделайте свои собственные тесты.

Ответ 4

Если у вас нет индекса, чем нет разницы. Поскольку оракул выполняет полное сканирование таблицы и оценивает выражение для каждой строки. Вы можете поместить индекс в столбец, чтобы ускорить оба запроса.

CREATE INDEX my_like_idx
ON my_table( my_field );

Этот индекс является более гибким и ускоряет запрос с использованием подобных. Он будет работать для любого сравнения, начиная с символов и с помощью заполнителя (%) в конце. Oracle выполняет проверку диапазона индексов, чтобы найти все соответствующие строки.

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

Этот индекс ускоряет запрос с помощью substr. Но индекс очень важен для сравнения только первых 6 символов.

Если вы запросите фрагмент начала в середине. Создание индекса, основанного на функции, поможет.

WHERE substr(my_field,2,5) = 'earch'
WHERE my_field like '%earch%'

Ответ 5

Здесь действительно два вопроса:

• Для чего Oracle будет производить более точную мощность и калькуляцию?
• Какой метод более гибкий с точки зрения потенциальных методов доступа?

Это может варьироваться в зависимости от версии, но оба теста довольно легко тестировать, и таким образом вы уверены, что у вас есть лучшая информация для вашей версии и ваших данных.

Запуск планов выполнения для обоих запросов с использованием...

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

select * from table(dbms_xplan.display);

и

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

select * from table(dbms_xplan.display);

Вы можете увидеть разницу в плане выполнения, в зависимости от наличия индексов и т.д., но также сравнить оценки мощности с фактическим результатом, который вы получаете от:

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

Один из двух методов может быть значительно более точным, чем другой.

Если ни один из них не является очень точным, и ожидается, что этот запрос будет выполняться в течение нетривиального промежутка времени, тогда рассмотрите возможность использования динамической выборки для улучшения оценки, поскольку при неправильной оценке мощности оптимизатор может в любом случае выбрать метод субоптимального доступа.

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

select * from table(dbms_xplan.display);

Что касается использования индекса, оба метода могут использовать метод доступа на основе индексов. Предикат LIKE, вероятно, более удобен для индексирования и может использовать сканирование диапазона или быстрое сканирование полного индекса. Метод SUBSTR, безусловно, может использовать быстрое сканирование полного индекса, но будет ли оптимизатор считать, что сканирование диапазона лучше всего проверяется на вашей собственной версии - мое воспоминание состоит в том, что он не будет, но кто скажет, что substr (my_column, 1, n) не будет распознаваться как частный случай, если не сейчас, то в будущем?

Ответ 6

Я бы описал оба. Но я бы предположил, что "LIKE" будет намного быстрее, потому что он использует двоичный поиск по индексу (если поле индексируется). Если вы используете метод SUBSTR, вы получите полное сканирование таблицы, так как Oracle должна обрабатывать строку за строкой.