优化Oracle查询

大数据集上的CUBE操作确实非常昂贵，因此您需要检查是否真的需要内部查询中的所有数据。因为我看到您在内部进行COUNT，然后在外部查询中进行计数求和。因此，换句话说，请给我所有组合A1-A8（-A7）的A7行数。然后仅获取由WHERE子句过滤的所选组合的SUM。我们可以通过限制某些列本身的CUBE来确保优化此效果，但是到目前为止，我已经注意到的非常明显的事情如下。

如果您在下面的查询中使用并具有正确的索引o Table1和Table_reject，则两个查询都可以利用索引并减少需要连接和进一步处理的数据集。我不确定100％，但是可以进行部分多维数据集处理，需要检查一下。

聚集索引-> A1上的Table1需要，而NAME上的Table_Reject需要聚集索引。

非聚集索引-> A3，A9上的Table1需要和B3，B2上的Table_reject需要

    SELECT qry1.
    (
        SELECT  A1, A2,A3,A4,A5,A6,A7,A8
        FROM table1
        WHERE A8 >= NEXT_DAY ( trunc(to_date('17/09/2013 12:00:00','dd/mm/yyyy hh24:mi:ss')) ,'SUN' )
    )qry1
    LEFT JOIN
    (
        select B3,B2,ID
        from table_reject 
        where name = 'smith'
    )qry2
        ON qry1.A3 = qry2.B3 and qry1.A9=qry2.B2
    WHERE qry2.ID IS NULL

编辑1：

我试图找出如果对所有列执行此操作，或者仅对结果集中需要的列执行操作，则CUBE运算符结果会有什么不同。我发现的是CUBE函数的工作方式，您无需在所有列上都执行CUBE。因为最后您只关心CUBE生成的组合，其中A1和A8不为NULL。尝试此链接，然后查看输出。

在此处输入链接说明

查询1和Query2只是用于比较CUBE结果集的大多数内部查询。

Query3和Query4是您尝试使用的同一查询，并且在两种情况下，您看到的结果都是相同的。

    DECLARE @NEXT_DAY DATE = NEXT_DAY ( trunc(to_date('17/09/2013 12:00:00','dd/mm/yyyy hh24:mi:ss')) ,'SUN' )

    SELECT distinct A1 ,sum(total) as sum_total  FROM 
    (   
       SELECT  A1,COUNT(A7) AS total,A8
       FROM (
                select a.a1,a.a7,a.a8  
                from table1 a
                left join  (select * from table_reject where name = 'smith') b 
                on A.A3 = B.B3 and A.A9 =B.B2
                where B.ID is null
            ) t1
       WHERE A8 >= @NEXT_DAY
       GROUP BY 
       CUBE(A1,A8)
    )INN
    WHERE   INN.A1 IS NOT NULL AND
            INN.A8 IS NOT NULL
    GROUP BY A1
    ORDER BY sum_total DESC ;

编辑3

正如我在评论中提到的，这是Round3更新。我无法更改评论，但我的意思是Edit3而不是Round3。

您查询中的新变化也将WHERE A8 >= @NEXT_DAY条件添加到最里面的左侧联接选择where A8 >= @NEXT_DAY AND B.ID is null中。极大地改善了选择。

在您的最后一条评论中，您提到查询需要30-35秒的时间，并且随着您更改A8的值而不断增加。现在，在执行时间上，您没有提到结果集中有多少数据。为什么这很重要？因为如果我的查询返回的最终结果集为500万行，这将花费90％的时间将数据拖放到UI上，或将文件输出为您正在使用的任何输出方法。但是应该衡量实际的性能，查询开始多久才开始给出前几行。因为到那时Optimizer已经确定了执行计划，而DB正在执行该计划。但是我同意，如果查询返回100行并花费10秒，那么执行计划可能会出错。

为了演示我所做的是我创建了虚拟数据。并针对它进行查询。我的表Test_CubeData中有9M行，具有与您为Table1解释的相同的列号和数据类型。我有第二个表Table_Reject，其中有80K行，其中有列数以及我从查询中得出的数据类型。测试此表的最极端；名称列只有一个值“ smith”，并且所有80K行的ID均为null。因此会影响内部左连接结果的列值为B2和B3。

在这些测试中，我在两个表上都没有任何索引。两者都是堆。您会看到结果仍在几秒钟之内，并且结果集中的数据范围可以接受。随着我的结果数据集的增加，完成时间也会增加。如果我创建解释索引，那么它将为我提供所有这些测试案例的索引搜索操作。但在某些时候索引也会耗尽，并变为索引扫描。一个肯定的例子是，如果我的A8列的过滤器值是该列中的最小日期值。在这种情况下，Optimizer将看到所有9M行都需要参与内部选择和CUBE，并且大量数据将在内存中进行处理。这是预期的。另一方面，让我们看一下查询的另一个示例。我在A8列中具有唯一的32873值，并且这些值几乎平均分布在9M行中。因此，每个A8值有260至300行。现在，如果我执行查询对于查询执行时间之间的任何单个值，最小值，最大值或任何事物，都不应更改。

注意下面每张图像中突出显示的文本，这些文本指示选择了A8过滤器的值，仅在选择列表中的重要列中使用*，在内部左联接查询中添加了A8过滤器，执行计划显示了两个表上的TableScan操作，查询执行时间（以秒为单位），以及查询返回的总行数。

我希望这将清除对查询性能的一些疑问，并帮助您设定正确的期望。

**Table Row Counts**

**TableScan_InnerLeftJoin**

**TableScan_FullQuery_248Rows**

**TableScan_FullQuery_5K**

**TableScan_FullQuery_56K**

**TableScan_FullQuery_480k**