示例 现在让我们来演示一下虚拟列在模拟数据仓库设计中的具体使用。 1、创建一个标准化PRODUCT表，使用立体的星型模式设计。 create table product (product_key number(6) not null ,description varchar2(1000) not null ……
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
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提供的一个非常有用的功能。这一功能为数据建模人员提供了显示逻辑属性的优化方案。充分理解数据库的特性可以版主你更加简单更加快速地进行数据仓库设计。
翻译
相关推荐
-
数据库设计需做好前期工作 Agile方法不适合
有很多企业认为数据建模以及设计良好的数据库是浪费时间的工作，对此专家的回答很直接：决不能忽视数据库设计过程。
-
SAP HANA数据建模秘籍
SAP HANA是一个全新的数据库平台，它提供了全新的数据建模方式，使得传统关系型数据库管理系统（RDBMS）得到了进一步的扩展。
-
NoSQL数据建模技术
NoSQL 数据库经常被用作很多非功能性的地方，如，扩展性，性能和一致性的地方。这些NoSQL的特性在理论和实践中都正在被大众广泛地研究着。
-
TDWI解决方案峰会分享主数据管理最佳实践
在近期召开的TDWI(数据仓库协会)解决方案峰会上，几位从事主数据管理(MDM)和数据治理的专家以演讲方式分享了他们的经验和MDM最佳实践。