Partial Range Scan 5

Take advantage of “ROWNUM” for partial range scan

ROWNUM is a fake column which is usually used to limit the number of the records that returned by the query.
Please note that ROWNUM is not the sequence number of the record that is processed, but the sequence number of the record that is returned by the query. That’s to say, even if the SQL query has ROWNUM <=10 predicate, the actual records processed by the query is most likely more than 10.

Take advantage of “Inline View/Scalar Sub Query” for partial range scan

Include the data that must be processed via “Full Range Scan” inside one inline view, and this can make sure the other part of the SQL can be processed via “Partial Range Scan”. Otherwise, the whole SQL query would be processed via “Full Range Scan”.

SELECT a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM department a, employee b, salary c WHERE b.deptno = a.deptno AND c.empno = b.empno AND a.location = 'SEOUL' AND b.job = 'MANAGER' AND c.sal_ym = '200512'
ORDER BY a.dept_name, b.hire_date, c.sal_ym;

Since the SQL statement above has the ORDER BY clause, it seems the SQL statement can only be executed via “Full Range Scan”. But considering the data volume in the table department and employee are not very large, we can join these two tables first and then join the table salary. What’s more, in order not to sort by column sal_ym in the table salary, we can create one index on the columns (empno+sal_ym). This way, the SQL statement above can be rewritten as follows,

SELECT /*+ ORDERED USE_NL(x y)*/ a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM (SELECT a.dept_name, b.hire_date, b.empno, b.emp_name FROM dept a, employee b WHERE b.deptno = a.deptno AND a.location = 'SEOUL' AND b.job='MANAGER' ORDER BY a.dept_name, b.hire_date) x, salary y WHERE y.empno = x.empno AND y.sal_ym = '200512';

Another example,

SELECT a.product_cd, product_name, avg_stock FROM product a, ( SELECT product_cd, SUM(stock_qty) / (:b2 - :b1) avg_stock FROM prod_stock WHERE stock_date BETWEEN :b1 AND :b2 GROUP BY product_cd) b WHERE b.product_cd = a.product_cd AND a.category_cd = '20';

Can be rewritten as follows,

SELECT a.product_cd, product_name, (SELECT SUM(stock_qty) / (:b2 - :b1) FROM prod_stock b WHERE b.product_cd = a.product_cd AND b.stock_date BETWEEN :b1 AND :b2 ) avg_stock FROM product a WHERE category_cd = '20';

Take advantage of “Function” for partial range scan

Take a look at the following SQL statement…

SELECT y.cust_no, y.cust_name, x.bill_tot FROM ( SELECT a.cust_no, SUM(bill_amt) bill_tot FROM account a, charge b WHERE a.acct_no = b.acct_no AND b.bill_cd = ‘FEE’
AND b.bill_ym between :b1 and :b2 GROUP BY a.cust_no HAVING SUM(b.bill_amt) > 1000000) x, Customer y WHERE y.cust_no = x.cust_no AND y.cust_status = 'ARR' AND ROWNUM <= 30;

Though the SQL statement only needs to query the customer that has status with ‘ARR’, the inline view still needs to group all the customers. Obviously, this is not very efficient as the inline view performs much useless operation. What’s more, the SQL statement cannot return the first set of data until the inline view is completely processed.
To resolve this issue, we can take advantage of function as follows…

CREATE OR REPLACE FUNCTION cust_arr_fee_func ( v_custno IN varchar2, v_start_ym in varchar2, 
v_end_ym IN varchar2) RETURN number AS 
Ret_val number(14); 
BEGIN 
SELECT SUM(bill_amt) INTO ret_val FROM account a, 
charge b WHERE a.acct_no = b.acct_no AND a.cust_no = v_cust_no AND b.bill_cd = 'FEE' AND b.bill_ym BETWEEN v_start_ym AND v_end_ym;
RETURN ret_val;
END cust_arr_fee_func;

SELECT cust_no, cust_name, CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) 
FROM customer WHERE cust_status = 'ARR' AND CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) >= 1000000 
AND ROWNUM <= 30;

The SQL statement calls the function twice, and it can be rewritten by using of inline view…

SELECT cust_no, cust_name, bill_tot FROM ( SELECT ROWNUM, cust_no, cust_name, CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) bill_tot FROM customer WHERE cust_status = 'ARR') WHERE bill_tot >= 1000000 AND ROWNUM <= 30;

Please note that the inline view includes one fake column – ROWNUM, which is used to prevent the view merging.