oracle分區(qū)表創(chuàng)建(自動按年、月、日分區(qū))實戰(zhàn)記錄
前言：
工作中有一張表一年會增長100多萬的數(shù)據(jù)，量雖然不大，可是表字段多，所以一年下來也會達到 1G，而且只增不改，故考慮使用分區(qū)表來提高查詢性能，提高維護性。
oracle 11g 支持自動分區(qū)，不過得在創(chuàng)建表時就設置好分區(qū)。
如果已經(jīng)存在的表需要改分區(qū)表，就需要將當前表 rename后，再創(chuàng)建新表，然后復制數(shù)據(jù)到新表，然后刪除舊表就可以了。
一、為什么要分區(qū)（Partition）
1、一般一張表超過2G的大小，ORACLE是推薦使用分區(qū)表的。
2、這張表主要是查詢，而且可以按分區(qū)查詢，只會修改當前最新分區(qū)的數(shù)據(jù)，對以前的不怎么做刪除和修改。
3、數(shù)據(jù)量大時查詢慢。
4、便于維護，可擴展：11g 中的分區(qū)表新特性：Partition（分區(qū)）一直是 Oracle 數(shù)據(jù)庫引以為傲的一項技術(shù)，正是分區(qū)的存在讓 Oracle 高效的處理海量數(shù)據(jù)成為可能，在 Oracle 11g 中，分區(qū)技術(shù)在易用性和可擴展性上再次得到了增強。
5、與普通表的 sql 一致，不需要因為普通表變分區(qū)表而修改我們的代碼。
二、oracle 11g 如何按天、周、月、年自動分區(qū)
2.1 按年創(chuàng)建
numtoyminterval(1, 'year')
--按年創(chuàng)建分區(qū)表
create table test_part
(
?? ID NUMBER(20) not null,
?? REMARK VARCHAR2(1000),
?? create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));
--創(chuàng)建主鍵
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
-- Create/Recreate indexes
create index test_part_create_time on TEST_PART (create_time);2.2 按月創(chuàng)建
numtoyminterval(1, 'month')
--按月創(chuàng)建分區(qū)表
create table test_part
(
?? ID NUMBER(20) not null,
?? REMARK VARCHAR2(1000),
?? create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));
--創(chuàng)建主鍵
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;2.3 按天創(chuàng)建
NUMTODSINTERVAL(1, 'day')
--按天創(chuàng)建分區(qū)表
create table test_part
(
?? ID NUMBER(20) not null,
?? REMARK VARCHAR2(1000),
?? create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));
--創(chuàng)建主鍵
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;2.4 按周創(chuàng)建
NUMTODSINTERVAL (7, 'day')
--按周創(chuàng)建分區(qū)表
create table test_part
(
?? ID NUMBER(20) not null,
?? REMARK VARCHAR2(1000),
?? create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));
--創(chuàng)建主鍵
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;2.5 測試
可以添加幾條數(shù)據(jù)來看看效果，oracle 會自動添加分區(qū)。 --查詢當前表有多少分區(qū) select table_name,partition_name from user_tab_partitions where table_name='TEST_PART'; --查詢這個表的某個（SYS_P21）里的數(shù)據(jù) select * from TEST_PART partition(SYS_P21);
三、numtoyminterval 和 numtodsinterval 的區(qū)別
3.1 numtodsinterval(<x>,<c>) ，x 是一個數(shù)字，c 是一個字符串。
把 x 轉(zhuǎn)為 interval day to second 數(shù)據(jù)類型。
常用的單位有 ('day','hour','minute','second')。
測試一下：
select sysdate, sysdate + numtodsinterval(4,'hour') as res from dual;
結(jié)果：
3.2 numtoyminterval (<x>,<c>)
將 x 轉(zhuǎn)為 interval year to month 數(shù)據(jù)類型。
常用的單位有 ('year','month')。
測試一下：
select sysdate, sysdate + numtoyminterval(3, 'year') as res from dual;
結(jié)果：
四、默認分區(qū)
4.1 partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd'))。
表示小于 2018-11-01 的都放在 part_t01 分區(qū)表中。
五、給已有的表分區(qū)
需要先備份表，然后新建這個表，拷貝數(shù)據(jù)，刪除備份表。
-- 1. 重命名
alter table test_part rename to test_part_temp;
-- 2. 創(chuàng)建 partition table
create table test_part
(
?? ID NUMBER(20) not null,
?? REMARK VARCHAR2(1000),
?? create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));-- 3. 創(chuàng)建主鍵
alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;
-- 4. 將 test_part_temp 表里的數(shù)據(jù)遷移到 test_part 表中
insert into test_part_temp select * from test_part;
-- 5. 為分區(qū)表設置索引
-- Create/Recreate indexes create index test_part_create_time_1 on TEST_PART (create_time);
-- 6. 刪除老的 test_part_temp 表
drop table test_part_temp purge;
-- 7. 作用是：允許分區(qū)表的分區(qū)鍵是可更新。
-- 當某一行更新時，如果更新的是分區(qū)列，并且更新后的列植不屬于原來的這個分區(qū)，
-- 如果開啟了這個選項，就會把這行從這個分區(qū)中 delete 掉，并加到更新后所屬的分區(qū)，此時就會發(fā)生 rowid 的改變。
-- 相當于一個隱式的 delete + insert ，但是不會觸發(fā) insert/delete 觸發(fā)器。
alter table test_part enable row movement;
六、全局索引和 Local 索引
我的理解是：
當查詢經(jīng)?？绶謪^(qū)查，則應該使用全局索引，因為這是全局索引比分區(qū)索引效率高。
當查詢在一個分區(qū)里查詢時，則應該使用 local 索引，因為本地索引比全局索引效率高。
總結(jié)
到此這篇關(guān)于oracle分區(qū)表創(chuàng)建(自動按年、月、日分區(qū))的文章就介紹到這了,更多相關(guān)oracle分區(qū)表創(chuàng)建內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
PLSQL Developer連接oracle數(shù)據(jù)庫配置教程
這篇文章主要介紹了PLSQL Developer連接oracle數(shù)據(jù)庫配置步驟,本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2019-09-09
這篇文章介紹了Oracle中執(zhí)行動態(tài)SQL的方法，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
oracle官網(wǎng)下載資源報錯400?Bad?Request?Request?Header?Or?Cookie
這篇文章主要介紹了oracle官網(wǎng)下載資源報錯400?Bad?Request?Request?Header?Or?Cookie?Too?Large解決辦法,這通常是因為您的瀏覽器發(fā)送的請求頭或Cookie過大,超出了服務器允許的限制,需要的朋友可以參考下2025-02-02
Oracle執(zhí)行計劃查看方法匯總及優(yōu)劣對比詳解
這篇文章主要介紹了Oracle執(zhí)行計劃查看方法匯總及優(yōu)劣對比,在?Oracle?數(shù)據(jù)庫中,查看執(zhí)行計劃是優(yōu)化?SQL?語句性能的重要工具,本文給大家介紹了幾種常用的查看執(zhí)行計劃的方法及其優(yōu)劣比較,需要的朋友可以參考下2024-11-11
這篇文章主要介紹了oracle多條記錄取第一條的實現(xiàn)方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
安裝oracle11g INS-30131執(zhí)行安裝程序驗證所需的初始設置失敗的解決方法
這篇文章主要為大家詳細介紹了安裝oracle11g INS-30131執(zhí)行安裝程序驗證所需的初始設置失敗的解決方法，感興趣的小伙伴們可以參考一下2016-07-07
Oracle性能究極優(yōu)化(Oracle 性能優(yōu)化)
Linux Journal 發(fā)表了一篇優(yōu)化 Oracle 數(shù)據(jù)庫的文章，感覺十分的有用。簡要介紹其摘要和大家共同分享 Linux 在企業(yè)級數(shù)據(jù)庫上應用2007-03-03
這篇文章主要介紹了Linux虛擬機下安裝Oracle 11G教程圖文詳解，本文文字圖解相結(jié)合的形式給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-04-04
統(tǒng)計信息主要是描述數(shù)據(jù)庫中表,索引的大小,規(guī)模,數(shù)據(jù)分布狀況等的一類信息,下面這篇文章主要給大家介紹了關(guān)于Oracle收集和查看統(tǒng)計信息的方法,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-05-05
以下是對Oracle中的數(shù)據(jù)庫基本常用命令進行了總結(jié)介紹，需要的朋友可以過來參考下2013-08-08