奇怪。请帮忙分析一下为什么Rollup比简单SUM速度还快。

simplelife

是Try几个Oracle分析函数的时候发现使用rollup的语句比不使用的简单sum还要快些。搞不懂怎么回事，特别上来请教一下大家。

我用的是HPUX上架设的Oracle 8174
有一个比较大的Table，共170多万行记录。

使用如下语句RUN40次平均用时4.322525秒

1. 
   
2. SELECT NVL(segment,'SUMMARY') AS segment,SUM(qty) FROM
   
3. tablename
   
4. GROUP BY ROLLUP(NVL(segment,SUMMARY))
   
5. ---------------------------------------------------------------------------------
   
6. FA        5314660
   
7. Item 2        550
   
8. PACKAGE        2606406
   
9. PCBA        4980446
   
10. SMT        3189386
    
11.         16091448
    
12. ---------------------------------------------------------------------------------
    

复制代码

使用如下语句在DB Server 的 Loading非常低的时候，RUN40次平均用时6.3488秒
在正常Loading的时候在11秒到25秒之间

1. 
   
2. SELECT segment,SUM(qty) FROM
   
3. tablename
   
4. GROUP BY segment
   
5. ---------------------------------------------------------------------------------
   
6. FA        5314660
   
7. Item 2        550
   
8. PACKAGE        2606406
   
9. PCBA        4980446
   
10. SMT        3189386
    
11. ---------------------------------------------------------------------------------
    

复制代码

我不明白相同的结果使用rollup还多做了运算，为什么速度反而快这么多？

想请教一下看各位有什么看法。谢谢

附上执行计划如下:

1. 
   
2. SQL>; set autot on
   
3. SQL>; SELECT NVL(segment,'SUMMARY') AS segment,SUM(qty) FROM tablename
   
4.   2  GROUP BY ROLLUP(NVL(segment,'SUMMARY'));
   
5. 
   
6. SEGMENT         SUM(QTY)                                                      
   
7. --------------- ----------                                                      
   
8. FA                 5314660                                                      
   
9. Item 2                 550                                                      
   
10. PACKAGE            2606406                                                      
    
11. PCBA               4980446                                                      
    
12. SMT                3189386                                                      
    
13.                   16091448                                                      
    
14. 
    
15. 已选择6行。
    
16. 
    
17. 
    
18. Execution Plan
    
19. ----------------------------------------------------------                     
    
20.    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7817 Card=120559         
    
21.           7 Bytes=26523134)                                                     
    
22.                                                                                 
    
23.    1    0   SORT (GROUP BY ROLLUP) (Cost=7817 Card=1205597 Bytes=26523         
    
24.           134)                                                                  
    
25.                                                                                 
    
26.    2    1     TABLE ACCESS (FULL) OF 'TABLENAME' (Cost=2241 Card=1205597         
    
27.            Bytes=26523134)                                                      
    
28.                                                                                 
    
29. 
    
30. 
    
31. 
    
32. 
    
33. Statistics
    
34. ----------------------------------------------------------                     
    
35.           0  recursive calls                                                   
    
36.          12  db block gets                                                      
    
37.       14760  consistent gets                                                   
    
38.           0  physical reads                                                     
    
39.           0  redo size                                                         
    
40.         379  bytes sent via SQL*Net to client                                   
    
41.          57  bytes received via SQL*Net from client                             
    
42.           2  SQL*Net roundtrips to/from client                                 
    
43.           2  sorts (memory)                                                     
    
44.           0  sorts (disk)                                                      
    
45.           6  rows processed                                                     
    
46. 
    
47. SQL>; SELECT segment,SUM(qty) FROM tablename
    
48.   2  GROUP BY segment;
    
49. 
    
50. SEGMENT         SUM(QTY)                                                      
    
51. --------------- ----------                                                      
    
52. FA                 5314660                                                      
    
53. Item 2                 550                                                      
    
54. PACKAGE            2606406                                                      
    
55. PCBA               4980446                                                      
    
56. SMT                3189386                                                      
    
57. 
    
58. 
    
59. Execution Plan
    
60. ----------------------------------------------------------                     
    
61.    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=826 Card=1205597         
    
62.            Bytes=26523134)                                                      
    
63.                                                                                 
    
64.    1    0   SORT (GROUP BY NOSORT) (Cost=826 Card=1205597 Bytes=265231         
    
65.           34)                                                                  
    
66.                                                                                 
    
67.    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABLENAME' (Cost=826 Car         
    
68.           d=1205597 Bytes=26523134)                                             
    
69.                                                                                 
    
70.    3    2       INDEX (FULL SCAN) OF 'IDX2_72QSY' (NON-UNIQUE) (Cost=2         
    
71.           6 Card=1205597)                                                      
    
72.                                                                                 
    
73. 
    
74. 
    
75. 
    
76. 
    
77. Statistics
    
78. ----------------------------------------------------------                     
    
79.           0  recursive calls                                                   
    
80.           0  db block gets                                                      
    
81.      195228  consistent gets                                                   
    
82.           0  physical reads                                                     
    
83.           0  redo size                                                         
    
84.         368  bytes sent via SQL*Net to client                                   
    
85.         152  bytes received via SQL*Net from client                             
    
86.           3  SQL*Net roundtrips to/from client                                 
    
87.           1  sorts (memory)                                                     
    
88.           0  sorts (disk)                                                      
    
89.           5  rows processed                                                     
    
90. 
    

复制代码

sshd

The execution plans makes the difference.
One is full table scan + sort while the other is full index scan then table access by rowid + non sort.

From the output is looks that you have an index on the segment column, and it only has a few distinct values.
The ratio is 6/17000000.

This index is unselective and you should drop it.

simplelife

谢谢sshd的解释。不过是我没有解释清楚意思。执行计划我有看。
我疑惑的是如此相似的查询为什么Ora会采取这样两种执行计划。
Ora的选择标准是什么？
为什么使用了rollup和不使用会关联到Index的抉择
谢谢。。。

sshd

In both SQLs, oracle need to sort on segment.

case1:
GROUP BY ROLLUP(NVL(segment,SUMMARY))
the index on segment will not be used because functions are applied to segment, thus oracle will do full table scan, then sort on segment

case 2)
GROUP BY segment
the index on segment will be used to access the table, and the results are in accending or decending order (since the leaf level of the index tree are sorted by segment ), no more sorts are needed

Since the index on tablename(segment ) is unselective, it's basically is useless, and you will need to drop it.