--create sales table
CREATE TABLE sales
(trans_date DATE, cust_id INT, sales_amount NUMBER);--insert sale rows INSERT /*+ APPEND */
INTO SALES
SELECT TRUNC(SYSDATE, 'year') + MOD(ROWNUM, 366) TRANS_DATE,
MOD(ROWNUM, 100) CUST_ID,
ABS(DBMS_RANDOM.RANDOM) / 100 SALES_AMOUNT
FROM ALL_OBJECTS;
SELECT COUNT(*) FROM sales;--go on inserting rows
BEGIN
FOR I IN 1 .. 6 LOOP
INSERT /*+ APPEND */
INTO SALES
SELECT TRANS_DATE,
CUST_ID,
ABS(DBMS_RANDOM.RANDOM) / 100 SALES_AMOUNT
FROM SALES;
COMMIT;
END LOOP;
END;--create index organized table
CREATE TABLE TIME_HIERARCHY(DAY PRIMARY KEY, MMYYYY, MON_YYYY, QTR_YYYY, YYYY) ORGANIZATION INDEX AS
SELECT DISTINCT TRANS_DATE DAY,
CAST(TO_CHAR(TRANS_DATE, 'mmyyyy') AS NUMBER) MMYYYY,
TO_CHAR(TRANS_DATE, 'mon-yyyy') MON_YYYY,
'Q' || CEIL(TO_CHAR(TRANS_DATE, 'mm') / 3) || ' FY' ||
TO_CHAR(TRANS_DATE, 'yyyy') QTR_YYYY,
CAST(TO_CHAR(TRANS_DATE, 'yyyy') AS NUMBER) YYYY
FROM SALES;--create materialized view
CREATE MATERIALIZED VIEW MV_SALES BUILD IMMEDIATE REFRESH ON DEMAND ENABLE QUERY REWRITE AS
SELECT SALES.CUST_ID,
SUM(SALES.SALES_AMOUNT) SALES_AMOUNT,
TIME_HIERARCHY.MMYYYY
FROM SALES, TIME_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
GROUP BY SALES.CUST_ID, TIME_HIERARCHY.MMYYYY;
analyze table sales compute statistics;
analyze table time_hierarchy compute statistics;alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=trusted;--query by Month
SELECT TIME_HIERARCHY.MMYYYY, SUM(SALES_AMOUNT)
FROM SALES, TIME_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
GROUP BY TIME_HIERARCHY.MMYYYY
--query by Quarter SELECT TIME_HIERARCHY.QTR_YYYY, SUM(SALES_AMOUNT)
FROM SALES, TIME_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
GROUP BY TIME_HIERARCHY.QTR_YYYY;--create dimension
CREATE DIMENSION TIME_HIERARCHY_DIM
LEVEL DAY IS TIME_HIERARCHY.DAY
LEVEL MMYYYY IS TIME_HIERARCHY.MMYYYY
LEVEL QTR_YYYY IS TIME_HIERARCHY.QTR_YYYY
LEVEL YYYY IS TIME_HIERARCHY.YYYY
HIERARCHY TIME_ROLLUP(DAY CHILD OF MMYYYY CHILD OF QTR_YYYY CHILD OF YYYY)
ATTRIBUTE MMYYYY DETERMINES MON_YYYY;--drop dimension
DROP DIMENSION TIME_HIERARCHY_DIM;--yearly query
SELECT TIME_HIERARCHY.YYYY, SUM(SALES_AMOUNT)
FROM SALES, TIME_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
GROUP BY TIME_HIERARCHY.YYYY;--create index organized table
CREATE TABLE CUSTOMER_HIERARCHY(CUST_ID PRIMARY KEY, ZIP_CODE, REGION) ORGANIZATION INDEX AS
SELECT CUST_ID,
MOD(ROWNUM, 6) || TO_CHAR(MOD(ROWNUM, 1000), 'fm0000') ZIP_CODE,
MOD(ROWNUM, 6) REGION
FROM (SELECT DISTINCT CUST_ID FROM SALES);analyze table CUSTOMER_HIERARCHY compute statistics;
drop materialized view mv_sales;
--recreate materialized view
CREATE MATERIALIZED VIEW MV_SALES BUILD IMMEDIATE REFRESH ON DEMAND ENABLE QUERY REWRITE AS
SELECT CUSTOMER_HIERARCHY.ZIP_CODE,
TIME_HIERARCHY.MMYYYY,
SUM(SALES.SALES_AMOUNT) SALES_AMOUNT
FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID
GROUP BY CUSTOMER_HIERARCHY.ZIP_CODE, TIME_HIERARCHY.MMYYYY;--query by month
SELECT CUSTOMER_HIERARCHY.ZIP_CODE,
TIME_HIERARCHY.MMYYYY,
SUM(SALES.SALES_AMOUNT) SALES_AMOUNT
FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID
GROUP BY CUSTOMER_HIERARCHY.ZIP_CODE, TIME_HIERARCHY.MMYYYY--query by year
SELECT CUSTOMER_HIERARCHY.REGION,
TIME_HIERARCHY.YYYY,
SUM(SALES.SALES_AMOUNT) SALES_AMOUNT
FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID
GROUP BY CUSTOMER_HIERARCHY.REGION, TIME_HIERARCHY.YYYY;DROP DIMENSION time_hierarchy_dim;
CREATE DIMENSION SALES_DIMENSION
LEVEL CUST_ID IS CUSTOMER_HIERARCHY.CUST_ID
LEVEL ZIP_CODE IS CUSTOMER_HIERARCHY.ZIP_CODE
LEVEL REGION IS CUSTOMER_HIERARCHY.REGION
LEVEL DAY IS TIME_HIERARCHY.DAY
LEVEL MMYYYY IS TIME_HIERARCHY.MMYYYY
LEVEL QTR_YYYY IS TIME_HIERARCHY.QTR_YYYY
LEVEL YYYY IS TIME_HIERARCHY.YYYY
HIERARCHY CUST_ROLLUP(CUST_ID CHILD OF ZIP_CODE CHILD OF REGION)
HIERARCHY TIME_ROLLUP(DAY CHILD OF MMYYYY CHILD OF QTR_YYYY CHILD OF YYYY)
ATTRIBUTE MMYYYY DETERMINES MON_YYYY;SELECT CUSTOMER_HIERARCHY.REGION,
TIME_HIERARCHY.YYYY,
SUM(SALES.SALES_AMOUNT) SALES_AMOUNT
FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY
WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY
AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID
GROUP BY CUSTOMER_HIERARCHY.REGION, TIME_HIERARCHY.YYYY;--DROP DIMENSION SALES_DIMENSION;
ALTER SYSTEM FLUSH buffer_cache;
ALTER SYSTEM FLUSH SHARED_POOL;SELECT * FROM dba_dimensions;
SELECT * FROM user_indexes WHERE index_type ='IOT - TOP';
SELECT * FROM user_indextypes;SELECT * FROM TIME_HIERARCHY;
SELECT * FROM CUSTOMER_HIERARCHY;
Oracle 创建和删除维
原创emanlee ©著作权
文章标签 系统 文章分类 JavaScript 前端开发
上一篇:转基因技术
下一篇:ASP.NET性能计数器
-
文件的创建和删除
文件的创建和删除
文件的创建和删除 -
freeRTOS任务创建和删除
FreeRTOS 中任务的创建和删除使用 xTaskCreate 和 vTaskDelete 函数。
c语言 开发语言 嵌入式硬件 Powered by 金山文档 句柄 -
BAT创建和删除文件
分享知识 传递快乐在日常工作中以tomcat做容器来启动项目,但tomcat不能自动删除编译mcat编译文件color 3eec...
bat创建文件 bat删除文件 tomcat编译文件 bat命令 删除和创建