palsplitpartition/ATTRIB_MDAY_NUMBER_VAL_RDC_alpha_create.sql at master · xueyubingsen/palsplitpartition · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
-- Create table
create table VESTEK.ATTRIB_MDAY_NUMBER_VAL_RDC
(
  entity_uid              INTEGER not null,
  end_dt                  NUMBER not null,
  start_dt                NUMBER not null,
  attribute_definition_id INTEGER not null,
  partition_dt            INTEGER not null,
  partition_id            INTEGER not null,
  value                   NUMBER not null,
  last_chg_user_nm        VARCHAR2(30) default USER not null,
  last_chg_dt_tm          DATE default SYSDATE not null
)
partition by range (PARTITION_DT)
subpartition by hash (ENTITY_UID)
(
  partition AMNUM_VAL_RDC_2008 values less than (2009)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2008P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2008P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2008P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2008P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2008P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2008P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2008P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2008P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2008P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2008P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2008P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2008P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2009 values less than (2010)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2009P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2009P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2009P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2009P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2009P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2009P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2009P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2009P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2009P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2009P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2009P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2009P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2010 values less than (2011)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2010P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2010P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2010P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2010P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2010P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2010P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2010P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2010P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2010P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2010P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2010P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2010P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2011 values less than (2012)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2011P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2011P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2011P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2011P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2011P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2011P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2011P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2011P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2011P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2011P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2011P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2011P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2012 values less than (2013)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2012P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2012P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2012P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2012P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2012P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2012P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2012P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2012P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2012P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2012P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2012P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2012P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2013 values less than (2014)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2013P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2013P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2013P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2013P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2013P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2013P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2013P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2013P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2013P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2013P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2013P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2013P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2014 values less than (2015)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2014P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2014P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2014P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2014P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2014P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2014P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2014P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2014P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2014P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2014P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2014P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2014P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2015 values less than (2016)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2015P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2015P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2015P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2015P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2015P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2015P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2015P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2015P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2015P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2015P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2015P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2015P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2016 values less than (2017)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2016P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2016P02 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2016P03 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2016P04 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2016P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2016P06 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2016P07 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2016P08 tablespace RDC_DSOS_04,
    subpartition AMNUM_VAL_RDC_2016P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2016P10 tablespace RDC_DSOS_02,
    subpartition AMNUM_VAL_RDC_2016P11 tablespace RDC_DSOS_03,
    subpartition AMNUM_VAL_RDC_2016P12 tablespace RDC_DSOS_04
  ),
  partition AMNUM_VAL_RDC_2017 values less than (2018)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_2017P01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P02 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P03 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P04 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P06 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P07 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P08 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P10 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P11 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_2017P12 tablespace RDC_DSOS_01
  ),
  partition AMNUM_VAL_RDC_MAXX values less than (MAXVALUE)
    tablespace RDC_DSOS_01
    pctfree 10
    initrans 10
    maxtrans 255
    storage
    (
      initial 2560
      next 5K
    )
  (
    subpartition AMNUM_VAL_RDC_MAXXP01 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP02 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP03 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP04 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP05 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP06 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP07 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP08 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP09 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP10 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP11 tablespace RDC_DSOS_01,
    subpartition AMNUM_VAL_RDC_MAXXP12 tablespace RDC_DSOS_01
  )
);
-- Create/Recreate indexes
create index VESTEK.IDX_ATTRIB_MNUM_RDC_ATDEF on VESTEK.ATTRIB_MDAY_NUMBER_VAL_RDC (ATTRIBUTE_DEFINITION_ID, END_DT, START_DT, ENTITY_UID, PARTITION_DT, PARTITION_ID)
  nologging  local;
-- Create/Recreate primary, unique and foreign key constraints
alter table VESTEK.ATTRIB_MDAY_NUMBER_VAL_RDC
  add constraint PK_ATTRIB_MDAY_NUMBER_VAL_RDC primary key (ENTITY_UID, END_DT, START_DT, ATTRIBUTE_DEFINITION_ID, PARTITION_DT, PARTITION_ID, VALUE)
  using index
  local;
alter index VESTEK.PK_ATTRIB_MDAY_NUMBER_VAL_RDC nologging;
alter table VESTEK.ATTRIB_MDAY_NUMBER_VAL_RDC
  add constraint FK_ATDEF_ATMDAYNUMVALRDC foreign key (ATTRIBUTE_DEFINITION_ID)
  references VESTEK.ATTRIB_DEFINITION (ATTRIBUTE_DEFINITION_ID);
alter table VESTEK.ATTRIB_MDAY_NUMBER_VAL_RDC
  add constraint FK_ENT_ATMDAYNUMVALRDC foreign key (ENTITY_UID)
  references VESTEK.ENTITY (ENTITY_UID);