Oracle 11g—对数百万行执行分析函数

Question

我的应用程序允许用户收集测量数据作为实验的一部分，并且需要能够报告所进行的所有测量。

下面是我的表格的简化版本：

CREATE TABLE EXPERIMENTS(
  EXPT_ID INT,
  EXPT_NAME VARCHAR2(255 CHAR)
);
CREATE TABLE USERS(
  USER_ID INT,
  EXPT_ID INT
);
CREATE TABLE SAMPLES(
  SAMPLE_ID INT,
  USER_ID INT
);
CREATE TABLE MEASUREMENTS(
  MEASUREMENT_ID INT,
  SAMPLE_ID INT,
  MEASUREMENT_PARAMETER_1 NUMBER,
  MEASUREMENT_PARAMETER_2 NUMBER
);

在我的数据库中有2000个实验，每个实验有18个用户。每个用户有6个样本要测量，每个样本要进行100次测量。

这意味着数据库中当前存储了2000*18*6*100=21600000个测量值。

我正试图编写一个查询，为每个用户获取测量参数1和2的AVG（），这将返回大约36000行。

我的查询速度非常慢——我已经让它运行了30多分钟，但它什么都没有回来。我的问题是：有没有一种有效的方法来获得平均值？实际上有可能在合理的时间内，比如2分钟内，得到这么多数据的结果吗？还是我不切实际？

这是（再次简化的版本）我的查询：

SELECT 
    E.EXPT_ID,
    U.USER_ID,
    AVG(MEASUREMENT_PARAMETER_1) AS AVG_1,
    AVG(MEASUREMENT_PARAMETER_2) AS AVG_2
FROM 
    EXPERIMENTS E, 
    USERS U, 
    SAMPLES S,
    MEASUREMENTS M
WHERE
 U.EXPT_ID = E.EXPT_ID
 AND S.USER_ID = U.USER_ID
 AND M.SAMPLE_ID = S.SAMPLE_ID
GROUP BY E.EXPT_ID, U.USER_ID

这将为每个expt_id/user_id组合和2个测量参数的平均值返回一行。

Answer 1

对于您的查询，在任何情况下，DBMS都需要读取完整的测量表。到目前为止，这是要读取的数据中最大的部分，也是如果查询优化得很好（稍后会讨论），则需要花费最多时间的部分。这意味着查询的最小运行时间是读取存储它的完整测量表所需的时间。您可以通过检查有多少数据（以MB或GB为单位），并检查从硬盘读取这些数据所需的时间（或表的存储位置）来获得粗略估计。如果您的查询运行速度慢了5倍或更多，您可以确定有优化的空间。

关于如何优化oracle查询，有大量的信息（教程、可能非常宝贵的个人提示和一般实践列表）。你不会很快了解所有这些信息。但是，如果您提供查询的执行计划（oracle的查询优化器认为这是实现查询的最佳方式），我们将能够发现可以优化的步骤并提出解决方案。