Parsing Oracle data scanning Oracle SQL Optimization-Guided Local scanning (4)

Flexible application of ROWNUM virtual column Rownum is not a physical column actually exists in the table, but a virtual column that can be used in any SQL statement. If you can correctly understand the concept and features of Rownum, you can apply it to the Where condition. The pilot optimizer only processes the specified number of rows. Therefore, this is a part of the scope of local range scanning. However, if you do not understand the features of Rownum, you may not be able to output the expected results, nor can you run it in the expected data scan mode. Now we will discuss how to use Rownum to implement local range scanning. Www.2cto.com feature 1: ensure that the data range is large enough. See the following example statement: Select * from t where tno> 100 and rownum <= 10; this statement scans the data that meets the conditions and assigns the result record to the Rownum value, when Rownum exceeds 10, the query is stopped and the returned results are returned. This is an ideal local range scan, but if the value of tno> 100 is met, there are only less than 10 pieces of data, the execution engine may scan more data to meet the Rownum <= 10 condition. The worst case is to scan the data that meets all conditions and then return the actual record row, this is contrary to our original intention. The reason is that the data that meets the conditions is too narrow and the batch array containing the results cannot be filled as soon as possible. Therefore, if you want to use Rownum to scan a large data range, you must first ensure that the data range meeting the driving query conditions is large enough. It is better to exceed the number set by Rownum in the statement. Feature 2: The Rownum value is assigned before the internal storage operation is executed and stored together with the corresponding data row. This feature often affects the result set of the Rownum virtual column sorting (order by) or grouping (group by) query statement. See the following example statement: Select rownum, item_cd, catory_cd From product Where deptno like '000000' and qty> 0 and rownum <= 10 Order by item_cd; www.2cto.com the intention of executing this statement may be to sort all the data that meets the conditions and then output the first 10 records. However, this statement cannot achieve the original purpose. Before Order by is executed, Rownum has been assigned to the data rows that meet all the query conditions in Where and saved in the temporary tablespace, because Rownum <= 10 has been filtered before the sorting operation is executed, the final Order by operation only sorts 10 rows of data, therefore, it is impossible to output data in the real Order of Rownum, but the result after Order by is directly output. This is essentially because of the Rownum feature that allows data to be read from globally scanned data during sorting. This SQL statement must scan all data that meets the conditions in all ranges, store them in the internal temporary storage area, and then perform the sorting operation, the Rownum value is assigned before the internal storage operation is executed and stored together with the corresponding data row. Therefore, when the Rownum condition exists in the query condition, the sorting will not be sorted according to the overall data range that meets the condition, but will only be sorted according to the range limited by Rownum, therefore, the actual results are different from the expected results. The same problem occurs when Rownum is used for grouping, as shown in the following statement: Select deptno, sum (qty) from product Where deptno like '200' and rownum <= 10 Group by deptno; also, due to this feature of Rownum, only 10 rows of data will be grouped. So how can we solve this problem so that statements can be run according to the local range scan and data can be displayed according to the expected query results? The preceding statement can be rewritten using a dynamic view to achieve our goal, as shown in the following figure: Sort operation: Select rownum, item_cd, catory_cd From (select * from product where deptno like '000000' and qty> 0 Order by item_cd) Where rownum <= 10; Grouping operation: Select rownum, deptno, total From (Select deptno, sum (qty) total From product www.2cto.com Where deptno like '000000' Group by deptno) Where rownum <= 10; obviously, this is not a full local range data scan, because subqueries that constitute a dynamic view are executed by scanning all the range data, however, the statement is executed in a partial range scan. The performance is very dependent on the query performance of the subquery, which is actually a mandatory compromise. Therefore, pay special attention to it during use. Feature 3: Rownum exists in a logical set, that is, a select statement will have a corresponding Rownum. You can flexibly use this feature to output only a specific Rownum or a range of Rownum result sets. See the following example: Select t2.deptno, t2.totqty From (select rownum as rnum, t1.deptno, t1.totqty From (select deptno, sum (qty) as totqty From product www.2cto.com Where deptno like '000000' and rownum <= 120% Group by deptno) t1) t2 Where rnum <= 5 and rownum = 1; this statement outputs only 5th rows of data from the group statistical results of the internal subquery, mainly because each level of subquery (each Select statement) is used) the Rownum generated in has different functions and meanings. In subquery t1, Rownum indicates the Rownum that meets the query condition result set. In subquery t2, Rownum is named rnum by alias, indicating the Rownum of the final result after the group by operation; because other restrictions are not used, even if the row scan with rnum = 5 is found, it will not stop, but will continue to scan all data rows accepted from the subquery, until the end. Therefore, the rownum = 1 constraint is added to the query at the outermost layer, so that the scanning is immediately stopped after the data with rnum = 5 is found to avoid reading unnecessary data. There is also a very common application that uses Rownum, which is to implement paging query. In the process of daily application system development, paging query requirements are very common, you can use paging queries to distribute the query performance pressure on a wide range of data scans. See the following statement example: Select * from (Select rownum rn, deptno, totqty From (select deptno, sum (qty) as totqty From product www.2cto.com Where deptno like '100' Group by deptno) where rownum <= 50) t1 Where rn> = 1; this statement can be output from the grouping statistics results of internal subqueries, from 1st to 50th results records. Therefore, Rownum can be used to control the number and range of records output by scanning. From javacoffe's column