oracle 创建表分区

以system身份登陆数据库，查看 v$option视图，如果其中Partition为TRUE，则支持分区功能；否则不支持。
dba_segments bytes 查看表多大 10g左右算大，3000w数据。
分区主要解决查询和维护功能。
io情况 1次io 128块 io花费10ms
分区条件：
当表达到GB大小且继续增长
需要将历史数据和当前的数据分开单独处理，
比如历史数据仅仅需要只读，而当前数据则实现DML。
共性：不同的分区之间必须有相同的逻辑属性，
比如表名，列名，数据类型，约束等。
个性：各个分区可以有不同的物理属性，比如pctfree, tablespaces.
a分区坏了，不影响b分区使用
也就是即使某些分区不可用，其他分区仍然可用。

特殊性：含有LONG、LONGRAW数据类型的表不能进行分区。
分区表查询（select * from dictionary where TABLE_NAME like '%PART%'；
dba_tab_partitions）
分区的优点：
节约维护时间：
单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。

在实践中，比较大的索引（开并行），建立索引：
第一步：
 alter session set workarea_size_policy=manual;
 alter session set sort_area_size=1073741824;
 alter session set sort_area_retained_size=1073741824;
 alter session set db_file_multiblock_read_count=128;--
第二步:xxx
 那如果这个表比较大？ 建立一个时间可能要一个上午的时间，或者中间有什么问题，你想终止，
终止完之后，你必须重新建立了，那有什么办法可以分割索引的创建吗？
答：一个一个分区的建。
那么怎么一个个分区的建呢？
是不是希望可以按照分区一个个建立?
先把分区建立失效的索引，然后重建。

创建索引：create index  index_local on test_part(object_id)   local unusable;  
去那里是不是失效索引（ STATUS）：select * from dba_ind_partitions where index_name='INDEX_LOCAL'

然后再批量重建这个索引就可以了:
declare
begin
  for j in (select u.index_name,u.partition_name,ui.table_name from user_ind_partitions u,user_indexes ui where u.status ='UNUSABLE' and u.index_name = ui.index_name) loop
    execute immediate 'alter index '||j.index_name|| ' rebuild partition '||j.partition_name||' online';
  end loop;
end;
ORACLE分区类型：
范围分区、散列分区、列表分区、组合分区
可以对索引和表分区，全局索引只能按范围分区，可以将其定义在任何类型的分区或非分区表上。
通常全局索引比局部索引需要更多的维护
1.Range分区：行映射到基于列值范围的分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。
Range 分区，又成为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。
有多少个表是范围分区

创建范围分区时，必须指定以下内容
分区方法：range
分区列
标识分区边界的分区描述
使用Range 分区的时候，要记住几条规则：
每个分区都包含VALUES LESS THAN字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

建立范围的分区的表
CREATE TABLE t_part (a INTEGER)
PARTITION BY RANGE(a)
(PARTITION p1 VALUES LESS THAN (5),
 PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN (7),
PARTITION p4 VALUES LESS THAN (8)
);
alter table t_part add partition p5 values less than(9);增加表空间。
insert into t_part values(5);
这个值是在哪个分区里面呢?
values less than 
严格的小于号 p2
如何查询分区是否是范围分区?
select partitioning_type from User_Part_Tables  where table_name='T_PART';

比如说我原来的表空间我建立的不对
我其实想批量move 到另外一个表空间?
BEGIN
FOR x IN (SELECT partition_name
FROM user_tab_partitions
WHERE table_name =’T_PART’)
LOOP
EXECUTE IMMEDIATE ‘alter table t_part move partition ‘
|| x.partition_name tablespace oss91_data;
END LOOP;
END;

2. HASH 分区 何为 hash 分区?

在下面这种
情况下，使用hash分区比range分区更好：
事先不知道需要将多少数据映射到给定范围的时候
分区的范围大小很难确定，或者很难平衡的时候
Range分区使数据得到不希望的聚集时
性能特性，如并行DML、分区剪枝和分区连接很重要的时候
创建散列分区时，必须指定以下信息
分区方法：hash
分区列
分区数量或单独的分区描述

创建hash分区:
有两种方法
哪两种
create table DAODAO
(
  USERNUMBER VARCHAR2(32) not null
  )
partition by hash (USERNUMBER)
(
  partition P1
    tablespace USERS,
  partition P2
    tablespace USERS,
  partition P3
    tablespace USERS,
  partition P4
    tablespace USERS,
  partition P5
    tablespace USERS,
  partition P6
    tablespace USERS,
  partition P7
    tablespace USERS,
  partition P8
    tablespace USERS
);

那么有没有另外简单一点的语法创建hash 分区呢?
create table daodao1(usernumber varchar2(32))
partition by hash(usernumber) partitions 8;

hash 分区一般是2的n次方吧，有没有见过6,10这种分区的方式

hash 分区一般使用的场景?

第一： 解决高并发，热点块问题，
为什么可以解决高并发,热点块呢?
一天的数据入库压力比较大
那么，如果我插入3,4,5
连续的插入一个块
那么如果是hash分区之后
3一个块，4是插入一个块，5是插入一个块

由于分区在物理上是独立分开的，
也就是说不同的分区肯定不是同一个数据块，
这样当你插入数据的时候，由于插入到不同的分区，
也就是插入到了不同的数据块：
简单的说：
  比如你原来插入1,2,3,4,..10 
create table t as select object_id from dba_objects where 1=2;
insert into t select rownum from dual connect by level <10;

我如何查询一行数据在哪个数据块?
select dbms_rowid.rowid_block_number(rowid),object_id from t;（大家的块号是一样的吧）

那么就有问题了，并发高的时候都插入同一个块了，就会造成资源紧张
create table t_part(object_id number) partition by hash(object_id) partitions 2;（分区表的建立）
insert into t_part select rownum from dual connect by level <10;
select dbms_rowid.rowid_block_number(rowid),object_id from t_part;

select ora_hash(object_id,1),object_id from t_part;  --其中1=2-1

第一个是分区别名称
第二个是分区数目减1
所以,我是可以通过ora_hash知道数据是插入到哪个分区上的
这个问题,理解了吗?

什么叫索引高度?


查询一个数据
就会hash分区函数计算->高位为3的索引查询->表数据查询




3.List分区：列表分区
List分区可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值
不同于Range分区和Hash分区，


Range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。
hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。
List分区的优点在于按照自然的方式将无序和不相关的数据集合分组。
List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。
List分区时必须指定的以下内容
分区方法：list
分区列
分区描述，每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值:
create table sales_list
    (provcode  number(5)
    )
    partition by list (provcode)

    (partition p1 values (1),
    partition  p2 values  (2),
    partition  p3 values  (3),
    partition  p4 values(DEFAULT) 
    );


何为分区修剪？
在数据仓库中分区修剪是一种非常有效的性能特性。
分析修剪分析SQL中的WHERE 和FROM字句，从而在查询中消除不不必要分区。
分区修剪技术能大大的减少从磁盘上读取的数据量，从而缩短运行时间，改善查询性能，
减少资源浪费。即使你的索引分区和表分区不同，分区修剪也可以在索引上生效
（global partition index），从而消除不必要的索引分区。

分区修剪的特性依赖SQL语句，Oracle 有两种分区修剪：
动态修剪和静态修剪。静态修剪发生在编译时期，在执行计划指定的时候，
已经知道那些分区会被使用。而动态修剪发生在运行时，也就是说在运行的时候，
才会知道那些分区会被用到。例如，WHERE字句里面包含一个函数或者子查询用于返回分区键的值。


select * from test a
where createtime >= trunc(sysdate)-1/3
and createtime < trunc(sysdate)-1/24

把trunc（sysdate）改成定义的时间格式
single
在编译的时候就知道是访问哪个分区了

总结原因:
===================
因为trunc在生成执行计划的时候并不去计算这个值 ,将他作为未知，是一个函数 
to_date 这个oracle在生成执行计划前是知道真实的值的 ,很简单to_date这个在解析之前会去计算这个值 
而trunc,或者a,b,c,d函数在解析的时候是不会去计算精确值的 
 
to_date(trunc(sysdate)-1/24,'yyyy-mm-dd') 这个是多个函数了，其实也是不会计算的,也会扫描多个区


分区交换：
Exchange partition提供了一种方式，让你在表与表或分区与分区之间迁
移数据，注意不是将表转换成分区或非分区的形式，而仅只是迁移表中数
据(互相迁移)，由于其号称是采用了更改数据字典的方式，因此效率最高(
几乎不涉及io操作)。

Exchange partition适用于所有分区格式，你可以将
数据从分区表迁移到非分区表，也可以从非分区表迁移至分区表，或者从
hash partition到range partition诸如此类吧。

注意：在将不符合分区表的数据迁移到分区表中时，可能出现ora-14099的错误，虽然可以用without validation去解决，但是此时进入分区表的数据可能不符合分区规则。所以without validation一定要慎用。


1、CREATE TABLE daodao_a (a INTEGER)
PARTITION BY RANGE(a)
(PARTITION p1 VALUES LESS THAN (5),
 PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN (7),
PARTITION p4 VALUES LESS THAN (8)
);

2、insert into daodao_a values(5);
insert into daodao_a values(6);
insert into daodao_a values(7);
commit;


3、create table daodao_temp (a integer); 
insert into daodao_temp values(9);
commit
4、alter table daodao_a exchange partition p2  with table daodao_temp without validation;（交换数据 把不是分区的表交换到分区）
alter table daodao_a exchange partition p2  with table daodao_temp INCLUDING INDEXES without validation;（包含索引交换）
5、select * from daodao_a where a=9;（没有数据）（
是什么原因呢：
select * from daodao_a  where a=9 等价
select * from daodao_a partition(p4) where a=9 
）
6、指定分区：select * from daodao_a partition(p2) 

hash分区

一、 create table test_normal as select * from dba_objects;

二、create table test_part
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1)
)
partition by hash(OBJECT_ID) partitions 16;

三、insert into test_part select * from test_normal ;
commit;

11g的要执行这个（drop table test_part;
create table test_part
 partition by hash(OBJECT_ID) partitions 16
  as select * from dba_objects
）

四、set timing on;

五 测试查询时间 下面那个更快
select object_id from test_part where object_id=20;快
select object_id from test_normal where object_id=20;慢