Oracle database 分区表/簇表/物化视图/dblink

分区表

建表时创建分区后可指定表空间
按分区查看数据
select * from sale partition(p1) ;
截断p3分区
alter table sale truncate partition p3 update global indexes;
查看表的所有分区
select table_name,partition_name,high_value from user_tab_partitions;

range分区

以sale列创建分区、p1区上限值为1000 在表空间a中
partition by range(sale) (partition p1 values less than (1000) tablespace a);
为表sale添加一个p2分区
alter table sale add partition p2 values less than (maxvalue);

hash分区

以sale列创建分区、p1,p2接近平均分布
partition by hash(sale) (partition p1 ,partition p2 );

列表（list）分区

以city列创建分区 值为’herbin’ 插入到north区
partition by list(city) (partition north values (‘herbin’));

复合（Composite）分区

（把范围分区和散列分区相结合或者 范围分区和列表分区相结合。）
有三个range分区，对每个分区会有4个hash分区、共有12个分区
partition by range(sno) subpartition by hash(sname) subpartitions 4
(partition p1 values less than(1000),partition p2 values less than(2000),partition p3 values less than(maxvalue));

间隔（Interval）分区（可以自动创建分区）

一个月
partition by range(a_time) interval (numtoyminterval(1,’month’))
20120201之前的数据会存放在p1分区、超过一个月自动创建一个分区
(partition p1 values less than (to_date(‘20120201’,’yyyymmdd’)));

引用（Reference）分区

引用外键约束继承父表分区
PARTITION BY REFERENCE(o_fk);

虚拟列（Virtual ColumnBased）分区

允许修改分区键，并自动根据修改后的的值，决定记录是否被move到其它表分区
total_sal AS (salary+bonus)
partition by range (total_sal)
(partition p1 values less than (5000),partition p2 values less than (maxvalue))
enable row movement;

---

分区表索引

根据分区创建索引
create index sale_idx on sale(sales_count) local;
查看分区索引
select * from user_ind_partitions;
全局索引分区
create index sale_global_idx on sale(sales_count) global
partition by range (sales_count)(partition p1 values less than(1500),partition p2 values less than(maxvalue));

---

索引组织表 （加快select速度）

organization index pctthreshold 30 overflow tablespace users;

---

簇表 （加快a_id=b_id的查询速度）

创建簇段
create cluster cluster1(code_key number);
将a_id放到簇段中
create table a(a_id number, name varchar2(10)) cluster cluster1(a_id);
将b_id放到簇段中
create table b(b_id number, name varchar2(10)) cluster cluster1(b_id);
为簇创建索引
create index index1 on cluster cluster1 ;
查看簇
select * from user_clusters;
删除簇
drop cluster cluster1;

---

临时表

创建临时表
create global temporary table tmp_a (id int ,name varchar2(20)) on commit preserve rows;

只读表

alter table emp read only ;
alter table emp read write ;

压缩表（节省空间）

CREATE TABLE … COMPRESS BASIC;
CREATE TABLE … COMPRESS FOR OLTP…

---

物化视图

创建物化视图
create materialized view log on test;
创建物化视图 根据提交刷新 查询重写
create materialized view test_view1 refresh fast on commit enable query rewrite as select * from test;
创建物化视图 30秒刷新一次
create materialized view test_view2 refresh fast start with sysdate next sysdate+1/2880 with rowid as select * from test;
手动刷新
create materialized view test_view3 refresh fast with rowid as select * from test;
手动重建
exec dbms_mview.refresh (‘test_view3’,’C’);
手动刷新
exec dbms_mview.refresh (‘test_view3’,’F’);

---

dblink

创建dblink 使用链接符asm
sys用户下create public database link my_link connect to scott identified by scott using ‘asm’;
测试可以连接asm数据库
scott用户下select * from scott.test@my_link ;
创建asm数据库中scott.test的物化视图 30秒刷新一次
create materialized view test_view4 refresh fast start with sysdate next sysdate+1/2880 with rowid as select * from scott.test@my_link;