一、星型模式核心设计原则1 张事实表 N 张维度表事实表存度量数值 维度外键维度表扁平、宽表、不嵌套拒绝雪花统一使用代理键SK事实表按时间分区维度表用位图索引事实表用位图连接索引一致性维度贯穿全仓库先原子粒度后汇总二、业务案例零售销售事实销售流水维度时间、商品、门店、会员表名规范维度DIM_XXX事实FACT_XXX三、完整建表 SQLOracle 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 SCOPEBOTH; ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE 19.1.0; -- 按你版本执行计划里出现STAR TRANSFORMATION说明星型优化生效。七、设计要点总结背会就能用维度表一定用代理键事实表只存外键 数字维度扁平、不嵌套事实表必须按时间分区低基数字段建位图索引用位图连接索引提速统一维度全局复用先原子事实后汇总表