介绍分区－概念

以前一直做OLTP系统，接触分区比较少，现在开始仔细研究一下分区表和分区索引

首先为什么要使用分区技术呢？
因为分区技术提高了对大数据量表和索引的管理性、可用性和性能
对于分区表和分区索引，它们的逻辑结构是相同的，有相同的字段和字段类型，但物理结构是不同的，像pctfree、pctused、tablespace可以不同。但hash分区的物理结构除了tablespace外其他都继承table的物理结构

要注意的是分区表和分区索引中所有分区表空间的blocksize必须是相同的，下面使用实验方式来验证

SQL> alter system set db_2k_cache_size = 10m;

System altered.

SQL> create tablespace my2k datafile '/d01/app/oracle/oradata/myora10/my2k01.dbf' size 10m
  2  blocksize 2048;

Tablespace created.

SQL> create table tt ( a int ,b int)
  2  partition by range(a)
  3  (
  4  partition ps1 values less than (10) tablespace jason,
  5  partition ps2 values less than (20) tablespace my2k);
partition ps2 values less than (20) tablespace my2k)
                                               *
ERROR at line 5:
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace MY2K block size 2048 [partition
specification] conflicts with previously specified/implied tablespace JASON block size 8192
[partition specification]

SQL>  alter system set db_2k_cache_size = 0;

很明显，分区表创建在不同block_size的tablespace时报错

我以前认为分区索引必须创建在分区表上，其实不是这样的。全局分区索引不仅可以创建分区表上，而且也可以创建在普通表上

SQL> create table t(a int ,b char);

Table created.

SQL> create index t_i1 on t(a)
  2  global partition by range(a)
  3  (
  4  partition ps1 values less than (10),
  5  partition ps2 values less than (20),
  6  partition ps3 values less than (maxvalue));

Index created.

 

对于range partition和hash partition，可以使用多达16个column key

SQL> create table t(a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int,
  2                 a10 int,a11 int,a12 int,a13 int,a14 int,a15 int,a16 int,a17 int)
  3  partition by range(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17)
  4  (
  5  partition ps1 values less than (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17),
  6  partition ps2 values less than (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,18,19));
partition by range(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17)
                                                                          *
ERROR at line 3:
ORA-14014: maximum number of partitioning columns is 16

以上结果显示，partition columns最大数为16

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8745319/viewspace-588626/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8745319/viewspace-588626/