Partitioning
Generally, partitions are created on very large-scale database tables for dividing into multiple small parts and each part is called as “partition“.
By splitting a large table into smaller parts then data can access very fast because there is less data to sacn instead of large data of a table.
Types of partition in oracle :
1) Range Partition
2) List Partition
3) Hash Partition
If We Want To Access A Particular Partition Then We Follow The Following,
Syntax: Sql> Select * From <tn> Partition (<partition Name>);
1) Range Partition:
Range partitioning in Oracle is a powerful technique for managing large datasets efficiently. This method involves dividing a database table into smaller, more manageable pieces based on a specified range of values. For example, you might partition by in sql a sales data table by year, month, or even day. By doing so, you can improve query performance and manage data more effectively. Range partitioning makes it easier to archive old data and ensures that queries run faster by scanning only relevant Partitioning. It’s particularly useful for handling time-series data or any dataset with a natural range-based segmentation. Implementing range partitioning can significantly enhance database performance and maintainability through partition by oracle.
In This Method We Are Creating Partitions Table Based On A Particular Range Value.
Syntax: Create Table <tn> (<column Name1> <datatypes>[size], …………………..) Partition By Range (<key Column Name>) (Partition <partition Name1> Values Less Than(Value), Partition <partition Name2> Values Less Than(Value), ……..);
Ex:
Create Table Test1Â (Eid Int, Ename Varchar2(10), Sal Number (10)) Partition By Range(Sal) (Partition P1 Values Less Than (1000),partition P2 Values Less Than (2000), Partition P3 Values Less Than (3000));
Testing:
Sql> Insert Into Test1 Values(1,’sai’,2500);
Sql> Insert Into Test1 Values(2,’jones’,500);
…………………………………………………………………………;
………………………………………………………………………….;
Calling partition by in sql Partition:Â
Sql> Select * From Test1 Partition(P1);
Eid              Ename      Sal
————–Â Â Â Â Â Â Â Â ———-Â Â Â Â Â Â ———-
2              Jones        500
2) List Partition:
List partition in SQL is a technique used to enhance database performance by dividing large tables into smaller, more manageable pieces. This method involves segmenting a table based on distinct values in a specified column, such as regions or product categories. Each partition is stored separately, allowing for faster query execution and improved manageability. Using list partitioning, database administrators can optimize storage and retrieval efficiencies, leading to reduced latency and enhanced scalability. This technique is particularly useful in scenarios with distinct, non-overlapping data sets. By implementing list partitioning in SQL, businesses can achieve better data organization and streamlined database operations. In This Method We Are Creating Partitions Based On List Of Values.
Syntax:
Create Table <tn> (<column Name1> <datatype>[size],………………………………….)
Partition by SQL By List (<key Column Name>) (Partition <partition Name1> Values (Value1, Value2, ……),
Partition <partition Name2> Values (Value1, Value2,……), …………, Partition Others Values(Default));
Ex: Create Table Test2(Sno Int, Cname Varchar2(10))
Partition By List(Cname) (Partition P1 Values(‘oracle’,’mysql’),Partition P2 Values(‘java’,’php’), Partition Others Values(Default));
Testing:
Sql> Insert Into Test2 Values(1,’oracle’);
Sql> Insert Into Test2 Values(2,’c’);
………………………………………………………….
Calling A Particular Partition:
Sql> Select * From Test2 Partition(P1);
Sno        Cname
—–Â Â Â Â Â Â Â Â Â ———-
1 Oracle
3) Hash Partition:
Hash partitioning in SQL is a database optimization technique that distributes data evenly across multiple partitions using a hash function. This method can significantly enhance query performance, especially for large datasets. By hashing a key value, the data is assigned to a specific partition, ensuring a balanced load distribution. Hash partitioning is particularly useful for scenarios where uniform data distribution is critical. It also simplifies data management and retrieval processes. Implementing hash partitioning can lead to faster query execution times and improved database scalability. This technique is widely supported in modern SQL databases and is an effective strategy for database administrators looking to optimize performance.
In This Method Partitions Are Created By The System By Default.
Syntax:Â Create Table <tn> (<column Name1> <datatype>[size], ………………………………….) Partition By Hash (<key Column Name>) Partitions <number>;
Ex: Sql> Create Table Test3(Sno Int, Sal Number (10)) Partition By Hash(Sal) Partitions 5;
Note: If We Want To View All Partitions Information In Oracle Database Then We Use “User_tab_partitions” Data Dictionary.
Ex:
Sql> Desc User_tab_partitions;
Sql> Select Partition_name From User_tab_partitions
Where Table_name=’test3′;
Adding A New Partition:
Syntax:Â Alter Table <tn> Add Partition <partition Name> Values Less Than(Value);
Ex: Sql> Alter Table Test1 Add Partition P4 Values Less Than (4000);
Dropping A Partition:Â
Syntax:
Alter Table <tn> Drop Partition <partition Name>;
Ex:
Sql> Alter Table Test1 Drop Partition P1;
Note: If We Want To Know Whether Table Is Partitioned Or Not Then We Use “User_tables” Data Dictionary.
Ex:
Sql> Desc User_tables;
Sql> Select Partitioned From User_tables Where Table_name=’emp’;
Â
Summary of partition in oracle :
- Hash partitioning is good for queries with equality predicates. List partitioning is good for queries with IN or NOT IN predicates. Range partitioning is good for queries with range predicates.
- List partitioning is good for queries with IN or NOT IN predicates.
- Range partitioning is good for queries with range predicates.
For complete SQL tutorial please click here
Practice SQL online please clik
