oracle根據(jù)多字段創(chuàng)建分區(qū)表

最近有業(yè)務(wù)場景需要用多個字段做分區(qū)表，數(shù)據(jù)量比較大，保存時間也較長，經(jīng)過學(xué)習(xí)與實踐，算是基本完成，以下內(nèi)容為實踐樣例：

目前成都創(chuàng)新互聯(lián)已為上1000+的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)頁空間、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計、興平網(wǎng)站維護等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

---建表語句

create table t_table

(

areacode varchar2(10),

appdate date,

text varchar(10)

)

partition by range(appdate)--根據(jù)字段 appdate 創(chuàng)建主分區(qū)

interval(numtoyminterval(1,'MONTH')) --主分區(qū)按 月 自動創(chuàng)建分區(qū)

subpartition by list(areacode) --再按 地區(qū) 創(chuàng)建子分區(qū)

subpartition template( --指定明確的子分區(qū)信息

subpartition sub1 values('101'),

subpartition sub2 values('201'),

subpartition sub3 values('301')

)

(

partition mainpartition1 values less than(to_date('2019-04-01','yyyy-mm-dd'))--2019年4月1日前的放入mainpartition1分區(qū)，之后的自動分區(qū)

)

---模擬寫入測試數(shù)據(jù)

insert into t_table values('101',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-02-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-04-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-05-03','yyyy-mm-dd'),'a');

insert into t_table values('301',to_date('2019-04-01','yyyy-mm-dd'),'a');

--查詢數(shù)據(jù)

select * from t_table;

--查詢主分區(qū)數(shù)據(jù)

select *from t_table partition (mainpartition1);

--查詢子分區(qū)數(shù)據(jù)

select *from t_table subpartition (mainpartition1_sub1);

--查看自動創(chuàng)建的主分區(qū)

select * from user_tab_partitions where table_name='T_TABLE'

Oracle分區(qū)表怎么建唯

1、一般分區(qū)表都會很大，所以可以先創(chuàng)建表空間，為了讓分區(qū)表存放到單獨的表空間，否則默認會存放到USERS表空間

2、創(chuàng)建TABLESPACE TS1：

CREATE TABLESPACE TS1 DATAFILE '/data1/oracle/test.dbf' SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED；

3、創(chuàng)建分區(qū)表，需要確定按什么分區(qū)，比如按id或按時間段：

CREATE TABLE test_201602

(

ID NUMBER(10) NOT NULL,

CREATE_TIME DATE,

)

TABLESPACE TS1

PARTITION BY LIST (ID)

(PARTITION PT_1001 VALUES (1001) TABLESPACE TS1)；

4、繼續(xù)增加分區(qū)

ALTER TABLE test_201602 ADD PARTITION "PT_1003" VALUES (1003) LOGGING NOCOMPRESS;

ALTER TABLE test_201602 ADD PARTITION "PT_1004" VALUES (1004) LOGGING NOCOMPRESS;

5、查詢表及分區(qū)數(shù)量

select TABLE_NAME,PARTITION_COUNT,DEF_TABLESPACE_NAME from USER_PART_TABLES order by DEF_TABLESPACE_NAME;

ORACLE表分區(qū)

一.表分區(qū)策略

1.識別大表

采用ANALYZE TABLE語句進行分析，然后查詢數(shù)據(jù)字典獲得相應(yīng)的數(shù)據(jù)量。

2.大表如何分區(qū)

可根據(jù)月份，季度以及年份等進行分區(qū)；

3.分區(qū)的表空間規(guī)劃

要對每個表空間的大小進行估計

二.創(chuàng)建表分區(qū)

a.創(chuàng)建范圍分區(qū)的關(guān)鍵字是'RANGE'

1.范圍分區(qū)

create table ware_retail_part --創(chuàng)建一個描述商品零售的數(shù)據(jù)表

(

id integer primary key,--銷售編號

retail_date date,--銷售日期

ware_name varchar2(50)--商品名稱

)

partition by range(retail_date)

(

--2011年第一個季度為part_01分區(qū)

partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第二個季度為part_02分區(qū)

partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第三個季度為part_03分區(qū)

partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第四個季度為part_04分區(qū)

partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TEMP01

);

2.創(chuàng)建散列分區(qū)

3.組合分區(qū)：

4.interval 分區(qū)

三.創(chuàng)建索引分區(qū)

索引分區(qū)分為本地索引分區(qū)和全局索引分區(qū)，全局索引不反應(yīng)基礎(chǔ)表的結(jié)構(gòu)，要分區(qū)只能進行范圍分區(qū)。

創(chuàng)建索引分區(qū)要參照表分區(qū)

四.分區(qū)技術(shù)簡介

優(yōu)點：

1.減少維護工作量

2.增強數(shù)據(jù)的可用性

3.均衡I/O,提升性能

4.提高查詢速度

5.分區(qū)對用戶保持透明，用戶感覺不到分區(qū)的存在。

五,管理表分區(qū)

1.添加表分區(qū)

ALTER TABLE...ALTER PARATITION

2.合并表分區(qū)

3.刪除分區(qū)

ALTER TABLE...DROP PARTITION

刪除分區(qū)時，里面的數(shù)據(jù)也會被刪除。

-創(chuàng)建表和分區(qū)

create table sales--創(chuàng)建一個銷售記錄表

(

id number primary key,--記錄編號

goodsname varchar2(10),--商品名

saledate date--銷售日期

)

partition by range(saledate)--按照日期分區(qū)

(

--第一季度數(shù)據(jù)

partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,

--第二季度數(shù)據(jù)

partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,

--第三季度數(shù)據(jù)

partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,

--第四季度數(shù)據(jù)

partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2

);

--創(chuàng)建局部索引

create index index_3_4 on sales(saledate)

local(

partition part_seal tablespace tbsp_1,

partition part_sea2 tablespace tbsp_2,

partition part_sea3 tablespace tbsp_1,

partition part_sea4 tablespace tbsp_2

);

--并入分區(qū)

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

--重建局部索引

alter table sales modify partition part_sea4 rebuild unusable local indexes;

六.管理索引分區(qū)

刪除索引：DROP PARTITION

重建分區(qū)：REBUILT PARTITION

更名索引分區(qū)：RENAME PARTITION

分割索引分區(qū)：SPLIT PARTITION

oracle11g自動分區(qū)

在Oracle10g中，沒有定義間隔分區(qū)，只能通過范圍分區(qū)實現(xiàn)間隔分區(qū)功能，如果要實現(xiàn)自動創(chuàng)建分區(qū)，只能通過創(chuàng)建JOB或者scheduler來實現(xiàn)；而在11g中，Oracle直接提供了間隔分區(qū)功能，大大簡化了間隔分區(qū)的實現(xiàn)。

----注：oracle11g雖然可以自動分區(qū)，但是分區(qū)的名字不能自定義，對于需要定時刪除分區(qū)時沒法處理，不如通過時間范圍來手工分區(qū)。詳見

create table HIP_LOG_NODE_Part

(

ID?????????????????? VARCHAR2(32)???????? not null,

RECORD_TIME????????? DATE

)tablespace TB_HIP_LOG_NODE

PARTITION BY RANGE (RECORD_TIME) interval (numtoyminterval(1, 'month'))

STORE IN (TB_HIP_LOG_NODE)

(

partition hip_log_node_partition values less than (to_date('2019-08-01 00:00','yyyy-MM-dd HH24:mi')) tablespace TB_HIP_LOG_NODE

);

1、Oracle11g有間隔分區(qū)功能，對于使用Range分區(qū)的可以按年，月，日來自動生成分區(qū)。

2、2019-08-01前的數(shù)據(jù)（包含8月份的數(shù)據(jù)）會放入hip_log_node_partition?分區(qū)，8月1日后的數(shù)據(jù)每月只要有數(shù)據(jù)，就會自動創(chuàng)建一個分區(qū)。也就是從9月開始，開始新建分區(qū)。

3、interval函數(shù)--將數(shù)值按標準換算為日期

numtodsinterval、numtodsinterval函數(shù),將數(shù)字轉(zhuǎn)成年月，時分秒

詳見：

4、查看表分區(qū) select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';

5、插入數(shù)據(jù)再次查看分區(qū)，詳見：

6、修改分區(qū)、合并分區(qū)、拆分分區(qū)，詳見 ：

7、創(chuàng)建索引（分區(qū)索引、全局索引） :

非分區(qū)字段創(chuàng)建主鍵，則創(chuàng)建主鍵local索引時必須加上分區(qū)字段

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (主鍵字段,分區(qū)字段) USING INDEX LOCAL;

8、oracle 10g創(chuàng)建表分區(qū)

9、刪除

1.不保留，直接刪除：

alter table table_name drop/truncate partition partition_name;

具體用drop還是truncate，得你自己衡量，drop的話原來的分區(qū)和數(shù)據(jù)直接就沒有了，truncate的話，只是數(shù)據(jù)沒有了，分區(qū)還在。

oracle如何按照mod建分區(qū)

根據(jù)數(shù)據(jù)表字段值的范圍進行分區(qū)。

創(chuàng)建完分區(qū)表后向表中添加一些數(shù)據(jù)，declarenamevarchar2(10)。fractionnumber(5)。gradenumber(5)。inumber(8)：=1。begin。foriin1。100000LOOP。當我們的查詢語句不指定分區(qū)的時候，如果分區(qū)字段出現(xiàn)在where條件之后，Oracle會自動根據(jù)字段值的范圍掃描響應(yīng)的分區(qū)。