Oracle SQL性能

Question

我有2个这样的SQL：

1.

select * from Customer_source s
where not exist 
(Select 1 from Customer_target t
 where s.CST_ID = t.CST_ID and ( s.NAME <> t.NAME
     or s.GENDER <> t.GENDER
     or .. or ) 
)

1. ​

select * from Customer_source s
where not exist (Select 1 from Customer_target t
 where s.CST_ID = t.CST_ID and ( s.NAME || s.GENDER || ... <> t.NAME || t.GENDER || ... ) 
)

告诉我SQL是最好的性能

Answer 1

两人做的事情不一样。例如，只考虑两个列，其值如下：

 Name    Gender
 ABC       M
 ABC      NULL

字符串连接将是'ABCM' <> 'ABC' --它们不相等，因此计算结果为true。

相等的方法是'ABC' <> 'ABC' OR 'M' <> NULL。这将计算为FALSE或NULL，这是FALSE。这里是db<>fiddle。

还有其他一些不太可能产生相同值的情况。例如：

ABCfe    male
ABC      female

还有其他的情况，因为值转换为字符串。例如，当使用date时，会删除||的时间组件。

所以，你应该选择符合你意愿的版本。否则，您可能应该使用第一个版本，因为它对每种类型都使用了本机比较--这似乎更适合您想要做的事情。

我认为这里的语义比性能更重要。也就是说，在尝试优化之前，选择能够准确反映您的意图的代码。

Answer 2

正如注释中提到的那样，第二种方法是危险的，因为它连接了值(a,b将与ab, null匹配)，如果有索引，也不会使用索引。

因此，根据我的经验，第一个查询具有良好的性能，并且还将以正确的逻辑获取记录。

Answer 3

TLDR；#1最适合小型OLTP查询，#2最好用于大型数据仓库查询。

如果对相关列进行索引，且查询只处理一小部分数据，则与OR条件的连接更快。但是，如果这些列没有索引，或者查询处理了很大百分比的数据，那么连接连接列的速度就会更快。

性能比较可以归结为典型的数据库性能选择--索引读取和嵌套循环联接对一小部分行更好，而完整表扫描和散列连接对于较大百分比的行更好。

OR条件是可行的--它们是简单的比较条件，可以通过在一个或多个索引中遍历有序数据来快速查找。但是，不能在散列联接中使用OR条件-- Oracle散列联接一次只能比较两个值。

串连列不可使用--合并的值不存储在索引中，因此Oracle无法遍历索引以查找相关的值。但是，级联列只需要一个比较，所以它们可以用于散列反连接。

下面的测试用例处理所有数据，并显示比OR版本运行得更快的级联版本。如果删除索引，OR版本的执行情况会更糟。

--Create tables, insert 1M sample rows, created indexes, and gather optimizer statistics.
create table customer_source(cst_id number, name varchar2(100), gender varchar2(1));
create table customer_target(cst_id number, name varchar2(100), gender varchar2(1));

insert into customer_source select level, rpad(level, 10, 'A'), 'F' from dual connect by level <= 1000000;
insert into customer_target select level, rpad(level, 10, 'A'), 'F' from dual connect by level <= 1000000;

create index customer_source_idx1 on customer_source(cst_id);
create index customer_source_idx2 on customer_source(name);
create index customer_source_idx3 on customer_source(gender);

create index customer_target_idx1 on customer_target(cst_id);
create index customer_target_idx2 on customer_target(name);
create index customer_target_idx3 on customer_target(gender);

begin
    dbms_stats.gather_table_stats(user, 'customer_source');
    dbms_stats.gather_table_stats(user, 'customer_target');
end;
/

--#1: OR version.
--The explain plan shows a "FILTER" operation that re-reads an index repeatedly.
explain plan for
select * from Customer_source s
where not exists
(Select 1 from Customer_target t
 where s.CST_ID = t.CST_ID and ( s.NAME <> t.NAME
    or s.GENDER <> t.GENDER) 
);

select * from table(dbms_xplan.display);

--#2: Concatenation version.
--The explain plan shows a "HASH JOIN ANTI" operation.
explain plan for
select * from Customer_source s
where not exists (Select 1 from Customer_target t
 where s.CST_ID = t.CST_ID and ( s.NAME || s.GENDER <> t.NAME || t.GENDER) 
);

select * from table(dbms_xplan.display);

通常，“最佳”查询是运行速度最快的查询。Concatenating值很难看，正如其他人指出的那样，如果您的列是空的，甚至可能无法工作。但是在数据仓库中，为了性能起见，编写奇怪的条件来启用散列联接并不少见。虽然您可以自己对查询进行基准测试，但是了解这些概念是很好的，这样您就知道为什么要编写奇怪的查询了。