偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

Oracle使用hash分區(qū)優(yōu)化分析函數(shù)查詢

數(shù)據(jù)庫(kù) Oracle 數(shù)據(jù)庫(kù)運(yùn)維
本文將介紹Oracle使用hash分區(qū)優(yōu)化分析函數(shù)查詢，一般需要通過(guò)創(chuàng)建一個(gè)分區(qū)表和普通表，插入數(shù)據(jù)等步驟來(lái)進(jìn)行優(yōu)化。

在ORACLE中的分析函數(shù)都是基于某幾個(gè)字段劃分計(jì)算窗口，然后在窗口內(nèi)進(jìn)行聚合，排名，等等計(jì)算。我想如果我們數(shù)據(jù)表的hash分區(qū)字段與分析函數(shù)中的partition by 字段一致的時(shí)候，應(yīng)該可以大大加快分析函數(shù)的運(yùn)行效率。因?yàn)槊總€(gè)分區(qū)上的數(shù)據(jù)可以單獨(dú)進(jìn)行運(yùn)算?；ゲ桓缮?，下面試驗(yàn)來(lái)驗(yàn)證我的想法.

***步：創(chuàng)建一個(gè)分區(qū)表和普通表，表結(jié)構(gòu)與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)
);

第二步：準(zhǔn)備數(shù)據(jù)，從dba_object中把數(shù)據(jù)插入到兩個(gè)表?？偣膊迦霐?shù)據(jù)1610880。

insert into t_partition_hash select * from dba_objects;


insert into t_partition_hash select * from dba_objects;

第三步：本采用RANK函數(shù)對(duì)兩個(gè)表進(jìn)行查詢。

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次的運(yùn)行時(shí)間分別為：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í)行時(shí)間分別為：141.954s，89.656s，77.906s，98.5s，75.906s.

由此可見(jiàn)采用有效的HASH分區(qū)表可以有效提升分析函數(shù)在oracle中的執(zhí)行效率。我相信隨著數(shù)據(jù)量的增加，將會(huì)有更明顯的效果，回頭再測(cè)試一個(gè)項(xiàng)目中遇到的類似問(wèn)題。

【編輯推薦】

  1. 善用Oracle表空間設(shè)計(jì)提升數(shù)據(jù)庫(kù)性能
  2. 優(yōu)化數(shù)據(jù)庫(kù)大幅度提高Oracle的性能
  3. Oracle設(shè)置系統(tǒng)參數(shù)進(jìn)行性能優(yōu)化
責(zé)任編輯：彭凡 來(lái)源： ITPUB
Oraclehash優(yōu)化
相關(guān)推薦

2010-04-19 13:43:38

Oracle分析函數(shù)

2017-03-09 13:11:48

Oracle分析函數(shù)

2010-10-25 10:55:11

Oracle函數(shù)索引

2018-06-26 15:58:06

數(shù)據(jù)庫(kù)MySQL索引優(yōu)化

2009-06-03 10:32:36

Oracle性能優(yōu)化分區(qū)技術(shù)

2010-04-20 13:30:42

2021-01-24 11:46:26

自動(dòng)化Web 優(yōu)化

2019-03-19 14:52:00

性能優(yōu)化MySQL數(shù)據(jù)庫(kù)

2021-08-07 05:00:20

存儲(chǔ)系統(tǒng)

2010-04-22 12:46:55

Oracle hint

2017-09-05 09:02:06

Oraclenot null優(yōu)化

2021-07-04 07:07:06

Ceph分布式存儲(chǔ)架構(gòu)

2017-07-19 09:53:42

Oracle分區(qū)問(wèn)題

2010-04-28 16:45:27

Oracle Inst

2023-07-26 12:38:42

PyGWalker數(shù)據(jù)類型

2021-01-20 08:07:52

oracle分區(qū)單表

2018-04-28 16:20:31

機(jī)器學(xué)習(xí)算法分發(fā)鏈路

2017-05-08 17:40:23

Oracle視圖優(yōu)化案例分析

2010-05-07 11:00:25

Oracle多表查詢

2010-04-12 17:47:01

Oracle多表查詢
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)