SQL & PL/SQL
Recently, I have been helping a company in redesigning their schema, and one issue was that they stored hieracal trees in varchar2(4000) column, one full branch per row.
Data was if this kind:
/Oracle/8/1/5/Microsoft /Oracle/8/1/5/VMS /Oracle/8/1/5/0/1/VMS
Their main problem was that searching in this was really ugly, as they had to full table/index scan a lot, when searching using like '%%'
I came up with this solution to convert this into a small table that can be queried using connect by instead:
CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )
PL/SQL is an excellent language for Oracle. Integrated with the database, highly useful extensions to SQL, extremely powerful when exploiting Oracle Objects, and in spite of all this, it is still way easy to learn. But like any procedural programming language, one can get lazy with it, tending to accept the first solution arrived at. Looping constructs in particular seem to be used as crutches rather than necessary components of a solution. Bad looping causing performance issues is a problem that liters the PL/SQL landscape. But it is easy to spot and fix.
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.