ORACLE数据库星型模型设计实例

一、星型模式核心设计原则

1. 1 张事实表 + N 张维度表
2. 事实表存度量（数值）+ 维度外键
3. 维度表扁平、宽表、不嵌套（拒绝雪花）
4. 统一使用代理键（SK）
5. 事实表按时间分区
6. 维度表用位图索引，事实表用位图连接索引
7. 一致性维度贯穿全仓库
8. 先原子粒度，后汇总

二、业务案例（零售销售）

• 事实：销售流水
• 维度：时间、商品、门店、会员

表名规范：

• 维度：DIM_XXX
• 事实：FACT_XXX

三、完整建表 SQL（Oracle 11g/12c/19c 通用）

1. 创建时间维度

CREATE TABLE DIM_DATE ( DATE_SK NUMBER(10) PRIMARY KEY, CALENDAR_DATE DATE NOT NULL, YEAR_NUM NUMBER(4) NOT NULL, MONTH_NUM NUMBER(2) NOT NULL, DAY_NUM NUMBER(2) NOT NULL, WEEK_NUM NUMBER(2) NOT NULL, QUARTER_NUM NUMBER(1) NOT NULL, IS_WEEKEND CHAR(1) NOT NULL, CREATE_TIME DATE DEFAULT SYSDATE ); CREATE BITMAP INDEX IDX_BM_DATE_YEAR ON DIM_DATE(YEAR_NUM); CREATE BITMAP INDEX IDX_BM_DATE_MONTH ON DIM_DATE(MONTH_NUM);

2. 创建商品维度

CREATE TABLE DIM_PRODUCT ( PRODUCT_SK NUMBER(10) PRIMARY KEY, PRODUCT_NK VARCHAR2(32) NOT NULL, -- 业务编码 PRODUCT_NAME VARCHAR2(100) NOT NULL, CATEGORY1 VARCHAR2(50) NOT NULL, CATEGORY2 VARCHAR2(50) NOT NULL, BRAND VARCHAR2(50) NOT NULL, PRICE NUMBER(10,2) NOT NULL, IS_CURRENT CHAR(1) NOT NULL, -- SCD2 EFF_START_DATE DATE NOT NULL, EFF_END_DATE DATE NOT NULL, CREATE_TIME DATE DEFAULT SYSDATE ); CREATE BITMAP INDEX IDX_BM_PROD_CATEGORY1 ON DIM_PRODUCT(CATEGORY1); CREATE BITMAP INDEX IDX_BM_PROD_BRAND ON DIM_PRODUCT(BRAND);

3. 创建门店维度

CREATE TABLE DIM_STORE ( STORE_SK NUMBER(10) PRIMARY KEY, STORE_NK VARCHAR2(32) NOT NULL, STORE_NAME VARCHAR2(100) NOT NULL, PROVINCE VARCHAR2(50) NOT NULL, CITY VARCHAR2(50) NOT NULL, CHANNEL_TYPE VARCHAR2(20) NOT NULL, CREATE_TIME DATE DEFAULT SYSDATE ); CREATE BITMAP INDEX IDX_BM_STORE_CITY ON DIM_STORE(CITY);

4. 创建会员维度

CREATE TABLE DIM_MEMBER ( MEMBER_SK NUMBER(10) PRIMARY KEY, MEMBER_NK VARCHAR2(32) NOT NULL, MEMBER_NAME VARCHAR2(50) NOT NULL, SEX CHAR(1) NOT NULL, AGE_GROUP VARCHAR2(20) NOT NULL, MEMBER_LEVEL VARCHAR2(20) NOT NULL, CREATE_TIME DATE DEFAULT SYSDATE ); CREATE BITMAP INDEX IDX_BM_MEM_SEX ON DIM_MEMBER(SEX); CREATE BITMAP INDEX IDX_BM_MEM_LEVEL ON DIM_MEMBER(MEMBER_LEVEL);

四、创建分区事实表（核心）

CREATE TABLE FACT_SALES ( DATE_SK NUMBER(10) NOT NULL, PRODUCT_SK NUMBER(10) NOT NULL, STORE_SK NUMBER(10) NOT NULL, MEMBER_SK NUMBER(10) NOT NULL, SALES_AMT NUMBER(18,2) NOT NULL, -- 销售额 SALES_QTY NUMBER(10) NOT NULL, -- 销量 COST_AMT NUMBER(18,2) NOT NULL, -- 成本 PROFIT_AMT NUMBER(18,2) NOT NULL, -- 利润 CREATE_TIME DATE DEFAULT SYSDATE ) PARTITION BY RANGE (DATE_SK) ( PARTITION P202501 VALUES LESS THAN (20250201), PARTITION P202502 VALUES LESS THAN (20250301), PARTITION P_MAX VALUES LESS THAN (MAXVALUE) ); -- 位图连接索引（Oracle 星型转换关键） CREATE BITMAP INDEX IDX_BM_SALES_DATE ON FACT_SALES(DIM_DATE.DATE_SK) FROM FACT_SALES, DIM_DATE WHERE FACT_SALES.DATE_SK = DIM_DATE.DATE_SK; CREATE BITMAP INDEX IDX_BM_SALES_PRODUCT ON FACT_SALES(DIM_PRODUCT.PRODUCT_SK) FROM FACT_SALES, DIM_PRODUCT WHERE FACT_SALES.PRODUCT_SK = DIM_PRODUCT.PRODUCT_SK;

五、典型查询 SQL（星型模型标准写法）

案例：按年月 + 品类 + 城市统计销售额

SELECT d.YEAR_NUM, d.MONTH_NUM, p.CATEGORY1, s.CITY, SUM(f.SALES_AMT) AS TOTAL_SALES, SUM(f.SALES_QTY) AS TOTAL_QTY FROM FACT_SALES f JOIN DIM_DATE d ON f.DATE_SK = d.DATE_SK JOIN DIM_PRODUCT p ON f.PRODUCT_SK = p.PRODUCT_SK JOIN DIM_STORE s ON f.STORE_SK = s.STORE_SK WHERE d.YEAR_NUM = 2025 GROUP BY d.YEAR_NUM, d.MONTH_NUM, p.CATEGORY1, s.CITY ORDER BY TOTAL_SALES DESC;

六、Oracle 星型模型必须开启的参数

ALTER SYSTEM SET STAR_TRANSFORMATION_ENABLED = TRUE SCOPE=BOTH; ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE = '19.1.0'; -- 按你版本

执行计划里出现：STAR TRANSFORMATION说明星型优化生效。

七、设计要点总结（背会就能用）

1. 维度表一定用代理键
2. 事实表只存外键 + 数字
3. 维度扁平、不嵌套
4. 事实表必须按时间分区
5. 低基数字段建位图索引
6. 用位图连接索引提速
7. 统一维度，全局复用
8. 先原子事实，后汇总表