时间戳

首页 > 解决方案 > 使用函数来轻松编写 SQL 真的很慢

sql - 使用函数来轻松编写 SQL 真的很慢

问题描述

create or replace FUNCTION FUNCTION_X
(
  N_STRING IN VARCHAR2 
) RETURN VARCHAR2 AS 
BEGIN
  RETURN UPPER(translate(N_STRING, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'));
END FUNCTION_X;

SELECT 大约需要 5 秒（80k + 行）

SELECT TABLE_A.STRING_X
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.ID = TABLE_A.IDTB
WHERE
   UPPER(UPPER(translate(TABLEB.STRING_X, 
   'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
   =
   UPPER(translate(TABLEB.N_STRING, 
   'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')

使用函数需要超过 3 分钟（80k + 行）

SELECT TABLE_A.STRING_X
    FROM TABLE_A
    INNER JOIN TABLE_B ON TABLE_B.ID = TABLE_A.IDTB
    WHERE
       FUNCTION_X(TABLE_A.STRING_X) = FUNCTION_X(TABLE_B.N_STRING)

我不知道是什么让它如此沉重。

标签： sqloracle

解决方案

如果您的第一个查询，查询中的UPPER(UPPER(translate(...)))内联只需要 5 秒并且表很大，我会看看您是否有一个基于函数的索引，在一个或两个表上都有这些函数。

您可能知道，索引存储数据的排序版本，以便可以快速找到行。但它们仅在您搜索在索引中排序的数据时才有用。（想想一本书中的索引，其中关键字按字母顺序排序——对于搜索特定单词很有用，对于查找以字母“r”结尾的单词的引用不太有用）。

如果有一个基于函数的索引UPPER(UPPER(translate(...)))有助于您的原始查询，那么当您的查询指定时，您将失去好处FUNCTION_X(...)。Oracle 不够聪明，无法意识到它们是相同的功能。您需要根据您在查询中实际使用的表达式创建基于函数的索引 - 即 on FUNCTION_X(...)。

此外，您可以通过告诉 Oracle 您的函数是确定性的（即，始终为相同的输入返回相同的值）并打算在 SQL 查询中使用来提高性能。因此，除了基于函数的索引之外，更好的函数定义是：

create or replace FUNCTION FUNCTION_X
(
  N_STRING IN VARCHAR2 
) RETURN VARCHAR2 
DETERMINISTIC -- add this
AS 
PRAGMA UDF;   -- add this too
BEGIN
  RETURN UPPER(translate(N_STRING, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü','ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'));
END FUNCTION_X;

推荐阅读