ITPub博客

首页 > Linux操作系统 > Linux操作系统 > A study of Inline view and analytic function

A study of Inline view and analytic function

原创 Linux操作系统 作者:chncaesar 时间:2013-09-10 11:05:27 0 删除 编辑
show rel
release 1102000300

desc orders;
Name         Null Type         
------------ ---- ------------ 
ORDER_ID          NUMBER(38)   
CREATE_DATE       DATE         
CUSTOMER_ID       NUMBER(19)   
TOTAL_AMOUNT      NUMBER(38,3) 

insert into orders 
select level, trunc(sysdate - mod(level, 31)), mod(level, 100), mod(level, 1000)
from dual connect by level <= 10000;
commit;
EXEC  DBMS_STATS.GATHER_TABLE_STATS(user, 'ORDERS',cascade=>true);


Orders表里有1万个订单,属于100个客户,这些订单生成于最近31天内。求每个客户订单金额最高的订单生成日期!先看inline view形式的SQL:
select orders.create_date, orders.customer_id, orders.total_amount
from orders, 
  (select max(total_amount) highest, customer_id 
   from orders group by customer_id) hh
where orders.total_amount=hh.highest;

-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       |    16 (100)|          |
|*  1 |  HASH JOIN           |        |  1000 | 28000 |    16  (13)| 00:00:01 |
|   2 |   VIEW               |        |   100 |  1300 |     8  (13)| 00:00:01 |
|   3 |    HASH GROUP BY     |        |   100 |   700 |     8  (13)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| ORDERS | 10000 | 70000 |     7   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | ORDERS | 10000 |   146K|     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ORDERS"."TOTAL_AMOUNT"="HH"."HIGHEST")

再观察使用分析函数的SQL:

SELECT * FROM (
  select create_date, 
       customer_id,
       total_amount,
       RANK() over(partition by customer_id order by total_amount desc) rank
from orders)
WHERE RANK=1;

-----------------------------------------------------------------------------------
| Id  | Operation                | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |       |       |     8 (100)|          |
|*  1 |  VIEW                    |        | 10000 |   468K|     8  (13)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|        | 10000 |   146K|     8  (13)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | ORDERS | 10000 |   146K|     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RANK"=1)
   2 - filter(RANK() OVER ( PARTITION BY "CUSTOMER_ID" ORDER BY 
              INTERNAL_FUNCTION("TOTAL_AMOUNT") DESC )<=1)

在这里,分析函数性能优于inline view,由于不用做hash连接。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-772455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    910232