参见《Oracle SQL高级编程》
一个典型的数据仓库查询:
这个查询中sales 是事实表 , 一般很大。
Products , customers , channels是维度表 , 一般很小,可以有冗余。
SH@ prod1> explain plan for
2 select sum( s.quantity_sold ) , sum( s.amount_sold ) from sales s , products p , customers c , channels ch
3 where
4 s.prod_id = p.prod_id and
5 s.cust_id = c.cust_id and
6 s.channel_id = ch.channel_id and
7 p.prod_name = 'Y box' and
8 c.cust_first_name = 'Abigail' and
9 ch.channel_desc = 'Direct_sales' ;
Explained.
执行计划如下:
SH@ prod1> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2309889988
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------