November 10 2014

star_transformation_enabled

要实现星型转换的前提条件:
– star_transformation_enabled = true

alter system set STAR_TRANSFORMATION_ENABLED=true;
– 事实表在关联字段上创建外键,指向维度表的主键。

SQL> select CONSTRAINT_NAME,STATUS,VALIDATED from user_constraints where table_name=’SALES’ and CONSTRAINT_TYPE=’R’;

CONSTRAINT_NAME STATUS VALIDATED
—————————— ——– ————-
SALES_CHANNEL_FK ENABLED NOT VALIDATED
SALES_TIME_FK ENABLED NOT VALIDATED
SALES_PRODUCT_FK ENABLED NOT VALIDATED
SALES_CUSTOMER_FK ENABLED NOT VALIDATED
SALES_PROMO_FK ENABLED NOT VALIDATED

alter table sales enable validate constraint SALES_CHANNEL_FK;
alter table sales enable validate constraint SALES_TIME_FK;
alter table sales enable validate constraint SALES_CUSTOMER_FK;

SQL> select CONSTRAINT_NAME,STATUS,VALIDATED from user_constraints where table_name=’TIMES’ and CONSTRAINT_TYPE=’P’;

CONSTRAINT_NAME STATUS VALIDATED
—————————— ——– ————-
TIMES_PK ENABLED NOT VALIDATED

SQL> select CONSTRAINT_NAME,STATUS,VALIDATED from user_constraints where table_name=’CUSTOMERS’ and CONSTRAINT_TYPE=’P’;

CONSTRAINT_NAME STATUS VALIDATED
—————————— ——– ————-
CUSTOMERS_PK ENABLED NOT VALIDATED

SQL> select CONSTRAINT_NAME,STATUS,VALIDATED from user_constraints where table_name=’CHANNELS’ and CONSTRAINT_TYPE=’P’;

CONSTRAINT_NAME STATUS VALIDATED
—————————— ——– ————-
CHANNELS_PK ENABLED NOT VALIDATED

alter table times enable validate constraint TIMES_PK;
alter table customers enable validate constraint CUSTOMERS_PK;
alter table channels enable validate constraint CHANNELS_PK;
– 在事实表的外键列上创建bitmap索引。

SQL> select index_name,index_type,STATUS from user_indexes where table_name=’SALES’;

INDEX_NAME INDEX_TYPE STATUS
—————————— ————————— ——–
SALES_PROMO_BIX BITMAP N/A
SALES_CHANNEL_BIX BITMAP N/A
SALES_TIME_BIX BITMAP N/A
SALES_CUST_BIX BITMAP N/A
SALES_PROD_BIX BITMAP N/A
– 收集优化器统计信息。

exec dbms_stats.gather_table_stats(user,’sales’,cascade=>true);
exec dbms_stats.gather_table_stats(user,’times’,cascade=>true);
exec dbms_stats.gather_table_stats(user,’channels’,cascade=>true);
exec dbms_stats.gather_table_stats(user,’customers’,cascade=>true);

 

SELECT ch.channel_class, c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,times t,customers c,channels ch
WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND
s.channel_id = ch.channel_id AND
c.cust_state_province = ‘CA’ AND
ch.channel_desc IN (‘Internet’,’Catalog’) AND
t.calendar_quarter_desc IN (‘1999-01′,’1999-02’)
GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc;

第一个阶段:
SELECT s.amount_sold
FROM saless
WHERE time_id IN (SELECT time_id
FROM times
WHERE calendar_quarter_desc
IN(‘1999-Q1′,’1999-Q2’))

1999-01-01
1999-04-01
sales表的time_id列上的bitmap索引的结构:
key start_rowid end_rowid bitmap
…..
1999-01-01 xxx yyy 100101010100001000
…..
1999-04-01 xxx yyy 000000100001010010
….

100101010100001000
000000100001010010
==================
100101110101011010 =>得到符合time_id的where条件的bitmap

AND cust_id IN (SELECT cust_id
FROM customers
WHERE cust_state_province = ‘CA’)

c1
c2
sales表的cust_id列上的bitmap索引的结构:
key start_rowid end_rowid bitmap
…..
c1 xxx yyy 001010001001100010
…..
c2 xxx yyy 100100010010001001
….

001010001001100010
100100010010001001
==================
101110011011101011 =>得到符合cust_id的where条件的bitmap
AND channel_id IN(SELECT channel_id
FROM channels
WHERE channel_desc IN
(‘Internet’,’Catalog’));

ch1
ch2

key start_rowid end_rowid bitmap
…..
ch1 xxx yyy 100010000101010100
…..
ch2 xxx yyy 011000101000100010
….

100010000101010100
011000101000100010
===================
111010101101110110 => 得到符合channel_id的where条件的bitmap
100101110101011010
101110011011101011
111010101101110110
=====================
100000000001000010 =>得到符合所有where条件的bitmap,

把这串bitmap里的1转换为rowid,这叫做result set

select time_id, cust_id, channel_id, amount_sold
from sales where rowid in (result_set);

第二个阶段:
根据第一阶段返回的time_id,到times表里取出calendar_quarter_desc。
根据第一阶段返回的cust_id,到customers表里取出cust_city。
根据第一阶段返回的channel_id,到channels表里取出channel_class。
最后分组汇总。

星型转换之前的执行计划:

没有用st

星型转换之后的执行计划

st1 st2



Copyright 2019. All rights reserved.

Posted 2014年11月10日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *