Partial Range Scan 4

Replace SORT operation by (index) access pathReplace SORT operation by (index) access path

In order to eliminate the “SORT” operation from the SQL execution plan, we can add the columns used in the ORDER BY clause into the index. This way, we can take advantage of this index to avoid the Full Range Scan operation.

SELECT ord_dept, ordqty * 1000 FROM order WHERE ord_date like '2005%' ORDER BY ord_dept DESC

In the SQL statement above, the condition used to filter (drive) the data set is using the column “ord_date” while the column used in the ORDER BY clause is the column “ord_dept”. If the data set returned by applying the condition “orde_date like ‘2005%’” is large, the Full Range Scan will respond slowly. However, if there is also one index on the column ord_dept, we can rewrite the SQL statement as follows,

SELECT /*+ INDEX_DESC (a ord_dept_index)*/ * FROM order a WHERE a.ord_date like ‘2005%’ AND ord_dept > ' ';

This way, we not only remove the ORDER BY clause (by using the hint INDEX_DESC) which make the Partial Range Scan possible, but also make the ord_dept the driving column and the column ord_date be the “filter” column. According the principle of the Partial Range Scan, if the filtering condition causes large data volume, the execution speed will be fast.

Use index scan only for partial range scanUse index scan only for partial range scan

If all the columns used by the SQL statement are included in the index, then the optimizer can only access the index to get the data. There is no need to scan the table in this case.
This is very efficient as the I/O will be reduced.
As a result, to instruct the optimizer to choose this index scan, we need to think carefully for those candidate columns that can be included in the index.

MAX and MIN functionsMAX and MIN functions

Since MAX and MIN are aggregate functions, it seems that if the SQL statement has these function used then the Partial Range Scan is impossible.
However, in the new optimizer, there is a special process operation for the MAX/MIN function which uses the Partial Range Scan which make the MAX/MIN have good response time.
For example, the index pk_order is based on the column (deptno, seq)…

SELECT MAX(seq) + 1 FROM order WHERE deptno = '1234';

EXECUTION PLAN

SELECT STATEMENT 
  SORT (AGGREGATE) 
    FIRST ROW 
       INDEX (RANGE SCAN (MIN/MAX)) OF ‘pk_order’ (UNIQUE)

Please note the “FIRST ROW” and “RANGE SCAN(MIN/MAX) in the execution plan. They make the SQL engine doesn’t need to wait until all the deptno ‘1234’ are scanned before returning the result.
The SQL statement above is almost executed by the optimizer as the SQL statement below…

SELECT /*+ INDEX_DESC(order pk_order) */ NVL(MAX(SEQ), 0) + 1 FROM order WHERE dept_no = '1234' AND ROWNUM =1;

Please note the hint “INDEX_DESC” and ROWNUM are used in the SQL statement “explicitly tell” the optimizer to choose partial range scan.