With Oracle 12c, you can limit the number of rows returned, not only by specifying the number of rows, but also by specifying the percent of total number of rows in the table.
Before we dive into examples, let's think about that for a second. With Oracle 12c, you can manage your result set based on information the database already knows about your data. The database already makes decisions on how to best retrieve data (hello optimizer!), but now developers and DBAs can being to take direct advantage of this information. Gnarly.
The syntax for row-limiting clauses by present looks a lot like row-limiting clause by row.
Before we dive into examples, let's think about that for a second. With Oracle 12c, you can manage your result set based on information the database already knows about your data. The database already makes decisions on how to best retrieve data (hello optimizer!), but now developers and DBAs can being to take direct advantage of this information. Gnarly.
The syntax for row-limiting clauses by present looks a lot like row-limiting clause by row.
This query will return the top 1% of rows in the PSOPRDEFN table, as ordered by the LASTSIGNONDTTM.
You can also offset by a particular number of rows, and then return a percentage, like so:
At this time you can NOT offset by a percentage. This makes pagination for percentages a bit more challenging, as you must know the previous number of rows returned, before fetching the next percentage of data.
Examples above only display ROWS ONLY parameter, but WITH TIES is also available with row-limiting by percent.
NEXT WEEK: Monday, Row-limiting clause with effective-dated rows. Tuesday, we put all of it together, sorta.
Comments
Post a Comment