需求：为一个range分区表添加hash子分区

需求：为一个range分区表添加hash子分区，避免产生热点块

解决方案：截止Oracle 11g还没有能够直接实现上述需求的命令（如果谁有更方便的办法，请不吝赐教啊），所以计划采取建新表->导数据->改表名的方法实现上述需求。评估现场数据量，因为有历史数据清除机制，所以全表现有10个分区，共有百万条记录，插入时间在可承受范围内。

操作步骤如下

1. create table T_NEW
   
2. (
   
3.   id NUMBER(15),
   
4.   name VARCHAR2(40),
   
5.   age NUMBER(3),
   
6.   regist_time DATE not null,
   
7. )
   
8. partition by range (regist_time)
   
9. SUBPARTITION BY HASH(name) SUBPARTITIONS 12
   
10. (
    
11.   partition PART20140721 values less than (TO_DATE(' 2014-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
12.     tablespace TS_TEST
    
13.     pctfree 10
    
14.     initrans 1
    
15.     maxtrans 255
    
16.     storage
    
17.     (
    
18.       initial 1M
    
19.       next 1M
    
20.       minextents 1
    
21.       maxextents unlimited
    
22.       pctincrease 0
    
23.     ),
    
24.   partition PART20140722 values less than (TO_DATE(' 2014-07-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
25.     tablespace TS_TEST
    
26.     pctfree 10
    
27.     initrans 1
    
28.     maxtrans 255
    
29.     storage
    
30.     (
    
31.       initial 1M
    
32.       next 1M
    
33.       minextents 1
    
34.       maxextents unlimited
    
35.       pctincrease 0
    
36.     ),
    
37.   partition PART20140723 values less than (TO_DATE(' 2014-07-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
38.     tablespace TS_TEST
    
39.     pctfree 10
    
40.     initrans 1
    
41.     maxtrans 255
    
42.     storage
    
43.     (
    
44.       initial 1M
    
45.       next 1M
    
46.       minextents 1
    
47.       maxextents unlimited
    
48.       pctincrease 0
    
49.     ),
    
50.   partition PART20140724 values less than (TO_DATE(' 2014-07-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
51.     tablespace TS_TEST
    
52.     pctfree 10
    
53.     initrans 1
    
54.     maxtrans 255
    
55.     storage
    
56.     (
    
57.       initial 1M
    
58.       next 1M
    
59.       minextents 1
    
60.       maxextents unlimited
    
61.       pctincrease 0
    
62.     ),
    
63.   partition PART20140725 values less than (TO_DATE(' 2014-07-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
64.     tablespace TS_TEST
    
65.     pctfree 10
    
66.     initrans 1
    
67.     maxtrans 255
    
68.     storage
    
69.     (
    
70.       initial 1M
    
71.       next 1M
    
72.       minextents 1
    
73.       maxextents unlimited
    
74.       pctincrease 0
    
75.     ),
    
76.   partition PART20140727 values less than (TO_DATE(' 2014-07-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
77.     tablespace TS_TEST
    
78.     pctfree 10
    
79.     initrans 1
    
80.     maxtrans 255
    
81.     storage
    
82.     (
    
83.       initial 1M
    
84.       next 1M
    
85.       minextents 1
    
86.       maxextents unlimited
    
87.       pctincrease 0
    
88.     ),
    
89.   partition PART20140728 values less than (TO_DATE(' 2014-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    
90.     tablespace TS_TEST
    
91.     pctfree 10
    
92.     initrans 1
    
93.     maxtrans 255
    
94.     storage
    
95.     (
    
96.       initial 1M
    
97.       next 1M
    
98.       minextents 1
    
99.       maxextents unlimited
    
100.       pctincrease 0
     
101.     ),
     
102.   partition PART20140729 values less than (TO_DATE(' 2014-07-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
103.     tablespace TS_TEST
     
104.     pctfree 10
     
105.     initrans 1
     
106.     maxtrans 255
     
107.     storage
     
108.     (
     
109.       initial 1M
     
110.       next 1M
     
111.       minextents 1
     
112.       maxextents unlimited
     
113.       pctincrease 0
     
114.     ),
     
115.   partition PART20140730 values less than (TO_DATE(' 2014-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
116.     tablespace TS_TEST
     
117.     pctfree 10
     
118.     initrans 1
     
119.     maxtrans 255
     
120.     storage
     
121.     (
     
122.       initial 1M
     
123.       next 1M
     
124.       minextents 1
     
125.       maxextents unlimited
     
126.       pctincrease 0
     
127.     ),
     
128.   partition PART20140731 values less than (TO_DATE(' 2014-07-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
129.     tablespace TS_TEST
     
130.     pctfree 10
     
131.     initrans 1
     
132.     maxtrans 255
     
133.     storage
     
134.     (
     
135.       initial 1M
     
136.       next 1M
     
137.       minextents 1
     
138.       maxextents unlimited
     
139.       pctincrease 0
     
140.     ),
     
141.   partition PART20140801 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
142.     tablespace TS_TEST
     
143.     pctfree 10
     
144.     initrans 1
     
145.     maxtrans 255
     
146.     storage
     
147.     (
     
148.       initial 1M
     
149.       next 1M
     
150.       minextents 1
     
151.       maxextents unlimited
     
152.       pctincrease 0
     
153.     ),
     
154.   partition PART20140802 values less than (TO_DATE(' 2014-08-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
155.     tablespace TS_TEST
     
156.     pctfree 10
     
157.     initrans 1
     
158.     maxtrans 255
     
159.     storage
     
160.     (
     
161.       initial 1M
     
162.       next 1M
     
163.       minextents 1
     
164.       maxextents unlimited
     
165.       pctincrease 0
     
166.     ),
     
167.   partition PART20140803 values less than (TO_DATE(' 2014-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
168.     tablespace TS_TEST
     
169.     pctfree 10
     
170.     initrans 1
     
171.     maxtrans 255
     
172.     storage
     
173.     (
     
174.       initial 1M
     
175.       next 1M
     
176.       minextents 1
     
177.       maxextents unlimited
     
178.       pctincrease 0
     
179.     ),
     
180.   partition PART20140804 values less than (TO_DATE(' 2014-08-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
181.     tablespace TS_TEST
     
182.     pctfree 10
     
183.     initrans 1
     
184.     maxtrans 255
     
185.     storage
     
186.     (
     
187.       initial 1M
     
188.       next 1M
     
189.       minextents 1
     
190.       maxextents unlimited
     
191.       pctincrease 0
     
192.     ),
     
193.   partition PART20140805 values less than (TO_DATE(' 2014-08-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     
194.     tablespace TS_TEST
     
195.     pctfree 10
     
196.     initrans 1
     
197.     maxtrans 255
     
198.     storage
     
199.     (
     
200.       initial 1M
     
201.       next 1M
     
202.       minextents 1
     
203.       maxextents unlimited
     
204.       pctincrease 0
     
205.     )
     
206. );
     
207. 
     
208. ALTER TABLE T_NEW NOLOGGING;
     
209. INSERT INTO T_NEW SELECT * FROM T;
     
210. ALTER TABLE T_NEW LOGGING;
     
211. 
     
212. RENAME T TO T_OLD;
     
213. RENAME T_NEW TO T;
     
214. -- Add comments to the columns
     
215. comment on column T.regist_time
     
216.   is '用户注册时间';
     
217. 
     
218. -- Create/Recreate indexes
     
219. create index IND_T_NAME on T (NAME)
     
220.   local;
     
221. create index IND_T_REGISTTIME on T (REGIST_TIME)
     
222.   local;
     
223.   
     
224. EXEC dbms_stats.gather_table_stats('USER','T');
     
225. DROP TABLE T_OLD;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28998293/viewspace-1242524/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28998293/viewspace-1242524/