Thursday, 9 October 2014

Oracle Table Partitioning

As salamo alaikum wa rahmatullah,

 Table partition for range with example.

Create table sales_range_ex
 salesman_id number(5),
salesman_name varchar2(50),
salesman_amount number(10,2),
sales_date date),
partition by range (sales_date)
partition sales_jan2014 value less than To_Date('31-01-2014','dd-mon-YYYY'),
partition sales_feb2014 value less than To_Date('29-Feb-2015,'dd-mon-yyyy'),
partition sales_mar2014 value less than To_Date('31-Mar-2014','dd-mon-yyyy'),
partition sales_apr2014 value less than To_Date('30-Apr-2014','dd-mon-yyyy')

insert data into table, that is normal insertion process,

Insert into sales_range_ex value(10001,'Zahangeer Khan',66666.90,'22-Jan-2014')
Insert into sales_range_ex value(10002,'John Dell',666.90,'22-Feb-2014')
Insert into sales_range_ex value(10001,'Darrell john',66666.90,'22-Mar-2014')

Selecting data normally,

Select * from sales_range_ex;

Selecting data by partition

Select * from sales_range_ex partition (sales_jan2014);

Select * from sales_range_ex partition (sales_feb2014);

Select * from sales_range_ex partition (sales_mar2014);

Thanks and Best Regards
Mohammad Shahnawaz
passion for oracle