Oracle -使关联查询更高效

mcvgt66p 于 2023-08-03 发布在 Oracle

关注(0)|答案(2)|浏览(133)

Oracle RDBMS：18C
这个主题是我上一个主题的后续：
Oracle - Where clause having null value condition does not give corresponding result
因为最后一个问题已经解决了（以获得正确的结果），所以我关闭了它。
这是另一个问题，关于性能，所以我提出了这个新问题。
为了让我的问题更清楚，请先阅读上面的链接。

穿刺者的答案最后一个职位使用解码正是我所需要的。

但是后来我发现查询效率不是很高，因为我的外部表比内部表大得多，这可能是导致查询速度变慢的原因（与in操作符相比，请参见下面的速度测试表）
由于专利问题，我不能上传我的自然工作表。
话虽如此，让我采取下面的小样本表来解释我的实际工作表：
db<>fiddle的

With Desired_Column_Combination as (
  Select 'A1' ColA,'B2' ColB,'C2' ColC from dual union all 
  Select 'A1' ColA,'B1' ColB,'' ColC from dual union all --Should've 3 records
  Select 'A2' ColA,'' ColB,'C3' ColC from dual union all --Should've 2 records
  Select 'A3' ColA,'B1' ColB,'C1' ColC from dual
)
select * 
from SampleTable S
where exists (
  select 1 from Desired_Column_Combination d
  where decode(s.ColA, d.ColA, 1, 0) = 1
    and decode(s.ColB, d.ColB, 1, 0) = 1
    and decode(s.ColC, d.ColC, 1, 0) = 1
);

字符串
在我的工作台上：
<1>. Desired_Column_Combination是用户的选择，并且条件的量可以高达70选择…from....我使用VBA根据用户的选择制作了一个动态的Desired_Column_Combination表。
<2>Desired_Column_Combination中的所有条件都100%来自SampleTable，因此SampleTable中至少有1条记录与Desired_Column_Combination表中的条件相对应。换句话说，Desired_Column_Combination表是SampleTable的子集。
<3>SampleTable具有关于ColA、B和C的索引。
<4>.我只有查询表权限，不能删除/更新/更改表。
然后我试着理解exists运算符，希望自己解决。
下面是几个小时的谷歌搜索后的一个简短结论：
通常，如果外部表比内部表大得多，则使用in操作符更好。另一方面，如果内部表包含的行比外部表多，并且内部表有索引，则使用exists比in快。
然后我在工作台上做了一些速度测试：x1c 0d1x的数据
测试结果似乎与我的google结论相同，因为我的外部表SampleTable比内部表Desired_Column_Combination大得多，所以exists运算符的性能比in运算符差。
我的问题是：
<1>.有没有一种方法可以使用in运算符，同时不过多地扩展where-clause？（据我所知，为了在空值查询下使用in，我们需要在当Desired_Column_Combination有空值条件时，在where-clause中指定每一列为空）
<2>.如果exists是唯一的答案，我们是否可以重写查询，使外部表变为内部表，反之亦然，以使exists运行得更快？

oracle

来源：https://stackoverflow.com/questions/76794714/oracle-make-a-correlated-query-more-efficient

2条答案

按热度按时间

v09wglhw1#

DECODE解决方案的问题是它不能被索引，因为函数的输入来自两个不同的表。如果它只是简单的NULL处理，并且您想要索引的好处，我建议使用简单的NVL函数和匹配的索引。

select * 
from SampleTable S
where exists (
  select 1 from Desired_Column_Combination d
  where NVL(s.ColA,' ') = NVL(d.ColA,' ')
    and NVL(s.ColB,' ') = NVL(d.ColB,' ')
    and NVL(s.ColC,' ') = NVL(d.ColC,' ')
);

字符串
现在是一个匹配的索引，最好包括所有三列：

CREATE OR REPLACE INDEX index1 ON Desired_Column_Combination 
(NVL(colA,' '),NVL(colB,' '),NVL(colC,' '))

型
仅供参考，Oracle默认NULL逻辑的原因是NULL不打算成为有助于行标识的有意义的值。它的意思是“未知”或没有任何意义-它表示 * 缺少 * 任何有意义的东西，所以通常情况下，如果连接键列中有一个NULL值，你就不想找到子行。这样一行的标识将是未定义的、不可知的和不确定的。当然，也有例外（例如：当元数据是键的一部分时，比如在类型2历史表中，您不想使用虚构的结束日期），因此Oracle允许它，但您必须做一些额外的工作才能使它们被视为有意义的键值，这些键值是行标识的一部分。NVL是最简单的方法，但是将函数应用于列会抛出索引使用，除非您创建相应的基于函数的索引来匹配。但通常当您发现自己处于这种情况时，您的数据模型有问题，其中NULL被用于它不打算用于的事情。

赞(0）回复(0）举报 2023-08-03

zpjtge222#

您可以使用AND和OR代替DECODE：

With Desired_Column_Combination (colA, ColB, colC) as (
  Select 'A1', 'B2', 'C2' from dual union all 
  Select 'A1', 'B1', NULL from dual union all
  Select 'A2', NULL, 'C3' from dual union all
  Select 'A3', 'B1', 'C1' from dual
)
SELECT * 
FROM   SampleTable S
WHERE  EXISTS (
  SELECT 1
  FROM   Desired_Column_Combination d
  WHERE  (s.ColA = d.ColA OR (s.colA IS NULL AND d.colA IS NULL))
  AND    (s.ColB = d.ColB OR (s.colB IS NULL AND d.colB IS NULL))
  AND    (s.ColC = d.ColC OR (s.colC IS NULL AND d.colC IS NULL))
);

字符串
或者，因为你有一个静态的选项列表，你不需要使用IN，EXISTS或join，而可以在主查询上使用WHERE过滤器：

SELECT * 
FROM   SampleTable S
WHERE  (colA, colB, colC) IN (('A1', 'B2', 'C2'), ('A3', 'B1', 'C1'))
OR     (colB IS NULL AND (colA, colC) IN (('A2', 'C3')))
OR     (colC IS NULL AND (colA, colB) IN (('A1', 'B1')));

型

赞(0）回复(0）举报 2023-08-03

我来回答

Oracle -使关联查询更高效

2条答案

相关问题

热门标签

最新问答