Oracle分区表
Oracle数据库支持分区技术，通过分区技术把分区列不同的记录放到不同的分区中Oracle可以包含多个分区，每个分区都是独立的段，查询数据时可以访问各个分区的数据，也可以直接指定分区的方法来进行查询。分区表也提高了Oracle管理数据量较大的表的性能在生成中应用的也较多。
分区表有以下优点：
1）将数据分散到各个分区中，降低了数据损坏的可能性
2）可以对单独的分区进行备份和恢复
3）可以将分区映射到不同的物理磁盘上从而可以分散磁盘I/O
4）提高可管理性、可用性
分区种类:
1）范围分区（range）
2）哈希分区（hash）
3）列表分区（list）
4）范围－哈希复合分区（range-hash）
5）范围－列表复合分区（range-list）
示例：（范围分区（range））
1.创建交换分区测试表
SCOTT@ORA11GR2>create table test_part
2 (
3 nid number(10) constraint pk_test_p primary key,
4 idcard varchar2(18),
5 pdd date not null
6 )
7 partition by range (pdd)
8 (
9 partition par_2000 values less than (to_date('2001-01-01','yyyy-mm-dd')),
10 partition par_2001 values less than (to_date('2002-01-01','yyyy-mm-dd')),
11 partition par_2002 values less than (to_date('2003-01-01','yyyy-mm-dd')),
12 partition part_maxvalue values less than (maxvalue)
13 );
Table created.
2.创建索引插入数据
SCOTT@ORA11GR2>create index idx_test_part on test_part(idcard);
Index created.
SCOTT@ORA11GR2>insert into test_part values(500,'2000',to_date('20000101','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>insert into test_part values(501,'2001',to_date('20010201','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>insert into test_part values(502,'2002',to_date('20020301','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
3.查看索引状态
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART VALID
4.创建与测试表相同表结构的表test
SCOTT@ORA11GR2>create table test
2 (
3 nid number(10) constraint pk_test primary key,
4 idcard varchar2(18),
5 pdd date not null
6 );
Table created.
SCOTT@ORA11GR2>insert into test select rownum,rpad(rownum,18,'0'),to_date('20020101','yyyymmdd')+rownum from dual connect by rownum<=100;
100 rows created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select min(pdd),max(pdd) from test;
MIN(PDD) MAX(PDD)
------------------- -------------------
2002-01-02 00:00:00 2002-04-11 00:00:00
5.交换分区
SCOTT@ORA11GR2>alter table test_part exchange partition par_2002 with table test;
Table altered.
SCOTT@ORA11GR2>select count(*) from test;
COUNT(*)
----------
1
SCOTT@ORA11GR2>select * from test;
NID IDCARD PDD
---------- ------------------ -------------------
502 2002 2002-03-01 00:00:00
SCOTT@ORA11GR2>select count(*) from test_part;
COUNT(*)
----------
102
SCOTT@ORA11GR2>select count(*) from test_part partition (par_2002);
COUNT(*)
----------
100
6.查看索引状态
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART UNUSABLE
7.重建索引
SCOTT@ORA11GR2>alter index idx_test_part rebuild online;
Index altered.
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART VALID
SCOTT@ORA11GR2>create table test_part
2 (
3 nid number(10) constraint pk_test_p primary key,
4 idcard varchar2(18),
5 pdd date not null
6 )
7 partition by range (pdd)
8 (
9 partition par_2000 values less than (to_date('2001-01-01','yyyy-mm-dd')),
10 partition par_2001 values less than (to_date('2002-01-01','yyyy-mm-dd')),
11 partition par_2002 values less than (to_date('2003-01-01','yyyy-mm-dd')),
12 partition part_maxvalue values less than (maxvalue)
13 );
Table created.
2.创建索引插入数据
SCOTT@ORA11GR2>create index idx_test_part on test_part(idcard);
Index created.
SCOTT@ORA11GR2>insert into test_part values(500,'2000',to_date('20000101','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>insert into test_part values(501,'2001',to_date('20010201','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>insert into test_part values(502,'2002',to_date('20020301','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
3.查看索引状态
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART VALID
4.创建与测试表相同表结构的表test
SCOTT@ORA11GR2>create table test
2 (
3 nid number(10) constraint pk_test primary key,
4 idcard varchar2(18),
5 pdd date not null
6 );
Table created.
SCOTT@ORA11GR2>insert into test select rownum,rpad(rownum,18,'0'),to_date('20020101','yyyymmdd')+rownum from dual connect by rownum<=100;
100 rows created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select min(pdd),max(pdd) from test;
MIN(PDD) MAX(PDD)
------------------- -------------------
2002-01-02 00:00:00 2002-04-11 00:00:00
5.交换分区
SCOTT@ORA11GR2>alter table test_part exchange partition par_2002 with table test;
Table altered.
SCOTT@ORA11GR2>select count(*) from test;
COUNT(*)
----------
1
SCOTT@ORA11GR2>select * from test;
NID IDCARD PDD
---------- ------------------ -------------------
502 2002 2002-03-01 00:00:00
SCOTT@ORA11GR2>select count(*) from test_part;
COUNT(*)
----------
102
SCOTT@ORA11GR2>select count(*) from test_part partition (par_2002);
COUNT(*)
----------
100
6.查看索引状态
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART UNUSABLE
7.重建索引
SCOTT@ORA11GR2>alter index idx_test_part rebuild online;
Index altered.
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_PART VALID
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31362743/viewspace-2123520/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
日志挖掘—logminer
请登录后发表评论
登录
全部评论
