oracle学习之table

oracle表

  我们使用数据库进行操作的时候，毫无疑问肯定要接触表，这个时候是创建普通表、索引表，还是分区表等？如何设计合理的表就是一个看似简单，但是却很重要的事情。

  普通表：

  这是我们最常使用、最常见的表，只要接触过oracle的人就可以轻松创建。当然这个表只是被极少的使用，因为其表更新开销很大，而且不能方便对数据进行操作，例如删除其中一部分数据，但是无法释放空间。

  分区表：

  分区表是按照表中的某个字段进行分区，可以设置单个分区和组合分区。而查询的时候通过加分区条件可以对表中数据进行分区消除工作，然后提高查询速率。而且可以删除数据过程中通过truncate释放空间等优点，使用比较普遍。具体分类可以分为：范围分区、列表分区、hash分区。下面介绍关于分区表的sql语句：

  创建列表分区：(这里直接写了几个简单分区表建法)

  create table t1

  （a1 number,

     a2  number）

  partition by list(a1)

  (

   partition part_01 values(1),

   partition part_02 values(2)

   );

  创建范围分区：

  create table t2

  (b1 number,

   b2 number)

  partition by range(b1)

  (

   partition  part_01 values less than(2),

  partition part_02 values less than(3),'

  partition part_max values less than(maxvalue)

  );

 hash分区：

  create talbe t3

  (

  c1 number,

  c2 number

  )

  partition by hash(c1)

  partitions 9;    --这里9代之分区个数

  组合分区：

 create table t4

  (

  d1 number,

  d2 number

  )

  partiton by list(d1)

  subparttition by range(d2)

  subpartition template

  (

  subpartition p_1 values less than (2),

  subpartition p_2 values less than (3),

  subpartition p_max values less than (maxvalue)

   )

  (

  partition p1 values(1),

  partition p2 values(2),

  partition p_other values(default)

  );

  分区表建好了，不过在实际生产实践中好多非专业人员都不知道使用利用分区条件进行查询操作，导致分区表白白占用资源，而且不能对生产起到作用，下面从具体使用过程中遇到的问题进行一一解决。

  分区表查询：既然建了分区表就不要浪费，查询时候要加入分区条件，可以高效定位到分区，提高查询速率，例如t1表中加入where a1=1这样的话可以快速定位到a1=1这个分区，跳过a1=2。

  分区表删除：大家都知道普通的表对数据删除的时候，不能释放空间，但是使用truncate的时候就可以释放空间。这时候分区表就可以大显身手了。例如在某生产库里面某过程中有这样的语句EXECUTE IMMEDIATE 'ALTER TABLE xxx TRUNCATE PARTITION PART_'||V_MONTH;毫无疑问，这样就可以实现清除数据释放表空间的作用了，这里xxx是一个分区表。

  分区交换：

  alter table t1 exchange partition part_1 with table tempxxx;

  通过该指令可以进行分区交换，将t1中的数据交换至tmpxxx中，不过要注意只能从单分区表到普通表转换、组合分区表想单分区表转换，其实我感觉这个使用不是很多。

  增加分区：

  增加分区其实就是把原来的一个分区分成两个。

  语句是：alter table t2 split partition part_max at(4) into (partition part_03,partition p_max);

  不过要注意这个语句只是针对范围分区，对于t2而言，本来在part_02和p_max间增加一个part_03分区，而分界点肯定为4了，这样通过查看user_segments就可以明白，多了一个分区part_03。

  那么知道如何增加分区，如何合并分区呢?

  语句为：alter table t2 merge partitions part_03,p_max into  partition p_max;

  其实这个语句更简单，是把part_03和part_max合并为一个part_max分区，当然这个只是把前面一个分区合并到后面一个分区内，要不然又要报错了。

  简单增加删除分区：

  对于最大分区为max时候，如果不删除max分区，就只能用split和merge分别进行增加和合并分区，但是如果最大分区不为max分区时候就可以随意增加分区了，语句为：

  alter table t1 add partition part_03 values(3);

  hash分区：

  create table tt1(testt01 number,testt02 number)

  partition by hash(testt01)

  partitions 6;   

  这样一种简单的写法就创建了一个hash分区表。这部分后面分区最好为偶数。

  索引组织表，这里只简单介绍，关于行溢出之类的概念可以参照网上的索引组织表介绍：索引组织表介绍（这里是引用），或者参照online documents，建表语句如下

  create table  tt1(testt01 number parimary key,testt02 number)

  organization index;   

  表介绍快结束了，差点忘了，还有一个临时表，这个在特殊的场合效率很高滴，记得第一次说听说这个东西，感觉很神奇，一定会在今后用到。其实很简单，这里简短说下。

  临时表顾名思义就是一个临时的表，不仅可以在commit或者session退出之后对数据进行高效清楚，而且对于不同的回话都是独立的，也就是说a用户登录对全局临时表进行操作后，这时候b用户登录查看全局临时表，根本不会看到任何记录，是不是很神奇，这点可以自行测试哈。

  创建语句：

  create global temporary table tt2(t number)

  on commit delete/preserve rows;（顺便加一句，oracle过程中使用临时表要用基于session的，因为插入数据会自动隐式提交，而且过程里面不能使用commit，要不然数据会重复）

  临时表的使用场合就不用我多说了，基于commit的全局临时表，无论commit或者关闭会话都会清空；而基于session的全局临时表，关闭会话会清空，是不是方便，而且产生的log非常少哦，自己动手试试吧。

  真正生产实践中，各位要根据实际情况建表，这里写的只是参考。