[PL/SQL] Performance tuning — Table Partition

Hey, Do you wanna do a full table scan with 999999999… records ? 😎😎

What is Partition ?

• Basically, partition is an concept was introcuded in Oracle from performance aspect. For i.e instead of storing entire information of a table in a single storage. We can just logically split the storage into multiple partitions. And whenever we want to retrieve the information, we can just get the information from a specific partition. So Oracle will avoid full table scan. That partitions are typically done base on logical key column(s).
• Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.
• The most attractive benefit of partitioning is that performance improvements that can be achieved when querying partitioned tables. Partition pruning is the term used to describe when the predicates of a query are such that the entire table does not need to be scanned, but only a subset of the partitions.

What are key benefits?

• Increases performance by only working on the data that is relevant.
• Improves availability through individual partition manageability.
• Decreases costs by storing data in the most appropriate manner.
• Is easy as to implement as it requires no changes to applications and queries.
• Is a mature, well proven feature used by thousands of Oracle customers.

What are Partitioning Methods ?

Oracle supports a wide array of partitioning methods:

• Range Partitioning — the data is distributed based on a range of values.
• List Partitioning The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.
• Auto-List Partitioning — extends the capabilities of the list method by automatically defining new partitions for any new partition key values.
• Hash Partitioning — an internal hash algorithm is applied to the partitioning key to determine the partition.
• Composite Partitioning — combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.
• Multi-Column Range Partitioning — an option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.
• Interval Partitioning — extends the capabilities of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.
• Reference Partitioning Partitions — a table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.
• Virtual Column Based Partitioning — allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.
• Interval Reference Partitioning — an extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.

1. Range Partitioning

Let’s make a demo to see how much performance will be improved by using partition. Here we’ll observe by Explain plan.

Selecting query and pressing “F5” on PL/SQL Developer

Before partition : it costs 1274 with “TABLE ACCESS FULL”

After partition : it costs only 110 with “PARTITION RANGE SINGLE”

Here we can see it searched on single partition 8

• The same with index, if you use function on an indexed/partitioned column, then the index/partition will be skipped. As below, it took “PARTITION RANGE ALL” and searched from the 1st partition to 12th partition.

• Partition has lots of power

• We can use INTERVAL PARTITION (Oracle 11.1 onwards) to automatically partition a table by a date column after every month

INTERVAL = 1 million potential partitions

There is a curiousity point when using interval partition. When we “SELECT * FROM demo” even when the table is empty, but the Explain plan show that it goes through over 1 million partitions. There of course are not 1 million partitions in the table, yes because it is empty. That’s a curiosity you’ll see when you scan a interval partition table top to bottom, we’re listing the potential partitions that may one day come into existence.

Now if we start inserting, then it’ll automatically partition a table monthly

2. List Partitioning (Oracle 12.2 onwards)

Whenever we insert data having country = ‘VN’, it’ll be stored in a different partition called “p_vietnam”

Note

Let’s assume we don’t have a default partition then if we insert data with “country” out of defined partitions, it’ll raise an error

INSERT INTO emp(empno, country, state, salary) VALUES(7, 'France', 'Paris', 700);
--> ORA-14400: inserted partition key does not map to any partition

In Oracle 12c, we could use “AUTOMATIC” keyword, then Oracle will automatically creates partitions when it encounter new values.

3. Multi Columns List Partitioning (Oracle 12.2 onwards)

For older version of Oracle

For Oracle 12.2 onwards

And the same with List Partitioning, we can use AUTOMATIC keyword too

Let’s imagine we have a transaction table with billion of rows

Then we want to retrieve details of all the customers from EMEA region

• We’re taking about 30% data
• Indexing will be ineffective (Index is useful when we retreive 5–10% data)
• Full table scan will take too much time

In this case, we can use “Partitioned by region”. For each partitionings, it creates different segments where the data is being stored base on the region. Which means on my database, on my storage, on my disk I’ll have the data corresponding to each specific storages in the different areas/segments.

Now, if we retrieve EMEA data, if we see in the explain plan, we’ll see full table scan but only for that particular partition and this particular concept called “Partition Prunning”.

• Full Partition Scan (Less data, much faster)

NOTE

Any table is bigger than 2 GB, maybe it’s possibly contain table partitioning

How Oracle actually stores the data physically on disk?

Oracle store data in blocks which is called data blocks.

There are 2 types of blocks :

• The branch block : does not have any physical information about the actual location of the table where the data is being stored. It just has the pointer to the leaf blocks
• The leaf blocks : are the lowest level blocks which have actual information about the data.

And the data is stored randomly (not sequently) in blocks

SELECT * FROM transaction_demo;

Let’s see how many blocks are being used by the table. Here we can see, after inserting data, it creates 4096 blocks and consumed 32 (MB)

SELECT segment_name, blocks, bytes/1024/1024 MB
  FROM user_segments
 WHERE segment_name = 'TRANSACTION_DEMO';

Let’s see know how many blocks are being used by the index. Here we have 4 branched blocks and a huge number of 1875 leaf blocks

ANALYZE INDEX idx_tran_id VALIDATE STRUCTURE;
SELECT name, height, br_blks, lf_blks
  FROM index_stats