-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcr_part_table.txt
183 lines (158 loc) · 7.45 KB
/
cr_part_table.txt
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
-- Please focus on the keywords of the statements, not on the table and column names!
-- Data dictionary views about partitioned tables and indexes:
-- (DBA_PART_TABLES, DBA_PART_INDEXES, DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS,
-- DBA_TAB_SUBPARTITIONS, DBA_IND_SUBPARTITIONS, DBA_PART_KEY_COLUMNS, DBA_SUBPART_KEY_COLUMNS
-- DBA_SUBPARTITION_TEMPLATES)
drop table eladasok;
drop table eladasok2;
drop table eladasok3;
drop table eladasok4;
drop table eladasok5;
-- Range partitioned table (RANGE):
CREATE TABLE eladasok (szla_szam NUMBER(5),
szla_nev CHAR(30),
mennyiseg NUMBER(6),
het INTEGER )
PARTITION BY RANGE (het)
(PARTITION negyedev1 VALUES LESS THAN (13) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users,
PARTITION negyedev2 VALUES LESS THAN (26) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE example,
PARTITION negyedev3 VALUES LESS THAN (39) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users)
;
insert into eladasok values(100, 'Sport cikkek', 231, 2);
insert into eladasok values(101, 'Irodai termekek', 1200, 3);
insert into eladasok values(102, 'Eszkozok', 43, 4);
insert into eladasok values(103, 'Gepek', 21, 6);
insert into eladasok values(104, 'Butorok', 31, 7);
insert into eladasok values(105, 'Ingatlan', 3, 8);
insert into eladasok values(106, 'Szolgaltatasok', 200, 9);
insert into eladasok values(107, 'Elelmiszer', 300, 40); -- we cannot insert this record, 40 > 39
-- Hash partitioned table (HASH):
CREATE TABLE eladasok2 (szla_szam NUMBER(5),
szla_nev CHAR(30),
mennyiseg NUMBER(6),
het INTEGER )
PARTITION BY HASH ( het )
(PARTITION part1 TABLESPACE users,
PARTITION part2 TABLESPACE example,
PARTITION part3 TABLESPACE users )
;
-- List partitioned table (LIST, VALUES):
CREATE TABLE eladasok3 (szla_szam NUMBER(5),
szla_nev CHAR(30),
mennyiseg NUMBER(6),
het INTEGER )
PARTITION BY LIST ( het )
(PARTITION part1 VALUES(1,2,3,4,5) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users,
PARTITION part2 VALUES(6,7,8,9) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE example,
PARTITION part3 VALUES(10,11,12,13) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users ) -- ide sem tud beszúrni > 13-at
;
-- Subpartitions (Hash within Range)
CREATE TABLE eladasok4 (szla_szam NUMBER(5),
szla_nev CHAR(30),
mennyiseg NUMBER(6),
het INTEGER )
PARTITION BY RANGE ( het )
SUBPARTITION BY HASH (mennyiseg)
SUBPARTITIONS 3
(PARTITION negyedev1 VALUES LESS THAN ( 13 ) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users,
PARTITION negyedev2 VALUES LESS THAN ( 26 ) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE example,
PARTITION negyedev3 VALUES LESS THAN ( 39 ) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users )
;
-- Range-list subpartitions based on a template. It is useful when we have
-- a lot of partitions, and we don't have to list them one by one.
-- info about templates: DBA_SUBPARTITION_TEMPLATES
CREATE TABLE eladasok5 (szla_szam NUMBER(5),
szla_nev CHAR(30),
mennyiseg NUMBER(6),
het INTEGER )
PARTITION BY RANGE ( mennyiseg )
SUBPARTITION BY LIST (het)
SUBPARTITION TEMPLATE
(SUBPARTITION lista VALUES(1,2,3,4,5), SUBPARTITION other VALUES(DEFAULT))
(PARTITION kicsi VALUES LESS THAN (100) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users,
PARTITION kozepes VALUES LESS THAN (500) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE example,
PARTITION nagy VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
STORAGE(INITIAL 8K NEXT 8K) TABLESPACE users )
;
/**********
Information about objects created above in the data dictionary views:
---------------------------------------------------------------------
SELECT table_name, partitioning_type, subpartitioning_type,
partition_count, def_subpartition_count
FROM dba_part_tables WHERE owner='NIKOVITS';
t_name p_type sub_type p_count sub_count
--------------------------------------------------
ELADASOK RANGE NONE 3 0
ELADASOK2 HASH NONE 3 0
ELADASOK3 LIST NONE 3 0
ELADASOK4 RANGE HASH 3 3
ELADASOK5 RANGE LIST 3 2
SELECT table_name, partition_position, partition_name, composite,
subpartition_count, high_value
FROM dba_tab_partitions WHERE table_owner='NIKOVITS' ORDER BY 1,2;
t_name pos p_name comp sub_c high_value
-------------------------------------------------------
ELADASOK 1 NEGYEDEV1 NO 0 13
ELADASOK 2 NEGYEDEV2 NO 0 26
ELADASOK 3 NEGYEDEV3 NO 0 39
ELADASOK2 1 PART1 NO 0 null
ELADASOK2 2 PART2 NO 0 null
ELADASOK2 3 PART3 NO 0 null
ELADASOK3 1 PART1 NO 0 1, 2, 3, 4, 5
ELADASOK3 2 PART2 NO 0 6, 7, 8, 9
ELADASOK3 3 PART3 NO 0 10, 11, 12, 13
ELADASOK4 1 NEGYEDEV1 YES 3 13
ELADASOK4 2 NEGYEDEV2 YES 3 26
ELADASOK4 3 NEGYEDEV3 YES 3 39
ELADASOK5 1 KICSI YES 2 100
ELADASOK5 2 KOZEPES YES 2 500
ELADASOK5 3 NAGY YES 2 MAXVALUE
SELECT table_name, partition_name, subpartition_position,
subpartition_name, high_value
FROM dba_tab_subpartitions WHERE table_owner='NIKOVITS'
AND table_name='ELADASOK5' ORDER BY 1,2,3;
t-name p_name pos subp_name high_value
-------------------------------------------------------------
ELADASOK5 KICSI 1 KICSI_LISTA 1, 2, 3, 4, 5
ELADASOK5 KICSI 2 KICSI_OTHER DEFAULT
ELADASOK5 KOZEPES 1 KOZEPES_LISTA 1, 2, 3, 4, 5
ELADASOK5 KOZEPES 2 KOZEPES_OTHER DEFAULT
ELADASOK5 NAGY 1 NAGY_LISTA 1, 2, 3, 4, 5
ELADASOK5 NAGY 2 NAGY_OTHER DEFAULT
SELECT name, column_name, column_position FROM dba_part_key_columns WHERE owner='NIKOVITS';
name col pos
--------------------------
ELADASOK HET 1
ELADASOK2 HET 1
ELADASOK3 HET 1
ELADASOK4 HET 1
ELADASOK5 MENNYISEG 1
SELECT name, column_name, column_position FROM dba_subpart_key_columns WHERE owner='NIKOVITS';
name col pos
--------------------------
ELADASOK4 MENNYISEG 1
ELADASOK5 HET 1
Segment name is the same as the table name. The real segments (data objects)
are the partitions (subpartitions):
SELECT segment_name, partition_name, segment_type
FROM dba_segments WHERE owner='NIKOVITS' AND segment_name='ELADASOK5';
segment_name partition_name segment_type
--------------------------------------------------
ELADASOK5 KICSI_LISTA TABLE SUBPARTITION
ELADASOK5 KICSI_OTHER TABLE SUBPARTITION
ELADASOK5 KOZEPES_LISTA TABLE SUBPARTITION
ELADASOK5 KOZEPES_OTHER TABLE SUBPARTITION
ELADASOK5 NAGY_LISTA TABLE SUBPARTITION
ELADASOK5 NAGY_OTHER TABLE SUBPARTITION
**********/