Limiting the percentage of ordered rows retrieved in Oracle 12c

Lczinkoc's Blog

In a previous post I compared two solutions for the same ranking
problem. Now I would like to compare two solutions for the same
percentages report.
The goal of the query: Let's see the first 5% rows of the total
rows according to the salary in descending way.
Let's consider the solution in Oracle 12c, where we can use the
new FETCH {FIRST|NEXT}  <pct> PERCENT ROWS clause:
One of the possible traditional solution uses
the cumulative distribution (CUME_DIST) analytic function.
Let's consider the execution plan for that query which 
uses the new FETCH {FIRST|NEXT} <pct> PERCENT ROWS clause:

Now we examine the execution plan of the query that uses the CUME_DIST analytic function:

We can observe that BOTH queries use the SAME EXECUTION plan,
but with different number of consistent gets (1551 versus 7!). 
Other metrics are same. 
I created a greater table:I executed the following queries:

Of course, I…

View original post 48 more words

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.