建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網營銷解決方案
如何使用Hash分區(qū)優(yōu)化Oracle分析函數
原理：數據表的hash分區(qū)字段與分析函數中的partition by 字段一致的時候，每個分區(qū)上的數據可以單獨進行運算，互不干涉。所以可以很快的提高Oracle分析函數的運行效率。具體測試步驟如下：
創(chuàng)新互聯(lián)-專業(yè)網站定制、快速模板網站建設、高性價比海陵網站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式海陵網站制作公司更省心,省錢,快速模板網站建設找我們，業(yè)務覆蓋海陵地區(qū)。費用合理售后完善，10余年實體公司更值得信賴。
***步：創(chuàng)建一個分區(qū)表和普通表，表結構與DBA_OBJECTS一致：
- create table t_partition_hash(
- object_name varchar2(128),
- subobject_name varchar2(30),
- object_id number,
- data_object_id number,
- object_type varchar2(19),
- created date,
- last_ddl_time date,
- timestamp varchar2(19),
- status varchar2(7),
- temporary varchar2(1),
- generated varchar2(1),
- secondary varchar2(1)
- )
- partition by hash(object_type)(
- partition t_hash_p1 tablespace USERS,
- partition t_hash_p2 tablespace USERS,
- partition t_hash_p3 tablespace USERS,
- partition t_hash_p4 tablespace USERS,
- partition t_hash_p5 tablespace USERS,
- partition t_hash_p6 tablespace USERS,
- partition t_hash_p7 tablespace USERS,
- partition t_hash_p8 tablespace USERS
- );
- create table t_big_hash(
- object_name varchar2(128),
- subobject_name varchar2(30),
- object_id number,
- data_object_id number,
- object_type varchar2(19),
- created date,
- last_ddl_time date,
- timestamp varchar2(19),
- status varchar2(7),
- temporary varchar2(1),
- generated varchar2(1),
- secondary varchar2(1)
- );
#p#
第二步：準備數據，從dba_object中把數據插入到兩個表?？偣膊迦霐祿?610880。
- insert into t_partition_hash select * from dba_objects;
- insert into t_partition_hash select * from dba_objects;
第三步：本采用RANK函數對兩個表進行查詢。
- begin
- insert into t_rank
- select object_id,
- rank() over (partition by object_type order by object_id) r_object_id,
- rank() over (partition by object_type order by subobject_name) r_subobject_name ,
- rank() over (partition by object_type order by created) r_created,
- rank() over (partition by object_type order by last_ddl_time) r_last_ddl_time ,
- rank() over (partition by object_type order by status) r_object_type
- from t_partition_hash;
- end;
使用hash分區(qū)表總共執(zhí)行5次的運行時間分別為：46.156s,33.39s,40.516s 34.875s 38.938s.
- begin
- insert into t_rank
- select object_id,
- rank() over (partition by object_type order by object_id) r_object_id,
- rank() over (partition by object_type order by subobject_name) r_subobject_name ,
- rank() over (partition by object_type order by created) r_created,
- rank() over (partition by object_type order by last_ddl_time) r_last_ddl_time ,
- rank() over (partition by object_type order by status) r_object_type
- from t_big_table;
- end;
使用非分區(qū)表執(zhí)行5次的執(zhí)行時間分別為：141.954s，89.656s，77.906s，98.5s，75.906s.
由此可見采用有效的HASH分區(qū)表可以有效提升Oracle分析函數中的執(zhí)行效率。我相信隨著數據量的增加，將會有更明顯的效果，回頭再測試一個項目中遇到的類似問題。
善用Oracle表空間設計提升數據庫性能
優(yōu)化數據庫大幅度提高Oracle分析函數的性能
Oracle設置系統(tǒng)參數進行性能優(yōu)化
【編輯推薦】
- 修改Oracle存儲過程所需代碼
- 對Oracle存儲過程的總結
- 實現(xiàn)Oracle存儲過程的實際應用的代碼
- 深入高性能的Oracle動態(tài)SQL開發(fā)
- Oracle SQL的優(yōu)化規(guī)則解析
分享標題：如何使用Hash分區(qū)優(yōu)化Oracle分析函數
地址分享：http://www.dlmjj.cn/article/cddcgop.html
