在多列上的连接条件与在串联列上的单连接? [英] Join conditions on multiple columns versus single join on concatenated columns?

问题描述

我观察到有两种方法可以在多个表上实现目标.结果集中的一列将被更新，并且可能需要速度.结果集可以通过以下方式获得:

I observe there are two ways to achieve an objective on multiple tables. A column in the resultset will be updated and speed may be a requirement. The resultset is obtained either by:

情况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;

OR

情况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;

两者给出的结果相同，只是连接条件有所不同.哪个会更快地运行/执行?

with both giving the same result, with only the join condition varying. Which will run/exec faster?

eval_id是NUMBER，report_type和course_name是VARCHAR2.

eval_id is NUMBER, report_type and course_name are VARCHAR2.

案例1的开发者具有以下统计信息:[SELECT-3077行，0.048秒]获取了结果集 ...已执行1条语句，影响3077行，执行/获取时间:0.048/0.236秒[1成功，0警告，0错误]

From the developer used, case 1 has the following stats: [SELECT - 3077 row(s), 0.048 secs] Result set fetched ... 1 statement(s) executed, 3077 row(s) affected, exec/fetch time: 0.048/0.236 sec [1 successful, 0 warnings, 0 errors]

情况2:[SELECT-3077行，0.019秒]获取了结果集 ...执行了1条语句，影响了3077行，执行/获取时间:0.019/0.194秒[1成功，0警告，0错误]

while case 2: [SELECT - 3077 row(s), 0.019 secs] Result set fetched ... 1 statement(s) executed, 3077 row(s) affected, exec/fetch time: 0.019/0.194 sec [1 successful, 0 warnings, 0 errors]

结果表明情况2更快.这会在任何平台(例如开发人员)和数据库中通用吗?这是依赖于数据类型还是串联总是很昂贵?我实际上不需要连接的结果.谢谢.

The results suggest case 2 is faster. Will this be universal across any platform (ide, developer) and database? Is this dependent on the data-type or is concatenation always expensive? I don't actually need the result of the concatenation. Thanks.

推荐答案

我认为带有串联的版本实际上总是比较慢.

I think the version with concatenation will practically always be slower.

如果您要单独比较的任何列都有索引，则数据库通常将能够使用索引来优化联接.比较串联时，它必须执行全表扫描，因为计算结果不会出现在索引中.

If any of the columns you're comparing individually have indexes, the database will normally be able to use the indexes to optimize the join. When you compare concatenations, it has to perform full table scans, because the result of a calculation won't be in the index.

即使没有索引列，数据库仍然可以更有效地执行比较.它一次比较一对列，并且一旦其中一个比较失败就可以停止.使用串联时，它必须首先合并两行中的所有列，然后进行字符串比较.

And even if the columns are not indexed, the database can still perform the comparisons more efficiently. It compares one pair of columns at a time, and can stop as soon as one of those comparisons fails. When using the concatenation, it has to first combine all the columns, in both rows, then do a string comparison.

最后，如果任何列为数字，则串联将需要将数字转换为字符串的附加步骤.

Finally, if any of the columns are numeric, the concatenation will require the additional step of converting the number to a string.

这篇关于在多列上的连接条件与在串联列上的单连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！