Oracle分区表

select rowid,id,display,key_value,key_type,lang from oa.dicts

-->>物理层面的
--从物理层面上提速
--一、内存的提升
--修改SGA
 alter system set sga_max_size=4096M scope=spfile;
 alter system set sga_target=4096M scope=spfile;
 
--二、硬盘
 --1.表空间与SSD的结合
 --2.N个硬盘（重点）
 -- oracle的分区（又一杀手锏）
 --分区又分多种
 
 
 --三、cpu
 
 
 --1、创建分区对应的表空间
--模拟3个表空间，对应3个分区
create tablespace data_p1
    logging datafile 'C:\DATA2\001.dbf'
    size 30m 
    autoextend on next 5m maxsize 1024m
    extent management local
    segment space management auto;


create tablespace data_p2
    logging datafile 'C:\DATA2\002.dbf'
    size 30m 
    autoextend on next 5m maxsize 1024m
    extent management local
    segment space management auto;
    
create tablespace data_p3
    logging datafile 'C:\DATA2\003.dbf'
    size 30m 
    autoextend on next 5m maxsize 1024m
    extent management local
    segment space management auto;

-- 此3个表空间，只用于分区表
-- 不用于默认值
-- 建立一个默认的表空间
create tablespace data_default
    logging datafile 'C:\DATA2\default.dbf'
    size 30m 
    autoextend on next 5m maxsize 1024m
    extent management local
    segment space management auto;
    
-- 建立一个临时表空间
create temporary tablespace cat_temp
    tempfile 'C:\DATA2\temp_001.dbf' size 30m
    autoextend on next 5m maxsize 1024m
    extent management local;
    
-- 建立用户
create user cat identified by 123456
    default tablespace data_default
    temporary tablespace cat_temp;
    
-- 授权
grant connect,resource,dba to cat;

 


 
create table t_user(
   id number primary key,
   user_name varchar2(500),
   record_date number
);
select oa.myseq.nextval from dual;
create sequence myseq2;

select myseq2.nextval from dual;

insert into t_user values (myseq2.nextval,'小明',201801);
commit;
select count(1) from t_user

-- 快速让我们的数据倍增
insert into t_user(
  select 
    myseq2.nextval id,
    user_name,
    record_date
  from t_user
);
-- 400万

insert into t_user(
  select 
    myseq2.nextval id,
    user_name,
    201803 record_date
  from t_user
);

-- 算时间(耗时1.270秒)
select count(1) from t_user where record_date=201802

-- 创建分区表
create table t_user2(
   id number primary key,
   user_name varchar2(500),
   record_date number
)
PARTITION BY RANGE (record_date)
(
PARTITION part01 VALUES LESS THAN (201802) TABLESPACE data_p1,
PARTITION part02 VALUES LESS THAN (201803) TABLESPACE data_p2，
PARTITION part03 VALUES LESS THAN (201804) TABLESPACE data_p3
);  
-------------
insert into t_user2(
   select * from t_user
)

select count(1) from t_user2


alter tablespace data_default
   add datafile 'c:\data2\DEFAULT02.DBF'
   size 100m
   autoextend on next 5m maxsize 2048m;
   
   
-- 算时间(没分区：耗时1.47秒，有分区0.45秒)  
select count(1) from t_user2 where record_date=201802--按时间字段分区，随着时间的流失，数据的增长，性能不会因为
--数据量的增大而下降

select * from dba_part_tables where table_name='T_USER2'
select * from dba_tab_partitions where table_name='T_USER2'

select SUM(BYTES)/1024/1024 from dba_free_space 
where tablespace_name='DATA_DEFAULT'

select * from dba_free_space 
where tablespace_name='DATA_DEFAULT'

select count(1) from t_user