Oracle database provides several partitioning methods to distribute data within a table. Here are the commonly used partitioning methods in Oracle:
Range Partitioning:
Range partitioning divides data based on a specified range of values. You define a partition key column, and each partition represents a range of values for that column. For example, you can partition a table by date ranges, such as monthly or yearly partitions.
List Partitioning:
List partitioning allows you to specify explicit values for partitioning. Each partition is associated with a specific set of values defined in the partition key column. This method is useful when you want to partition data based on discrete values, such as region, department, or category.
Hash Partitioning:
Hash partitioning distributes data based on a hash function applied to the partition key column. The hash function evenly distributes the rows across partitions, ensuring data is spread evenly. This method is suitable when you want to achieve load balancing across multiple disks or when there is no clear range or list criterion for partitioning.
Interval Partitioning:
Interval partitioning is a variation of range partitioning that automatically creates new partitions as data is inserted. You specify a partitioning interval, and Oracle automatically creates partitions for data falling outside the existing partitions. This method simplifies the management of large and rapidly growing datasets.
Composite Partitioning:
Composite partitioning combines multiple partitioning methods within a single table. It allows you to define sub-partitions within each partition using a different partitioning method. For example, you can have range partitioning at the top level and hash partitioning within each range partition.
Reference Partitioning:
Reference partitioning is used when you have a parent-child relationship between two tables, and you want to partition the child table based on the parent table's partitioning scheme. The child table inherits the partitioning key from the parent table, simplifying data management and maintaining data integrity.
Re-Partitioning:
Repartitioning tables in an Oracle database can be accomplished using various methods, depending on your specific requirements and Oracle version. Here's a general approach to re-partitioning tables in Oracle:
1.Evaluate the current partitioning scheme: Before making any changes, analyze the existing partitioning scheme for the table you want to re-partition. Determine the current partitioning strategy, including the partition key, number of partitions, and partitioning method.
2.Create a new table with the desired partitioning scheme: Use the CREATE TABLE statement to create a new table with the updated partitioning scheme. Define the partition key, number of partitions, and partitioning method based on your requirements. You can also specify additional attributes such as tablespace, compression, and storage settings.
CREATE TABLE new_table
PARTITION BY <partitioning_method>(<partition_key>)
( PARTITION partition_name1 VALUES <value_range>,
PARTITION partition_name2 VALUES <value_range>,
...
)
TABLESPACE <tablespace_name>;
3.Copy data from the original table to the new table: Use the INSERT INTO ... SELECT statement to populate the new table with data from the original table. Ensure that the data is inserted into the appropriate partitions based on the new partitioning scheme.
INSERT INTO new_table
SELECT * FROM original_table;
4.Validate the data in the new table: Perform checks and tests to ensure that the data has been accurately copied to the new table and that the new partitioning scheme meets your requirements. Verify the integrity of the data and ensure that any business logic or constraints are maintained.
5.Rename or drop the original table and rename the new table: Once you are confident that the new table is functioning correctly,
you can choose one of the following approaches:
a. Rename the original table:
RENAME original_table TO original_table_old;
RENAME new_table TO original_table;
b.Drop the original table:
DROP TABLE original_table;
RENAME new_table TO original_table;
6.Rebuild indexes and other dependent objects: If there are any indexes, constraints, triggers, or other objects associated with the table, you may need to rebuild or modify them to align with the new partitioning scheme. Review the dependencies and adjust them accordingly.
7.Test and validate the re-partitioned table: Perform thorough testing to ensure that the re-partitioned table behaves as expected. Validate its performance and functionality to confirm that the new partitioning scheme has achieved the desired improvements.
Remember to back up your data and thoroughly test the process in a non-production environment before performing these steps on a production database. Re-partitioning can have significant implications, so it's crucial to execute the process carefully and verify the results.
It's important to note that re-partitioning a table can be a resource-intensive operation, especially for large tables. Therefore, it's recommended to perform this process during a maintenance window or during low database activity periods. Additionally, always take backups and thoroughly test the process on a non-production environment before applying it to a production database
Defragmentation:
Defragmentation, on the other hand, focuses on optimizing the storage and physical layout of data within a table or an index. It addresses fragmentation issues that can occur due to insertions, updates, and deletions over time. Fragmentation refers to the scattered or disorganized arrangement of data blocks within a table or index, leading to inefficient disk I/O and reduced performance. Defragmentation aims to reorganize and consolidate the data blocks to eliminate fragmentation and improve data access efficiency.
Defragmentation in Oracle can be achieved using different techniques such as:
Table or Index Rebuilding: Rebuilding a table or index involves creating a new structure and moving the data into it, effectively reorganizing the storage. This process helps eliminate fragmentation and can be done online or offline, depending on the Oracle version and table/index availability requirements.
Shrink Operations: Oracle provides shrink operations for tables, indexes, and table partitions. Shrinking aims to reclaim unused space within segments by compacting the data and reducing fragmentation. This operation can be done online or offline.
Table or Index Compression: Oracle offers compression techniques such as Advanced Row Compression and Advanced Index Compression. These methods reduce the storage footprint by compressing the data and can also help in reducing fragmentation.
The primary goal of defragmentation is to optimize the storage and improve data access efficiency, while re-partitioning focuses on modifying the partitioning scheme to meet specific requirements related to data distribution, manageability, and performance. Both processes can be valuable for maintaining a well-performing Oracle database, but they address different aspects of data organization and storage optimization.
High level task
Step1: drop indexes & disable constraints
Step2: rename table & constraints
Step3: create table with (new) partition
Step4: insert data from renamed table.
Step5: create indexes
Step6: Grants/Synonyms/Compile
Step7: Diable parallel on table and indexes.
Step8: Lock table stats on backup table
Step9: Run stats on TABLE1 and TABLE2
Step10: Run utlrp in the end of whole activity
Enjoy the work and do like me if you like this article!!!
