Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Partition Pruning

Description

Partition pruning is an optimization, limiting the scope of your query according to the criteria you have specified. It is the skipping of unnecessary data partitions in a query. By doing this, you can greatly reduce the amount of data output from the disk and time spent on processing data as well as improve query performance and resource availability.

Note In versions lower than CUBRID 9.0, partition pruning has been executed at the query compiling stage. However, in version of CUBRID 9.0 or higher, it is executed at the server side at the query execution stage. Therefore, in version of CUBRID 9.0 or higher, partition pruning can be executed for more complex and various queries than existing versions. However, it is not available to print out the query information for a partitioning pruning query and optimization of ORDER BY SKIP, and GROUP BY SKIP is not supported.

Example 1

The following example shows how to create the olympic2 table to be partitioned based on the year the Olympic Games were held, and retrieve the countries that participated in the Olympic Games since the 2000 Sydney Olympic Games. In the WHERE clause, partition pruning takes place when equality or range comparison is performed between a partition key and a constant value.

In this example, the before_1996 partition that has a smaller year value than 2000 is not scanned.

CREATE TABLE olympic2

( opening_date DATE, host_nation VARCHAR(40))

PARTITION BY RANGE ( EXTRACT (YEAR FROM opening_date) )

( PARTITION before_1996 VALUES LESS THAN (1996),

  PARTITION before_MAX VALUES LESS THAN MAXVALUE );

 

SELECT opening_date, host_nation FROM olympic2 WHERE EXTRACT ( YEAR FROM (opening_date)) >= 2000;

Example 2

The following example shows how to retrieve the method of getting the effects of partition pruning by retrieving data with a specific partition when partition pruning does not occur. In the first query, partition pruning does not occur because the value compared is not in the same format as that of the partition expression.

Therefore, you can use the same effect of partition pruning by specifying the appropriate partition as shown in the second query.

SELECT host_nation FROM olympic2 WHERE opening_date >= '2000 - 01 - 01';

 

SELECT host_nation FROM olympic2__p__before_max WHERE opening_date >= '2000 - 01 - 01';

Example 3

The following example shows how to specify the search condition to make a partition pruning in the hash partitioned table, called the manager table. For hash partitioning, partition pruning occurs only when equality comparison is performed between a partition key and a constant value in the WHERE clause.

CREATE TABLE manager (

code INT,

name VARCHAR(50))

PARTITION BY HASH ( code) PARTITIONS 4;

 

SELECT * FROM manager WHERE code = 10053;

Remark
  • The partition expression and the value compared must be in the same format.
  • To enable pruning for hash partitioning and list partitioning, use the following partitioning key expression in the WHERE clause. The following constant expression does not include any table columns and any other conditions are not allowed.
    • <partitioning key> = <constant expression>
    • <partitioning key> { IN | = SOME | = ANY } ( <constant expression list> )
  • To enable pruning for range partitioning, use the following partitioning key expression in the WHERE clause.
    • <partitioning key> { < | > | = | <= | >= | } <constant expression>
    • <partitioning key> BETWEEN <constant expression> AND <constant expression>