Hi. I'm Scott

Sorry, nothing really on Oracle 12c here. But happy Holidays, eh!

The row-limiting clause in Oracle 12c is available for use in a variety of ways and applications. I've discussed two of them in this blog ( CTAS with Row-Limiting Clause and Row-Limiting Clause with Effective Dated Rows ).  With Oracle DataPump, you can use the Row-Limiting Clause by specifying it as part of a subquery data filter. See the example below:  DataPump still continues to have the SAMPLE data_filter, but samples taken this way are applied to block level samples, not row level samples.  From Oracle's DBMS_DATAPUMP doc :  For Export jobs, specifies a percentage for sampling the data blocks to be moved. This filter allows subsets of large tables to be extracted for testing purposes. If you need a true subset of rows exported from a table, use the row-limiting clause with a data filter.

The following is another example of how row-limiting clause can be applied. Row limiting clauses can be any select statements, this includes CREATE TABLE AS SELECT statements. In the following example, we are able to create a new table in our PeopleSoft database by selecting the top 10% of rows by EMPLID. You may recall from previous posts that CTAS operations in Oracle 12c include statistics. This is true for operations which include the Row-Limiting clauses as well. As you can see, we have a new table, with a brand new set of statistics to go along with it. 

I have the extreme pleasure to work with several PeopleSoft applications. Keen readers of this blog series may have noticed this already; plus 10 Internet points if you did. PeopleSoft makes create use of effective dated rows. (note to self, write a blog post on managing effective dated rows some day).  When querying a table with effective dates, we usually end up with a query like so:   In this case, we are selecting the most recent row according to the maximum effective date and the maximum effective sequence. One might expect that the explain plan for this could be terrible, but it's actually not bad. We indexed properly. Actually, this is a pretty good explain plan, so good in-fact that I wasn't sure how Oracle would improve on this, but I wouldn't have got this far if there wasn't some good pay off at the end. We can re-write this SQL to use the row-limiting clause, and order by to get the same result, like so: At the very least, this looks cle...

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. 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 r...

For several versions now, other database platforms, such as MySQL and Microsoft SQL Server have been able to select the first x  number of rows; Oracle has not, until 12c. Over the next several days, and several posts, I'm going to discuss implementation and use cases based on this particular feature. Before Oracle 12c, row limits had to be done like this:  This works, mostly, but there were some challenges, such as plan management, data set unpredictability, and generally, it's just confusing to look at. I'm not afraid to say, this seems 'hack-ish'. Let's not even talk about pagination.  Now with 12c, we can replace nested SELECT statements and rownum with this instead:  Same basic premise, return only the first three rows, based on the ORDER BY sorting. Pretty simple actually.  And if you want to page through the results? We can do that too:  In this query, we skip the first row, and return the second row only. If you are thin...

Did you know Oracle has it's own YouTube channel? Well, it has several actually with topics ranging from Oracle's various racing platforms to Oracle RDBMS. On July 16, 2013, Oracle's Tom Kyte did his own speech called 'Top 12 Features of Oracle 12c". If you don't want to hear it from me, listen to Tom.