SQL & PL/SQL
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.
This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.
As the discipline of software development evolves, software products grow in size and the code grows in complexity, the need for a set of guidelines and standards becomes increasingly evident. In this article I will enumerate some of the industry best practices that the experts have proposed and emphasized, in the context of PL/SQL programming.
Many Oracle professional do not realize the important of having good statistics for the Oracle cost-based SQL optimizer (CBO). The CBO has received a "bum-rap" from some Oracle professionals who wonder why the CBO is generating sub-optimal execution plans for their SQL statements.