【TechTarget中国原创】示例
现在让我们来演示一下虚拟列在模拟数据仓库设计中的具体使用。
1、创建一个标准化PRODUCT表，使用立体的星型模式设计。
create table product
(product_key number(6) not null
,description varchar2(1000) not null
,cost number (7,0) not null
,markup_percent number (3,0) not null
,total_cost number (7,0) as (cost * (1 + (markup_percent / 100))) virtual not null
check (total_cost < 125001)
,states number not null
,az_flag varchar2(1) as (decode(bitand (states,1),1,'Y','N')) virtual not null
,ct_flag varchar2(1) as (decode(bitand (states,2),2,'Y','N')) virtual not null
,ri_flag varchar2(1) as (decode(bitand (states,4),4,'Y','N')) virtual not null
)
partition by range (total_cost)
(
partition low_cost_partition values less than (1000)
,partition moderate_cost_partition values less than (10000)
,partition high_cost_partition values less than (MAXVALUE)
); |
请注意加粗的虚拟列新语法。运算属性“total cost”内联为空，它被当做分割列。每个派生的state flag属性都是基于“state”列。
STATE STATE NUMBER
------------ ------------
ARIZONA 1
CONNECTICUT 2
RHODE ISLAND 4 |
2、显示的虚拟列表达式实际以元数据的形式存储在数据字典中。
select column_name, virtual_column, data_default
from user_tab_cols
where table_name = 'PRODUCT'
order by column_id;
COLUMN_NAME VIR DATA_DEFAULT
------------------------------ --- ------------------------------------
PRODUCT_KEY NO
DESCRIPTION NO
COST NO
MARKUP_PERCENT NO
TOTAL_COST YES "COST"*(1+"MARKUP_PERCENT"/100)
STATES NO
AZ_FLAG YES DECODE(BITAND("STATES",1),1,'Y','N')
CT_FLAG YES DECODE(BITAND("STATES",2),2,'Y','N')
RI_FLAG YES DECODE(BITAND("STATES",4),4,'Y','N') |
3、加载PRODUCT表
insert /*+ APPEND */ into product
(PRODUCT_KEY,DESCRIPTION,COST,MARKUP_PERCENT,STATES)
select rownum
,rpad(to_char(rownum),1000)
,case floor(dbms_random.value(1,6))
when 1 then 10
when 2 then 100
when 3 then 1000
when 4 then 10000
when 5 then 100000
end
,case floor(dbms_random.value(1,4))
when 1 then 5
when 2 then 10
when 3 then 25
end
,case floor(dbms_random.value(1,3))
when 1 then 1
when 2 then 3
end
from dba_objects
where rownum < 50001;
commit; |
4、 在虚拟“state flag ”列中创建本地分割索引。
create bitmap index product_az_ix on product (az_flag) local;
create bitmap index product_ct_ix on product (ct_flag) local;
create bitmap index product_ri_ix on product (ri_flag) local;
select a.index_name, a.index_Type, b.column_Expression
from user_indexes a
,user_ind_expressions b
where a.index_name = b.index_name
and a.index_name like 'PRODUCT_%_IX';
INDEX_NAME INDEX_TYPE COLUMN_EXPRESSION
------------- --------------------- ------------------------------------
PRODUCT_AZ_IX FUNCTION-BASED BITMAP DECODE(BITAND("STATES",1),1,'Y','N')
PRODUCT_CT_IX FUNCTION-BASED BITMAP DECODE(BITAND("STATES",2),2,'Y','N')
PRODUCT_RI_IX FUNCTION-BASED BITMAP DECODE(BITAND("STATES",4),4,'Y','N') |
5、收集统计数据
execute dbms_stats.gather_table_stats(user,'PRODUCT' ,cascade=>true, estimate_percent=> 100, granularity=> 'PARTITION');
select blocks, num_rows, avg_row_len from user_tables where table_name = 'PRODUCT';
BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
7284 50000 1024 |
6、验证ARIZONA和RHODE ISLAND的偏态分布。
select states,count(*) from product group by states order by states;
STATES COUNT(*)
---------- ----------
1 24910
3 25090
select ' ARIZONA' ,sum(decode(az_flag,'Y',1,0)) "AZ Yes"
,sum(decode(az_flag,'N',1,0)) "AZ No"
,' RHODE ISLAND',sum(decode(ri_flag,'Y',1,0)) "RI Yes"
,sum(decode(ri_flag,'N',1,0)) "RI No"
from product;
'ARIZONA AZ Yes AZ No 'RHODEISLAND' RI Yes RI No
-------- ---------- ---------- ------------- ---------- ----------
ARIZONA 50000 0 RHODE ISLAND 0 50000 |
7、比较ARIZONA和RHODE ISLAND的优化查询计划。
select /*+ gather_plan_statistics */ product_key from product where az_flag = 'Y';
PRODUCT_KEY
-----------
1
.
.
.
49999
50000 rows selected.
select * from table(dbms_xplan.display_cursor(null,null,'allstats cost last'));
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
| 1 | PARTITION RANGE ALL| | 1 | 50000 | 1979 (1)| 50000 |00:00:00.61 | 10011 | 7147 |
|* 2 | TABLE ACCESS FULL | PRODUCT | 3 | 50000 | 1979 (1)| 50000 |00:00:00.46 | 10011 | 7147 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(BITAND("STATES",1),1,'Y','N')='Y')
select /*+ gather_plan_statistics */ product_key from product where ri_flag = 'Y';
no rows selected
select * from table(dbms_xplan.display_cursor(null,null,'allstats cost last'));
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------
| 1 | PARTITION RANGE ALL | | 1 | 1 | 4 (0)| 0 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PRODUCT | 3 | 1 | 4 (0)| 0 |00:00:00.01 | 5 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 3 | | | 0 |00:00:00.01 | 5 |
|* 4 | BITMAP INDEX SINGLE VALUE | PRODUCT_RI_IX | 3 | | | 0 |00:00:00.01 | 5 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("RI_FLAG"='Y') |
在这里，列函数的偏差结果可以被优化器捕获。这造成优化器对待每个查询成本的方式会有所不同，从而制定不同的查询计划。注意，估计行数要和实际处理行数匹配。
在这里，我们看到优化器针对“total cost”虚拟列进行了分区修剪。
select count(*) from product where total_cost > 10000;
COUNT(*)
----------
19885
select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 788 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE SINGLE| | 19885 | 252K| 788 (1)| 00:00:10 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | PRODUCT | 19885 | 252K| 788 (1)| 00:00:10 | 3 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COST"*(1+"MARKUP_PERCENT"/100)>10000) |
虚拟列是Oracle 11g提供的一个非常有用的功能。这一功能为数据建模人员提供了显示逻辑属性的优化方案。充分理解数据库的特性可以版主你更加简单更加快速地进行数据仓库设计。