Oracle 使用维进行查询重写

时间:2024-04-13 10:40:23

Oracle  使用维进行查询重写

conn / as sysdba
alter user sh account unlock identified by sh;
conn sh/sh
query_rewrite_integrity =  TRUSTED


--物化视图的定义

select query from user_mviews where MVIEW_NAME='CAL_MONTH_SALES_MV';

CREATE MATERIALIZED VIEW cal_month_sales_mv
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc,SUM(s.amount_sold) AS dollars
FROM sales s,times t 
WHERE s.time_id = t.time_id
GROUP BY t.CALENDAR_MONTH_DESC;

SELECT   t.calendar_month_desc,sum(s.amount_sold) AS dollars
FROM     sales s,times t
WHERE    s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

---------------------------------------------------------------------------
--当我们运行的查询和视图一致的时候,会直接查询视图而不进行真的查询
SQL> SELECT   t.calendar_month_desc,sum(s.amount_sold) AS dollars
    FROM     sales s,times t
    WHERE    s.time_id = t.time_id
    GROUP BY t.calendar_month_desc;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=35 Bytes=525)
   1    0   TABLE ACCESS (FULL) OF 'CAL_MONTH_SALES_MV' (Cost=3 Card=3
          5 Bytes=525)

--------------------------------------------------------------------------
--因为我们定义了维,维中描述了月和年的关系,当我们求年的汇总的时候就会汇总
月的结果,而不是真的查询。--------------------建立自己的维-----------------

CREATE DIMENSION "SH"."TIMES_DIM2"
LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_DESC") 
LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR") 
HIERARCHY "CAL_ROLLUP2" ("MONTH" CHILD OF "YEAR") 
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_DESC" 
ATTRIBUTE "YEAR" DETERMINES "TIMES"."CALENDAR_YEAR";


alter system set query_rewrite_integrity =  TRUSTED;
--alter system set query_rewrite_integrity =STALE_TOLERATED;

SELECT   t.CALENDAR_YEAR, sum(s.amount_sold) AS dollars
FROM     sales s,  times t
WHERE    s.time_id = t.time_id
GROUP BY t.CALENDAR_YEAR;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=4 Bytes=108)
   1    0   SORT (GROUP BY) (Cost=17 Card=4 Bytes=108)
   2    1     HASH JOIN (Cost=16 Card=99 Bytes=2673)
   3    2       TABLE ACCESS (FULL) OF 'CAL_MONTH_SALES_MV' (Cost=3 Card=35 Bytes=525)
   4    2       VIEW (Cost=13 Card=136 Bytes=1632)
   5    4         SORT (UNIQUE) (Cost=13 Card=136 Bytes=1632)
   6    5           TABLE ACCESS (FULL) OF 'TIMES' (Cost=12 Card=1461 Bytes=17532)


上面使用了查询重写,代价为17下面改变参数,禁止了查询重写,代价为2223

alter system set query_rewrite_integrity=ENFORCED;
SELECT   t.CALENDAR_YEAR,sum(s.amount_sold) AS dollars
FROM     sales s,times t
WHERE    s.time_id = t.time_id
GROUP BY t.CALENDAR_YEAR;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2223 Card=4 Bytes=96 )
   1    0   SORT (GROUP BY) (Cost=2223 Card=4 Bytes=96)
   2    1     HASH JOIN (Cost=1329 Card=1016271 Bytes=24390504)
   3    2       TABLE ACCESS (FULL) OF 'TIMES' (Cost=12 Card=1461 Bytes=17532)
   4    2       PARTITION RANGE (ALL)
   5    4         TABLE ACCESS (FULL) OF 'SALES' (Cost=1218 Card=1016271 ytes=12195252)

如果你在你的sh用户中看不到我的结果,自己建立一个维,老的维可能有点问题,oracle原厂越来越不严谨了

CREATE DIMENSION "SH"."TIMES_DIM2"
LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_DESC") 
LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR") 
HIERARCHY "CAL_ROLLUP2" ("MONTH" CHILD OF "YEAR") 
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_DESC" 
ATTRIBUTE "YEAR" DETERMINES "TIMES"."CALENDAR_YEAR";


-----------------------------例子2--------------------------------
--建立自己的物化视图,用来统计每个月每个城市的消费
--原来有个客户维,这个为比较复杂,关联了其它表。

set long 10000
select dbms_metadata.get_ddl('DIMENSION','CUSTOMERS_DIM','SH') FROM DUAL;


这个语句可以查看维的定义!有关联的语法

视图1为统计城市

CREATE MATERIALIZED VIEW cal_month_sales_cust_mv
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc,CUST_CITY,SUM(s.amount_sold) AS dollars
FROM sales s,times t , CUSTOMERS c
WHERE s.time_id = t.time_id and s.CUST_ID=c.CUST_ID        
GROUP BY t.CALENDAR_MONTH_DESC,CUST_CITY;

视图2为统计城市代码,城市代码在客户维中有描述

CREATE MATERIALIZED VIEW cal_month_sales_cust_mv2
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc,CUST_CITY_id,SUM(s.amount_sold) AS dollars
FROM sales s,times t , CUSTOMERS c
WHERE s.time_id = t.time_id and s.CUST_ID=c.CUST_ID        
GROUP BY t.CALENDAR_MONTH_DESC,CUST_CITY_id;

验证查询重写--合计了每个月,每个城市的消费

SELECT t.calendar_month_desc,CUST_CITY,SUM(s.amount_sold) AS dollars
FROM sales s,times t , CUSTOMERS c
WHERE s.time_id = t.time_id and s.CUST_ID=c.CUST_ID        
GROUP BY t.CALENDAR_MONTH_DESC,CUST_CITY;

验证查询重写--合计了每年,每个城市的消费,使用了时间维

SELECT t.CALENDAR_YEAR,CUST_CITY,SUM(s.amount_sold) AS dollars
FROM sales s,times t , CUSTOMERS c
WHERE s.time_id = t.time_id and s.CUST_ID=c.CUST_ID        
GROUP BY t.CALENDAR_YEAR,CUST_CITY;

验证查询重写--合计了每年,每个国家的消费,使用了时间维,客户维

SELECT t.CALENDAR_YEAR,COUNTRY_NAME,SUM(s.amount_sold) AS dollars
FROM sales s,times t , CUSTOMERS c,COUNTRIES  gj
WHERE s.time_id = t.time_id 
and s.CUST_ID=c.CUST_ID    
and c.COUNTRY_ID=gj.COUNTRY_ID    
GROUP BY t.CALENDAR_YEAR,COUNTRY_NAME;

维其实很简单,就是定义了层次关系而已!