-- Partícionálással kapcsolatos katalógusok (adatszótár nézetek): -- (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) drop table eladasok; drop table eladasok2; drop table eladasok3; drop table eladasok4; drop table eladasok5; -- Particionálás tartományok alapján 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); -- ezt már nem tudja beszúrni, 40 > 39 -- Particionálás hash kulcs alapján CREATE TABLE eladasok2 (szla_szam NUMBER(5), szla_nev CHAR(30), mennyiseg NUMBER(6), het INTEGER ) PARTITION BY HASH ( het ) (PARTITION part1 SEGMENT CREATION IMMEDIATE TABLESPACE users, PARTITION part2 SEGMENT CREATION IMMEDIATE TABLESPACE example, PARTITION part3 SEGMENT CREATION IMMEDIATE TABLESPACE users ) ; insert into eladasok2 select * from eladasok; -- Particionálás lista alapján 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 ; insert into eladasok3 select * from eladasok; -- Alparticiók létrehozása partición belül (range-en belül hash) 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 ) ; insert into eladasok4 select * from eladasok; -- Range-list alparticiók létrehozása minta alapján. Ez akkor hasznos ha nagyon sok -- partíció lenne, és így nem kell őket egyesével felsorolni -- mintára vonatkozó infók: 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 ) ; insert into eladasok5 select * from eladasok; commit; /********** A fenti táblákra vonatkozó információk az adatszótár nézetekben: ---------------------------------------------------------------- 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 A szegmens neve megegyezik a tábla nevével, a tényleges adatobjektumok pedig a partíciók illetve alpartíciók lesznek: 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 **********/