Creating partition of database tables

[LaMarcus]

A large database has so many tools to handle but tell me some methods to distribution of particular object on different places,Is it possible to do if yes then let me know.How would I distribute ? Can you provide me some information about table partitioning,if a database table is large to store on the server,we can place it somewhere else.explain me with the examples.

[Praetor]

In current days,organization uses a large database with a lot of size (in gigabytes and Terrabytes).
Oracle database provide much more feature of partitioning the object of database from earlier versions.
Assume the table "sales_data" contains millions of records but all the records belong to four years only i.e. 1988, 1989,1990 and 1991 and you require only the latest record of the database you will query to access the data like this -

SQL>select sum(amount) from sales_data where production_year=1990;
SQL>select product_name,sum(amount) from sales_data where production_year=1991
  Group by products;

The oracle will scan the whole table to fetch the record and consume much time to produce the result of the query.But if you are using partitioning then it would be much easier to handle the performance of your database.

[Katty]

Creating range partition of table:

create table sales_data (production_year number(4),
                  products varchar2(10),
                  amount number(10,2))
     partition by range (production_year)
     partition part1 values less than (1992) tablespace Tab1,
     partition part2 values less than (1993) tablespace Tab2,
     partition part3 values less than (1994) tablespace Tab3,
     partition part4 values less than (1995) tablespace Tab4,
 partition part5 values less than (MAXVALUE) tablespace Tab5;

The above created partition table shows that table has been partitioned into 5 segments according to production_year column of the table and continuously stored in different tablespaces through which the performance would be much better to access the table.

[kelfro]

Hash partitioning :

hash partitioning of table provides the method to access the table according to their hash partitioned values.Using partition keys,the rows are mapped in the partition segments.

To create the partition of the "Device_list" table using hash partitioning-

CREATE TABLE Device_list
 (Device_id NUMBER, Description VARCHAR2 (60))

         PARTITION BY HASH (Device_id)
         PARTITIONS 8
STORE IN (tbs1, tbs2, tbs3, tbs4);

[Zecho]

List partitioning :
Using list partitioning ,you can control over the mapping of rows in the partition.
it allows different order of data and unrelated collection of rows to be grouped together very easily.
The statement below will create a list partition of table:

Create table students (student_id number(5),
                  Name varchar2(20),
                  Address varchar2(10,2),
                  City varchar2(20),
                  Fee_payment number(10,2))
                  Partition by list (city),
Partition north_India values (‘DELHI’,’CHANDIGARH’),
Partition east_India values (‘KOLKOTA’,’PATNA’),
Partition south_India values (‘HYDERABAD’,’BANGALORE’,’CHENNAI’),
Partition west India values (‘MUMBAI’,’GOA’);