Partial Range Scan

The partial range scan usage rule

If we can change the Full Range Scan to Partial Range Scan sometimes, the SQL execution performance will most likely be improved greatly. However, not all the Full Range Scan can be converted to Partial Range Scan.
If the SQL execution plan has “SORT” operations, like SORT(UNIQUE), SORT(JOIN), SORT(AGGREGATE), SORT(ORDER BY), SORT(GROUP BY), etc, we can deems the optimizer doesn’t choose Partial Range Scan, instead it chooses the Full Range Scan operation.
Besides, if the SQL statements have set operations, like UNION, MINUS, INTERSECT, then the SQL statements cannot be executed via Partial Range Scan as the set operation will sort all the data (SORT (UNIQUE)) to remove the duplicated records. But UNION ALL can be executed via Partial Range Scan.

The requirements for Partial Range Scan

Generally, if the SQL statement has ORDER BY clause, the SQL statement cannot be executed via Partial Range Scan. However, if the column in the ORDER BY clause is indexed and the index is used for the driving index, then the SQL statement can be executed via Partial Range Scan.

SELECT ord_date, ordqty * 1000 FROM order WHERE ord_date like '200512%' ORDER BY ord_date;

If the column ord_date is indexed, the optimizer can ignore the ORDER BY clause and then the SQL statement can be executed via Partial Range Scan.
As a result, not all SQL statements that have ORDER BY clause cannot be executed via the Partial Range Scan. Only when the SORT operation comes up in the SQL execution plans that the Partial Range Scan cannot be
applied.

Partial Range Scan in different optimizer mode

Generally, the SQL statement in FIRST_ROWS will be executed via Partial Range Scan and in ALL_ROWS will be executed via Full Range Scan. If want to instruct the optimizer to choose Partial Range Scan, we can use some hints, like INDEX or FIRST_ROWS (n). In general, set the optimizer mode to “FIRST_ROWS” in OLTP system.