All articles have been reprinted with the written consent of their respective authors.

Hierarchical Query Can Contain Table Joint in 9i
By Victor Slootsky, BAE Systems 

If you want run a report where employees are placed in the hierarchical order and also see the department name for each employee, you need to run the query below: 

SELECT level, e.ename, d.dname 
FROM emp e, dept d
WHERE e.deptno = d.deptno
START WITH e.ename='WARD'
CONNECT BY PRIOR e.mgr=e.empno

Unfortunately in Oracle 8i and below you will get a message: ORA-01437 cannot have join with CONNECT BY.  This is a limitation of Oracle 8i.

You might utilize some workaround using views or temporary tables but the easiest way is to upgrade your database to Oracle 9i. Then you will get the desired result:

LEVEL      ENAME      DNAME
---------- ---------- -------------
1          WARD       SALES
2          BLAKE      SALES
3          KING       ACCOUNTING

The interesting thing is that Oracle 9i Documentation still does not allow a table joint in the hierarchical query even though the Oracle 9i database engine runs this query without any problems.
 

Microstate Response-time Performance Profiling, Part 2
Danisment Gazi Unal 

Getting optimum performance for mission-critical Oracle systems is an extremely complex task. For senior Oracle DBAs, measuring internal services waits within an Oracle database is a critical aspect of advanced response-time profiling. The accuracy of measurements is an important factor in all types of research. However, there are no 100-percent accurate measurements in Nature; there are always some distortions in the measurements. How can a method be accepted as an accurate method? The answer depends on the impact of measurement errors.

The goal of this article is to adapt microstate accounting, which is a more accurate measurement technique provided by OS (operating system) vendors, to database management systems (DBMSs). Part 1 of the article outlined the current measurement techniques available and the errors that can occur with them. Part 2 discusses how to correct these errors and introduces the theoretical approach of Microstate Response-time Performance Profiling.

Click Here for the article.
  

Just the Stats, Ma'am Part 2
By Robert Catterall, CheckFree Corp

A DB2 statistics report can be the key to better performance.

In my previous column, I wrote about DB2 for OS/390 and z/OS monitor statistics detail reports. I ended the column after covering data set open/close activity; in this column, I pick up the story with a quick look at some of the remaining report data blocks. 

Recall that my example is a long-format statistics report generated via IBM's DB2 Performance Monitor product (DB2PM). The same (or very similar) information can generally be found in statistics detail reports produced by other mainframe DB2 performance monitoring tools. You can view these data fields via the online displays provided by most DB2 monitor products if you'd rather not look at a piece of paper (personally, I like paper).

Click here for the article.  
  

Managing Exceptional Behavior, Part 2
By Steven Feuerstein

In part 1, I offered suggestions for both an overall exception handling strategy and best practices for raising exceptions in your programs. In this article, I complete my treatment of error handling in PL/SQL, with a look at how best to handle exceptions once they have been raised. 

For handling exceptions, there are two main considerations: 

1. Deciding which errors should be handled and which can go unhandled in any given block of code. 

2. Constructing reusable code elements that allow the handling (and logging) of errors in consistent, useful ways. 

I touch on both of these topics in the following best-practice recommendations. 

Click Here for the article.  

This article was previously published by Oracle Magazine.

Announcing a new, three-day seminar by Steven Feuerstein on the PL/SQL language this Fall:

Visit http://www.minmaxplsql.com for all the details...

MIN-MAX PL/SQL ... on November 11-13 or December 9-11, you can learn how to:

• MIN-imize bugs! 
• MIN-imize maintenance! 
• MAX-imize productivity! 
• MAX-imize quality of programming life!

MIN-MAX PL/SQL is a best practices and optimization seminar unlike any you have attended before. Steven has carefully chosen from Oracle's many new PL/SQL features and his numerous recommended best practices to create a course that will radically change (for the better!) the way you design and implement PL/SQL-based applications. 

You can get lots more information (including a download for your manager that explains the instant ROI on this seminar) and register for MIN-MAX PL/SQL by visiting the Blast Off PL/SQL site: www.minmaxplsql.com. 

Seating is limited, so visit www.minmaxplsql.com and register right away!

PS - Special discounts of 20% are available for customers of Quest Software and companies that register 3 or more students (across all the dates)!

Cluster That Index!
By Christoffer Hedgate,  sql.nu

One topic that is sometimes discussed in SQL Server communities is whether or not you should always have clustered indexes on your tables. Andy Warren discussed this briefly in one of his articles in the Worst Practices-series (Not Using Primary Keys and Clustered Indexes), here I will give my view on this matter. I will show you why I think you should always have clustered indexes on your tables, and hopefully you might learn something new about clustered indexes as well. 

Click here for the article.  

This article was previously published by SQL Server Central.
 

Work Breakdown Structure Tom Mochal, www.tenstep.com

The best way to build a workplan for your project is to reuse the workplan of a similar project that was completed successfully. However, if you do not have a similar workplan to use as a model, you may need to build the workplan from scratch. The best way to do this is through the Work Breakdown Structure (WBS). The general process is as follows:

Click here for tips and techniques on work breakdown structures.
 

The Best Project Management Book in the World!* (*In the author's humble opinion.)

Tom Mochal, who has contributed our monthly project management column for almost two years, recently published a new book called Lessons in Project Management. This book is unlike any other project management book on the market today. Most project management books are basically textbooks. They are dry to begin with and don't focus on the practical advice that most people need to run their projects. Lessons in Project Management was written for the millions of people who work on typical projects on an ongoing basis. The format showcases lessons for project managers and team members who face common problems on common projects. (Just like you.) Click here for more information on the book, including an excerpt and preliminary feedback from readers. (http://www.TenStep.com/98.3.5LessonsinPMBook.htm)

Free Webcasts
For detailed information on these free webcasts, please visit our News and Events page.

• Solving the Top Five Pains for Oracle E-Business Site Administrators
  Tuesday, November 11, 2003
  9:00 a.m. – 10:00 a.m. PST, 17:00 – 18:00 GMT
     
• Successful Exchange Migrations the First Time
  joint webcast with Callisma
  Wednesday, November 12, 2003
  8:00 a.m. – 9:00 a.m. PST, 11:00 a.m. – 12:00 p.m. EST
   
• Understanding Oracle Database Workload
  Wednesday, November 12, 2003
  11:00 a.m. PST, 1:00 p.m. CST,  2:00 p.m. EST (19:00 GMT)
     
• DB2 OS/390 SQL Tuning Tips & Techniques
  Wednesday, November 12, 2003
  9:00 a.m. PST, 11:00 a.m. CST,  12:00 p.m. EST (19:00 GMT)
     

  Visit the Webcast Archives

Interactive Crossword Puzzle: "Fairy Tales"
 

1 Down - Oracle, for Example

Test your knowledge with the Pipeline Newsletter's Monthly Crossword Puzzle.  Click here to Play!

We love getting white papers, tips, articles, and code examples/archives from our readers around the world.  Send your submission to newsletter@quest-pipelines.com.  If your article is published, it will be mailed to over 28,000 subscribers.

Our mission at Quest Software - RevealNet Labs is to anticipate the daily responsibilities and challenges faced by database professionals.  Our products help thousands of people solve problems and implement solutions every day.  This newsletter is designed to help facilitate the sharing of information among database professionals.  

About the Newsletter

This newsletter is distributed to Quest Software - RevealNet Labs customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please visit http://qlist01.quest.com/UnsubMailingList/

Subscribe a Friend!

Do you know someone who would like to receive the Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.

Past Issues of the Pipeline Newsletter

October, 2003 September, 2003 August, 2003 July, 2003

June, 2003 May, 2003 April, 2003 March, 2003

February, 2003 January, 2003 December, 2002 November, 2002