Table Partition Queries and DML

Oracle is capable of restricting queries to specific partitions using a feature named partition pruning (sometimes termed partition elimination). What developers might not realise is that it’s possible to write queries and DML statements that target individual partitions by specifying the partition name directly.

(Note: Be aware that table partitioning is an enterprise edition only option that is licensed separately.)

For the examples used below let’s create a simple table with just 1 column and 100 rows, partitioned into ranges to 10 rows each:

CREATE TABLE part_test
   (id   NUMBER(10) NOT NULL)
PARTITION BY RANGE (id)
   (PARTITION part_test_p1 VALUES LESS THAN (11)
   ,PARTITION part_test_p2 VALUES LESS THAN (21)
   ,PARTITION part_test_p3 VALUES LESS THAN (31)
   ,PARTITION part_test_p4 VALUES LESS THAN (41)
   ,PARTITION part_test_p5 VALUES LESS THAN (51)
   ,PARTITION part_test_p6 VALUES LESS THAN (61)
   ,PARTITION part_test_p7 VALUES LESS THAN (71)
   ,PARTITION part_test_p8 VALUES LESS THAN (81)
   ,PARTITION part_test_p9 VALUES LESS THAN (91)
   ,PARTITION part_test_p10 VALUES LESS THAN (101))
/

INSERT INTO part_test
SELECT ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 100
/

COMMIT
/

SELECT

If we wanted to get the rows from the fifth partition we can write our query as:

SELECT *
FROM   part_test PARTITION (part_test_p5)

and we get the rows from that one partition:

        ID
----------
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50

10 rows selected.

Probably not surprisingly, if we provide a WHERE clause filter that excludes the rows from the partition we get back no rows, rather than have Oracle complain about any inconsistency in our query:

SQL> SELECT *
  2  FROM   part_test PARTITION (part_test_p5)
  3  WHERE  id >= 70
  4  /

no rows selected

DELETE

We can perform DML operations on specific partitions too. For example, if we wanted to delete the rows in the second partition we would execute:

SQL> DELETE
  2  FROM   part_test PARTITION (part_test_p2)
  3  /

10 rows deleted.

UPDATE

Updating rows is also possible but if the result of the update would cause a row to end up in another partition then we need to enable row movement on the table, i.e.:

SQL> UPDATE part_test PARTITION (part_test_p3)
  2  SET    id = id - 10
  3  /
UPDATE part_test PARTITION (part_test_p3)
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> ALTER TABLE part_test ENABLE ROW MOVEMENT
  2  /

Table altered.

SQL> UPDATE part_test PARTITION (part_test_p3)
  2  SET    id = id - 10
  3  /

10 rows updated.

INSERT

Insert into a specific partition is possible but rather unnecessary as Oracle can figure out which partition the data should go to:

SQL> INSERT INTO part_test PARTITION (part_test_p3)
  2  VALUES (25)
  3  /

1 row created.

If the partition specified does not match the partition that the data specified should go into then an error is thrown:

SQL> INSERT INTO part_test PARTITION (part_test_p3)
  2  VALUES (35)
  3  /
INSERT INTO part_test PARTITION (part_test_p3)
            *
ERROR at line 1:
ORA-14401: inserted partition key is outside specified partition

TRUNCATE

Lastly, although it’s a DDL statement and not DML, it’s possible to truncate an individual table partition:

SQL> ALTER TABLE part_test TRUNCATE PARTITION part_test_p1
  2  /

Table truncated.

Remote Databases

Unfortunately as of Oracle 11.2 it’s not possible to reference specific table partitions for remote tables, i.e. over a database link. An attempt to do so will result in:

ORA-14100: partition extended table name cannot refer to a remote object

Finding combinations of values in SQL

All developers are familiar with finding entries based on scalar comparisons but some don’t realise that Oracle can perform comparisons involving combinations of values too.

Consider a table, named COMBINATIONS in the examples below, with two columns X and Y where each column is populated with values 1 through 10 such that all combinations are present, i.e. 100 rows. Now, the SQL to find the row where X is 2 and Y is 8 would look like:

SELECT *
FROM   combinations
WHERE  x = 2
AND    y = 8

Another way of writing this is:

SELECT *
FROM   combinations
WHERE  (x,y) = ( (2,8) )

What we’ve done here is described x and y as a combination of values, i.e. (x,y), and compared that to the combination (2,8). Okay so why do this? One reason is that it’s possible to find multiple combinations of values using the IN operator, i.e.:

SELECT *
FROM   combinations
WHERE  (x,y) IN ( (2,8)
                , (5,3)
                , (6,6) )

This is a rather better way than writing something like:

SELECT *
FROM   combinations
WHERE  (x = 2 AND y = 8)
OR     (x = 5 AND y = 3)
OR     (x = 6 AND y = 6)

Furthermore, the combinations we’re looking for can come from a sub-query (assume the table SELECTED_COMBINATION has columns A and B):

SELECT *
FROM   combinations c
WHERE  (c.x,c.y) IN ( SELECT s.a, s.b
                      FROM   selected_combinations s )

Not surprisingly the NOT IN operator can be used too:

SELECT *
FROM   combinations
WHERE  (x,y) NOT IN ( (2,8)
                    , (5,3)
                    , (6,6) )

As always when using the NOT IN operator, be very careful of NULLs. The example below returns 88 rows and not 98 as some might expect as all rows where Y is 8 get excluded:

SELECT *
FROM   combinations
WHERE  (x,y) NOT IN ( (NULL,8)
                    , (5,3)
                    , (6,6) )

The following returns no rows at all:

SELECT *
FROM   combinations
WHERE  (x,y) NOT IN ( (NULL,NULL)
                    , (5,3)
                    , (6,6) )