【问题】请问以下两种SQL中:
select name, sum(price) from tbl order by sum(price);对sum(price)计算了两次?
select name, sum(price) t from tbl order by t;对sum(price)计算了一次?
如何证明?谢谢!
select name, sum(price) from tbl order by sum(price);对sum(price)计算了两次?
select name, sum(price) t from tbl order by t;对sum(price)计算了一次?
如何证明?谢谢!
【测试】
1、建立自定义聚合函数
create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
sum_string varchar2(4000),
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
--对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number
--终止聚集函数的处理,返回聚集函数处理的结果.
)
/
create or replace type body string_sum_obj is
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
begin
v_self := string_sum_obj(null);
return ODCICONST.Success;
end;
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is
begin
/* 连接 */
self.sum_string := self.sum_string || value;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
end if;
/* 最小值 */
if self.sum_string>value then
self.sum_string:=value;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
begin
/* 连接 */
self.sum_string := self.sum_string || v_next.sum_string;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
/* 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is
begin
return_value:= self.sum_string ||to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:xFF');
--聚合函数的实质就是一个对象
sum_string varchar2(4000),
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
--对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number
--终止聚集函数的处理,返回聚集函数处理的结果.
)
/
create or replace type body string_sum_obj is
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
begin
v_self := string_sum_obj(null);
return ODCICONST.Success;
end;
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is
begin
/* 连接 */
self.sum_string := self.sum_string || value;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
end if;
/* 最小值 */
if self.sum_string>value then
self.sum_string:=value;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
begin
/* 连接 */
self.sum_string := self.sum_string || v_next.sum_string;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
/* 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is
begin
return_value:= self.sum_string ||to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:xFF');
DBMS_LOCK.sleep(1);
return ODCICONST.Success;
end;
end;
/
create or replace function ConnStrSum(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;
return ODCICONST.Success;
end;
end;
/
create or replace function ConnStrSum(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;
2、在自定义聚合函数中加入了延时1s和最终处理时间
SQL> select connstrsum(C2),connstrsum(C2) from test;
CONNSTRSUM(C2) CONNSTRSUM(C2)
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91939294 2010-11-02 18:48:07:.828000000 91939294 2010-11-02 18:48:07:.828000000
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91939294 2010-11-02 18:48:07:.828000000 91939294 2010-11-02 18:48:07:.828000000
SQL> select connstrsum(C2), connstrsum(C2) from test group by C3 order by connstrsum(C2);
CONNSTRSUM(C2) CONNSTRSUM(C2)
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91 2010-11-02 18:48:30:.093000000 91 2010-11-02 18:48:30:.093000000
92 2010-11-02 18:48:28:.093000000 92 2010-11-02 18:48:28:.093000000
93 2010-11-02 18:48:27:.093000000 93 2010-11-02 18:48:27:.093000000
94 2010-11-02 18:48:29:.093000000 94 2010-11-02 18:48:29:.093000000
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91 2010-11-02 18:48:30:.093000000 91 2010-11-02 18:48:30:.093000000
92 2010-11-02 18:48:28:.093000000 92 2010-11-02 18:48:28:.093000000
93 2010-11-02 18:48:27:.093000000 93 2010-11-02 18:48:27:.093000000
94 2010-11-02 18:48:29:.093000000 94 2010-11-02 18:48:29:.093000000
SQL> select connstrsum(C2), connstrsum(C2) from test group by C3 order by connstrsum(C2
||1);
CONNSTRSUM(C2) CONNSTRSUM(C2)
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91 2010-11-02 18:51:03:.640000000 91 2010-11-02 18:51:03:.640000000
92 2010-11-02 18:50:59:.640000000 92 2010-11-02 18:50:59:.640000000
93 2010-11-02 18:50:57:.640000000 93 2010-11-02 18:50:57:.640000000
94 2010-11-02 18:51:01:.640000000 94 2010-11-02 18:51:01:.640000000
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
91 2010-11-02 18:51:03:.640000000 91 2010-11-02 18:51:03:.640000000
92 2010-11-02 18:50:59:.640000000 92 2010-11-02 18:50:59:.640000000
93 2010-11-02 18:50:57:.640000000 93 2010-11-02 18:50:57:.640000000
94 2010-11-02 18:51:01:.640000000 94 2010-11-02 18:51:01:.640000000
SQL>
【结论】
第一条说明两列执行时间相同,说明同时执行
第二条说明两列执行时间相同,并且结果中每条之差1S,则说明orderby 后面的SUM没有延误时间,即同时执行
第三条说明不同的SUM函数延误时间。
从上面SQL执行的时间中可以看到同样的聚合函数是一起执行的,即oracle在执行解析时和查询时已经优化过了