August 13, 2020

USING ORACLE PARTITION

Oracle Partition structure can be separated according to the desired conditions and behave as if indexing separately from the standard index structure (pk-fk-uk) feature. If it is more than 2 GB of large DB structures, it will be healthy to use. When we do partition by months, for example, a bear is just like the update, insert. Although it has a structure that makes transactions, it can only run the information for other months in read-only mode. It can be used with the partition in indices we define in the local or global structure. The data in the partition structure is held over different tablespaces.

Nonpartition and Partition
Nonpartition and Partition

 

Single Level Partition

It can be applied to one or more columns. We can value according to the type of value in which the data in the table is kept.

Single Level Partition
Single Level Partition

 

  • List Partitioning

In the partitioning structure of the data limited to a certain value, It is kept.

Sample: I partitioned IT, UK, US ones that I want according to country_ids from Locations table.

List Partitioning
List Partitioning

 

Since my computer is oracle XE, there is no partition feature. For this, it must be an enterprise edition and above. You can check it from the command prompt. It should be true if it was installed. To solve the error, you introduce “rdbms / lib” folder of “oracle_home” folder to CMD console and make other settings from there.

"oracle_home" and "rdbms/lib"
“oracle_home” and “rdbms/lib”
  • Range Partitioning

Installs the partition structure according to a certain range. Although it is separate for each partition, it is made according to the values in a particular column. It is also the most used partition structure.

Range Partitioning
Range Partitioning

 

Here, we separated the date values according to the data before April, June, October and January and kept them separately in the tablespace. We can see the example usage form as below.

You can see the speed of the partition architecture in the form.

  • Hash Partitioning

It can be used if we cannot divide the data in a table into a certain range.

Hash Partitioning
Hash Partitioning

Composite Partitioning

It can be used as a hybrid partition structure. Combinations such as range and list, list and hash, hash and hash are used.

Composite Partitioning
Composite Partitioning

 

Leave a Reply

Your email address will not be published. Required fields are marked *