Toad World Pipeline

July 2008 Issue

PIPELINE is the monthly newsletter of Toad World - your trusted source for Toad and database knowledge for Oracle, DB2 and MySQL professionals.

Upcoming Events

Gain valuable insight through webcasts, seminars, trade shows and user group meetings held around the world.

Sept. 21-25, U.S.
Oracle Open World
Oct. 21-22, U.S.
Speak-Tech: Best of Oracle PL/SQL Seminar
Oct. 29-30, U.S.
Oracle PL/SQL Programming 2008 Conference

Click here for a full list of worldwide events

Toad® is Turning 10 – Join the Party!

It's hard to believe that our favorite, green amphibian has hit the double digits!

Thank you to the one million database professionals who have catapulted Toad to the top of the list for Oracle development and administration solutions. We look forward to 10 more years of helping you build, manage and maintain high-performing database applications.

Please join us on Toad World and celebrate by signing Toad's birthday card, downloading a new birthday-theme wallpaper or taking a brief three-question survey.

Do you have a favorite Toad story you want to share? We're listening! Submit your story for a chance to win one of five customized Toad iPod Nanos!

Oracle: Make Oracle Work Smarter, Not Harder

New Oracle Tuning Series by Guy Harrison

Getting started with SQL tuning involves mastering two fundamentals: finding SQL that warrants tuning, and working out how to make those SQLs work more efficiently. Tuning SQL reduces the demands that the application makes on the database. Every downstream activity - contention, memory efficiency and IO load - will be improved as a result. For this reason, tuning SQL is an essential pre-requisite for an efficient and scalable database server. >Read the series

DB2 Webcast: Performance Automation in DB2 LUW

by Jim Wankowski

This presentation will discuss the new autonomic tuning features introduced in DB2 LUW v9.5. It will provide guidelines and tips for achieving optimal performance using the new automation features. This presentation touches on many aspects of DB2 performance that span tuning opportunities for databases, and the overall system. Key topics covered include understanding DB2 memory structures and their performance implications, monitoring methods available for DB2 LUW and learning about space management's affect on performance >View the webcast

MySQL: Query Performance Optimization

Excerpt from High Performance MySQL by Baron Schwartz

Query optimization, index optimization, and schema optimization go hand in hand. As you gain experience writing queries in MySQL, you will come to understand how to design schemas to support efficient queries. Similarly, what you learn about optimal schema design will influence the kinds of queries you write. This chapter begins with general query design considerations—the things you should consider first when a query isn't performing well. We then dig much deeper into query optimization and server internals. We show you how to find out how MySQL executes a particular query, and you'll learn how to change the query execution plan. Finally, we look at some places MySQL doesn't optimize queries well and explore query optimization patterns that help MySQL execute queries more efficiently. >Read full chapter

Software Development Tips & Techniques: Design Your Solution for Reuse

by Tom Mochal

An environment and culture that promotes reuse can offer tremendous value to your company. Most developers think of reuse in terms of code reuse. In fact, that is usually the logical place to start. The major benefit to reusing code is that applications will be faster and less expensive to build. There should be no question that applications can be assembled from reusable objects faster than they can be built from scratch, so faster development time is a major benefit. >Read full article

Test Your PL/SQL Knowledge

What will happen when you run the following block of code?

DECLARE
   value_error EXCEPTION;

   TYPE employees_tt IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_employees   employees_tt;
BEGIN
   SELECT * BULK COLLECT INTO l_employees
     FROM employees WHERE 1 = 2;

   FOR indx IN l_employees.FIRST .. l_employees.LAST
   LOOP
      DBMS_OUTPUT.put_line (l_employees (indx).first_name);
   END LOOP;
EXCEPTION WHEN VALUE_ERROR THEN
   DBMS_OUTPUT.put_line ('Exception trapped!');
END;

An unhandled VALUE_ERROR exception.
An unhandled NO_DATA_FOUND exception.
One line of output displayed on the screen: "Exception trapped!"
A compilation error; you will not be able to run the block at all.

Please send your responses along with your name, city and country, to Puzzler@toadworld.com by July 20, 2008. We will randomly select four winners who submit the correct answer, and each winner will receive a Toad World t-shirt!

June's Puzzler Answer:

c. This is the normal behavior of Oracle: readers never block writers and writers never block readers.

As for the others:

(a) You can use autonomous transactions to avoid the mutating table error in a trigger when you are only querying data. You must keep in mind, however, that your trigger cannot see any uncommitted changes to the table.

(b) This is a classic usage of autonomous transactions. You commit your insert into the log from within an AT procedure and then even if you rollback later, you don't lose the log entry.

(d) Generally, if you call your own function from within a query, you are not allowed to execute any DML operations, such as an insert. But if that function is an autonomous transaction, then you can perform such an insert – so just pass the ROWID as an argument and then you can log that queried row!

Winners:

David Alappat from Mumbai, India
Klaus-Dieter Doell from Ingelheim, Germany
Anne Bella Korah from Minnesota, USA
Paul Rylah from Coventry, UK

Click here to review June's puzzler.

Do you have a paper or article that you'd like to share? Maybe a favorite utility or snippet of code? Have you found an ingenious way to use a feature in Toad? Send them to us at Feedback@toadworld.com.

Do you know someone who would like to receive the Toad World PIPELINE Newsletter? If so, click here!

You are receiving this e-mail because you are a Pipelines or Toad World Community user, attended an event, downloaded trial software or registered for technical white papers from Quest Software, Inc. To be unsubscribed from all future mailings, including event announcements, please go to http://www.quest.com/unsubscribe.

© 2008 Quest Software Incorporated. ALL RIGHTS RESERVED.
Quest Software and its products are trademarks and registered trademarks of Quest Software, Inc. in the U.S.A.
and/or other countries. All other trademarks and registered trademarks are property of their respective owners.
View Quest Software's Privacy Policy