Scribd
Upload
12 views1 page

Understanding Oracle Table Partitions

Table partitions divide large tables into smaller and more manageable pieces to improve query performance. There are two main types of table partitioning: range partitioning which divides data into partitions based on a range of values of a column, and list partitioning which assigns data to partitions based on a list of discrete values in a column. Partition pruning is a technique where the Oracle query optimizer ignores partitions that cannot contain rows relevant to a query based on the WHERE clause, improving performance by only accessing the necessary partitions.

Uploaded by

lixo healthcare
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
12 views1 page

Understanding Oracle Table Partitions

Table partitions divide large tables into smaller and more manageable pieces to improve query performance. There are two main types of table partitioning: range partitioning which divides data into partitions based on a range of values of a column, and list partitioning which assigns data to partitions based on a list of discrete values in a column. Partition pruning is a technique where the Oracle query optimizer ignores partitions that cannot contain rows relevant to a query based on the WHERE clause, improving performance by only accessing the necessary partitions.

Uploaded by

lixo healthcare
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

What are Table partitions?

• Table partitions are used to improve the performance of a retrieval/SELECT


query
• Index will work effectively only when data retrieved is lesser than 15% data
volume
• If retrieved data volume by a select query is more than 15% every time than
table partition will be the right choice
• Types of Table partition  LIST, RANGE, HASH

RANGE - PARTITION
*******************
CREATE TABLE sales
(
sale_id number ,
product_ id number,
price number
)
PARTITIONS BY RANGE (sales_id) (
partition s1 values less than (10000) tablespace ts1,
partition s2 values less than (3000) tablespace ts2,
partition s3 values less than (MAXVALUE) tablespace ts3
);

LIST PARTITION
*******************
CERATE TABLE dept_region
(dept_id NUMBER NOT NULL,
dept_name VARCHAR2(20),
trans_value NUMBER (10,2)
state VARCHAR2(2)
)
PARTITIONS BY LIST(state)
(
partition EMEA VALUES ('AND','UAE','AFGH'),
partition APAC VALUES ('AUS','IND'),
partition LATAM VALUES ('JAMICA','MEXIO'),
partition unknow VALUES ('DEFAULT')
);
_________________________
What is partition pruning ?
***********************
In a SQL query if we have partitioned table and appropriate column is called in
WHERE clause then ORACLE
query optimizer decides which partitions will be ignored and which partitions will
be accessed and read.
This is called partition pruning. It is an essential performance feature for data
warehouses.

You might also like