Search notes:

Oracle: Partition pruning

When executing SQL statements involving partitioned tables, the optimizer tries to exclude partitions that need not be touched because, for example, the where clause makes it impossible to find some values in certain partitions.

This feature is referred to as partitioning pruning.

Identifying partition pruning in execution plans

When executing an SQL statement where partition pruning takes effect, the corresponding execution plan fills the columns PARTITION_START and PARTITION_STOP (for example in the plan_table).

For statements that are serially (not parallely) executed, pruning information is also found in the columns OPERATION and OPTIONS.

Partition pruning on DATE columns

Oracle's documentation points out that using a where clause on a date column of a partitioned table only executes partition pruning if

the table (or index) is created with the to_date() function and a 4-digit format mask and
to_date with a 2 or 4 digit mask is also used in the where clause.

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1788447902, '216.73.217.21', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/objects/tables/partitions/pruning/index(54): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

Oracle: Partition pruning

Identifying partition pruning in execution plans

Partition pruning on DATE columns

See also