Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle Partition


Create a table which hold userdata
CREATE TABLE TXN
   (    TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2)
   )


Insert data into the table with appx. 10000 recs per month for 1 year months can be increased/decreased using 0..11)
begin
for z in 0..11 loop
for i in 1..323 loop
  for j in 1..31 loop
insert into txn1 values(sysdate-433+j+(31*z),1212*i,2323232*i,122323*i,(select
decode(trunc(dbms_random.value(1,5)),1,'CN',2,'MD',3,'CO',4,'TR','CN') from dual));
end loop;
end loop;
end loop;
end;
/


Range_partition

 CREATE TABLE TXN_RANGE
( TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY RANGE (tdate)
(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p4 VALUES LESS THAN (TO_DATE('31/12/2015', 'DD/MM/YYYY')) TABLESPACE TEST);


List_partition
CREATE TABLE TXN_LIST
( TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY LIST (ACODE)
(
  PARTITION Chennai VALUES ('CN'),
  PARTITION madurai VALUES ('MD'),
  PARTITION Coimb VALUES ('CO'),
  PARTITION trichy VALUES ('TR')
  );


HASH_partition

CREATE TABLE TXN_HASH
(TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY HASH (TDATE)
PARTITIONS 4
STORE IN (TEST, TEST, TEST, TEST);


Composite  partition(Range-List)
CREATE TABLE TXN_RANGE_LIST
(TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY RANGE (TDATE)
SUBPARTITION BY LIST (ACODE)
SUBPARTITION TEMPLATE
(
  SUBPARTITION Chennai VALUES ('CN'),
  SUBPARTITION madurai VALUES ('MD'),
  SUBPARTITION Coimb VALUES ('CO'),
  SUBPARTITION trichy VALUES ('TR')
  )
(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE TEST);


To insert data use the below commad for all partitioned tables
insert into TXN_RANGE_LIST select * from txn1;




views :
dba_tab_partitions
select table_name,partition_name,num_rows,blocks,high_value from user_tab_partitions

dba_tab_subpartitions
select table_name,partition_name,subpartition_name,tablespace_name,num_rows from user_tab_subpartitions

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart

7 Steps to configure BDR replication in postgresql

Top 20 Highest Paying URL Shortener Companies - Best URL Shorteners To Earn Fast