ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle分区表学习(三)

oracle分区表学习(三)

原创 Linux操作系统 作者:Nalternative 时间:2011-02-11 09:20:18 0 删除 编辑

分区的好处:

  1. 提高数据的可用性
  2. 由于从数据库中去除了大段,相应的减轻了管理的负担
  3. 改善某些查询的性能
  4. 可以把修改分布到多个单独的分区上,从而减少大容量OLTP系统上的竞争

测试:

        启动oracle发现监听启动不起来:

监听该对象时出错: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12542: TNS: 地址已被占用
 TNS-12560: TNS: 协议适配器错误
  TNS-00512: 地址已在使用
   32-bit Windows Error: 48: Unknown error

并没有更改配置,网络断开重新启动又正常了,可能和网络有点关联。


  1. 提高数据的可用性

             建立分区表,使其中的一个分区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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    174315