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 /
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
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.
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 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
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.
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
what have done for insert failure?
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.
Here is the situtation:
P201301 TO_DATE(‘ 2013-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
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.
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:
FROM test1 PARTITION (P201301)
WHERE <date column> < <high value of Test2 partition prior to SYS_P1042>