SQL & PL/SQL
OUTER-JOIN is a very handy feature of SQL. But its value at solving certain classes of SQL query problems aside, it is also one of the most error prone Oracle SQL extensions we can put to use. Even advanced developers can make these mistakes. So let us discuss the error prone nature of this feature, and how to fix it.
PL/SQL is a great language. It's relatively simple to learn, is well integrated with the Oracle database, and can often be the most efficient way to perform complex or large scale database operations. In fact PL/SQL is so useful, it's difficult to believe that its origin is SQL*Forms -- and that PL/SQL was once was an optional cost add-on to the database. Working with Oracle over the past few decades has come a long way, and PL/SQL had evolved into a mature, robust and highly functional database language.
Top-n Query is one of the more advanced SQL problems. How does one retrieve N first (or least) rows from a record set? For example, how does one find the top five highest-paid employees in a given department?
Everyone knows the basic features of sql*plus, but one underused feature that can make your scripts an order of magnitude more useful is the ability to store and reuse values, including values read from the database, in variables. This lets you use user-defined and database values not just in subsequent queries, but also in calls to other scripts and SQL*Plus's other functionality.
One of the primary tests for DBMS reliability is what's known as the ACID test. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. This is not true for non-ACID compliant systems. Non-ACID-compliant systems are vulnerable to data inconsistency, and generally aren't taken seriously for any application where data integrity is important. Now, in 10gR2, Oracle offers us the option to break its ACID compliance.
There are always a few topics in regards to writing SQL that always seem to come up more often than others. Querying data with case insensitivity is one of those topics. And Oracle has addressed this issue in many of their release. But in Oracle 10gR2 they have reached new levels. This article takes a dive into case insensitivity and how it is now handled in 10gR2. For the better!
DML error logging is a new feature for 10gR2. Have you ever tried to update 30 million records, only to have the update fail after twenty minutes because one record in 30 million fails a check constraint? Or, how about an insert-as-select that fails on row 999 of 1000 because one column value is too large? With DML error logging, adding one clause to your insert statement would cause the 999 correct records to be inserted successfully, and the one bad record to be written out to a table for you to resolve.
If you're looking to tune an SQL statement or a batch job, a common way to find out what happened during the execution of the SQL is to run an extended SQL trace and examine the wait events. But what happens if you are using parallel execution, and all your trace file contains is the parallel execution wait events that are generally considered idle events? Your trace file shows how long your query took to run, and the work involved in controlling the PQ slaves, but the real details of what took up all the execution time are actually to be found in the corresponding PQ slave trace files in the BDUMP directory.
Much of the OLAP reporting feature embedded in Oracle SQL is ignored. People turn to expensive OLAP reporting tools in the market - even for simple reporting needs. This article outlines some of the common OLAP reporting needs and shows how to meet them by using the enhanced aggregation features of Oracle SQL.
The article is divided in two sections. The first introduces the GROUP BY extensions of SQL, and the second uses them to generate some typical reports. A section at the end introduces the common OLAP terminologies.
The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will expore these AWR tables and expose their secrets.