oracle分区表学习（三）
分区的好处：
- 提高数据的可用性
- 由于从数据库中去除了大段，相应的减轻了管理的负担
- 改善某些查询的性能
- 可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争
测试：
启动oracle发现监听启动不起来：
监听该对象时出错: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12542: TNS: 地址已被占用
TNS-12560: TNS: 协议适配器错误
TNS-00512: 地址已在使用
32-bit Windows Error: 48: Unknown error
并没有更改配置，网络断开重新启动又正常了，可能和网络有点关联。
- 提高数据的可用性
建立分区表，使其中的一个分区offline，查询测试：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> CREATE TABLE sales_range
2 (salesman_id NUMBER(5),
3 salesman_name VARCHAR2(30),
4 sales_amount NUMBER(10),
5 sales_date DATE)
6 COMPRESS
7 PARTITION BY RANGE(sales_date)
8 (PARTITION sales_before2000 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY')) tablespace mytest1,
9 PARTITION sales_after2000 VALUES LESS THAN(TO_DATE('01/01/2050','DD/MM/YYYY')) tablespace mytest2);
Table created
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate-11);
1 row inserted
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate);
1 row inserted
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate-20*365);
1 row inserted
SQL> select *From sales_range partition (sales_before2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ -----------
234 4354 1991-2-16 1
SQL> select *From sales_range partition (sales_after2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ -----------
234 4354 2011-1-31 1
234 4354 2011-2-11 1
SQL> alter tablespace mytest1 offline;
Tablespace altered
SQL> select *From sales_range;
select *From sales_range
ORA-00376: 此时无法读取文件 9
ORA-01110: 数据文件 9: 'F:\ORACLE\PRODUCT\MYTEST1.DBF'
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate);
1 row inserted
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate-20*365);
insert into sales_range(salesman_id,salesman_name,sales_date) values(234,'4354',sysdate-20*365)
ORA-00376: 此时无法读取文件 9
ORA-01110: 数据文件 9: 'F:\ORACLE\PRODUCT\MYTEST1.DBF'
SQL> insert into sales_range(salesman_id,salesman_name,sales_date) values(4,'4354',sysdate);
1 row inserted
SQL> select *From sales_range where salesman_id=4;
select *From sales_range where salesman_id=4
ORA-00376: 此时无法读取文件 9
ORA-01110: 数据文件 9: 'F:\ORACLE\PRODUCT\MYTEST1.DBF'
SQL> select *From sales_range where sales_date>trunc(sysdate);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ -----------
234 4354 2011-2-11 1
234 4354 2011-2-11 1
4 4354 2011-2-11 1
SQL>
1、如果应用在查询中使用了分区键，就能够提高这些应用的可用性
2、优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
3、出现错误的停机时间会减少，因为恢复所需的工作量大幅减少。
减少管理负担
改善语句性能
并行DML
查询性能：分区消除，并行操作
分区为2的次幂，分布均匀测试:参考
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
create or replace
procedure hash_proc
( p_nhash in number,
p_cursor out sys_refcursor )
authid current_user -------调用者权限
as
l_text long;
l_template long :=
'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
'from t partition ( $PNAME$ ) union all ';
begin
begin
execute immediate 'drop table t';
exception when others
then null;
end;
execute immediate '
CREATE TABLE t ( id )
partition by hash(id)
partitions ' || p_nhash || '
as
select rownum
from all_objects';
for x in ( select partition_name pname,
PARTITION_POSITION pos
from user_tab_partitions
where table_name = 'T'
order by partition_position )
loop
l_text := l_text ||
replace(
replace(l_template,
'$POS$', x.pos),
'$PNAME$', x.pname );
end loop;
open p_cursor for
'select pname, cnt,
substr( rpad(''*'',30*round( cnt/max(cnt)over(),2),''*''),1,30) hg
from (' || substr( l_text, 1, length(l_text)-11 ) || ')
order by oc';
end;
/
SQL> variable x refcursor
SQL> set autoprint on
SQL> exec hash_proc( 4, :x );
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------------------------------------
p1 14426 *****************************
p2 14358 *****************************
p3 14635 ******************************
p4 14254 *****************************
SQL> exec hash_proc( 5, :x );
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------------------------------------
p1 7215 **************
p2 14359 *****************************
p3 14635 ******************************
p4 14254 *****************************
p5 7211 **************
SQL> exec hash_proc( 6, :x );
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------------------------------------
p1 7215 **************
p2 7315 ***************
p3 14635 ******************************
p4 14255 *****************************
p5 7211 **************
p6 7044 **************
已选择6行。
SQL> exec hash_proc( 7, :x );
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------------------------------------
p1 7215 ***************
p2 7315 ***************
p3 7325 ***************
p4 14256 ******************************
p5 7211 ***************
p6 7044 **************
p7 7310 ***************
已选择7行。
SQL> exec hash_proc( 8, :x );
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------------------------------------
p1 7215 *****************************
p2 7315 ******************************
p3 7325 ******************************
p4 7099 *****************************
p5 7211 *****************************
p6 7044 ****************************
p7 7310 ******************************
p8 7158 *****************************
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25323853/viewspace-687000/，如需转载，请注明出处，否则将追究法律责任。
