Databases Reference
In-Depth Information
When we use the
RANK()
function, in step 8, the execution plan changes, as shown in the
following screenshot:
We also have a
SORT
operation, namely
WINDOW
SORT
PUSHED
RANK
, in this query, after
the full table scan of the
CUSTOMERS
table.
If we use the
DENSE_RANK()
function, the execution plan is the same, as when we have
used the
RANK()
function, the only difference is that the
FILTER
predicate is applied.
There's more...
Please note that execution time for the last two queries—those using analytical functions—is
longer than the time required to execute the query in step 3 and step 7. This is not noticed in
the example because the
CUSTOMERS
table is very small.
If we execute the following script on the
SALES
table (about 1 million rows), you will observe
that the second query executes in about 175 percent of the time needed by the first query:
SET TIMING ON
SELECT * FROM (
SELECT * FROM sh.SALES ORDER BY AMOUNT_SOLD DESC
) WHERE ROWNUM < 11;
