<\/p>\n
ROWNUM is a fake column which is usually used to limit the number of the records that returned by the query.
\nPlease 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\u2019s to say, even if the SQL query has ROWNUM <=10 predicate, the actual records processed by the query is most likely more than 10.<\/p>\n
Include the data that must be processed via \u201cFull Range Scan\u201d inside one inline view, and this can make sure the other part of the SQL can be processed via \u201cPartial Range Scan\u201d. Otherwise, the whole SQL query would be processed via \u201cFull Range Scan\u201d.<\/p>\n
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'\nORDER BY a.dept_name, b.hire_date, c.sal_ym;<\/code><\/pre>\nSince the SQL statement above has the ORDER BY clause, it seems the SQL statement can only be executed via \u201cFull Range Scan\u201d. 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\u2019s 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,<\/p>\n
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';<\/code><\/pre>\nAnother example, <\/p>\n
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';<\/code><\/pre>\nCan be rewritten as follows, <\/p>\n
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';<\/code><\/pre>\nTake advantage of \u201cFunction\u201d for partial range scan<\/h3>\n
Take a look at the following SQL statement\u2026 <\/p>\n
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 = \u2018FEE\u2019\nAND 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;<\/code><\/pre>\nThough the SQL statement only needs to query the customer that has status with \u2018ARR\u2019, 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\u2019s more, the SQL statement cannot return the first set of data until the inline view is completely processed.
\nTo resolve this issue, we can take advantage of function as follows\u2026 <\/p>\n
CREATE OR REPLACE FUNCTION cust_arr_fee_func ( v_custno IN varchar2, v_start_ym in varchar2, \nv_end_ym IN varchar2) RETURN number AS \nRet_val number(14); \nBEGIN \nSELECT SUM(bill_amt) INTO ret_val FROM account a, \ncharge 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;\nRETURN ret_val;\nEND cust_arr_fee_func;\n\nSELECT cust_no, cust_name, CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) \nFROM customer WHERE cust_status = 'ARR' AND CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) >= 1000000 \nAND ROWNUM <= 30;<\/code><\/pre>\nThe SQL statement calls the function twice, and it can be rewritten by using of inline view\u2026<\/p>\n
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;<\/code><\/pre>\nPlease note that the inline view includes one fake column \u2013 ROWNUM, which is used to prevent the view merging.<\/p>\n","protected":false},"excerpt":{"rendered":"
Partial Range Scan 5<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1332","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1332"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1332\/revisions"}],"predecessor-version":[{"id":1333,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1332\/revisions\/1333"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1332"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}