Rewriting complex SQL for high performance

I am now about one year into writing my next book “Oracle SQL Tuning: The Definitive Reference” and I’ve discovered some fascinating new principles of SQL tuning, counterintuitive approaches that can make your SQL run faster than ever before.

Some guru's rightfully say that you should always solve a problem using SQL without using PL/SQL unless absolutely necessary, but there is a limit to that technology.  Once a SQL query has subqueries nested within subqueries, subqueries in the select clause (a scalar subquery), subqueries in the from clause (an in-line view), the SQL becomes difficult to read, hard to maintain, and difficult to optimize.  In these cases, we can use some powerful Oracle tools to divide and conquer complex SQL statements.

Because SQL is a declarative language we can formulate equivalent queries with numerous techniques.  SQL is a “state space” query language where you simply request the desired rows, there are many ways to write the same query.

Back in the 1970’s, programming competitions were not concerned with who could solve a problem the fastest but with who could write the solution that ran the most efficiently.  Today that has changed, and SQL developers are charged with getting the right rows back as quickly as possible, regardless of the internal execution plan or the time required to execute the query!

In the examples below we will see a simple two-way table query that can be written in these forms, each returning identical results, but with vastly different performance:

Complex SQL behaves in the same fashion, and you can re-write SQL to improve performance.  With the introduction of advanced analytical SQL functions such as the PIVOT operator in 11g, SQL coders can now solve complex programming problems without using PL/SQL.

SQL was never designed to perform complex process logic, and the performance of complex SQL will often be far worse than when the SQL is decomposed into manageable simple queries.  Complex queries can be re-written in many ways, all with the same results and different performance:

Follow the link below to take a closer look at these powerful SQL re-writing techniques.

http://www.dba-oracle.com/t_sql_tuning_restructure_with_clause_temporary_intermediate_tables.htm

To pre-order my SQL tuning book, follow the link below where you can pre-order it for 30%-off by buying it directly from the publisher:

http://www.rampant-books.com/book_1001_oracle_sql_tuning.htm


*****************************************
Visualize your Oracle database trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle.  Ion quickly isolate the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:

http://www.ion-dba.com

*****************************************

Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf

*****************************************

There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.
If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.
Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list:

http://www.dba-oracle.com/jobs.htm