oracle 对现有的表进行列表分区

最新推荐文章于 2025-01-26 23:56:18 发布

转载最新推荐文章于 2025-01-26 23:56:18 发布 · 145 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wlzhang/p/4755214.html

文章标签：

#数据库

本文详细介绍了如何使用SQL语句创建多个表空间并进行分区操作，包括设置数据文件大小、重用特性、自动扩展等参数，并通过分区查询优化数据访问效率。

create tablespace pur120000  
datafile 'D:\orcldata\pur120000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur150000  
datafile 'D:\orcldata\pur150000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur220000 
datafile 'D:\orcldata\pur220000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur320000 
datafile 'D:\orcldata\pur320000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur340000 
datafile 'D:\orcldata\pur340000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur360000 
datafile 'D:\orcldata\pur360000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur370000 
datafile 'D:\orcldata\pur370000.dbf' size 1024m  reuse 
autoextend on next 1024m;


create tablespace pur420000
datafile 'D:\orcldata\pur420000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur450000
datafile 'D:\orcldata\pur450000.dbf' size 1024m  reuse 
autoextend on next 1024m;

create tablespace pur650000
datafile 'D:\orcldata\pur650000.dbf' size 1024m  reuse 
autoextend on next 1024m;



 CREATE TABLE base_purchaseinfofq partition by list(provinceareaid)
    (
     partition pur120000 values ('120000') tablespace pur120000,
     partition pur150000 values ('150000') tablespace pur150000,
     partition pur220000 values ('220000') tablespace pur220000,
     partition pur320000 values ('320000') tablespace pur320000,
     partition pur340000 values ('340000') tablespace pur340000,
     partition pur360000 values ('360000') tablespace pur360000,
     partition pur370000 values ('370000') tablespace pur370000,
     partition pur420000 values ('420000') tablespace pur420000,
     partition pur450000 values ('450000') tablespace pur450000,
     partition pur650000 values ('650000') tablespace pur650000
    )
     AS SELECT * FROM base_purchaseinfo where provinceareaid in(
'120000','150000','220000','320000','340000','360000','370000','420000','450000','650000')
  ;

 
 
 select * from base_purchaseinfofq t where t.provinceareaid=650000;

select * from base_purchaseinfofq partition(pur650000) where provinceareaid=650000