In the last post I noted how it’s possible to run SQL queries and DML operations against specific table partitions using syntax shown below:
SELECT * FROM part_test PARTITION (part_test_p5)
The above query references the partition by name, i.e. PART_TEST_P5. Oracle support an alternate syntax that permits the partition to be referenced by a data value, i.e.:
SELECT * FROM part_test PARTITION FOR (45)
The value of 45 in the above query is simply a value that Oracle can use to resolve to a specific partition. Any value will do. The value used can involve an expression but the use of a bind variables will throw an exception:
SQL> VARIABLE part_val NUMBER SQL> EXEC :part_val := 45 PL/SQL procedure successfully completed. SQL> SELECT * 2 FROM part_test PARTITION FOR (:part_val) 3 / FROM part_test PARTITION FOR (:part_val) * ERROR at line 2: ORA-14763: Unable to resolve FOR VALUES clause to a partition number
The PARTITION FOR syntax works for SELECT, INSERT, UPDATE, DELETE and even TRUNCATE.