Условия объединения для нескольких столбцов в сравнении с одиночным соединением для объединенных столбцов?

Question

Я наблюдаю, что есть два способа достижения цели на нескольких столах. Столбец в наборе результатов будет обновлен, и скорость может быть требованием. Результирующий набор получается либо:

Случай 1:

select ert.* 
from eval_rep_track ert
inner join 
(
        select erp.evaluation_fk, erp.report_type, LTRIM(erp.assign_group_id, '/site/') course_name
        from eval_report_dup@prod erp
        inner join eval_report er
        on er.id = erp.id
        where erp.status='queue'
        and er.status='done'
) cat

on ert.eval_id || '.' || ert.report_type || '.' || ert.course_name = cat.evaluation_fk || '.' || cat.report_type || '.' || cat.course_name;

ИЛИ ЖЕ

Случай 2:

select ert.* 
from eval_rep_track ert
inner join 
(
        select erp.evaluation_fk, erp.report_type, LTRIM(erp.assign_group_id, '/site/') course_name
        from eval_report_dup@prod erp
        inner join eval_report er
        on er.id = erp.id
        where erp.status='queue'
        and er.status='done'
) cat
on ert.eval_id = cat.evaluation_fk  
and ert.report_type = cat.report_type  
and ert.course_name = cat.course_name;

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

eval_id is NUMBER, тип_отчета и имя_курса VARCHAR2,

Исходя из использованного разработчика, случай 1 имеет следующую статистику: [SELECT - 3077 строк, 0,048 секунд] Получен набор результатов... Выполнено 1 операторов, 3077 затронуто строк, время выполнения / выборки: 0,048/0,236 сек [1 успешно, 0 предупреждений, 0 ошибок]

в то время как случай 2: [SELECT - 3077 строк, 0,019 с] Извлечен набор результатов... Выполнено 1 операторов, 3077 строк затронуто, время выполнения / выборки: 0,019/0,194 с [1 успешно, 0 предупреждения, 0 ошибок]

Результаты показывают, что случай 2 быстрее. Будет ли это универсальным для любой платформы (ide, developer) и базы данных? Это зависит от типа данных или конкатенация всегда дорогая? На самом деле мне не нужен результат конкатенации. Благодарю.

Answer 1

Я думаю, что версия с конкатенацией будет практически всегда медленнее.

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

И даже если столбцы не проиндексированы, база данных может выполнять сравнения более эффективно. Он сравнивает одну пару столбцов за раз и может остановиться, как только произойдет сбой одного из этих сравнений. При использовании конкатенации необходимо сначала объединить все столбцы в обеих строках, а затем выполнить сравнение строк.

Наконец, если какой-либо из столбцов является числовым, для конкатенации потребуется дополнительный шаг преобразования числа в строку.

Answer 2

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

Для любого конкретного запроса вы, вероятно, можете написать наиболее правильный запрос, используя конкатенацию. Но вы почти наверняка представите тонкие ошибки, которые будут кусать вас, когда вы получите данные, которые вы не ожидаете. В этом случае, как только ваши столбцы содержат точку, есть вероятность, что вы неверно сопоставите данные ('a.b' || '.' || null = 'a' || '.' || 'b.'). В других случаях у вас будут другие тонкие проблемы - даты и числа могут быть неявно преобразованы в строки с использованием различных настроек уровня сеанса, которые могут давать разные результаты (ваш NLS_DATE_FORMAT может содержать компонент времени или нет, поэтому ваше объединенное значение может включать или не включать сравнение времени). Если вы будете объединять столбцы часто, вы получите множество запросов с очень тонкими ошибками, основанными на данных в таблице и пользователях, выполняющих код. Это ужасно с точки зрения обслуживания и поддержки. Производительность должна быть не более чем второстепенной задачей.

С точки зрения производительности правильное соединение почти наверняка превзойдет подход конкатенации. Оптимизатор сможет учитывать нормальные индексы для различных столбцов, которые являются частью объединения, при создании плана запроса при правильном объединении. Если вы объединяете значения, в лучшем случае Oracle может выполнить полное сканирование обычного индекса, чтобы собрать все данные, которые должны быть объединены вместе. Но это, вероятно, будет гораздо менее эффективно (особенно если у вас есть более нескольких тысяч строк).

Возможно ли теоретически, что конкатенационный подход будет более эффективным для какого-то запроса? Конечно. Разработчик-садист может создать индекс на основе функций для связанного результата, избежать создания индекса для отдельных столбцов и создать тестовый пример, в котором подход с конкатенацией будет более эффективным. Однако это легко исправить, создав соответствующий соответствующий индекс (или индексы) для основных столбцов. Возможно ли, что конкатенация будет более эффективной для какого-либо запроса, поскольку она не позволяет оптимизатору использовать индекс, который он иначе хочет использовать? Конечно. Но это почти наверняка указывает на то, что у вас есть проблема с настройками оптимизатора или статистикой, которая должна быть устранена, вместо того, чтобы бросать бинты на проблему.

Answer 3

Это зависит от индексов на таблицах. Обычно индексы определяются списком столбцов, но не объединением столбцов (как выражением), поэтому, как правило, вторая версия быстрее с индексацией, как обычно.

Тем не менее, DBA может (по любой причине, вероятно, опьянение или безумие) решить создать индекс на объединение столбцов. В этом случае первая версия оператора может использовать индекс, а вторая версия - нет.