ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 分区表实验过程

分区表实验过程

原创 Linux操作系统 作者：howard_zhang 时间：2008-10-14 18:57:38 0 删除 编辑

一、创建测试表
CREATE TABLE par_test
  (    ID NUMBER,
       NAME VARCHAR2(10),
       cardid number
  )
 PARTITION BY RANGE (ID)
(PARTITION testP1  VALUES LESS THAN (1000)
  TABLESPACE par1,
PARTITION testp2  VALUES LESS THAN (2000)
  TABLESPACE par2,
PARTITION testp3  VALUES LESS THAN (3000)
  TABLESPACE par3,
PARTITION testp4  VALUES LESS THAN (4000)
 TABLESPACE par4
);

declare
begin
for i in 1..7999 loop
  insert into par_test values(i,'test'||i,i+1);
  if mod(i,1000)=0 then
  commit;
  end if;
end loop;
end;
/

SQL> COL SEGMENT_NAME FORMAT A12
select segment_name,partition_name,TABLESPACE_NAME from dba_segments where segment_name='PAR_TEST';

SEGMENT_NA PARTITION_NAME                 TABLESPACE_NAME
---------- ------------------------------ ------------------------------
PAR_TEST   TESTP1                         PAR1
PAR_TEST   TESTP2                         PAR2
PAR_TEST   TESTP3                         PAR3
PAR_TEST   TESTP4                         PAR4

SQL>

SQL> SELECT COUNT(*) FROM PAR_TEST PARTITION(TESTP1);

  COUNT(*)
----------
       999

SQL> SELECT COUNT(*) FROM PAR_TEST PARTITION(TESTP2);

  COUNT(*)
----------
      1000

SQL> SELECT COUNT(*) FROM PAR_TEST PARTITION(TESTP3);

  COUNT(*)
----------
      1000

SQL> SELECT COUNT(*) FROM PAR_TEST PARTITION(TESTP4);

  COUNT(*)
----------
      1000

CREATE INDEX IND_TESTPAR ON PAR_TEST (CARDID) LOCAL
(PARTITION testp1  TABLESPACE par1,
PARTITION testp2  TABLESPACE par2,
PARTITION testp3  TABLESPACE par3,
PARTITION testp4  TABLESPACE par4
);


SQL> COL SEGMENT_NAME FORMAT A12
SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
segment_name='IND_TESTPAR';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
IND_TESTPAR  TESTP1                         PAR1
IND_TESTPAR  TESTP2                         PAR2
IND_TESTPAR  TESTP3                         PAR3
IND_TESTPAR  TESTP4                         PAR4

SQL>

添加分区

ALTER TABLE par_test ADD PARTITION testp5 VALUES LESS THAN (5000);

====如果在建立分区的时候指定了MAXVALUE在添加分区的时候会报如下错误：

SQL> ALTER TABLE par_test ADD PARTITION testp6 VALUES LESS THAN (4000);
ALTER TABLE par_test ADD PARTITION testp6 VALUES LESS THAN (4000)
                                   *
第 1 行出现错误:
ORA-14074: 分区界限必须调整为高于最后一个分区界限

解决办法：
检查HIGH_VALUS

SQL> select table_name,partition_name,high_value from dba_tab_partitions WHERE T
ABLE_NAME='PAR_TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------

PAR_TEST                       TESTP
MAXVALUE

PAR_TEST                       TESTP1
1000

PAR_TEST                       TESTP2
2000

PAR_TEST                       TESTP3
3000

删除分区：

SQL> ALTER TABLE PAR_TEST DROP PARTITION TESTP;

表已更改。

结论：
在建立分区的时候可以先建立足够多的分区，否则如果指定了MAXVALUE则在添加分区的时候会出现这个问题。

添加分区：

SQL> ALTER TABLE par_test ADD PARTITION testp5 VALUES LESS THAN (5000);

表已更改。

SQL> select table_name,partition_name,high_value from dba_tab_partitions WHERE T
ABLE_NAME='PAR_TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------

PAR_TEST                       TESTP1
1000

PAR_TEST                       TESTP2
2000

PAR_TEST                       TESTP3
3000

PAR_TEST                       TESTP4
4000

PAR_TEST                       TESTP5
5000

检查分区表和索引的状态：

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
  2  segment_name='IND_TESTPAR';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
IND_TESTPAR  TESTP1                         PAR1
IND_TESTPAR  TESTP2                         PAR2
IND_TESTPAR  TESTP3                         PAR3
IND_TESTPAR  TESTP4                         PAR4
IND_TESTPAR  TESTP5                         USERS

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
  2  segment_name='PAR_TEST';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
PAR_TEST     TESTP1                         PAR1
PAR_TEST     TESTP2                         PAR2
PAR_TEST     TESTP3                         PAR3
PAR_TEST     TESTP4                         PAR4
PAR_TEST     TESTP5                         USERS

SQL>
SQL> ALTER TABLE par_test ADD PARTITION testp6 VALUES LESS THAN (6000);

表已更改。

SQL> ALTER TABLE par_test ADD PARTITION testp7 VALUES LESS THAN (7000);

表已更改。

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
  2  segment_name='IND_TESTPAR';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
IND_TESTPAR  TESTP1                         PAR1
IND_TESTPAR  TESTP2                         PAR2
IND_TESTPAR  TESTP3                         PAR3
IND_TESTPAR  TESTP4                         PAR4
IND_TESTPAR  TESTP5                         USERS
IND_TESTPAR  TESTP6                         USERS
IND_TESTPAR  TESTP7                         USERS

已选择7行。

=====当增加表分区时，LOCAL索引被自动维护===============

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
  2  segment_name='PAR_TEST';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
PAR_TEST     TESTP1                         PAR1
PAR_TEST     TESTP2                         PAR2
PAR_TEST     TESTP3                         PAR3
PAR_TEST     TESTP4                         PAR4
PAR_TEST     TESTP5                         USERS
PAR_TEST     TESTP6                         USERS
PAR_TEST     TESTP7                         USERS

已选择7行。

===========创建全局索引=====================


CREATE INDEX IND_TESTPAR1 ON PAR_TEST (ID) global
partition by range(id)
(PARTITION testP1  VALUES LESS THAN (1000)
  TABLESPACE par1,
PARTITION testp2  VALUES LESS THAN (2000)
  TABLESPACE par2,
PARTITION testp3  VALUES LESS THAN (3000)
  TABLESPACE par3,
PARTITION testp4  VALUES LESS THAN (4000)
 TABLESPACE par4,
PARTITION testp5  VALUES LESS THAN (5000),
PARTITION testp6  VALUES LESS THAN (6000),
PARTITION testp7  VALUES LESS THAN (7000),
PARTITION otherp VALUES LESS THAN (maxvalue)
);

select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
segment_name='IND_TESTPAR1';

合并分区操作：

SQL> alter table par_test merge partitions TESTP4,OTHERP into partition testp;

表已更改。

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
segment_name='PAR_TEST';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
PAR_TEST     TESTP1                         PAR1
PAR_TEST     TESTP2                         PAR2
PAR_TEST     TESTP3                         PAR3
PAR_TEST     TESTP4                         PAR4
PAR_TEST     TESTP                          USERS

SQL> SELECT COUNT(*) FROM PAR_TEST PARTITION(TESTP);

  COUNT(*)
----------
      3001

删除分区：

alter table par_test drop partition testp;

SQL> select segment_name,partition_name,TABLESPACE_NAME from dba_segments where
segment_name='PAR_TEST';

SEGMENT_NAME PARTITION_NAME                 TABLESPACE_NAME
------------ ------------------------------ ------------------------------
PAR_TEST     TESTP1                         PAR1
PAR_TEST     TESTP2                         PAR2
PAR_TEST     TESTP3                         PAR3
PAR_TEST     TESTP4                         PAR4

分区索引测试
SQL> set autotrace traceonly
SQL> select * from par_test where id=32;


执行计划
----------------------------------------------------------
Plan hash value: 827287693

--------------------------------------------------------------------------------

------------------------------------

| Id  | Operation                           | Name         | Rows  | Bytes | Cos

t (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

------------------------------------

|   0 | SELECT STATEMENT                    |              |     2 |    66 |
 3   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE             |              |     2 |    66 |
 3   (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| PAR_TEST     |     2 |    66 |
 3   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                 | IND_TESTPAR1 |     2 |       |
 1   (0)| 00:00:01 |     1 |     1 |

--------------------------------------------------------------------------------

------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"=32)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed


SQL> select * from par_test where cardid=3 and id=2;


执行计划
----------------------------------------------------------
Plan hash value: 43400626

--------------------------------------------------------------------------------

----------------------------------

| Id  | Operation                          | Name        | Rows  | Bytes | Cost
(%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------------

|   0 | SELECT STATEMENT                   |             |     1 |    33 |     1

   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |             |     1 |    33 |     1

   (0)| 00:00:01 |     1 |     1 |

|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PAR_TEST    |     1 |    33 |     1

   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IND_TESTPAR |     1 |       |     1

   (0)| 00:00:01 |     1 |     1 |

--------------------------------------------------------------------------------

----------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=2)
   3 - access("CARDID"=3)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        108  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select * from par_test where id=2 and cardid=3;


执行计划
----------------------------------------------------------
Plan hash value: 43400626

--------------------------------------------------------------------------------

----------------------------------

| Id  | Operation                          | Name        | Rows  | Bytes | Cost
(%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------------

|   0 | SELECT STATEMENT                   |             |     1 |    33 |     1

   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |             |     1 |    33 |     1

   (0)| 00:00:01 |     1 |     1 |

|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PAR_TEST    |     1 |    33 |     1

   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IND_TESTPAR |     1 |       |     1

   (0)| 00:00:01 |     1 |     1 |

--------------------------------------------------------------------------------

----------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=2)
   3 - access("CARDID"=3)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        108  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

重建分区索引：

alter index ind_testpar rebuild partition testp1 online;

 

 

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

0
0
上一篇： 数据字典和动态性能表的文档
下一篇： 通过USER_TAB_MODIFICATIONS来查看数据表的DML过程
请登录后发表评论 登录
全部评论
howard_zhang

注册时间：2008-01-22

  • 博文量
    42
  • 访问量
    142754

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593