More Table Partition Queries and DML

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.

Advertisement