奇怪。請(qǐng)幫忙分析一下為什么Rollup比簡(jiǎn)單SUM速度還快。

simplelife

是Try幾個(gè)Oracle分析函數(shù)的時(shí)候發(fā)現(xiàn)使用rollup的語句比不使用的簡(jiǎn)單sum還要快些。搞不懂怎么回事，特別上來請(qǐng)教一下大家。

我用的是HPUX上架設(shè)的Oracle 8174
有一個(gè)比較大的Table，共170多萬行記錄。

使用如下語句RUN40次平均用時(shí)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. ---------------------------------------------------------------------------------
    

復(fù)制代碼

使用如下語句在DB Server 的 Loading非常低的時(shí)候，RUN40次平均用時(shí)6.3488秒
在正常Loading的時(shí)候在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. ---------------------------------------------------------------------------------
    

復(fù)制代碼

我不明白相同的結(jié)果使用rollup還多做了運(yùn)算，為什么速度反而快這么多？

想請(qǐng)教一下看各位有什么看法。謝謝

附上執(zhí)行計(jì)劃如下:

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. 
    

復(fù)制代碼

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的解釋。不過是我沒有解釋清楚意思。執(zhí)行計(jì)劃我有看。
我疑惑的是如此相似的查詢?yōu)槭裁碠ra會(huì)采取這樣兩種執(zhí)行計(jì)劃。
Ora的選擇標(biāo)準(zhǔn)是什么？
為什么使用了rollup和不使用會(huì)關(guān)聯(lián)到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.