要实践,请先运行oracle自带脚本$ORACLE_HOME/demo/schema/sales_history/sh_main.sql
维表和事实表是相对的,都是表对象。比如sales是事实表,存放销售记录,其中包含销售时间,销售客户ID等信息。维表可以是customers,times等,其中customers存客户信息 ,包含客户名,所属城市,州,... 这是典型的星型模式,就是事实表在中间,维表在周围(维表即被参照表)。而维(dimension)的概念和维表是完全不同的,维是一种数据库对象,它帮助组织和分组维信息到分层结构,代表了一种列之间的1:n关系,这些关系是不易被约束条件所表达的。
In Oracle9i, the dimensional information itself is stored in a dimension table. In
addition, the database object dimension helps to organize and group dimensional
information into hierarchies. This represents natural 1:n relationships between
columns or column groups (the levels of a hierarchy) that cannot be represented
with constraint conditions.
维不是必须的。但它可以帮助oracle的查询重写执行更复杂的查询重写。查询重写就是Oracle选择一种高效的执行计划,它重写你原始的SQL,但功能不变。
Dimensions do not have to be defined, but spending time creating them can yield
significant benefits, because they help query rewrite perform more complex types of
rewrite.
维创建之前,必须先有维表,比如customers.下边看看维的创建
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region IS (countries.country_region)
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
subregion CHILD OF
region
JOIN KEY (customers.country_id) REFERENCES country
);
level子句指出到customers表列的映射关系,hierarchy/child of指出各level间的父子关系。这样就告知Oracle在有些查询里,可以考虑这个关系而采用查询重写。参考《Oracle9i
Data Warehousing Guide
Release 2 (9.2)
March 2002
Part No. A96520-01》
查看维:
SQL> set serveroutput on size 10000;
SQL> execute sys.demo_dim.print_alldims;
DIMENSION NTEB2B.COMP_LEVEL
LEVEL DIM_COMP_LEVEL IS NTEB2B.E_ACC_COMPANIES.COMP_LEVEL
DIMENSION SH.CHANNELS_DIM
LEVEL CHANNEL IS SH.CHANNELS.CHANNEL_ID
LEVEL CHANNEL_CLASS IS SH.CHANNELS.CHANNEL_CLASS
HIERARCHY CHANNEL_ROLLUP (
CHANNEL
CHILD OF CHANNEL_CLASS
)
ATTRIBUTE CHANNEL DETERMINES SH.CHANNELS.CHANNEL_DESC
ATTRIBUTE CHANNEL_CLASS DETERMINES SH.CHANNELS.CHANNEL_CLASS
DIMENSION SH.CUSTOMERS_DIM
LEVEL CITY IS SH.CUSTOMERS.CUST_CITY
LEVEL COUNTRY IS SH.COUNTRIES.COUNTRY_ID
LEVEL CUSTOMER IS SH.CUSTOMERS.CUST_ID
LEVEL REGION IS SH.COUNTRIES.COUNTRY_REGION
LEVEL STATE IS SH.CUSTOMERS.CUST_STATE_PROVINCE
LEVEL SUBREGION IS SH.COUNTRIES.COUNTRY_SUBREGION
HIERARCHY GEOG_ROLLUP (
CUSTOMER
CHILD OF CITY
CHILD OF STATE
CHILD OF COUNTRY
CHILD OF SUBREGION
CHILD OF REGION
JOIN KEY SH.CUSTOMERS.COUNTRY_ID REFERENCES COUNTRY
)
ATTRIBUTE CITY DETERMINES SH.CUSTOMERS.CUST_CITY
ATTRIBUTE COUNTRY DETERMINES SH.COUNTRIES.COUNTRY_NAME
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_CREDIT_LIMIT
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_EMAIL
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_FIRST_NAME
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_GENDER
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_INCOME_LEVEL
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_LAST_NAME
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_MAIN_PHONE_NUMBER
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_MARITAL_STATUS
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_POSTAL_CODE
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_STREET_ADDRESS
ATTRIBUTE CUSTOMER DETERMINES SH.CUSTOMERS.CUST_YEAR_OF_BIRTH
ATTRIBUTE REGION DETERMINES SH.COUNTRIES.COUNTRY_REGION
ATTRIBUTE STATE DETERMINES SH.CUSTOMERS.CUST_STATE_PROVINCE
ATTRIBUTE SUBREGION DETERMINES SH.COUNTRIES.COUNTRY_SUBREGION
DIMENSION SH.PRODUCTS_DIM
LEVEL CATEGORY IS SH.PRODUCTS.PROD_CATEGORY
LEVEL PRODUCT IS SH.PRODUCTS.PROD_ID
LEVEL SUBCATEGORY IS SH.PRODUCTS.PROD_SUBCATEGORY
HIERARCHY PROD_ROLLUP (
PRODUCT
CHILD OF SUBCATEGORY
CHILD OF CATEGORY
)
ATTRIBUTE CATEGORY DETERMINES SH.PRODUCTS.PROD_CATEGORY
ATTRIBUTE CATEGORY DETERMINES SH.PRODUCTS.PROD_CAT_DESC
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_DESC
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_LIST_PRICE
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_MIN_PRICE
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_NAME
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_PACK_SIZE
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_STATUS
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_UNIT_OF_MEASURE
ATTRIBUTE PRODUCT DETERMINES SH.PRODUCTS.PROD_WEIGHT_CLASS
ATTRIBUTE SUBCATEGORY DETERMINES SH.PRODUCTS.PROD_SUBCATEGORY
ATTRIBUTE SUBCATEGORY DETERMINES SH.PRODUCTS.PROD_SUBCAT_DESC
DIMENSION SH.PROMOTIONS_DIM
LEVEL CATEGORY IS SH.PROMOTIONS.PROMO_CATEGORY
LEVEL PROMO IS SH.PROMOTIONS.PROMO_ID
LEVEL SUBCATEGORY IS SH.PROMOTIONS.PROMO_SUBCATEGORY
HIERARCHY PROMO_ROLLUP (
PROMO
CHILD OF SUBCATEGORY
CHILD OF CATEGORY
)
ATTRIBUTE CATEGORY DETERMINES SH.PROMOTIONS.PROMO_CATEGORY
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_BEGIN_DATE
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_COST
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_END_DATE
ATTRIBUTE PROMO DETERMINES SH.PROMOTIONS.PROMO_NAME
ATTRIBUTE SUBCATEGORY DETERMINES SH.PROMOTIONS.PROMO_SUBCATEGORY
DIMENSION SH.TIMES_DIM
LEVEL DAY IS SH.TIMES.TIME_ID
LEVEL FIS_MONTH IS SH.TIMES.FISCAL_MONTH_DESC
LEVEL FIS_QUARTER IS SH.TIMES.FISCAL_QUARTER_DESC
LEVEL FIS_WEEK IS SH.TIMES.WEEK_ENDING_DAY
LEVEL FIS_YEAR IS SH.TIMES.FISCAL_YEAR
LEVEL MONTH IS SH.TIMES.CALENDAR_MONTH_DESC
LEVEL QUARTER IS SH.TIMES.CALENDAR_QUARTER_DESC
LEVEL YEAR IS SH.TIMES.CALENDAR_YEAR
HIERARCHY CAL_ROLLUP (
DAY
CHILD OF MONTH
CHILD OF QUARTER
CHILD OF YEAR
)
HIERARCHY FIS_ROLLUP (
DAY
CHILD OF FIS_WEEK
CHILD OF FIS_MONTH
CHILD OF FIS_QUARTER
CHILD OF FIS_YEAR
)
ATTRIBUTE DAY DETERMINES SH.TIMES.CALENDAR_WEEK_NUMBER
ATTRIBUTE DAY DETERMINES SH.TIMES.DAY_NAME
ATTRIBUTE DAY DETERMINES SH.TIMES.DAY_NUMBER_IN_MONTH
ATTRIBUTE DAY DETERMINES SH.TIMES.DAY_NUMBER_IN_WEEK
ATTRIBUTE FIS_MONTH DETERMINES SH.TIMES.DAYS_IN_FIS_MONTH
ATTRIBUTE FIS_MONTH DETERMINES SH.TIMES.END_OF_FIS_MONTH
ATTRIBUTE FIS_MONTH DETERMINES SH.TIMES.FISCAL_MONTH_DESC
ATTRIBUTE FIS_MONTH DETERMINES SH.TIMES.FISCAL_MONTH_NAME
ATTRIBUTE FIS_MONTH DETERMINES SH.TIMES.FISCAL_MONTH_NUMBER
ATTRIBUTE FIS_QUARTER DETERMINES SH.TIMES.DAYS_IN_FIS_QUARTER
ATTRIBUTE FIS_QUARTER DETERMINES SH.TIMES.END_OF_FIS_QUARTER
ATTRIBUTE FIS_QUARTER DETERMINES SH.TIMES.FISCAL_QUARTER_DESC
ATTRIBUTE FIS_QUARTER DETERMINES SH.TIMES.FISCAL_QUARTER_NUMBER
ATTRIBUTE FIS_WEEK DETERMINES SH.TIMES.FISCAL_WEEK_NUMBER
ATTRIBUTE FIS_WEEK DETERMINES SH.TIMES.WEEK_ENDING_DAY
ATTRIBUTE FIS_YEAR DETERMINES SH.TIMES.DAYS_IN_FIS_YEAR
ATTRIBUTE FIS_YEAR DETERMINES SH.TIMES.END_OF_FIS_YEAR
ATTRIBUTE FIS_YEAR DETERMINES SH.TIMES.FISCAL_YEAR
ATTRIBUTE MONTH DETERMINES SH.TIMES.CALENDAR_MONTH_DESC
ATTRIBUTE MONTH DETERMINES SH.TIMES.CALENDAR_MONTH_NAME
ATTRIBUTE MONTH DETERMINES SH.TIMES.CALENDAR_MONTH_NUMBER
ATTRIBUTE MONTH DETERMINES SH.TIMES.DAYS_IN_CAL_MONTH
ATTRIBUTE MONTH DETERMINES SH.TIMES.END_OF_CAL_MONTH
ATTRIBUTE QUARTER DETERMINES SH.TIMES.CALENDAR_QUARTER_DESC
ATTRIBUTE QUARTER DETERMINES SH.TIMES.CALENDAR_QUARTER_NUMBER
ATTRIBUTE QUARTER DETERMINES SH.TIMES.DAYS_IN_CAL_QUARTER
ATTRIBUTE QUARTER DETERMINES SH.TIMES.END_OF_CAL_QUARTER
ATTRIBUTE YEAR DETERMINES SH.TIMES.CALENDAR_YEAR
ATTRIBUTE YEAR DETERMINES SH.TIMES.DAYS_IN_CAL_YEAR
ATTRIBUTE YEAR DETERMINES SH.TIMES.END_OF_CAL_YEAR
PL/SQL procedure successfully completed