程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 柯南君:Oracle分區技術之如何支撐大數據操作?

柯南君:Oracle分區技術之如何支撐大數據操作?

編輯:Oracle教程

柯南君:Oracle分區技術之如何支撐大數據操作?


前段時間,看了羅女士( 資深技術顧問 - Oracle 中國 顧問咨詢部)關於《大批量數據處理技術的演講》視頻,感覺受益良多,結合多年的知識積累,柯南君給大家分享一下:

交流內容:

一、Oracle的分區技術

(一)分區技術內容

1. 什麼是分區?

分區就是將一個非常大的table或者index 按照某一列的值,分解為更小的,易於管理的邏輯片段---分區。將表或者索引分區不會影響SQL語句以及DML(見備注語句,就和使用非分區表一樣,每個分區擁有自己的segment(見備注,因為,DDL(見備注能夠將比較大的任務分解為更小的顆粒。分區表只有定義信息,只有每個存放數據的分區才有各自的segment。就好象擁有多個相同列名,列類型的一個大的視圖。

    大數據對象(表,索引)被分成小物理段; 當分區表建立時,記錄基於分區字段值被存儲到相應的分區; 分區字段值可以修改(row movement enable); 分區可以存儲在不同的表空間; 分區可以有不同的物理存儲參數; 分區可以支持IOT表,對象表,LOB字段,varrays等; 備注:

    ① DML(data manipulation language):
    它們是SELECT、UPDATE、INSERT、DELETE,就象它的名字一樣,這4條命令是用來對數據庫裡的數據進行操作的語言;

    ② segment(data manipulation language):

    段(segment)是一種在數據庫中消耗物理存儲空間的任何實體(一個段可能存在於多個數據文件中,因為物理的數據文件
    是組成邏輯表空間的基本物理存儲單位)

    2.分區的好處?

    性能 - Select 和 DML操作只訪問指定分區

    - 並行DML操作

    - Patition - wise Join

    可管理性:數據刪除,數據備份 - 歷史數據清除

    - 提高備份性能

    - 指定分區的數據維護操作

    可用性 - 將故障局限在分區中

    - 縮短恢復時間

    分區目標優先級 - 高性能->數據維護能力->實施難度->高可用性(故障屏蔽能力)

    ③ 如何實施分區?

    \

    A . Range Partitioning(范圍分區)<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+CjxzdHJvbmc+ICAgICChvrC4wP231s72ob+jujwvc3Ryb25nPjwvcD4KPHA+CjwvcD4KPHA+CiAgICAgICAgICAgvs3Kx7j5vt3K/b7dv+Kx7dbQxLPSu9fWts61xCYjMjA1NDA7tcS3ts6nwLS7rrfWt9bH+KOswP3I56O6PC9wPgoKCiAgIFNxbLT6wusgIDxpbWcgY2xhc3M9"star" src="https://www.aspphp.online/shujuku/UploadFiles_3118/201701/2017012312222474.png" alt="收藏代碼">

    1. create table graderecord
    2. (
    3. sno varchar2(10),
    4. sname varchar2(20),
    5. dormitory varchar2(3),
    6. grade int
    7. )
    8. partition by range(grade)
    9. (
    10. partition bujige values less than(60), --不及格
    11. partition jige values less than(85), --及格
    12. partition youxiu values less than(maxvalue) --優秀
    13. )

    14. 備注:

      ① 分區字段:grade

      ② values less than 必須是確定值

      ③ 每個分區可以單獨指定物理屬性 例如:partition bujige values less than(60) tablespace data0

      ④ 說明:數據中有空值,Oracle機制會自動將其規劃到maxvalue的分區中。

      1)插入實驗數據:

      Sql代碼 收藏代碼
      1. insert into graderecord values('511601','魁','229',92);
      2. insert into graderecord values('511602','凱','229',62);
      3. insert into graderecord values('511603','東','229',26);
      4. insert into graderecord values('511604','亮','228',77);
      5. insert into graderecord values('511605','敬','228',47);
      6. insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
      7. insert into graderecord values('511607','明','240',90);
      8. insert into graderecord values('511608','楠','240',100);
      9. insert into graderecord values('511609','濤','240',67);
      10. insert into graderecord values('511610','博','240',75);
      11. insert into graderecord values('511611','铮','240',60);

        2)下面查詢一下全部數據,然後查詢各個分區數據,代碼一起寫:

        Java代碼 收藏代碼
        1. select * from graderecord;
        2. select * from graderecord partition(bujige);
        3. select * from graderecord partition(jige);
        4. select * from graderecord partition(youxiu);

          全部數據如下:

          \

          不及格數據如下:

          \

          及格數據如下:

          \

          優秀數據如下:

          \

          【范圍分區特點】:

          ① 最早、最經典的分區算法
          ② Range分區通過對分區字段值的范圍進行分區
          ③ Range分區特別適合於按時間周期進行數據的存儲。日、周、月、年等。
          ④ 數據管理能力強
          ⑤ 數據遷移
          ⑥ 數據備份
          ⑦ 數據交換
          ⑧ 范圍分區的數據可能不均勻
          ⑨ 范圍分區與記錄值相關,實施難度和可維護性相對較差

          B.hash (散列分區)

          【案例分析】:
          散列分區是根據字段的hash值進行均勻分布,盡可能的實現各分區所散列的數據相等。

          還是剛才那個表,只不過把范圍分區改換為散列分區,語法如下(刪除表之後重建):

          Sql代碼 收藏代碼
          1. create table graderecord
          2. (
          3. sno varchar2(10),
          4. sname varchar2(20),
          5. dormitory varchar2(3),
          6. grade int
          7. )
          8. partition by hash(sno)
          9. (
          10. partition p1,
          11. partition p2,
          12. partition p3
          13. );

            備注:

            ① 說明:散列分區即為哈希分區,Oracle采用哈希碼技術分區,具體分區如何由Oracle說的算,也可能我下一次搜索就不是這個數據了。

            1) 插入實驗數據,與范圍分區實驗插入的數據相同。

            然後查詢分區數據:

            Sql代碼 收藏代碼
            1. select * from graderecord partition(p1);
            2. select * from graderecord partition(p2);
            3. select * from graderecord partition(p3);

              p1分區的數據:

              \

              p2分區的數據:

              \

              p3分區的數據:

              \

              【HASH分區特點】:

              ?基於分區字段的HASH值,自動將記錄插入到指定分區。 ?分區數一般是2的冪 ?易於實施 ?總體性能最佳 ?適合於靜態數據 ?HASH分區適合於數據的均勻存儲 ?HASH分區特別適合於PDML和partition-wise joins。 ?支持 (hash) local indexes ?9i不支持 (hash)global indexes ?10g 支持(hash)global indexes HASH分區 ?數據管理能力弱 ?HASH分區對數據值無法控制
              c.列表分區

              【案例分析】:

              列表分區明確指定了根據某字段的某個具體值進行分區,而不是像范圍分區那樣根據字段的值范圍來劃分的。

              Sql代碼 收藏代碼
              1. create table graderecord
              2. (
              3. sno varchar2(10),
              4. sname varchar2(20),
              5. dormitory varchar2(3),
              6. grade int
              7. )
              8. partition by list(dormitory)
              9. (
              10. partition d229 values('229'),
              11. partition d228 values('228'),
              12. partition d240 values('240')
              13. )

                以上根據宿捨來進行列表分區,插入與范圍分區實驗相同的數據,做查詢如下:

                Sql代碼 收藏代碼
                1. select * from graderecord partition(d229);
                2. select * from graderecord partition(d228);
                3. select * from graderecord partition(d240);

                  d229分區所得數據如下:

                  \

                  d228分區所得數據如下:

                  \

                  d240分區所得數據如下:

                  \

                  列表分區特點】:

                  ?List分區通過對分區字段的離散值進行分區。 ?List分區是不排序的,而且分區之間沒有關聯關系 ?List分區適合於對數據離散值進行控制。 ?List分區只支持單個字段。 ?List分區具有與范圍分區相似的優缺點 –數據管理能力強 –List分區的數據可能不均勻 –List分區與記錄值相關,實施難度和可維護性相對較差

                  d.組合分區(范圍-散列分區,范圍-列表分區) 【案例分析】:

                  首先講范圍-散列分區。先聲明一下:列表分區不支持多列,但是范圍分區和哈希分區支持多列。

                  代碼如下:

                  Sql代碼 收藏代碼
                  1. create table graderecord
                  2. (
                  3. sno varchar2(10),
                  4. sname varchar2(20),
                  5. dormitory varchar2(3),
                  6. grade int
                  7. )
                  8. partition by range(grade)
                  9. subpartition by hash(sno,sname)
                  10. (
                  11. partition p1 values less than(75)
                  12. (
                  13. subpartition sp1,subpartition sp2
                  14. ),
                  15. partition p2 values less than(maxvalue)
                  16. (
                  17. subpartition sp3,subpartition sp4
                  18. )
                  19. );

                    注:以grade劃分范圍,然後以sno和sname劃分散列分區,當數據量大的時候散列分區則趨於“平均”。

                    插入數據:

                    Sql代碼 收藏代碼
                    1. insert into graderecord values('511601','魁','229',92);
                    2. insert into graderecord values('511602','凱','229',62);
                    3. insert into graderecord values('511603','東','229',26);
                    4. insert into graderecord values('511604','亮','228',77);
                    5. insert into graderecord values('511605','敬','228',47);
                    6. insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
                    7. insert into graderecord values('511607','明','240',90);
                    8. insert into graderecord values('511608','楠','240',100);
                    9. insert into graderecord values('511609','濤','240',67);
                    10. insert into graderecord values('511610','博','240',75);
                    11. insert into graderecord values('511611','铮','240',60);
                    12. insert into graderecord values('511612','狸','244',72);
                    13. insert into graderecord values('511613','傑','244',88);
                    14. insert into graderecord values('511614','萎','244',19);
                    15. insert into graderecord values('511615','猥','244',65);
                    16. insert into graderecord values('511616','丹','244',59);
                    17. insert into graderecord values('511617','靳','244',95);

                      查詢如下:

                      Sql代碼 收藏代碼
                      1. select * from graderecord partition(p1);
                      2. select * from graderecord partition(p2);
                      3. select * from graderecord subpartition(sp1);
                      4. select * from graderecord subpartition(sp2);
                      5. select * from graderecord subpartition(sp3);
                      6. select * from graderecord subpartition(sp4);

                        分區p1數據如下,本例中75分以下:

                        \

                        分區p2數據如下,本例中75分之上包括75分:

                        \

                        子分區sp1:

                        \

                        子分區sp2:

                        \

                        子分區sp3:

                        \

                        子分區sp4:

                        \

                        說明:當數據量越來越大時,哈希分區的分區表中數據越來越趨於平衡。

                        下面講范圍-列表分區

                        范圍-列表分區有兩種創立方式,先說說沒有模板的創建方式,這個表我要重建:

                        Sql代碼 收藏代碼
                        1. create table MobileMessage
                        2. (
                        3. ACCT_MONTH VARCHAR2(6), -- 帳期 格式:年月 YYYYMM
                        4. AREA_NO VARCHAR2(10), -- 地域號碼
                        5. DAY_ID VARCHAR2(2), -- 本月中的第幾天 格式 DD
                        6. SUBSCRBID VARCHAR2(20), -- 用戶標識
                        7. SVCNUM VARCHAR2(30) -- 手機號碼
                        8. )
                        9. partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
                        10. (
                        11. partition p1 values less than('200705','012')
                        12. (
                        13. subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),
                        14. subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),
                        15. subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')
                        16. ),
                        17. partition p2 values less than('200709','014')
                        18. (
                        19. subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),
                        20. subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),
                        21. subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')
                        22. ),
                        23. partition p3 values less than('200801','016')
                        24. (
                        25. subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),
                        26. subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),
                        27. subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')
                        28. )
                        29. )

                          插入實驗數據:

                          Sql代碼 收藏代碼
                          1. insert into MobileMessage values('200701','010','04','ghk001','13800000000');
                          2. insert into MobileMessage values('200702','015','12','myx001','13633330000');
                          3. insert into MobileMessage values('200703','015','24','hjd001','13300000000');
                          4. insert into MobileMessage values('200704','010','04','ghk001','13800000000');
                          5. insert into MobileMessage values('200705','010','04','ghk001','13800000000');
                          6. insert into MobileMessage values('200705','011','18','sxl001','13222000000');
                          7. insert into MobileMessage values('200706','011','21','sxl001','13222000000');
                          8. insert into MobileMessage values('200706','012','11','tgg001','13800044400');
                          9. insert into MobileMessage values('200707','010','04','ghk001','13800000000');
                          10. insert into MobileMessage values('200708','012','24','tgg001','13800044400');
                          11. insert into MobileMessage values('200709','014','29','zjj001','13100000000');
                          12. insert into MobileMessage values('200710','014','29','zjj001','13100000000');
                          13. insert into MobileMessage values('200711','014','29','zjj001','13100000000');
                          14. insert into MobileMessage values('200711','013','30','wgc001','13444000000');
                          15. insert into MobileMessage values('200712','013','30','wgc001','13444000000');
                          16. insert into MobileMessage values('200712','010','30','ghk001','13800000000');
                          17. insert into MobileMessage values('200801','015','22','myx001','13633330000');

                            查詢結果如下:

                            Sql代碼 收藏代碼
                            1. select * from MobileMessage;

                              \

                              分區p1查詢結果如下:

                              \

                              分區p2查詢結果如下:

                              \

                              子分區xiaxun2查詢結果如下:

                              \

                              備注:

                              ① 說明:范圍分區 range(A,B)的分區法則,范圍分區都是 values less than(A,B)的,通常情況下以A為准,如果小於A的不用考慮B,直接插進去,如果等於A那麼考慮B,要是滿足B的話也插進去。

                              另一種范圍-列表分區,包含模板的(比較繁瑣,但是更加精確,處理海量存儲數據十分必要):

                              Sql代碼 收藏代碼
                              1. create table MobileMessage
                              2. (
                              3. ACCT_MONTH VARCHAR2(6), -- 帳期 格式:年月 YYYYMM
                              4. AREA_NO VARCHAR2(10), -- 地域號碼
                              5. DAY_ID VARCHAR2(2), -- 本月中的第幾天 格式 DD
                              6. SUBSCRBID VARCHAR2(20), -- 用戶標識
                              7. SVCNUM VARCHAR2(30) -- 手機號碼
                              8. )
                              9. partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
                              10. subpartition template
                              11. (
                              12. subpartition sub1 values('01'),subpartition sub2 values('02'),
                              13. subpartition sub3 values('03'),subpartition sub4 values('04'),
                              14. subpartition sub5 values('05'),subpartition sub6 values('06'),
                              15. subpartition sub7 values('07'),subpartition sub8 values('08'),
                              16. subpartition sub9 values('09'),subpartition sub10 values('10'),
                              17. subpartition sub11 values('11'),subpartition sub12 values('12'),
                              18. subpartition sub13 values('13'),subpartition sub14 values('14'),
                              19. subpartition sub15 values('15'),subpartition sub16 values('16'),
                              20. subpartition sub17 values('17'),subpartition sub18 values('18'),
                              21. subpartition sub19 values('19'),subpartition sub20 values('20'),
                              22. subpartition sub21 values('21'),subpartition sub22 values('22'),
                              23. subpartition sub23 values('23'),subpartition sub24 values('24'),
                              24. subpartition sub25 values('25'),subpartition sub26 values('26'),
                              25. subpartition sub27 values('27'),subpartition sub28 values('28'),
                              26. subpartition sub29 values('29'),subpartition sub30 values('30'),
                              27. subpartition sub31 values('31')
                              28. )
                              29. (
                              30. partition p_0701_010 values less than('200701','011'),
                              31. partition p_0701_011 values less than('200701','012'),
                              32. partition p_0701_012 values less than('200701','013'),
                              33. partition p_0701_013 values less than('200701','014'),
                              34. partition p_0701_014 values less than('200701','015'),
                              35. partition p_0701_015 values less than('200701','016'),
                              36. partition p_0702_010 values less than('200702','011'),
                              37. partition p_0702_011 values less than('200702','012'),
                              38. partition p_0702_012 values less than('200702','013'),
                              39. partition p_0702_013 values less than('200702','014'),
                              40. partition p_0702_014 values less than('200702','015'),
                              41. partition p_0702_015 values less than('200702','016'),
                              42. partition p_0703_010 values less than('200703','011'),
                              43. partition p_0703_011 values less than('200703','012'),
                              44. partition p_0703_012 values less than('200703','013'),
                              45. partition p_0703_013 values less than('200703','014'),
                              46. partition p_0703_014 values less than('200703','015'),
                              47. partition p_0703_015 values less than('200703','016'),
                              48. partition p_0704_010 values less than('200704','011'),
                              49. partition p_0704_011 values less than('200704','012'),
                              50. partition p_0704_012 values less than('200704','013'),
                              51. partition p_0704_013 values less than('200704','014'),
                              52. partition p_0704_014 values less than('200704','015'),
                              53. partition p_0704_015 values less than('200704','016'),
                              54. partition p_0705_010 values less than('200705','011'),
                              55. partition p_0705_011 values less than('200705','012'),
                              56. partition p_0705_012 values less than('200705','013'),
                              57. partition p_0705_013 values less than('200705','014'),
                              58. partition p_0705_014 values less than('200705','015'),
                              59. partition p_0705_015 values less than('200705','016'),
                              60. partition p_0706_010 values less than('200706','011'),
                              61. partition p_0706_011 values less than('200706','012'),
                              62. partition p_0706_012 values less than('200706','013'),
                              63. partition p_0706_013 values less than('200706','014'),
                              64. partition p_0706_014 values less than('200706','015'),
                              65. partition p_0706_015 values less than('200706','016'),
                              66. partition p_0707_010 values less than('200707','011'),
                              67. partition p_0707_011 values less than('200707','012'),
                              68. partition p_0707_012 values less than('200707','013'),
                              69. partition p_0707_013 values less than('200707','014'),
                              70. partition p_0707_014 values less than('200707','015'),
                              71. partition p_0707_015 values less than('200707','016'),
                              72. partition p_0708_010 values less than('200708','011'),
                              73. partition p_0708_011 values less than('200708','012'),
                              74. partition p_0708_012 values less than('200708','013'),
                              75. partition p_0708_013 values less than('200708','014'),
                              76. partition p_0708_014 values less than('200708','015'),
                              77. partition p_0708_015 values less than('200708','016'),
                              78. partition p_0709_010 values less than('200709','011'),
                              79. partition p_0709_011 values less than('200709','012'),
                              80. partition p_0709_012 values less than('200709','013'),
                              81. partition p_0709_013 values less than('200709','014'),
                              82. partition p_0709_014 values less than('200709','015'),
                              83. partition p_0709_015 values less than('200709','016'),
                              84. partition p_0710_010 values less than('200710','011'),
                              85. partition p_0710_011 values less than('200710','012'),
                              86. partition p_0710_012 values less than('200710','013'),
                              87. partition p_0710_013 values less than('200710','014'),
                              88. partition p_0710_014 values less than('200710','015'),
                              89. partition p_0710_015 values less than('200710','016'),
                              90. partition p_0711_010 values less than('200711','011'),
                              91. partition p_0711_011 values less than('200711','012'),
                              92. partition p_0711_012 values less than('200711','013'),
                              93. partition p_0711_013 values less than('200711','014'),
                              94. partition p_0711_014 values less than('200711','015'),
                              95. partition p_0711_015 values less than('200711','016'),
                              96. partition p_0712_010 values less than('200712','011'),
                              97. partition p_0712_011 values less than('200712','012'),
                              98. partition p_0712_012 values less than('200712','013'),
                              99. partition p_0712_013 values less than('200712','014'),
                              100. partition p_0712_014 values less than('200712','015'),
                              101. partition p_0712_015 values less than('200712','016'),
                              102. partition p_0801_010 values less than('200801','011'),
                              103. partition p_0801_011 values less than('200801','012'),
                              104. partition p_0801_012 values less than('200801','013'),
                              105. partition p_0801_013 values less than('200801','014'),
                              106. partition p_0801_014 values less than('200801','015'),
                              107. partition p_0801_015 values less than('200801','016'),
                              108. partition p_other values less than(maxvalue, maxvalue)
                              109. );

                                這個是帶有模板子分區的,模板子分區詳細到月中的天。這種分區模式只要建立了分區就會自動創建子分區的。

                                插入上面不帶模板分區實驗相同的數據,隨機查詢分區數據:

                                查詢分區p_0701_010的數據:

                                Sql代碼 收藏代碼
                                1. select * from MobileMessage partition(p_0701_010);

                                  查詢結果:

                                  \

                                  查詢子分區p_0701_010_sub4的數據:

                                  Sql代碼 收藏代碼
                                  1. select * from MobileMessage subpartition(p_0701_010_sub4);

                                    查詢結果如下:

                                    \

                                    查詢分區p_0706_011的數據:

                                    Sql代碼 收藏代碼
                                    1. select * from MobileMessage partition(p_0706_011);

                                      查詢結果如下:

                                      \

                                      查詢子分區p_0706_011_sub21的數據:

                                      Sql代碼 收藏代碼
                                      1. select * from MobileMessage subpartition(p_0706_011_sub21);

                                        查詢結果如下:

                                        \

                                        下面講講分區的維護操作:

                                        (1)分裂分區,以第一個范圍分區為例:

                                        Sql代碼 收藏代碼
                                        1. alter table graderecord split partition jige at(75)
                                        2. into(partition keyi,partition lianghao);

                                          把分區及格分裂為兩個分區:可以和良好。

                                          (2)合並分區,以第一個范圍分區為例:

                                          Sql代碼 收藏代碼
                                          1. alter table graderecord merge partitions keyi,lianghao
                                          2. into partition jige;

                                            把可以和良好兩個分區合並為及格。

                                            (3)添加分區,由於在范圍分區上添加分區要求添加的分區范圍大於原有分區最大值,但原有分區最大值已經為maxvalue,故本處以第二個散列分區為例:

                                            Sql代碼 收藏代碼
                                            1. alter table graderecord add partition p4;

                                              給散列分區例子又增加了一個分區p4 。

                                              (4)刪除分區,語法:

                                              Sql代碼 收藏代碼
                                              1. alter table table_name drop partition partition_name;

                                                (5)截斷分區,清空分區中的數據

                                                Sql代碼 收藏代碼
                                                1. alter table table_name truncate partition partition_name;

                                                  備注:

                                                  ① 說明:對待分區的操作同樣可以對待子分區,效果一樣。刪除一個分區會同時刪除其下的子分區。合並多個分區也會把他們的子分區自動合並。分裂分區時注意分裂點。

                                                  另外不帶模板子分區和帶有模板子分區的分區表操作的區別:帶有子分區模板的分區表在添加分區時候自動添加子分區,不帶模板子分區的分區表沒有這個功能;帶有子分區模板的分區表在更改分區時只需更改分區,不帶模板子分區的分區表在更改分區時一定注意連同子分區一起更改。

                                                  【復合分區特點】:

                                                  ?Oracle支持的Composite分區: ? Range-Hash,Range-List ?既適合於歷史數據,又適合於數據均勻分布 ?與范圍分區一樣提供高可用性和管理性 ?更好的PDML和partition-wisejoins性能 ?實現粒度更細的操作 ?支持復合 local indexes ?不支持復合composite global indexes?

                                                  3)分區索引
                                                  \
                                                  \
                                                  4)分區表索引的分類:
                                                  ① Local Prefixed index 局部前綴索引(local prefixed index):在這些索引中,分區鍵在索引定義的前幾列上。例如,一個表在名為LOAD_DATE 的列上進行區間分區, 該表上的局部前綴索引就是采用LOAD_DATE作為其索引列列表中的第一列。
                                                  ② Local Non-prefiexed index 局部非前綴索引(local nonprefixed index):這些索引不以分區鍵作為其列列表的前幾列。索引可能包含分區鍵列,也可能不包含。
                                                  注意: 這兩類索引(Local Prefixed index 與 Local Non-prefiexed index )都可以進行分區消除,前提是查詢的條件中包含索引分區鍵,它們都支持惟一性(只要局部非前綴索引包含分區鍵列)。
                                                  局部索引與表的分區數一致,如果新增一個分區,新增加的分區局部索引也會自動創建。全局索引則不行(即需要重建全局索引)。
                                                  【案例分析】: --下面通過實驗來說明索引的分區消除
                                                  <strong>     </strong>--創建一個分區表
                                                        CREATE TABLE partitioned_table ( a int, b int, data char(20))
                                                        PARTITION BY RANGE (a)
                                                        ( PARTITION part_1 VALUES LESS THAN(2) tablespace gcomm,      --以a字段進行分區,小於等於2的存在分區1,小於等於3的存在分區2
                                                        PARTITION part_2 VALUES LESS THAN(3) tablespace gmapdata )
                                                        --創建一個本地前綴索引   
                                                        create index local_prefixed on partitioned_table (a,b) local;
                                                        --創建一個本地非前綴索引
                                                        create index local_nonprefixed on partitioned_table (b) local;
                                                        --向表中插入數據
                                                        insert into partitioned_table select mod(rownum-1,2)+1, rownum, 'x' from all_objects;
                                                        --分析表
                                                        begin dbms_stats.gather_table_stats ( user,'PARTITIONED_TABLE',cascade=>TRUE );end;
                                                  
                                                        --以sys用戶登錄後 將gmapdata表空間置為離線
                                                        alter tablespace gmapdata offline;--分區2的數據包括其索引等都被置為離線狀態
                                                       <p>      select * from partitioned_table where a = 1 and b = 1;
                                                         A        B     DATA
                                                        ----  ------ --------------------
                                                         1       1        x
                                                       --將之前的plan_table表的數據清除
                                                       delete from plan_table;
                                                       --生成統計信息
                                                       explain plan for select * from partitioned_table where a = 1 and b = 1;
                                                       --查看統計信息結果
                                                       select * from table(dbms_xplan.display);
                                                       PLAN_TABLE_OUTPUT
                                                       --------------------------------------------------------------------------------
                                                       Plan hash value: 1622054381
                                                       --------------------------------------------------------------------------------
                                                       | Id  | Operation                                                  | Name                       | Rows  | Bytes | Pstart| Pstop
                                                       --------------------------------------------------------------------------------
                                                       |   0 | SELECT STATEMENT                                  |                                 |     1   |    28 |    
                                                       |   1 |  PARTITION RANGE SINGLE                        |                                 |     1   |    28 |    1      1
                                                       |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID  | PARTITIONED_TABLE  |     1   |    28 |    1      1
                                                       |*  3 |    INDEX RANGE SCAN                              | LOCAL_PREFIXED       |     1   |         |    1      1
                                                      --------------------------------------------------------------------------------
                                                      Predicate Information (identified by operation id):
                                                      ---------------------------------------------------
                                                     3 - access("A"=1 AND "B"=1)
                                                     注:可以進行查詢,可以通過本地前綴索引將分區2消除 由於分區2采用的表空間為gmapdata,而這個表空間在上述已將其離線,通過本地前綴索引在查詢的時候將分區2消除,只在第一個分區進行查詢,因此該查詢能夠成功查詢。
                                                  
                                                  <u>   再看下面的一個查詢: </u> 
                                                   select * from partitioned_table where b = 1; 
                                                  <span style="color: red;">提示:ora-00376:此時無法讀取文件11 </span>
                                                  <span style="color: red;">      ora-01110:數據文件11:‘D:\ORACE|PRODUCT\10.2.0\ORADATA\FGISDBGMAPDATA.DBF’</span>
                                                    delete from plan_table;
                                                    explain plan for select * from partitioned_table where b = 1;
                                                    select * from table(dbms_xplan.display);
                                                    PLAN_TABLE_OUTPUT
                                                    --------------------------------------------------------------------------------
                                                    Plan hash value: 440752652
                                                    --------------------------------------------------------------------------------
                                                   | Id  | Operation                                                      | Name                        | Rows  | Bytes |  Pstart| Pstop
                                                   --------------------------------------------------------------------------------
                                                   |   0 | SELECT STATEMENT                                   |                                     |     1 |    28 |   
                                                   |   1 |  PARTITION RANGE ALL                              |                                      |     1 |    28 |    1       2
                                                   |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID   | PARTITIONED_TABLE      |     1 |    28 |    1       2
                                                   |*  3 |    INDEX RANGE SCAN                               | LOCAL_NONPREFIXED     |     1 |        |    1       2
                                                   --------------------------------------------------------------------------------
                                                   Predicate Information (identified by operation id):
                                                   ---------------------------------------------------
                                                     3 - access("B"=1)
                                                     注:當查詢謂詞只有b,即采用非前綴索引,而且查詢的條件中又不含分區鍵a,因此在查詢時無法將分區2消除,導致在查詢分區2時提示數據文件不在。
                                                  <span style="font-family: Helvetica, Tahoma, Arial, sans-serif;">將本地前綴索引刪掉後:  </span><span style="font-family: Helvetica, Tahoma, Arial, sans-serif;"> </span></p><p>drop index local_prefixed;
                                                  select * from partitioned_table where a = 1 and b = 1;
                                                  A        B     DATA
                                                  ----  ------ --------------------
                                                  1       1        x
                                                  delete from plan_table;
                                                  explain plan for select * from partitioned_table where a = 1 and b = 1;
                                                  select * from table(dbms_xplan.display);
                                                  PLAN_TABLE_OUTPUT
                                                  --------------------------------------------------------------------------------
                                                  Plan hash value: 904532382
                                                  --------------------------------------------------------------------------------
                                                  | Id  | Operation                          | Name              | Rows  | Bytes | Pstart| Pstop 
                                                  --------------------------------------------------------------------------------
                                                  |   0 | SELECT STATEMENT                   |                   |     1 |    28 |
                                                  |   1 |  PARTITION RANGE SINGLE            |                   |     1 |    28 |   1       1
                                                  |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |   1       1
                                                  |*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |   1       1
                                                  --------------------------------------------------------------------------------
                                                  Predicate Information (identified by operation id):
                                                  ---------------------------------------------------
                                                     2 - filter("A"=1)
                                                     3 - access("B"=1)
                                                     
                                                      注:本地前綴索引刪除後,采用本地非前綴索引進行如上查詢也可以成功。可見本地非前綴索引也可以進行消除分區,主要取決於謂詞。該表利用a字段
                                                  進行分區,因此主要謂詞中有a字段的查詢,就可以成功查詢。<strong>
                                                  </strong></p><p></p><p><strong><span style="font-size:10px;">-局部索引和惟一約束介紹:</span></strong>
                                                  </p><p>CREATE TABLE partitioned
                                                   ( load_date date, id int, constraint partitioned_pk primary key(id) )
                                                   PARTITION BY RANGE (load_date)
                                                   ( PARTITION part_1 VALUES LESS THAN( to_date('01/01/2000','dd/mm/yyyy') ) ,
                                                     PARTITION part_2 VALUES LESS THAN( to_date('01/01/2001','dd/mm/yyyy') ))
                                                  
                                                  select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%';
                                                  
                                                  SEGMENT_NAME              PARTITION_NAME       SEGMENT_TYPE
                                                  --------------------  ---------------------  ------------------
                                                  PARTITIONED               PART_1                TABLE PARTITION
                                                  PARTITIONED               PART_2                TABLE PARTITION
                                                  PARTITIONED_PK                                  INDEX
                                                  <span style="color: red;">注:PARTITIONED_PK 索引沒有進行分區,因此可以保證唯一性</span><br style="color: red;" />
                                                  </p><p><strong>刪掉表重新創建並建立一個本地索引後在創建一個唯一索引</strong> 
                                                  </p><p>drop table partitioned
                                                  CREATE TABLE partitioned ( timestamp date, id int)
                                                   PARTITION BY RANGE (timestamp)
                                                   (PARTITION part_1 VALUES LESS THAN( to_date('01-1-2000','dd-mm-yyyy') ) ,
                                                    PARTITION part_2 VALUES LESS THAN( to_date('01-1-2001','dd-mm-yyyy') )
                                                   )
                                                  
                                                  create index partitioned_idx on partitioned(id) local;--創建一個本地索引
                                                  
                                                  select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%';
                                                  
                                                  SEGMENT_NAME                PARTITION_NAME                 SEGMENT_TYPE
                                                  ------------------------ ------------------------------ ------------------
                                                  PARTITIONED                 PART_1                         TABLE PARTITION
                                                  PARTITIONED                 PART_2                         TABLE PARTITION
                                                  PARTITIONED_IDX             PART_1                         INDEX PARTITION
                                                  PARTITIONED_IDX             PART_2                         INDEX PARTITION
                                                  
                                                  alter table partitioned add constraint partitioned_pk primary key(id);--在id上增加一個全局索引
                                                  提示:此列列表已有索引
                                                  <strong>注:分區索引無法 保證唯一性,因為如果要保證分區索引的唯一性,即分區1有id=1,那麼分區2中就不能有id=1,而我們如果做了這個限制,往不同
                                                  分區進行插數據就會降低分區表的靈活性。</strong>
                                                  
                                                  
                                                  </p>

                                                  ③ Global Prefixed index
                                                      全局索引使用一種有別於底層表的機制進行分區。表可以按一個TIMESTAMP 列劃分為10 個分區,而這個表上的一個全局索引可以按REGION 列劃分
                                                  為5 個分區。與局部索引不同,全局索引只有一類,這就是前綴全局索引(prefixed global index)。如果全局索引的索引鍵未從該索引的分區鍵開始
                                                  ,這是不允許的。這說明,不論用什麼屬性對索引分區,這些屬性都必須是索引鍵的前幾列。
                                                  
                                                  drop table partitioned
                                                  CREATE TABLE partitioned ( timestamp date, id int )
                                                   PARTITION BY RANGE (timestamp)
                                                   ( PARTITION part_1 VALUES LESS THAN ( to_date('01-1-2000','dd-mm-yyyy') ) ,
                                                     PARTITION part_2 VALUES LESS THAN ( to_date('01-1-2001','dd-mm-yyyy') )
                                                   )
                                                  
                                                  create index partitioned_index on partitioned(id) GLOBAL
                                                   partition by range(id)
                                                   (partition part_1 values less than(1000),
                                                    partition part_2 values less than (MAXVALUE) --全局索引必須指定最大值,否則會提示:ORA-14021:必須指定所有列的MAXVALUE
                                                   )
                                                  注:全局索引有一個要求,即最高分區(最後一個分區)必須有一個值為MAXVALUE 的分區上界。這可以確保底層表中的所有行都能放在這個索引中。
                                                  
                                                  全局索引可以創建一個唯一索引:
                                                  alter table partitioned add constraint partitioned_pk primary key(id);--創建唯一索引成功
                                                  注:該唯一索引是通過創建的全局索引來保證唯一,可以通過刪除其索引的錯誤來說明
                                                  drop index partitioned_index;提示:ora-02429:無法刪除用於強制唯一/主鍵的索引。
                                                  
                                                  --以下例子說明了全局索引必須是前綴的
                                                  create index partitioned_index2 on partitioned(timestamp,id) GLOBAL
                                                   partition by range(id)--以id為分區鍵 那麼其索引就必須將id置到最前面
                                                   (partition part_1 values less than(1000),
                                                    partition part_2 values less than (MAXVALUE)
                                                   )
                                                  提示:ORA-14038:全局分區索引必須是前綴
                                                  
                                                  數據倉庫與全局索引:
                                                  數據倉庫一般是通過數據的滑入劃出進行管理(即舊數據劃出,新數據滑入)。一個分區表中,如果進行分區的增刪改操作會造成全局索引失效。因此,
                                                  采用何種索引要根據系統的要求。
                                                  
                                                  實驗1:分區的滑入滑出導致全局索引失效,局部索引仍有效
                                                  --創建分區表  
                                                  CREATE TABLE partitioned ( timestamp date, id int )
                                                   PARTITION BY RANGE (timestamp)
                                                   ( PARTITION fy_2004 VALUES LESS THAN ( to_date('01-1-2005','dd-mm-yyyy') ) ,
                                                    PARTITION fy_2005 VALUES LESS THAN ( to_date('01-1-2006','dd-mm-yyyy') )
                                                   )
                                                  --對兩個分區都插入數據
                                                  insert into partitioned partition(fy_2004)
                                                    select to_date('31-12-2004', 'dd-mm-yyyy') - mod(rownum,360),
                                                           object_id
                                                      from all_objects;
                                                           
                                                  insert into partitioned partition(fy_2005)
                                                    select to_date('31-12-2005', 'dd-mm-yyyy') - mod(rownum,360),
                                                           object_id
                                                      from all_objects;
                                                  --分別創建一個本地索引和全局索引
                                                  create index partitioned_idx_local on partitioned(id) LOCAL;
                                                  create index partitioned_idx_global on partitioned(timestamp) GLOBAL;
                                                  --創建一個新表(用於裝載分區劃出的數據)
                                                  create table fy_2004 (timestamp date, id int); create table fy_2005 (timestamp date, id int); 
                                                  create index fy_2004_idx on fy_2004(id);create index fy_2005_idx on fy_2005(id);
                                                  --創建一個新表並插入數據
                                                  create table fy_2006 ( timestamp date, id int );
                                                  insert into fy_2006
                                                    select to_date('31-12-2006', 'dd-mm-yyyy') - mod(rownum,360),
                                                           object_id
                                                      from all_objects;
                                                  create index fy_2006_idx on fy_2006(id) nologging;
                                                  
                                                  create table fy_2007 ( timestamp date, id int );
                                                  insert into fy_2007
                                                    select to_date('31-12-2007', 'dd-mm-yyyy') - mod(rownum,360),
                                                           object_id
                                                      from all_objects;
                                                  create index fy_2007_idx on fy_2007(id) nologging;
                                                  
                                                  --將分區fy_2004的數據放到表fy_2004中,並刪除該分區
                                                  alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation;
                                                  alter table partitioned drop partition fy_2004;
                                                  --創建一個新分區,用於裝載新數據
                                                  alter table partitioned add partition fy_2006 values less than ( to_date('01-12-2007','dd-mm-yyyy') );
                                                  alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation;
                                                  
                                                  --最後查看索引的情況
                                                  select index_name, status from user_indexes where table_name='PARTITIONED';
                                                  1    PARTITIONED_IDX_LOCAL    N/A
                                                  2    PARTITIONED_IDX_GLOBAL    UNUSABLE
                                                  --發現全局索引已失效
                                                  
                                                  如果強制用其全局索引,會導致無法查詢
                                                  set autotrace on explain
                                                  select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */
                                                   count(*)
                                                    from partitioned
                                                   where timestamp between sysdate - 50 and sysdate;
                                                  ORA-01502: 索引 'LTTFM.PARTITIONED_IDX_GLOBAL' 或這類索引的分區處於不可用狀態
                                                  
                                                  --直接進行查詢,則會進行全表掃描
                                                  select count(*) from partitioned where timestamp between sysdate-50 and sysdate;
                                                  
                                                  實驗2:全局索引失效的解決辦法:
                                                  1)可對索引進行重建,
                                                  2)直接在進行分區刪改的時候 加上更新索引的字句(UPDATE GLOBAL INDEXES):
                                                  --刪除、交換分區時可加上索引更新的字句,增加一個分區不用進行更新索引,因為新增加的分區空行
                                                  alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES
                                                  alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES
                                                  
                                                  注:如果在對分區進行操作時加上了 UPDATE GLOBAL INDEXES 更新索引的字句,那麼全局索引就不會失效。
                                                  
                                                  實驗3:比較索引重建和更新索引所占用的資源情況:
                                                  begin runStats_pkg.rs_start;end;  
                                                  alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation;
                                                  alter table partitioned drop partition fy_2004;
                                                  alter table partitioned add partition fy_2006 values less than (to_date('01-1-2007','dd-mm-yyyy') );
                                                  alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation;
                                                  alter index partitioned_idx_global rebuild; --采用索引重建的方法
                                                  begin  runStats_pkg.rs_middle;end;
                                                  
                                                  alter table partitioned exchange partition fy_2005 with table fy_2005 including indexes without validation update global indexes;
                                                  alter table partitioned drop partition fy_2005 update global indexes;
                                                  alter table partitioned add partition fy_2007 values less than ( to_date('01-1-2008','dd-mm-yyyy') );
                                                  alter table partitioned exchange partition fy_2007 with table fy_2007 including indexes without validation update global indexes;
                                                  begin runStats_pkg.rs_stop;end;  --采用索引更新的方法
                                                  
                                                  輸出的結果:
                                                  Run1 ran in 936 hsecs
                                                  Run2 ran in 1101 hsecs
                                                  run 1 ran in 85.01% of the time
                                                  
                                                  實驗結果:其結果表明對全局索引進行更新要花更長時間。但是如果說系統不允許中斷的話,那麼還是應該采取索引更新的方法。
                                                  ④ Non Partition Index 備注:
                                                  Global索引的分區不同與表分區
                                                  Local索引的分區與表分區相同
                                                  An index is prefixed if it is partitioned on a left prefix of the index columns.
                                                  分區表上的非分區索引等同於Global索引

                                                  分區索引字典:

                                                  DBA_PART_INDEXES 分區索引的概要統計信息,可以得知每個表上有哪些分區索引,分區索引的類新(local/global,)
                                                  Dba_ind_partitions每個分區索引的分區級統計信息
                                                  Dba_indexesminusdba_part_indexes,可以得到每個表上有哪些非分區索引

                                                  5)分區選擇的策略

                                                  \

                                                  6)分區表設計原則

                                                  ① 表的大小:當表的大小超過1.5GB-2GB,或對於OLTP系統,表的記錄超過1000萬,都應考慮對表進行分區。
                                                  ② 數據訪問特性:基於表的大部分查詢應用,只訪問表中少量的數據。對於這樣表進行分區,可充分利用分區排除無關數據查詢的特性。
                                                  ③ 數據維護:按時間段刪除成批的數據,例如按月刪除歷史數據。對於這樣的表需要考慮進行分區,以滿足維護的需要。
                                                  ④ 數據備份和恢復: 按時間周期進行表空間的備份時,將分區與表空間建立對應關系。
                                                  ⑤ 只讀數據:如果一個表中大部分數據都是只讀數據,通過對表進行分區,可將只讀數據存儲在只讀表空間中,對於數據庫的備份是非常有益的。
                                                  ⑥ 並行數據操作:對於經常執行並行操作(如Parallel Insert,Parallel Update等)的表應考慮進行分區。
                                                  ⑦ 表的可用性:當對表的部分數據可用性要求很高時,應考慮進行表分區。

                                                  7)分區表的管理功能

                                                  ① 分區的增加(ADD)
                                                  ② 分區的刪除(DROP)
                                                  ③ 分區的合並(MERGE)
                                                  ④ 分區的清空(TRUNCATE)
                                                  ⑤ 分區的交換(EXCHANGE)
                                                  ⑥ 分區的壓縮(COALESE)
                                                  ⑦ 分區的移動(MOVE)
                                                  ⑧ 分區的分離(SPLIT)
                                                  ⑨ 修改分區的Default Attribute
                                                  分區的更名(RENAME)

                                                  8)分區索引的管理功能

                                                  ① 分區索引的刪除(DROP)
                                                  ② 分區索引的修改(MODIFY)
                                                  ③ 分區索引Default Attribute的修改
                                                  ④ 分區索引的重建(REBUILD)
                                                  ⑤ 分區索引的更名(RENAME)
                                                  ⑥ 分區索引的分離(SPLIT)
                                                  ⑦ 分區索引的Unusable

                                                  9)“滾動窗口”操作 - 大量數據高速裝載

                                                  \ \

                                                  分區索引字典

                                                  DBA_PART_INDEXES 分區索引的概要統計信息,可以得知每個表上有哪些分區索引,分區索引的類新(local/global,)
                                                  Dba_ind_partitions每個分區索引的分區級統計信息
                                                  Dba_indexesminusdba_part_indexes,可以得到每個表上有哪些非分區索引


                                                  2.Oracle的分區的交換功能

                                                  1)交換功能分類

                                                  ① 通過交換數據段,實現分區和非分區表的數據交換。以及子分區和分區表的數據交換
                                                  ② 非常快捷的數據移動方式。特別是沒有validation和索引維護操作時
                                                  ③ Local 索引自動維護
                                                  ④ Global索引必須重建 2) 分區交換的應用--- 全文檢索 \ 第一步:1:00數據的加載
                                                  insert into BF_DXX_stage(SJ,TEXT3) values(to_date("2004.03.02','YYYY.MM.DD'),'大撒反對撒');
                                                  第二步:建立context 索引
                                                  CREATE INDEX IDX_ BF_DXX _STAGE ON BF_DXX_stage(text3)
                                                  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
                                                  ('LEXER MYLEXER STORAGE MYSTORE FILTER CTXSYS.NULL_FILTER MEMORY 100M') parallel 4;
                                                  第三步:partition的交換
                                                  alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes; 3.遷移表空間(Transportable Tablespace)技術簡介 1) \

                                                  備注:

                                                  第一步:exp transport_tablespace=yes
                                                  第二步:FTP 數據文件和dmp文件
                                                  第三步:imp transport_tablespace=yes

                                                  2) 遷移表空間技術的作用

                                                  ① 業務系統數據向數據倉庫系統的遷移
                                                  ② 對業務系統和數據倉庫系統的數據進行定期歸檔
                                                  ③ 數據倉庫向數據集市的數據遷移
                                                  ④ 數據對外發布
                                                  ⑤ 按表空間進行時間點的數據恢復(TSPITR)

                                                  3)遷移表空間技術的優點

                                                  ① 性能大大高於export/import或PL/SQL編寫的程序
                                                  ② 由於Dmp文件只包含表空間的結構信息,因此該技術的真正開銷在於數據文件的傳輸。
                                                  對源系統的影響非常小
                                                  ③ 只需要將被遷移的表空間設置為只讀方式
                                                  ④ 可同時傳輸索引數據,避免在目的數據庫中重建索引

                                                  4)分區交換的應用--- ETL

                                                  ① 在源系統中,將需要抽取的數據以如下語句形式,抽取到建立在單獨表空間上的中間表中:
                                                  CREATE TABLE ... AS SELECT …
                                                  INSERT /*+ APPEND */ AS SELECT …
                                                  ② 以TTS方式將中間表的表空間傳輸到數據倉庫之中。
                                                  exp transportable_tablespace=Yes …
                                                  FTP 中間表表空間的數據文件
                                                  imp transportable_tablespace=Yes …
                                                  ③ 在數據倉庫中對中間表進行各種數據歸並等清洗工作,並建立需要的各種索引。
                                                  ④ 通過exchange技術,將中間表數據及索引直接交換到分區表中。
                                                  ⑤ Alter table <分區表> exchange partition <分區名> with table <中間表> including indexes;

                                                  5)分區交換的應用---重復記錄刪除

                                                  ① 問題描述: 在使用SQL*Loader進行數據加載sor_acct_dcc_saamt_c表時,由於操作失誤,重復加載,導致分區ETL_LOAD_DATE_0606出現重復記錄,也使得兩個唯一 索引:IDX_SAACNAMT_C_1,IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分區不可用(UNUSABLE)。
                                                  用戶在試圖重新創建該分區索引時,出現如下錯誤:
                                                  SQL> alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606;
                                                  alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606
                                                  ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

                                                  ② 在試圖刪除該分區的重復記錄時,又出現如下錯誤:
                                                  SQL> delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606)
                                                  where rowid not in (select min(rowid) from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN);
                                                  *
                                                  ORA-01502: index "GYFX.IDX_SAACNAMT_C_1' or partition of such index is in unusable state

                                                  ③ 簡單辦法是徹底刪除這兩個唯一索引,重新創建。
                                                  數據量大,時間太長。
                                                  影響系統的可用性。
                                                  更完備的解決方式
                                                  創建一個與sor_acct_dcc_saamt_c結構一樣的臨時表test。
                                                  SQL> create table test as select * from sor_acct_dcc_saamt_c where 1=2;
                                                  將sor_acct_dcc_saamt_c表分區ETL_LOAD_DATE_0606數據交換到臨時表test。
                                                  SQL> alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;

                                                  ④ 更完備的解決方式
                                                  刪除test中的重復記錄
                                                  delete from test
                                                  where rowid not in (select min(rowid) from test group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN);
                                                  因為test表沒有任何索引,可避免上述ORA-01502錯誤。
                                                  將臨時表test數據交換回sor_acct_dcc_saamt_c表分區ETL_LOAD_DATE_0606 。
                                                  alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;

                                                  ⑤ 更完備的解決方式
                                                  重新創建創建該分區索引IDX_SAACNAMT_C_1,IDX_SAACNAMT_C_2
                                                  alter index IDX_SAACNAMT_C_1 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;
                                                  alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;
                                                  此時重復記錄已經刪除,可避免上述ORA-01452錯誤

                                                  3.分區的評估

                                                  1)性能方面
                                                  相應速度
                                                  資源消耗(CPU、內存、I/O)
                                                  性能分析工具的使用:Oracle Trace, Autotrace, TKPROF
                                                  2) 其它方面
                                                  數據遷移能力
                                                  數據備份和恢復
                                                  數據擴展性(Add, Drop, Exchange, Merge, …)
                                                  數據高可用性














  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved