partition 分区:
1.对表进行单列的范围分区
partition by range (列名)
( partition s1 values less than(列名值的范围),
partition s2 values less than(列名值的范围));
2.对表进行多列的范围分区
partition by range(列名1,列名2)
( partition s1 values less than(列名值1的范围,列名值2的范围),
partition s2 values less than(列名值1的范围,列名值2的范围)
);
3. hash 分区
partition by hash(列名)
(partition s1,
partition s2
);
4. 复合分区
partition by range(列名1)
subpartition by hash(列名2,列名3)
( partition s1 values less than(10),
(subpartition t1,subpartition t2),
partition s2 values less than(20),
(subpartition t3,subpartition t4)
);
5. 列表分区
partition by list(列名)
( partition v1 values(列名值1),
partition v2 values(列名值2)
);
oracle中rank()、dense_rank、partition、over()、fetc的用法
merge into 可以把一个表中的行合并到另一个表中去,主要用途批量跟新数据
merge into 表名1 aa using 表名2 bb
on (aa.字段1=bb.字段1)
when matched then
update set aa.字段2=bb.字段2
delete where (aa.字段名='字段值')
when not matched then
insert values(aa.字段2,aa.字段3)