oracle分区之包含模板的'范围-列表分区'例子

来源：互联网发布：无法更改mac地址编辑：程序博客网时间：2024/05/18 15:29

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. );

原文链接：http://love-flying-snow.iteye.com/blog/573303

0 0