What is the difference between dynamic and static partition pruning

From the documentation:

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning.

* Static pruning occurs at compile-time, with the information about the partitions accessed being known beforehand.
* Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand.

A sample scenario for static pruning is a SQL statement containing a WHERE condition with a constant literal on the partition key column:

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = 1001;

There are some exceptions to this rule, listed here, for example if the partition pruning occurs using the results of a sub-query.

An exampel of Oracle using dynamic pruning is when operators or functions are used in the predicat (WHERE-condition). Also, statements that use bind variables against partition columns result in dynamic pruning:

var b1 number;
exec :B1 := 1001;

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = :B1;