oracle大資料量。表分區提示查詢效率

標籤：

現在業務有一張usertrack 記錄資料表。每天會產生30萬條資料。資料量大查詢效率會非常慢

所以我考慮通過表分區來提示效率  邏輯上是一張表。但是分區後會按照分區條件將資料分在不同的物理檔案

優點：

1)   改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

2)   增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

3)   維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

4)   均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能。

缺點：

分區表相關，已經存在的表沒有方法可以直接轉化為分區表。

 

表分區主要分為 定界分割  列表分區  散列分區  組合範圍散列分區  複合範圍散列分區(這個大大的部落格說的很詳細:http://www.cnblogs.com/leiOOlei/archive/2012/06/08/2541306.html)

 

我的業務情境選擇使用定界分割

因為分區表不能在已存在的表上建立 所以要備份資料刪除原來的表 再重新建立這個表為分區表

--將資料備份到暫存資料表create table usertracktemp

as select * from  usertrack

　

--檢查資料是否一致select count(1) from usertracktempselect count(1) from usertrack

--刪除表truncate table usertrack;  drop table usertrack

--建立表同時建立分區create table usertrack  PARTITION BY RANGE(createtime)  (   PARTITION  prot20151101 values LESS THAN (TO_DATE(‘2015-11-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  prot20151201 values LESS THAN (TO_DATE(‘2015-12-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  prot20160201 values LESS THAN (TO_DATE(‘2016-02-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  prot20160301 values LESS THAN (TO_DATE(‘2016-03-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  prot20160401 values LESS THAN (TO_DATE(‘2016-04-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  prot20160501 values LESS THAN (TO_DATE(‘2016-05-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)), PARTITION  protmaxt values less  THAN (maxvalue))  as  select  *  from usertracktemp 

我可以明確資料在這個範圍區間。。按每月來分區 所以是直接寫死了。不能明確的話先建立分區表。 寫個預存程序 時間分組。然後通過指標遍曆 再在分區表上建立新的分區

常用的幾個命令

 --查詢指定分區的資料select createtime from usertrack PARTITION(protmaxt)

--查詢指定表下面的所有分區select * from user_segments where segment_type=‘TABLE PARTITION‘ and segment_name=‘USERTRACK‘;

segment_name為表名 記得統一大寫

--有maxvalue分區條件的時候建立分區文法ALTER TABLE usertrackTest SPLIT PARTITION protmaxt AT (TO_DATE(‘2016-06-06 16:00:15‘,‘yyyy-mm-dd hh24:mi:ss‘)) INTO (PARTITION  part20160606, PARTITION protmaxt) UPDATE GLOBAL INDEXES

--沒有maxvalue分區條件的時候ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE(‘2003-06-01‘,‘YYYY-MM-DD‘)); 

這個時候我還要建立一個工作 每個月執行一次 建立新的分區。然後當前月產生的資料就插入到這個心的分區裡面

建立一個每月執行一次的預存程序

create or replace procedure CreatePartitionas--聲明變數 partitionName varchar(20); createSqlText varchar(300); partitionDate date;begin  partitionDate:=ADD_MONTHS(sysdate,1); partitionName:=‘part‘||to_char(partitionDate,‘yyyymmdd‘);
  createSqlText:=‘ALTER TABLE usertrackTest SPLIT PARTITION protmaxt AT (TO_DATE(‘‘‘||to_char(partitionDate,‘yyyy-mm-dd hh24:mi:ss‘)||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)) INTO (PARTITION  ‘|| partitionName||‘, PARTITION protmaxt) UPDATE GLOBAL INDEXES‘;  dbms_output.put_line(createSqlText); execute immediate createSqlText;end;

建立一個工作

　

--查詢工作
select * from sys.user_jobs

常用的時間間隔指令

1:每分鐘執行

Interval => TRUNC(sysdate,‘mi‘) + 1/ (24*60)

或

Interval => sysdate+1/1440

2:每天定時執行

例如：每天的淩晨1點執行

Interval => TRUNC(sysdate) + 1 +1/ (24)

3:每周定時執行

例如：每周一淩晨1點執行

Interval => TRUNC(next_day(sysdate,‘星期一‘))+1/24

4:每月定時執行

例如：每月1日淩晨1點執行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定時執行

例如每季度的第一天淩晨1點執行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q‘) + 1/24

6:每半年定時執行

例如：每年7月1日和1月1日淩晨1點

Interval => ADD_MONTHS(trunc(sysdate,‘yyyy‘),6)+1/24

7:每年定時執行

例如：每年1月1日淩晨1點執行

Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy‘),12)+1/24

效能測試

不加任何查詢條件

使用了分區條件進行篩選

可以看出各項指標分區後的都優於沒有分區的表

oracle大資料量。表分區提示查詢效率