PARTITION in Oracle

What is a PARTITION in Oracle?

With the help of Partitioning, It allows DBAs to split large tables into more manageable "sub-tables",  to improve database performance, manageability and availability.

What actually happens in partitioning ?

With table partitioning, a table can be physically divided into multiple smaller tables, called partitions, while logically it stays one table. This means your code stays the same, but full partition scans will be executed instead of a full table scan.

When to Partition a Table ?

- If Tables size greater than 2 GB , then it always considered for partitioning.

- Tables containing historical data, in which new data is added into the newest partition.

Example :

If you have a historical table where only the current month's data is updatable and the other 11 months are read only, then that table will be partitioned.

-When the contents of a table need to be distributed across different types of storage devices.

Benefits of partitioning

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Partitioning Reduces the downtime in case of data failure. And a failure of a particular partition will no way affect other partitions.

Maintenance operations can be carried out on selected partitions, while other partitions are available to users, So this will also reduce the downtime.

What is the advantage of partitions, by storing them in different Tablespaces??

1     Reduces the possibility of data corruption in multiple partitions.

2     Back-up and recovery of each partition can be done independently.

What are the Types of partitioning ?

1     Range partitions

2     List partitions

3     Hash partitions

4     Sub partitions

1     Range partitions

The table is divided in ranges, typically used for ‘date’ ranges  

It is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

2     List partitions

Here, a limited set of possible values is given ; such that the rows containing the same value are grouped.

Enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.

3     Hash partitions

Happens when a random distribution occurs.

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

4     Sub partitions

INDEX Partitioning

•              Non partitioned:              No partitioning applied

•              Globally partitioned:     The index is not partitioned on the same key as the table, offer                                                                higher flexibility.

•              Locally partitioned:       The index is partitioned on the same key as the partitioned table,

See Also

Most asked Interview Questions for 2+ Candidates

 

Frequently asked Datapump Scenarios

Database life of DBA 

 

Click here to Join Our Blog and Stay Updated

For more updates,  Like Us Our Facebook Page Here 

PARTITION in Oracle

What is a PARTITION in Oracle?

With the help of Partitioning, It allows DBAs to split large tables into more manageable "sub-tables",  to improve database performance, manageability and availability.

What actually happens in partitioning ?

With table partitioning, a table can be physically divided into multiple smaller tables, called partitions, while logically it stays one table. This means your code stays the same, but full partition scans will be executed instead of a full table scan.

When to Partition a Table ?

- If Tables size greater than 2 GB , then it always considered for partitioning.

- Tables containing historical data, in which new data is added into the newest partition.

Example :

If you have a historical table where only the current month's data is updatable and the other 11 months are read only, then that table will be partitioned.

-When the contents of a table need to be distributed across different types of storage devices.

Benefits of partitioning

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Partitioning Reduces the downtime in case of data failure. And a failure of a particular partition will no way affect other partitions.

Maintenance operations can be carried out on selected partitions, while other partitions are available to users, So this will also reduce the downtime.

What is the advantage of partitions, by storing them in different Tablespaces??

1     Reduces the possibility of data corruption in multiple partitions.

2     Back-up and recovery of each partition can be done independently.

What are the Types of partitioning ?

1     Range partitions

2     List partitions

3     Hash partitions

4     Sub partitions

1     Range partitions

The table is divided in ranges, typically used for ‘date’ ranges  

It is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

2     List partitions

Here, a limited set of possible values is given ; such that the rows containing the same value are grouped.

Enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.

3     Hash partitions

Happens when a random distribution occurs.

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

4     Sub partitions

INDEX Partitioning

•              Non partitioned:              No partitioning applied

•              Globally partitioned:     The index is not partitioned on the same key as the table, offer                                                                higher flexibility.

•              Locally partitioned:       The index is partitioned on the same key as the partitioned table,

See Also

Most asked Interview Questions for 2+ Candidates

 

Frequently asked Datapump Scenarios

Database life of DBA 

 

For more updates,  Like Us Our Facebook Page Here 

Datapump Scenarios - Frequently Asked

Q. How to split the datapump dumpfiles, into multiple and at multiple directories ?

PARALLEL parameter is used to improve the speed of the export.

This will be also more effective if you split the dumpfiles with DUMPFILE parameter across the filesystem.

Create 2 or 3 directories in different filesystems and use the commands effectively.

$ expdp / dumpfile=dir1:test_1.dmp, dir1:test_2.dmp, dir2:test_3.dmp, dir3:test_4.dmp logfile=dir1:test.log  full=y parallel=4

Q. How to limit the file size of dumpfile in Datapump ?

FILESIZE parameter is used to limit the dumpfile size. For eg., if you want to limit your dumpfiles to 5gb,  you can issue command as below

$ expdp / directory=dir1 dumpfile=test_%U.dmp logfile=test.log filesize=5120m full=y

where %U will assign numbers automatically from 1 to 99.

Q. How to import the dumpfile if the Database version are not same ?

VERSION parameter is used while taking export if you want to create a dumpfile which should be imported  into a DB which is lower than the source DB

Example:

If your source DB is 11g and target DB is 10g, you can't use the dumpfile taken from 11g expdp utility to import into 10g DB.

If you don’t use this VERSION parameter, then it will show you the error as Below

ORA-39142: incompatible version number 3.1 in dump file "/u01/datapump/test.dmp"

VERSION={COMPATIBLE | LATEST | version_string}

$ expdp / directory=dir1 dumpfile=test_1.dmp logfile=test.log VERSION=10.2.0

Q. How to improve the performace of Datapump while Importing ?

1) Import of full database should be split as tables first and indexes next. Use the parameter exclude  effectively to improve the speed of import.

EXCLUDE = INDEX,STATISTICS

This will not import the indexes and statistics which in turn only import the tables, hence improving the performance

2)Always set init.ora parameter cursor_sharing to exact which has a good effect on import's performance.

Q. How to find the contents of a dumpfile ?

If you are not sure about the schemas that were present in the dumpfile or tablespaces present inside the  dumpfile, etc., you can easily check the dumpfile for those information.

Use the below command

$  grep -a "CREATE USER" test_1.dmp

$  grep -a "CREATE TABLESPACE" test_1.dmp

The above command gives all the CREATE USER statements and CREATE TABLESPACE statements

which will be useful in many cases.

Note: You can also get the INDEXES and TABLES creation ddl from the dumpfile as well.

I’ll be updating the post whenever I come across things that can help improving the performance of datapump. 

For more updates,  Like Us Our Facebook Page Here