Sorry, nothing really on Oracle 12c here. But happy Holidays, eh!
Posts
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 rows 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 thinking
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.
Oracle 11g introduced the invisible indexes feature. Oracle 12c now introduces DBAs and developers to use of invisible columns. To create an invisible column, define a table as you normally would, and add the INVISIBLE flag, like so: In this case, the HOME_PHONE column is invisible. But what does that mean? Can you insert into the invisible column? Yes! What happens if you try to SELECT from the table? It's not there. What happens if I try to specifically select the invisible column? Well, now it's visible. WTF? So what good is this slightly invisible column? Invisible columns are NOT a security measure. They are a means of allowing developers a means to support activities where data can be stored in the database without obvious access to the data. What? No, seriously, this is a real thing. And Oracle has a feature which uses this functionality (of course). Temporal Validity . Check it out, it's pretty sweet, if Information Life Cycles are you
Starting with Oracle 10gR2, a nightly maintenance job is included by default to collect statistics on the data in your database. While there have been significant improvements to the algorithms used to calculate those statistics since it's initial release, these operations have all been single threaded. Now with Oracle 12c, DBAs can enable concurrent statistics collections. Different than Parallel This is different than parallel collection of statistics. Parallelism allowed for multiple threads to be used to collect statistics for a single database object or partition. Concurrent statistics collection allows for multiple jobs to run against a set of objects. Concurrent stats and parallel stats work together. The Math, with no concurrency: total threads = 1 * parallelism The Math, with concurrency: total threads = # of concurrent stats jobs * parallelism This is a potentially dangerous situation. Adjusting parallelism and concurrency to find the correct balance
Archive logging during import operations can be disabled. By disabling archive logging, we accept the trade-off of recovery with reduced resource contention on the database during data loads and index rebuilds on import. Disabling archive logging is especially handy for those DBA performing table or schema refreshes on a regular basis in non-production environments where recovery is less of a concern. Disabling archive logs can be performed on import operations invoked by command-line and parameter file. To disable archive logs on import, specify the following parameter: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y The screenshot below shows how this may be using during an import and remap to new schema. DBMS_DATAPUMP allows for additional granularity by accepting options to disable archive logs at the index or table level specifically. NOTE : This parameter is ignored for create table and index operations if the database or tablespace is in FORCE LOGGING mod
With the introduction of the LOGTIME parameter, DataPump operations will now report report timestamps from the system. LOGTIME parameter accepts four different values: NONE : (default) No timestamp information reported; classic 11g behavior. STATUS : Timestamp information only reported to STDOUT LOGFILE : Timestamp information only reported to specified log file. ALL : Timestamp information reported to both STDOUT and log file. Timestamp information can be included in DataPump operations using the DBMS_DATAPUMP package as well. The SET_PARMETER procedure can set the value of the LOGTIME parameter with the following syntax: Editor note: When I started this series, twitter friendly URL titles weren't part of the consideration. This is the same series, but better titles. Thanks
DataPump: Export Views as Tables Switching from RMAN, DataPump provides a new set of features that allow for increased abilities to export, transform and import data. To that end, Oracle Data Pump allows users to export a view as a table. This feature can be utilized whether data pump is accessed via command line, parameter file, or using the DBMS_DATAPUMP package. Command-line and parameter file options are the same (obviously). In the screenshot above, we are exporting two tables from a PeopleSoft database using the TABLES parameter. We are also exporting a view using the VIEWS_AS_TABLES parameter. When executed with these parameters, we see the following output in the log file and standard output: and Along with the data, exported views also include constraints and grants. Invoking this feature using the DBMS_DATAPUMP package is done like so: DBMS_DATAPUMP.METADATA_FILTER(h1,' VIEWS_AS_TABLES ' , ' in ( '' PSOPRDEFN_V
RMAN: SQL commands Description: This is one of those 'duh' features that makes so much sense that Oracle decided to implement four version after the introduction of RMAN. Previous to 12c, RMAN would execute SQL statements like so: NOTE: the SELECT statement is executed, but no results are returned to the user. While this is useful for scripting DDL statements. Now, with Oracle 12c, we get results like so: Voila! Results. Use Cases: Verifying that object recovery is successful. Scripting additional changes to be implemented immediately upon recovery. Enabling Data Guard commands Any shell scripts which execute RMAN commands, and then exits RMAN to connect to SQLPlus can be immediately simplified to perform these actions in one context. NOTE: RMAN !=SQLPlus. Formatting options are not available here. RMAN is providing better SQL Client functionality, but nothing nearly as robust as SQLPlus.
RMAN: SYSBACKUP Role Oracle has released several roles specifically for administers to perform specific actions. With the release of Oracle 12c, there is a new role specifically for managing recovery operations. the SYSBACKUP role allows named credentials to be used to restore partitions, tables views and so on, without necessarily needing to connect to with the SYSDBA privilege. Simply grant the role to the user, like you normally would and then that user can connect with named credentials. The SYSBACKUP role includes the following privileges by default: Select any transaction Unlimited tablespace Resumable Create any cluster Drop tablespace Alter session Audit any Create any table Create any dictionary Alter system Alter database Select any dictionary Alter tablespace SELECT_CATALOG_ROLE NOTE: no object grants are included in this privilege. Backup Operator does not necessarily need access to the data, only to recover data a specific points in time.