oracle 立方 和维,高手，谁能介绍一下“维dimension“有什么用途？

要实践，请先运行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