查询大量数据进行网页展示

Question

我是 Oracle 新手（了解一些基本 SQL），但任务不知何故分配给了我。

我的任务是优化查询。它连接一些大表（查询比较耗时）并将结果返回到网页（可以使用分页）。

我的问题是我应该朝什么方向看？如果能提供链接就更好了。

以下是一些详细信息：

采用的优化方法

index on dayNum, timeNum, entityID columns  

行数：

• time_seq: 100,000
• Indicator_set: 50,000,000
• tity amount: 1,000

查询：

select * 
  from time_seq seq,  
       (  
        select entityID, 
               dayNum,
               timeNum,
               sum(ind1) sum_ind1, 
               avg(ind2) avg_ind2, 
               max(ind3) max_ind3  
          from indicator_set  
         group by entityID, dayNum, timeNum 
         -- the dayNum timeNum are grouped to the time sequence standard 
         -- in time_seq table  
        ) sum  
where seq.entityID=sum.entityID  
  and seq.dayNum=sum.dayNum  
  and seq.timeNum=sum.timeNum  

表 time_seq

entityID, dayNum, timeNum
--------------------------------
object1, 20110818, 220000  
object1, 20110818, 223000  
object1, 20100818, 230000  
object1, 20110819, 220000  
object1, 20110819, 223000  
object1, 20100819, 230000 
object2, 20110818, 220000  
object2, 20110818, 223000   
object2, 20100818, 230000  
object2, 20110819, 220000  
object2, 20110819, 223000  
object2, 20100819, 230000  

表格indicator_set

entityID, dayNum, timeNUm, ind1, ind2, ind3
--------------------------------------------
object1, 20110818, 220000, 23,34,23
object1, 20110818, 220500, 23,54,543
object1, 20110818, 220530, 23,54,543
object1, 20110818, 220610, 23,54,543
object1, 20110818, 222900, 23,54,543
...

PS：抱歉，我没有提供表格和查询的确切格式/详细信息。就是太复杂了。

干杯~

Answer 1

您正在如何处理此查询的结果？假设查询返回大约 100,000 行（我假设联接不会消除 TIME_SEQ 中的大量行），则该查询没有多大意义。您通过网页向用户返回 100,000 个无序行 - 人类不可能翻阅 100,000 行，并且以无序方式呈现这些行（或在中间层）。

为了填充网页而尝试动态聚合 5000 万行数据几乎没有意义。这本质上是一个缓慢的操作——如果用户期望在一两秒内得到响应，那么您就没有时间读取和聚合 5000 万行。您可以在 INDICATOR_SET 上创建一个物化视图，用于预聚合数据。这将使插入和更新速度变慢，但会加快查询速度。

当然，即使您将 5000 万行预先聚合为 100,000 行，您仍然尝试连接两个 100,000 行表并向客户端发送 100,000 个无序行，这不太合理。您可以添加一个 ORDER BY 子句，以便行按合理的顺序排列，但这通常会增加查询时间，因为您引入了额外的排序。根据您想要排序的内容，可能有一些方法可以优化它。然而，我仍然会回到一个根本问题，即向人类客户端返回 100,000 行从根本上来说不是正确的方法。

Answer 2

在子查询中链接indicator_set 和time_seq 表是否会限制在大型indicator_set 表中分组的行数？

SELECT *
  FROM time_seq seq,
       (  SELECT ind.entityid,
                 ind.dayNum,
                 ind.timeNum,
                 SUM( ind.ind1 ),
                 AVG( ind.ind2 ),
                 MAX( ind.ind3 )
            FROM indicator_set ind,
                 time_seq ts
           WHERE ind.entity_id = ts.entityid
             AND ind.daynum    = ts.daynum
             AND ind.timenum   = ts.timenum
        GROUP BY ind.entityid,
                 ind.daynum,
                 ind.timenum) SUM
 WHERE seq.entityid = SUM.entityid
   AND seq.daynum   = SUM.daynum
   AND seq.timenum  = SUM.timenum

不过，外部查询仍将确保只从 time_seq 表中返回记录。

这有帮助吗？

编辑：顺便说一下，我会使用比 SUM 更好的表别名，因为它是一个 Oracle 函数。

Answer 3

您可以根据查询创建物化视图：

create materialized view mv as
select entityID, 
               dayNum,
               timeNum,
               sum(ind1) sum_ind1, 
               avg(ind2) avg_ind2, 
               max(ind3) max_ind3  
          from indicator_set  
         group by entityID, dayNum, timeNum;

该表会更小。您也可以在此物化视图中创建索引。您的选择将是这样的：

select * 
  from time_seq seq,  
      mv sum  
where seq.entityID=sum.entityID  
  and seq.dayNum=sum.dayNum  
  and seq.timeNum=sum.timeNum;

您的物化视图可以在快速模式、正常模式等下刷新...如果您想要更多信息，请看一下：
http://download.oracle.com/docs/cd /B13789_01/server.101/b10759/statements_6002.htm

http://orafaq.com/wiki/Oracle_Materialized_Views