其實range分區你學會了,其他的分區你也學會了,在這我就不敲了,咱們簡單來看一下,hash分區,hash分區實現負載均衡,平均分配值進行分區,那這個哈希就是平均分配的,也是一張表,還是partition by,只不過是變成hash了,然后根據咱們的分區字段,然后進行p1和p2,這回就直接寫p1,p2,p3,p4,但是我先做沒有去指定范圍,但是這個數據就會平均分布,然后他也是照樣可以去添加分區,這都是可以的,只不過是平均分布,然后類似于這種方式,看一下,用hash分區,平均分,就是p1這個里面5條數據,你如果插10條數據,p1里面5條,p2里面5條,就是這個意思,這個很簡單

還有一種是list分區,它是按照區域去劃分的

比如說create一個table,partition by list分區,按照城市去分區,給partition取個名字,east,west,按照這種去分,比如這是tianjin,這是dalian,按照天津和大連去分,按照字段值區分,east這只是一個名字,跟其他的沒關系,然后現在是按照city這個值去分的,我們可能會往這個表里灌數據,灌一千頭,一萬條,你要是tianjin的,數據進來一條tianjin的,或者是dalian的,我就把數據放到east,這個只是一個分區的名字,你如果要是xian的,我就放到west里邊,你要是上海呢,我就放到south里邊,你要是herbin,我就放到north這個區間里,others就放到other里,這個就是按照一個字段的不同表現形式,然后閾值去劃分的,他其實和range有點像,只不過是一種更好的分區的手段吧,可能我insert了5條記錄了以后,我查一下分區,那你看我insert了5條記錄了,那我就查一下east,east就是tianjin,dalian,相當于把所有天津和大連的,這個數據查出來了,如果你查這條SQL的話select * from personcity partition(east);查出來就有這個效果,這個是list分區 然后就是復合分區

其實部門分區怎么說呢,因為我是不怎么去建list分區,它是把范圍分區和散列分區進行相結合,兩種分區結合起來做這個劃分,范圍分區和散列分區相結合,都是可以的,就是范圍分區range,range和hash組合,這邊是range分區,這邊是hash分區進行一個組合,或者是范圍分區和列表分區list分區,他們兩個之間也可以做一個組合,有各種各樣的組合,然后這塊你看我怎么做的我這里正常來講是一個table,建立一個range分區,在最外層建一個range分區,按照這個number,就是最外層按照一個Number做一個range分區,然后里邊有一個subpartition子分區,分區里面再套一個分區,按照這個hash,再給我平均分區,按照這個sname,我可以走兩個字段,去做分區,比如number等于1的,這個可能不是主鍵,隨便舉個列子,number等于1的有好幾條記錄,number等于1的可能有10條記錄吧,這10條記錄都可以放到一個空間里了,number等于2的呢,這里也有10條,然后number等于1或者number等于2,在最開始的range分區的基礎之上,我在這個分區里面還可以分成4份,hash subpartition 4,好像不是這么說的,再往下看,是這樣的,就是這4份是平均的,接下來我看range是怎么去劃分range的,range我分成是這樣的,如果ID號小于1000,0到1000這些數據,放到外層的一個分區,然后1000到2000放到最外層的分區,他這里有點別扭,語法就是這樣的,沒辦法,我再描述一下,就是一開始,先看這個,partition by range(sno),一個range分區,把數據切分成了三份,第一份是0到1000,第二份是1000到2000,第三份是2000以上,2000加,大體上分了三個區間,然后我又做什么事了,其實這塊我們再看一下,subpartition by hash(sname) subpartition 4,一個區間按照name去分區,分成4份,其實是這樣的,4小格嗎,按照name哈希分區,應該是平均分配的,這個是關于復合分區,范圍分區和list分區怎么去整合,你這塊就不是平均了,就是按照list去分區,都可以去做組合的,list里面指定太麻煩了,有沒有什么快速的方法來指定,這個就沒有了,list其實怎么說呢,比如說省份,一共就那幾個省份,你采用list分區,就是按照不同的業務去劃分不同的分區,分區也不是說非得選哪一種,按照真實的業務你自己去選擇,剛才這塊沒問題吧

接下來我們看一下間隔分區,其實工作中正常來講,間隔分區是用的比較多的,一般我也是會用到間隔分區,其實很多數據結構我都會用到間隔分區,間隔維度主要是可以按照時間維度去進行分區,這是我最喜歡的事情,它是11g以后才出來的新特性,在實際工作中也是非常常用的,Interval分區,Interval Partition,這也是ORACLE一項非常引以為榮的技術,可以動態的去指定分區,讓高效的海量處理數據成為可能,他其實是range分區的一個延展,一個擴充,最終實現了range分區的一個自動化,咱們同學想一想,range分區你得手寫上P1,P2,P3,...,不夠了還得alter table,add partition,那現在這種間隔的分區,可以寫好了函數以后,他自己可以去進行分區,咱們可以看一下,create table,table名字叫做interval_sale,這個東西可以直接運行的,在這里咱們說range,range的一個升級版吧,叫做interval分區,它是可以去實現一個自動化分區

---

現在這個語句已經寫好了,首先create一個table叫做interval_sale,然后指定兩個字段,一個sid,和sdate,你注意看我們這里又一個date,他是一個timestamp類型的,有一個時間戳,然后我現在進行partition的時候,sdate時間進行分區,然后下面有一個函數,這個函數的目的就是,這個函數就是ORACLE里面的函數了,舉個例子,這個函數的最終執行結果呢,其實就是月份,SELECT numtominterval(1,'MONTH') FROM DUAL;

是按照0到01這個月份,做一個分區的,他自己回去累加的,舉個例子吧,你只要放一個分區的起始點即可,先DROP掉已經存在的,我要建的range分區,其實我在以前就建立過一遍了,就是這張表,interval_sale,這張表,包括這種分區模式,在我們上課之前已經建立過一遍了,聽好我要問的問題,剛才我把那張表給drop掉了,那現在請問,我要做這個事情,SELECT * FROM user_tab_partition,那我問你,之前建立的interval分區,還存在嗎,能理解我說的意思嗎,N多年以前,已經建立range分區這種表了,但是我剛才把他drop掉了,drop掉之后,range分區還存在嗎,我已經把表都刪了,那分區還存在嗎,問你一下,能理解我說的意思吧,咱們看一下,看見了吧,可能你看不出來

這個東西已經變樣了,但是這個東西還是存在的,flashback,閃回咋寫的,我有點忘了,我記得在同義詞里面有,flashback table 表名 to before drop;我要把這張表還原回來,然后to before drop,能理解我的意思吧,flashback哪張表啊,其實之前我建了interval_sale,然后to before drop,我要做這個事情,flashback table interval_saleto before drop,我要做這個事情,然后我又回來了

然后去查詢,看到了吧,那你告訴我,分區之后,他到底存不存在啊,是不是還是存在的,能理解我說的意思吧,因為什么啊,因為ORACLE里面有好多閃回機制,你把表drop了以后,我物理的分區其實還是一直存在的,如果你做什么事啊,除非你purge recyclebin

我現在繼續drop掉,drop掉之后呢,他的名字就變了個樣了

你這個表不存在了,就放到recyclebin里面了,垃圾箱里了,這個bin,這里面生成一堆啥東西我也不知道,總之它是通過這個能夠讓你在purge之前,purge recyclebin;在purge recyclebin的時候,我一執行,執行完之后我再去查詢,我只能把垃圾箱的數據全部down了之后,我這個分區才會物理的刪除,能理解我說的意思吧,有人的說ORACLE提供了很強大的機制,以前用SQLServer,老的版本,老的MSYQL的時候,經常SELECT * FROM,DELETE FROM 什么的,把你的數據全干掉了,或者誤操作把表刪掉了,那沒關系,ORACLE里面會有一些數據恢復的機制,包括很多種吧

你看這個SALE,就是我們剛才做的例子,我把它干掉,我們最開始建立的不是一個SALE,這個SALE應該是range分區,我把這個drop掉,drop掉之后呢,我就一查,我就查這個

是不是一樣的,然后我還可以把SALE這張表給flashback回來,然后我還可以去查到這個SALE,都是一樣的只要你刪除不recyclebin,都是沒問題的

都是可以flashback回來的,其實這個東西我不想講,看到之前有張表,同時提一下,咱們再回到這塊,回到interval這塊,我們剛才說了,這個東西是逐月遞增的,或者是按照其他的東西,按照年也可以,計算的話按照天也可以,這是一個函數,你可以看一下,這個東西一堆一堆的,計算日期函數,如果不明白的話,在這我只能告訴你,你要百度,你要自己課下去學習,說這個東西到底什么意思啊,他有tomonth,tomin,很多,很多區間去劃分的函數,所以說呢咱們在這按月份去說吧,那我先做看這一個,就是interval分區你只要指定一個起始值就可以了,那我這個起始值是多少呢,是2014年2月1號,也就說什么意思呢,2014年2月1號之前所有的數據,給我劃分成一個區間叫P1,從2014年2月1號以后,每隔一個月,給我建立一個分區,每隔一個月給我建立一個分區,他就是這種機制,咱們直接把這個create出來,create出來之后咱們看一下看一下這個partitions,當前interval_sale這張表里,肯這是沒問題的,現在是沒任何分區的,動態分區你看不出來

咱們去insert幾條記錄,INTERT INTO咱們的這幾張表,然后VALUES什么啊,values咱們的id,這里要傳一個timestamp類型的比如咱們來一個字符串吧,這樣去寫行嗎,先看看,我這里面是不是要用函數轉換啊,我現在寫一個1月,比如說1月7號吧,這個時間不行吧,這個月份應該怎么去轉換啊,應該有一個函數,來看一下,SELECT * FROM DUAL,這個函數的轉換應該是TO_TIMESTAMP,我就按照剛才的那個值,他有一個模板,我也記不住,咱們找一下,就他了,24小時的,就是時間轉換函數,之前我們也說了

可以把這個東西直接copy過來,行不行呢,插入成功

咱們這回去做這個事情,先不要著急看他,先看這個,SELECT * FROM 咱們的interval,這里是有一條數據的,因為我剛才插入了一條數據

但是你注意,我插入的是1月7號,咱們先查一下,這啥意思

怎么會是這樣呢,我刷新一下,SALE,好像出現了一個小問題,我再查一下,我剛才是指定了一個間隔分區,我再加入幾條記錄,難道不能夠這么去插入嗎,比如我加2號和3號,這塊我變成2月份,比如變成2月5號,比如這是3月份,3月6號,之前這條數據已經加完了,就是1月7號的我先insert,成功

成功以后再去看這條記錄,去看這張表,這張表是有3條數據的

咱們看partition,可能是不存在這里的

這是index,sorry,難怪會出現這個問題呢,這是index,咱們應該查tab_partition,SELECT * FROM user_tab_partition

已經出來了,我之前interval,現在已經有了,咱們再查一下吧SELECT * FROM interval_sale PARTITION(P1);

P1里頭有一條數據,這是之前咱們插入的1月7號,1月7號以前的都是屬于P1分區的,比如我再插入一個,比如我插入一個更小的值,你剛才不是說2014年嗎,我再來一個2012年,12年12月7號,提交

我再次去查這個P1,你就會發現兩天條記錄

這個東西就相當于什么啊,以你這個時間為基準,這之前的記錄都分成一個區域,從這個以后,每一個月,我會有一個區域,每一個月會有一個區,然后我通過查這個,里面分區的name,分區name是自定義的,你沒法去控制

如果你能查到這個表,查到這個東西,然后現在咱們看SELECT * FROM interval_sale PARTITION(SYS_P01);查詢這個,查詢,我現在看到了,2月5號的數據,我在查一下下個分區SELECT * FROM interval_sale PARTITION(SYS_P02);這個是3月6號的

有人說那一下年的呢,我再插入一條數據,比如咱們不這么去分,之前14年的1月份,2月份,3月份,都有了嗎包括之前的就不說了,現在我要來一個15年的12月7號,他兩是相同的,只不過年份不同,之前這是12年的,現在我來一個15年的,我再insert

成功了以后呢,現在我的分區可能有多了一個了

你會發現你有個103了,103他單獨這一個月,就是指定的這12月份這一個月,肯定是有一個單獨的分區的,你通過這你就能看到,上限是多少,上限是16年的1月1號,然后這邊的上限是多少啊,這邊的上限是4月1號,因為剛才我們建立的這條數據,就是102里面存的是這個,是3月6號,這個分區的上限是4月1號之前,就是一個月一個月的會去給你建分區,你只要滿足在14年2月1號之后,這個月份就會單獨的建立一個分區,這個就是一個interval分區,能理解我說的意思吧,分區之后數據文件是什么樣子的,這個是物理存儲到其他地方,我也不知道,你要問ORACLE的OCM,分區之后是放到哪兒,數據文件肯定是單獨獨立的,一個分區一個文件,肯定是這樣的,然后我看看這邊,分區這邊不斷增加會有問題嗎,分區數目有沒有限制,分區數目是沒限制的,就是你采用這種INTEVRVAL分區,這個是什么場景下用的,比如你們公司,一個月的數據量就會很大的,一個月的數據量就200,300萬,那你就不會做這個事情了,我就按照月份,按照時間維度,或者有的公司一天數據量就很大,按照天去分區,我一天一部分數據,一天一部分數據,或者是按照月走,或者是按照年走,或者是按照季度走,或者你按照week周走,一周一分區,他就是不同的文件,只要你硬盤足夠大,有空間的話,只要你硬盤足夠大,有空間的話是沒問題的,能明白我的意思吧,可以分開存儲,它本身就是分開,就是跟我們的分區索引是一樣的,只能采用分區字段，你只能在剛才我們看到的date,你只能給他建索引,一模一樣的,包括幾種形式去建是一樣的,我舉個例子吧,之前我們是把一個表放在一個物理存儲了,一塊區間去查了,現在我可以按照哈希分區,range分區,或者list分區,或者是其他的分區,現在咱們說一種range分區,按照月份,一個月份一個數據文件,一個月份一個單獨文件,他這個東西什么概念呢,我現在有一個client端,我要查詢的時候,我要查3月份的，3月份的我就查這一個數據文件,其他的數據文件是不查的,只要當前的ORACLE服務器磁盤夠用,能理解我的意思吧,這個分區是可以無線的加的,比如一個最簡單的例子,你建表一樣,你開始建了10張數據庫,數據庫里建立10張表,后來業務擴展了,建立100張表,100張表不就是100個數據文件嗎,然后你建了1000張表后發現,完了,咱們的ORACLE服務器不夠用了,怎么辦,要加硬盤,這個道理也是一樣的,能理解我說的意思吧,然后呢,還有什么,后期要加其他字段的索引,后期如果你要加其他字段的索引的話,那就不行了,那你如果加索引的話只能是走全表的索引了,就是跟分區沒關系了,就像有人說的,我把它進行分區了,你想在分區上加索引,你只能使用分區字段去加索引,但是如果你要是加在其它字段上,那就是全表索引了,全表的索引,就是global索引,然后呢,剛才咱們所說的是什么啊,有的人說我的業務就是這樣的,那其實這個東西,技術跟業務,是相結合的,跟設計是想結合的,比如我們怎么去做啊,咱們現在就這樣,我們就按照時間間隔,一個月份,然后我去做分區,你前臺的表單,然后訪問咱們的應用層,就是JAVA,數據庫,還有一種情況是跨分區,比如我們是一個月份一個月份的分區,然后你現在是想查1月份到5月份的數據,你說這個跟誰玩啊,你就想查1月份到5月份的數據,那他肯定效率不高啊,肯定是把這5塊都查了啊,他肯定是走5個數據文件嗎,你要是1月份到12月份1年的,那肯定是有走數據文件了,1月份到12月份都查了,那是全表掃描能理解我說的意思吧,所以說很多業務,分完區之后,你可以做緩沖表,你要是按照月份去查,你還可以去做客其他的事情,比如你跨分區查,分區之后的數據文件是什么樣子,這個之前說過了,分區不斷增加會不會有問題,就跟你數據庫表多建幾個一樣,能分開存儲嗎,能分開存儲然后這種分區怎么去建索引,跟正常的建索引是一樣的,分區索引,然后如果你在其它字段上去建索引,相當于全表去建索引,比如后期要加其他字段索引呢,那這個問題還是之前的話題,相當于加全表的索引,分區越多會影響全局索引的查詢速度嗎,這個不影響,分區索引和全局索引是兩碼事,他只是影響磁盤的IO,你想想之前一個索引,現在兩個索引,又建立三個索引,又建立了四個索引,維護的IO可能就更多了,但是分區越多影響查詢的速度,這個肯定是不影響的,為什么不影響,就是你還沒有理解分區索引的概念,你要知道把一張表分成三個區間之后,他就相當于單獨的一張表了,你分區越多,你建索引越多,索引其實還是在這一塊區域上去建的,這個意思,分區你可以理解為分表,分區你可以理解為單獨的建了一個表,接下來就是我剛才說的那個問題,分區索引是多個,分區索引看情況,如果是普通的local索引,肯定是多個,比如你有10個分區,然后你create index,然后什么什么on table,那個字段,然后這樣的一個local索引,其實就相當于你分10個區,建了10個索引,建10個索引是完全不影響咱們查,就是不影響SELECT,也不影響update的效率的,知道為什么嗎,因為我分了10個區以后,一條數據不可能跨分區去插入吧,能理解我說的意思吧,一條數據,比如我指定月份分區,你這條數據肯定是指定1月份,或者2月份的,你不可能是這條數據是1月份或者2月份的,所以你插入的時候只能往一個區里插,我這個索引只有一把索引,這是沒有任何性能影響的,然后咱們舉一個例子,剛才說到什么事啊,其實剛才我還是想說,設計跟你這個技術,是應該完全像結合的,比如這是咱們一個傳統的行業,表單,這里面有很多的復選框,比如說要你查詢,這里有一個開始的日期,這里有一個結束的日期,這是開始,然后呢讓你去填數,進行一個檢索,還有其他的條件咱們先不理會了下面就是展示數據的,就是這么一個小DEMO,假如你現在查的是1月1號,或者是1月15號,查1月15號到30號,到1月30號,然后走了一個JAVA層,然后DB層,走到DB層咱們是按照月份去分區的,所以說你都是1月份的,都是1月份的話,這個效率很高,因為什么啊,因為咱們month這個字段,date這個字段,月份的這個字段首先建了分區了,然后建了分區索引了,所以你要查這個數據呢,肯定是在一個數據文件里,如果你有這個情況,只想查1月15號,到3月8號的,你就想跨分區去查,查2月8號的,那他肯定會走兩個數據文件,因為你1月15號到1月30號,它是在一個數據文件里,但是從2月1號到2月8號呢,他又是在一個數據文件里,這個時候你就得去設計了,怎么去設計,你前端就得JS去驗證,如果輸入這個數字我就告訴它,我們不能跨月份去查詢,能理解我說的意思嗎,這個東西從設計的角度,去越過一些其他的問題,你覺得查詢上會覺得很慢的的一個問題,或者即使是跨分區也可以,我們之前就是有這么一個需求,就是要查一個季度的,1月份到3月份,怎么辦啊,正常我的物理表可以抽取月份的數據,抽取記錄的數據做匯總,那我就想查跨兩個月,1月份到2月份,那怎么辦,那你就剛脆讓他查唄,相對來講效率稍微會慢一點,但是這個都無所謂,你說我就想查1月15號到2月15號的,那肯定不行,就相當于你查整張表了差不多就是一年的所有數據,這個效率肯定是不行的,很多事情你是業務和設計解決才行的,就是技術加上業務,有時候設計要比技術更好,你設計的東西可以屏蔽很多查詢慢的問題,所以還是你在技術的基礎上,加上設計,你才能設計性能最好的產品,能理解我說的意思吧,一定是這樣的,我這個東西就像跨12個月份查,你沒辦法,你永遠查12塊數據,怎么可能快呢,還有,你就別用ORACLE了,把數據直接灌到內存里,來查嗎,很多種方案,那你就換解決方案,你就換技術,一種技術行不通,就換一種技術,肯定有一種行得通的,但是我還是要建立你什么啊,不要輕易地去換另外一種技術,從設計者的角度去考慮,你作為一個架構去考慮,不能說來了一個新東西我就換一種技術,你得考慮全方位,是不是非得有必要去用這種東西,在原有的設計基礎之上,真的解決不了這個業務了,那你還得考慮這個可行性行不行,那你這個時候才會去考慮去加一種新的技術,因為這個東西是有風險的,到底行不行的通,你還得派一個人去做一個需求調研,然后再去做這個事情,這個東西就是這樣的,軟件開發其實就是這樣的,今天咱們就到這兒吧,還記得我這個問題嗎,這個數據庫表結構,我再次說一下,可能你們之前理解的不太明白,現在我再花點時間把這個東西說一下,首先我有一張數據庫表,這個數據庫表是一個很大的tree,是一個很長的樹,層次結構也很深,數據也很多,可能幾千,幾萬條,首先這個TREE就有一個問題,做高效的查詢,那我用前端技術,異步加載,我根據ID查詢ID下的節點,子節點有了之后,再根據子節點查出這個子節點下的子節點,不讓你玩這個,不讓你玩這個異步加載,很多情況是不能異步加載的,異步加載有些場合可以,有些場合不行,就得讓你直接展示一棵樹,因為展示這棵樹不僅僅是前端,有人說你們這個業務很簡單,就是一顆樹展示到界面上,你一次查一個parentId,展示節點完全OK,但是還有很多很多業務場景,不僅僅是需要展示的還需要做統計分析的,做統計查詢做關聯,那這個時候你就需要查一顆整體的樹,就根據這個id查詢他的孩子,孫子,都查出來,然后要跟其他的一張大表,大的基表,千,萬,上億的數據,然后再和其他的表join,我用戶登錄的可能是這樣的一張表,但是過濾的時候是根據樹的層次結構去過濾的,你得join A,B,C,D這三張表,還有一種需求是說,你這棵樹層級很深,這是第一層,這是第二層,這是第三層,這是第四層,那我先做就想讓你查錢三層,第四層,第五層數據你不要,總之就這三需求,第一個需求就是你怎么能快速的檢索一棵樹,1,2,3,4,5都給我查出來,第二個需求是怎么從第一層查到第三層,如何實現數據量比較大的join,你讓這個性能變得高,就這三個問題,仔細好好想一想,其實就是利用咱們這種數據庫設計,然后加上一些特殊的東西就能實現,我的問題能理解吧,我的問題能理解的話,你就去想一想,去做一做,今天就到這兒吧,START WITH和CONNECTOR BY,你進行一個怎么說呢,開始可以,但是后來就不行了,START WITH可能首先來講性能可能低一點,如果能合理的樹形結構快一點,性能可能稍微高一點,當然這不是主要的,主要的問題是什么啊,主要是到最后三個問題,你進行START WITH CONNECTOR BY以后,然后你去join四張表了,最終我可能要過濾出來一千萬條數據,一條數據你就得從START WITH,CONNECTOR BY結尾,本身就是一個FOR循環,你這個數據還得去FOR循環,你想想這個性能就很慢,就是想一想,你可以百度,今天就到這兒吧

?

超強干貨來襲 云風專訪：近40年碼齡，通宵達旦的技術人生

總結

以上是生活随笔為你收集整理的Oracle之表分区、分区索引（二）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。