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;

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

相关文章