Oracle使用分区来避免磁盘争用

原创

Art_Hero 2011-11-10 21:14:26 博主文章分类：Oracle ©著作权

©著作权归作者所有：来自51CTO博客作者Art_Hero的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建分区表,使用分区来避免磁盘争用
今天我们主要讲解范围（range）分区，分区可以很好的提高效率。如果一个表的分区（片段）位于不同的设备上，吞吐量就会大大增加。分区还可以被独立地备份和恢复（即便它们正在使用中），这样可以减少备份期间可能出现的磁盘I/O问题。仅仅当分区被正确实现后，才能体现Oracle性能提高的良好特点。理解分区的最好方法就是看一个例子。根据deptno列，dept表被分成了3个分区（片段）。
#su - oracle
#sqlplus / as sysdba
SQL> startup
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> conn system/tiger
SQL> alter user scott account unlock identified by tiger;
SQL> conn scott/tiger
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

SQL> create table deptbak as select * from dept;

Table created.

SQL> drop table dept;
drop table dept
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop table dept cascade constraints;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
BIN$sV3WqBHABH3gRAAMKXL6gA==$0 TABLE
DEPTBAK                        TABLE

SQL> purge table dept;

Table purged.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
DEPTBAK                        TABLE

SQL> exit
# mkdir -p /opt/oracle/oracle/product/10.2.0/scott/tablespace/
# sqlplus /nolog
SQL> conn system/tiger
SQL> create tablespace dept1 datafile '/opt/oracle/oracle/product/10.2.0/scott/tablespace/dept1.dbf' size 500M;
SQL> create tablespace dept2 datafile '/opt/oracle/oracle/product/10.2.0/scott/tablespace/dept2.dbf' size 500M;
SQL> create tablespace dept3 datafile '/opt/oracle/oracle/product/10.2.0/scott/tablespace/dept3.dbf' size 500M;
SQL> conn scott/tiger
--使用3个分区创建表dept
SQL>create table dept
(
deptno number(2),
dept_name varchar2(30))
partition by range (deptno)
(partition d1 values less than (10) tablespace dept1,
partition d2 values less than (20) tablespace dept2,
partition d3 values less than (maxvalue) tablespace dept3);
--把数据添加到表的3个分区上：
SQL> insert into dept values (1,'ADMIN');
SQL>insert into dept values (7,'MGMT');
SQL>insert into dept values (10,'MANUF');
SQL>insert into dept values (15,'ACCT');
SQL>insert into dept values (22,'SALES');
--我们只从一个分区中选取数据并且只访问一个分区：
SQL> select * from dept partition (d1);

    DEPTNO DEPT_NAME
---------- ------------------------------
         1 ADMIN
         7 MGMT

SQL> select * from dept partition (d2);

    DEPTNO DEPT_NAME
---------- ------------------------------
        10 MANUF
        15 ACCT

SQL> select * from dept partition (d3);

DEPTNO DEPT_NAME
---------- ------------------------------
22 SALES

SQL> select * from dept where deptno = 22;

DEPTNO DEPT_NAME
---------- ------------------------------
22 SALES

----查看表是否已被分区
SQL> select table_name, partitioned from user_tables where table_name='DEPT';

TABLE_NAME PAR
------------------------------ ---
DEPT YES

----查看表中共有几个分区
SQL> select table_name, partition_count from user_part_tables where table_name = 'DEPT';

TABLE_NAME PARTITION_COUNT
------------------------------ ---------------
DEPT 3

----查看分区的具体信息
SQL> select segment_name, partition_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
DEPTBAK
TABLE USERS

SALGRADE
TABLE USERS

BONUS
TABLE USERS

EMP
TABLE USERS

DEPT
D1 TABLE PARTITION DEPT1

DEPT
D3 TABLE PARTITION DEPT3

8 rows selected.

SQL> exit

后续讲解其它分区，大家要多多支持我哦！