Execution Plan image:
COUNT is executed when the RowNum pseudo-column is used without specifying a maximum value for RowNum. COUNT receives rows from its child operations and increments the RowNum counter. If a limiting counter is used on the RowNum pseudo-column, then the COUNT STOPKEY operation is used instead of COUNT.
select Name, City, State, RowNum
from COMPANY
where City > 'Roanoke'
order by Zip;
The query shown in the preceding listing selects rows from the COMPANY table. Each row will have the original row number returned.
SORT ORDER BY
COUNT
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY
The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the WHERE clause condition (where City > 'Roanoke'). The ROWIDs from the City index scan are used to query the COMPANY table for the Name and State column values. For each row returned, the counter is incremented. Because of the use of the index, the rows that are returned are the "lowest" city names that are greater than the value 'Roanoke'. The rows are returned from the COMPANY$CITY index in ascending order of the City column’s value. The RowNum pseudo-column will then be calculated and put into the row. The SORT ORDER BY operation will order the rows by Zip, as requested in the ORDER BY clause.
Note: The RowNum values are assigned before the ordering takes place.
Execution Plan image:
Aggregation operation.
COUNT STOPKEY condition is generated. No More Rows receives rows from the previous operation and increments a counter; if the counter reaches a given threshold, a COUNT STOPKEY pseudo-column is used with a limiting counter.
select Name, City, State
from COMPANY
where City > 'Roanoke'
and Rownum <= 100;
The query shown in the preceding listing selects from the COMPANY table based on two criteria in its WHERE clause. The first criteria can use the COMPANY$CITY index to obtain ROWIDs for the rows to return. The second criteria limits the number of returned records to 100. Since the Name and State columns are required by the query, the COMPANY table will have to be accessed; the City values are already available via the index searches.
COUNT STOPKEY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY
The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the City value condition. The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. For each row returned, the counter is incremented so that only 100 rows are returned. Because of the use of the index, the rows that are returned are the 100 "lowest" City names that are greater than the value 'Roanoke', and City will sort the records.
The CREATE AS SELECT operation creates a table using selected information from another table.
Operation: CUBE SCAN
The CUBE SCAN operation retrieves cube organized data with inner join operations.
Option: PARTIAL OUTER
The CUBE SCAN PARTIAL OUTER operation retrieves data from at least one dimension of a cube with outer join operations while retrieving the rest of the dimensions of a cube with inner join operations.
Option: OUTER
The CUBE SCAN OUTER operation retrieves cube organized data with outer join operations.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center