Search code examples
sqloracleoptimizationsql-execution-plan

Is PARTITION RANGE ALL in your explain plan bad?

Here's my explain plan:

SELECT STATEMENT, GOAL = ALL_ROWS   244492 4525870 235345240
 SORT ORDER BY   244492 4525870 235345240
  **PARTITION RANGE ALL**   207633 4525870 235345240
   INDEX FAST FULL SCAN MCT MCT_PLANNED_CT_PK 207633 4525870 235345240

Just wondering if this is the best optimized plan for querying huge partitioned tables.

Using Oracle10g

Solution

  • PARTITION RANGE ALL just means that the predicates could not be used to perform any partition pruning. More info. Or, that the alternative (scanning the table blocks instead of using a fast full scan on the index) was estimated to be more expensive overall.

    If you can change the predicate to limit the affected rows to a small subset of the partitions, the database will be able to skip whole partitions when querying the table.