EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle Table Partition

By Priya PedamkarPriya Pedamkar

Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Home Data Science Data Science Tutorials Oracle Tutorial Oracle Table Partition

Oracle Table Partition

Introduction to Oracle Table Partition

PARTITION in Oracle database can be defined as a concept in which data objects like tables, indexes, and index-organized tables can be reduced or subdivided into smaller areas or pieces and these portioned data objects can be accessed at the much finer level which helps in increasing the performance as it works on only on the relevant data, it is also easy to maintain and decreases the cost by appropriately storing the data and also increases the availability.

Syntax

Let us now look at the syntax of creating the Oracle Table Partition below.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Create table table_name(column_1 datatype
Column_2 datatype, …, column_n datatype)
Partition by range (column)
(partition p1 values condition,………
…partition pN values condition);

Parameters

Below are the parameters mentioned :

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)

table_name: It refers to the name of the table we want to create
column_1 datatype, column_2 datatype, …, column_n datatype: These refer to the name of the columns and their data types
column: It refers to the column name based on which you will create the partition.

How to Perform Table Partition in Oracle?

In the previous section, we discussed the definition of table partition. In this section, we are going to discuss how to perform table partition in Oracle. To do this there are four ways in which we can do partition in Oracle.

• Range Partition
• Hash Partition
• List Partition
• Composite Partition

Let us now discuss each one of them below.

• Range Partition: This type of partition is used when data is distributed over a range of values. It maps data based on the ranges of values of the partitioning key and the range is already established for each partition and based on that the values are stored in each partition. It is one of the most common ways of doing partition.

• Hash Partition: it is used to evenly distribute data across all the partitions that are defined. The mapping of rows is based on the hash value of the partition key. Hash partition is also used as an alternative to range partition.

• List partition: This type of partition is completely different from the other two partitions. In list partition, the partition is defined by a discrete list of values. We can use more than one column as a partition key. In this way, it is different from range partition because in range partition a range of values is associated with partition whereas we can ourselves specify the list of values for the partition column.

• Composite partition: As the name suggests it is a type of partition where we are using the range method for partition and then we are using the hash method for subpartition.

Examples of Oracle Partition

Let us now look into a few examples so that we can get a better understanding of the concept.

Examples #1 – Using Partition by Range to Create a Table

In this section, we are going to create a table using the partition by range concept. Let us look at the query for the same.

Query:

CREATE table sale_product (year number(4),product_name varchar2(10), amount number(10))
partition by range (year)
(partition p1 values less than(2002),
partition p2 values less than(2003),
partition p3 values less than(2004),
partition p4 values less than(maxvalue));

In the above query, we can see that there are four partitions. The partition p1 is used to store the rows of the year 2002, similarly, partition p2 will store rows of the year 2003, partition p3 will store the rows of the year 2004 and the rest of the rows are stored in partition p4.

Let us execute the query and check the result.

Oracle Table Partition output 1

As per the screenshot above, we can see that the table sale_product has been created.

INSERTING DATA INTO THE PARTITIONED TABLE:

Let us now insert data into the table and check. So we will insert four rows of data into the table using the INSERT statement.

Query:

INSERT INTO sale_product (year, product_name, amount) VALUES ('2002', 'car', 400000);
INSERT INTO sale_product (year, product_name, amount) VALUES ('2003', 'truck', 1500000);
INSERT INTO sale_product (year, product_name, amount) VALUES ('2004', 'bike', 40000);
INSERT INTO sale_product (year, product_name, amount) VALUES ('2005', 'cycle', 4000);

Let us execute the query in the developer and check the result.

Oracle Table Partition output 2

As we can see in the screenshot the data has been inserted.

Now let us check the table data after insert.

Query:

SELECT * from sale_product;

Oracle Table Partition output 3

As we can see in the screenshot that four rows have been inserted.

Let us now check the partition details along with the partition name, position, and other details.

Query:

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALE_PRODUCT';

Let execute the query in and check the result.

Oracle Table Partition output 4

As we can see in the screenshot above the query displays the details.

Example #2 – Using Partition by Hash to Create a Table

In this example, we will create a table product with two columns. Let us look at the query below.

Query:

CREATE TABLE product(product_id NUMBER(4), description VARCHAR2 (100))
PARTITION BY HASH (product_id)
PARTITIONS 4
STORE IN (p1, p2, p3, p4);

In the above query, the partitioning column is product_id and p1, p2, p3, p4 are the tablespaces for the four partitions to store the respective values.

Let us execute the query and check the result.

output 5

As we can see in the screenshot above the table has been created successfully.

Example #3 – Using List Partition to Create a Table

In this section, we are going to discuss the concept of list partition to create a table. In this example, we are going to create a partition table employee_database with three columns. Let us prepare the query for the same.

Query:

CREATE table employee_database (employee_id number (5), employee_name varchar2(20),City varchar2(20))
Partition by list (City)
(Partition p1 values ('NEWDELHI','LUCKNOW'),
Partition p2 values ('ASANSOL','JAMSHEDPUR'),
Partition P3 values ('MUMBAI', 'GOA'),
Partition p4 values ('LONDON','CHICAGO'));

In the above query, there are four partitions and the portioning column is the city.

Let us execute the query and check the result.

output 6

As we can check the above screenshot the table has been created.

Example #4 – Using Composite Partition to Create a Table

In this example, we are going to create a table product_list using the concept of composite partition. Let us prepare the query for the same.

Query:

CREATE TABLE PRODUCT_LIST (product_id NUMBER, description VARCHAR (100), costprice NUMBER(5))
PARTITION BY RANGE (costprice)
SUBPARTITION BY HASH(description)
SUBPARTITIONS 4 STORE IN (tab1, tab2, tab3, tab4)
(PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (4000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

In this above query, we have created the partition using partition by range concept and four sub-partitions using partition by the hash concept.

Let us execute the query and check the result.

output 7

As the above screenshot, the table product_list has been created.

Conclusion

In this article, we discussed the concept of partition in Oracle. We began the article by the definition of the partition in the Oracle database and we then discussed the syntax and different ways to create a partition table. Later on, we went through examples for each concept.

Recommended Articles

This is a guide to Oracle Table Partition. Here we discuss how to Perform Table Partition in Oracle along with query examples for better understanding. You may also have a look at the following articles to learn more –

  1. Oracle COALESCE
  2. Oracle REPLACE()
  3. Oracle NVL()
  4. Oracle REGEXP
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More