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
Advertisements

4 thoughts on “Table Partition Queries and DML

    • Hi Harish,

      The INSERT failure in the examples is an attempt to insert a row into a partition where the data does not fit the definition for that partition. In my example I attempted to insert value 35 into partition part_test_p3, which holds values 21 through 30. Oracle rejects such an insert.

      The example is a bit of hypothetical one and is included for completeness. I’ve never had the need to specify the partition for an INSERT statement. However, it is useful to know how Oracle would react in this scenario should it ever occur.

      Mark

      • HI Mark,

        Here is the situtation:

        Source table:

        Test1:

        P201301 TO_DATE(‘ 2013-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

        Target table:

        Test2:

        SYS_P1042 TO_DATE(‘ 2013-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

        When i run below query it is error out:

        insert into Test2 partition(SYS_P1042) select * from Test PARTITION(P201301);

        ORA-14401: inserted partition key is outside specified partition

        Can you help me on this.

        Regards,
        Harish MC

      • Hi Harish,

        I would check the data in Test1 partition P201301 and the lower bound for Test2 partition SYS_P1042. I assume that Test2 has other partitions with a lower boundary then 1 Feb 2013. For example, let’s say Test2 has a partition with a high value of 1 Jan 2013. This would mean that Test2 SYS_P1042 will store values greater than or equal to 1 Jan 2013 and less than 1 Feb 2013. If Test1 partition P201301 has values less than 1 Jan 2013 then you’ll get ORA-14401.

        You should be able to check for data in Test1 like this with a query similar to:

        SELECT *
        FROM test1 PARTITION (P201301)
        WHERE <date column> < <high value of Test2 partition prior to SYS_P1042>

        Regards

        Mark

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s