今天刚好要用到物化视图,原表比较大,想建成分区,在网上找了一下找到如下代码:
create materialized view mv_entrance_partition
partition by range(DAY_id)
SUBPARTITION BY HASH (province_ID) SUBPARTITIONS 32
(
partition PART_200805 values less than ('20080600'),
partition PART_200806 values less than ('20080700'),
PARTITION PART_20080701 VALUES less than ('20080702'),
PARTITION PART_20080702 VALUES less than ('20080703'),
PARTITION PART_20080703 VALUES less than ('20080704'),
PARTITION PART_20080704 VALUES less than ('20080705'),
PARTITION PART_20080705 VALUES less than ('20080706'),
PARTITION PART_20080706 VALUES less than ('20080707'),
PARTITION PART_20080707 VALUES less than ('20080708'),
PARTITION PART_20080708 VALUES less than ('20080709'),
PARTITION PART_20080709 VALUES less than ('20080710'),
PARTITION PART_20080710 VALUES less than ('20080711'),
PARTITION PART_20080711 VALUES less than ('20080712'),
PARTITION PART_20080712 VALUES less than ('20080713'),
PARTITION PART_20080713 VALUES less than ('20080714'),
PARTITION PART_20080714 VALUES less than ('20080715'),
PARTITION PART_20080715 VALUES less than ('20080716'),
PARTITION PART_20080716 VALUES less than ('20080717'),
PARTITION PART_20080717 VALUES less than ('20080718'),
PARTITION PART_20080718 VALUES less than ('20080719'),
PARTITION PART_20080719 VALUES less than ('20080720'),
PARTITION PART_20080720 VALUES less than ('20080721'),
PARTITION PART_20080721 VALUES less than ('20080722'),
PARTITION PART_20080722 VALUES less than ('20080723'),
PARTITION PART_20080723 VALUES less than ('20080724'),
PARTITION PART_20080724 VALUES less than ('20080725'),
PARTITION PART_20080725 VALUES less than ('20080726'),
PARTITION PART_20080726 VALUES less than ('20080727'),
PARTITION PART_20080727 VALUES less than ('20080728'),
PARTITION PART_20080728 VALUES less than ('20080729'),
PARTITION PART_20080729 VALUES less than ('20080730'),
PARTITION PART_20080730 VALUES less than ('20080731'),
PARTITION PART_20080731 VALUES less than ('20080801'),
PARTITION PART_DEFAULT VALUES LESS THAN(maxvalue)
)
tablespace doms_mv
nologging
build immediate
refresh force
on demand
enable query rewrite
as
SELECT
DMS_TIME.DAY_ID,
DMS_TIME.DAY_NAME,
DMS_TIME.Month_ID,
DMS_AREA.PROVINCE_ID,
DMS_AREA.PROVINCE_NAME,
DMS_AREA.CITY_ID,
DMS_AREA.ALL_NAME,
DMS_TERMINAL.BRAND_ID,
DMS_TERMINAL.TERMINAL_ID,
DMS_ENTRANCE.ENTRANCE_ID,
DTL_LOGIN.LOGIN_FLAG,
DTL_LOGIN.LOGIN_NEW_FLAG,
DTL_LOGIN.MSISDN
FROM
DMS_TIME,
DMS_AREA,
DTL_LOGIN,
DMS_TERMINAL,
DMS_ENTRANCE
WHERE
DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID
AND DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID
AND DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID
AND DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID;
( DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID )
AND ( DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID )
AND ( DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID )
AND ( DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID )
其中带的参数不是很了解,有高手帮忙解释一下吗?这些参数有哪些使用方式?
在建分区的语句中
partition by range(DAY_id)
SUBPARTITION BY HASH (province_ID) SUBPARTITIONS 32
为什么建partition还要建SUBPARTITION 呢?SUBPARTITION 这个是不是必须的?-------------------
谢谢各位高手~~~~!
create materialized view mv_entrance_partition
partition by range(DAY_id)
SUBPARTITION BY HASH (province_ID) SUBPARTITIONS 32
(
partition PART_200805 values less than ('20080600'),
partition PART_200806 values less than ('20080700'),
PARTITION PART_20080701 VALUES less than ('20080702'),
PARTITION PART_20080702 VALUES less than ('20080703'),
PARTITION PART_20080703 VALUES less than ('20080704'),
PARTITION PART_20080704 VALUES less than ('20080705'),
PARTITION PART_20080705 VALUES less than ('20080706'),
PARTITION PART_20080706 VALUES less than ('20080707'),
PARTITION PART_20080707 VALUES less than ('20080708'),
PARTITION PART_20080708 VALUES less than ('20080709'),
PARTITION PART_20080709 VALUES less than ('20080710'),
PARTITION PART_20080710 VALUES less than ('20080711'),
PARTITION PART_20080711 VALUES less than ('20080712'),
PARTITION PART_20080712 VALUES less than ('20080713'),
PARTITION PART_20080713 VALUES less than ('20080714'),
PARTITION PART_20080714 VALUES less than ('20080715'),
PARTITION PART_20080715 VALUES less than ('20080716'),
PARTITION PART_20080716 VALUES less than ('20080717'),
PARTITION PART_20080717 VALUES less than ('20080718'),
PARTITION PART_20080718 VALUES less than ('20080719'),
PARTITION PART_20080719 VALUES less than ('20080720'),
PARTITION PART_20080720 VALUES less than ('20080721'),
PARTITION PART_20080721 VALUES less than ('20080722'),
PARTITION PART_20080722 VALUES less than ('20080723'),
PARTITION PART_20080723 VALUES less than ('20080724'),
PARTITION PART_20080724 VALUES less than ('20080725'),
PARTITION PART_20080725 VALUES less than ('20080726'),
PARTITION PART_20080726 VALUES less than ('20080727'),
PARTITION PART_20080727 VALUES less than ('20080728'),
PARTITION PART_20080728 VALUES less than ('20080729'),
PARTITION PART_20080729 VALUES less than ('20080730'),
PARTITION PART_20080730 VALUES less than ('20080731'),
PARTITION PART_20080731 VALUES less than ('20080801'),
PARTITION PART_DEFAULT VALUES LESS THAN(maxvalue)
)
tablespace doms_mv
nologging
build immediate
refresh force
on demand
enable query rewrite
as
SELECT
DMS_TIME.DAY_ID,
DMS_TIME.DAY_NAME,
DMS_TIME.Month_ID,
DMS_AREA.PROVINCE_ID,
DMS_AREA.PROVINCE_NAME,
DMS_AREA.CITY_ID,
DMS_AREA.ALL_NAME,
DMS_TERMINAL.BRAND_ID,
DMS_TERMINAL.TERMINAL_ID,
DMS_ENTRANCE.ENTRANCE_ID,
DTL_LOGIN.LOGIN_FLAG,
DTL_LOGIN.LOGIN_NEW_FLAG,
DTL_LOGIN.MSISDN
FROM
DMS_TIME,
DMS_AREA,
DTL_LOGIN,
DMS_TERMINAL,
DMS_ENTRANCE
WHERE
DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID
AND DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID
AND DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID
AND DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID;
( DMS_ENTRANCE.ENTRANCE_ID=DTL_LOGIN.ENTRANCE_ID )
AND ( DMS_AREA.CITY_ID=DTL_LOGIN.CITY_ID )
AND ( DMS_TIME.DAY_ID=DTL_LOGIN.DAY_ID )
AND ( DMS_TERMINAL.TERMINAL_ID=DTL_LOGIN.TERMINAL_ID )
其中带的参数不是很了解,有高手帮忙解释一下吗?这些参数有哪些使用方式?
在建分区的语句中
partition by range(DAY_id)
SUBPARTITION BY HASH (province_ID) SUBPARTITIONS 32
为什么建partition还要建SUBPARTITION 呢?SUBPARTITION 这个是不是必须的?-------------------
谢谢各位高手~~~~!
(
Gid number(10,3) primary key,
GName varchar2(36),
GDate date
)
partition by range(Gid) 选择要分区的字段
(
partition p1 values less than(10), 确定分区的范围
partition p2 values less than(20),
partition p3 values less than(30),
partition p4 values less than(300),
partition p5 values less than(600),
partition p6 values less than (maxvalue)最大分区的数据
);
物化视图不能进行分区,这是为什么,part字段是inst_id取10的模【mod(INST_ID, 10) PART】
这样处理不是不建不了分区?还是哪里语句还有问题? Thk~
-----------------------------------------------
create materialized view mv_ratable_history
partition by range(part)
(
partition ratable_history_1 values less than (1),
partition ratable_history_2 values less than (2),
partition ratable_history_3 values less than (3),
partition ratable_history_4 values less than (4),
partition ratable_history_5 values less than (5),
partition ratable_history_6 values less than (6),
partition ratable_history_7 values less than (7),
partition ratable_history_8 values less than (8),
partition ratable_history_9 values less than (9),
partition ratable_history_10 values less than (10)
)
tablespace TBS_DATA
nologging
refresh force
on demand
start with (sysdate) next (sysdate+1/60/24/1) WITH rowid
as
SELECT
BASE_ID,
INST_ID,
CYCLE_ID,
VALUE,
mod(INST_ID, 10) PART
FROM
tb_ratable_history
WHERE
cycle_id >= to_char(sysdate, 'yyyymm')
create index IDX01_MV_RATABLE_HISTORY_21 on MV_RATABLE_HISTORY_21 (
ofr_inst_id ASC
)
pctfree 10
initrans 2
storage
(
initial 64K
minextents 1
buffer_pool default
)
tablespace ICC_TBS_DATA_IDX
logging;create index IDX02_MV_RATABLE_HISTORY_21 on MV_RATABLE_HISTORY_21 (
part ASC
)
pctfree 10
initrans 2
storage
(
initial 64K
minextents 1
buffer_pool default
)
tablespace ICC_TBS_DATA_IDX
logging;