oracle分割槽表學習（三）

Nalternative發表於2011-02-11
Oracle

分割槽的好處：

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章