问一个运行速度的问题

wp_project

select sum( a+b) from tmp;和
select sum(a)+sum(b) from tmp;
的运行速度那个快？
sum(a+b)快?因为多了一个函数的调用？

rollingpig

hehe,有意思，随便测试一下

1. 
   
2. CREATE TABLE test_123 AS SELECT ROWNUM AS t, object_id
   
3. FROM dba_objects WHERE object_id IS NOT NULL
   
4. AND ROWNUM < 1000;
   
5. 
   
6.   DECLARE
   
7.   a NUMBER;
   
8. BEGIN
   
9. FOR i IN 0..10000 loop
   
10. SELECT Sum(t+object_id) INTO a FROM  test_123;
    
11. END LOOP;
    
12. END;
    
13. /
    
14. 
    
15. -- 大概 3.55 sec in 10000 loop
    
16. 
    
17.   DECLARE
    
18.   a NUMBER;
    
19. BEGIN
    
20. FOR i IN 0..10000 loop
    
21. SELECT Sum(t)+Sum(object_id) INTO a FROM  test_123;
    
22. END LOOP;
    
23. END;
    
24. /
    
25. 
    
26. -- 3.35 sec in 10000 loop
    
27. 
    

复制代码

第二种稍快一点点，基本可以忽略了……

rollingpig

多几个column的话，优势倒是越来越明显了

1. 
   
2. CREATE TABLE test_123 AS SELECT ROWNUM AS r, object_id r1 ,
   
3. Dbms_Random.random() AS r2   ,Dbms_Random.random() AS r3 ,
   
4. Dbms_Random.random() AS r4 ,
   
5. Dbms_Random.random() AS r5  
   
6. FROM dba_objects WHERE object_id IS NOT NULL
   
7. AND ROWNUM < 1000;
   
8. 
   
9. drop TABLE test_123 ;
   
10. 
    
11.   DECLARE
    
12.   a NUMBER;
    
13. BEGIN
    
14. FOR i IN 0..10000 loop
    
15. SELECT Sum(r+r1+r2+r3+r4+r5) INTO a FROM  test_123;
    
16. END LOOP;
    
17. END;
    
18. /
    
19. 
    
20. 11.8 sec in 10000 loop
    
21. 
    
22.   DECLARE
    
23.   a NUMBER;
    
24. BEGIN
    
25. FOR i IN 0..10000 loop
    
26. SELECT Sum(r)+  
    
27. Sum(r1)+  
    
28. Sum(r2)+   
    
29. Sum(r3)+   
    
30. Sum(r4)+   
    
31. Sum(r5)   
    
32. INTO a FROM  test_123;
    
33. END LOOP;
    
34. END;
    
35. /
    
36. 
    
37. 8.40 sec in 10000 loop
    

复制代码

doni

不过要注意,在有NULL的情况下,两个SQL得出的结果是不一样的