以前一直做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/