提高性能

Question

我不太了解SQL中的快捷方式，所以我希望利用这里的智慧来帮助加快我正在使用的查询速度。我目前使用的是Oracle 8i。

我有一个问题：

SELECT 
    NAME_CODE, ACTIVITY_CODE, GPS_CODE 
FROM
    (SELECT 
         a.NAME_CODE, b.ACTIVITY_CODE, a.GPS_CODE, 
         ROW_NUMBER() OVER (PARTITION BY a.GPS_DATE ORDER BY b.ACTIVITY_DATE DESC) AS RN
     FROM GPS_TABLE a, ACTIVITY_TABLE b
     WHERE a.NAME_CODE = b.NAME_CODE
       AND a.GPS_DATE >= b.ACTIVITY_DATE 
       AND TRUNC(a.GPS_DATE) > TRUNC(SYSDATE) - 2)
WHERE 
    RN = 1

这需要大约7分钟或者10秒的时间来运行。

现在GPS_TABLE现在是6.586.429行，并且随着新的GPS坐标被输入到系统中，它还在继续增长，每天它在6列中增长大约8.000行。

ACTIVITY_TABLE当前为1.989.093行，并且随着新活动被放入系统中而继续增长，每天在31列中增长约2.000行。

总而言之，所有这些都不是小表，我知道运行这个或类似的查询总是会有一段时间。正如你所看到的，我已经将它限制在最后两天的数据上，但是任何可以加速它的东西都将不胜感激。

Answer 1

你最强的过滤器似乎是GPS_TABLE最后两天的过滤器。它应该将GPS_TABLE过滤到大约15k行。因此，改进的最佳候选者之一是列GPS_DATE上的索引。

您会发现您的过滤器TRUNC(a.GPS_DATE) > TRUNC(SYSDATE) - 2等同于a.GPS_DATE > TRUNC(SYSDATE) - 2，因此，如果您更改查询，列上的一个简单索引将起作用。如果您不能更改它，可以在TRUNC(GPS_DATE)上添加一个基于函数的索引。

一旦建立了索引，我们就需要访问ACTIVITY_TABLE中的行。您的连接的问题是，我们将获得所有旧的活动，因此表的一大部分。这意味着原样的连接对于索引扫描将是无效的。

我建议您在ACTIVITY_TABLE(name_code, activity_date DESC)上定义一个索引和一个PL/SQL函数，它将在最少的工作量中检索最后一个活动，特别是使用这个索引：

CREATE OR REPLACE FUNCTION get_last_activity (p_name_code VARCHAR2, 
                                              p_gps_date DATE) 
RETURN ACTIVITY_TABLE.activity_code%type IS
   l_result ACTIVITY_TABLE.activity_code%type;
BEGIN
   SELECT activity_code
     INTO l_result
     FROM (SELECT activity_code
             FROM activity_table
            WHERE name_code = p_name_code
              AND activity_date <= p_gps_date
            ORDER BY activity_date DESC)
     WHERE ROWNUM = 1;
   RETURN l_result;
END;

修改您的查询以使用此函数：

SELECT a.NAME_CODE,
       a.GPS_CODE,
       get_last_activity(a.name_code, a.gps_date)
  FROM GPS_TABLE a
 WHERE trunc(a.GPS_DATE) > trunc(sysdate) - 2

Answer 2

优化SQL查询通常通过以下方式完成：

1. 添加一些索引
2. 尝试以不同的方式获取相同的信息

因此，首先为ACTIVITY_DATE添加一个索引，也许还会添加一些在条件中使用的其他字段。