Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.1 January 10, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) This Issue's URLs 3) Did you know... 4) Answer to the Pop Quiz 5) Your feedback --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you recursively display parent-child relationships in a SELECT statement? --------------------------------------------------------- 2) THIS ISSUE'S URLs --------------------------------------------------------- For a review of Oracle's accomplishments in 2000 and Oracle's plans for 2001, check out http://www.oracle.com/corporate/index.html?year2000.html. The following Oracle sites contain articles and white papers on Oracle products: http://technet.oracle.com/products/ http://metalink.oracle.com/ http://www.oracle.com/ http://www.olab.com/ http://www.oracle.com/oramag/ http://www.oracle.com/openworld/ In addition, more articles can be found at the following Web sites: http://www.ixora.com.au/ http://govt.oracle.com/~tkyte/ http://www.oracledba.co.uk/ http://www.orafaq.com/ http://www.quest.com/whitepapers http://www.dbasupport.com --------------------------------------------------------- 3) DID YOU KNOW... --------------------------------------------------------- * that Oracle recently released its Wireless Developer's Kit? It includes code samples, tips, and best practices for developing wireless applications using the Oracle9i Application Server Wireless edition. It enables businesses to access enterprise data and applications, such as customer relationship management, via wireless phones, two-way pagers, and handhelds. It also supports location-based service. For more information, go to http://www.oracle.com. * that the Oracle Development Tools User Group (ODTUG) Conference will be held in June 2001 in San Diego? If you're interested in attending, go to http://www.odtug.com/2001_conference.htm. If you wish to submit papers at the conference, go to http://www.odtug.com/2001_conference_abstracts.htm for more details. --------------------------------------------------------- 4) ANSWER TO THE POP QUIZ --------------------------------------------------------- You can use the CONNECT BY clause. Many applications contain data records that are organized in a hierarchical fashion. For instance, the employee table contains a list of employee records, and the manager for each employee. The manager record is in fact another employee and reports to another manager within the company. The only employee in the organization without a manager is the CEO---the boss! The chain of command within the company can be represented in the following table: SQL> select * from employees; EMPLOYEE MANAGER -------------------- -------------------- John Mary Mary Sue Sue Note that in the employees table, Mary is John's boss, and she reports to Sue, and Sue reports to no one. As you can see, the SELECT statement doesn't clearly represent the hierarchical relationship that exists within the employees table. Oracle provides the CONNECT BY clause to augment the SELECT statement. I'll illustrate its use with an example: SQL> SELECT employee, manager FROM employees CONNECT BY PRIOR manager = employee; EMPLOYEE MANAGER -------------------- -------------------- John Mary Mary Sue Sue Mary Sue Sue Sue 6 rows selected. In this example, we've displayed the manager of a given employee and continue to do so until an employee doesn't have a manager. Note that the SELECT statement doesn't have a WHERE clause, implying that every row in the table (and there are three rows) will be displayed. In addition, the CONNECT BY directs Oracle to recursively retrieve rows. Here's the CONNECT BY syntax: CONNECT BY PRIOR manager = employee In particular, for each row retrieved, say, employee John, the CONNECT BY clause instructs Oracle to look for an employee in the same table who happens to be the manager of John (PRIOR manager). So in the case of John, Mary is John's boss and will be displayed as the second row. Now that Mary is retrieved as the second row, Oracle displays Sue because she's Mary's boss; however, Sue doesn't report to anyone, so this recursive branch for John is terminated. In essence, we're able to display all of John's managers---all the way to the very top. Once John is processed, Oracle will process the next row in the table, Mary, and then Sue. As a result, a total of six rows are displayed. Although Oracle is able to recursively traverse the table to display the managers, it's very difficult for us to discern the actual reporting hierarchy of the employees. Let's improve this query: SQL> column indent format a40 SQL> select lpad(' ', level) || employee indent, level from employees connect by prior manager = employee SQL> / INDENT LEVEL ---------------------------------------- ---------- John 1 Mary 2 Sue 3 Mary 1 Sue 2 Sue 1 6 rows selected. A pseudo-column, called level, is provided by Oracle. We can make use of its value to illustrate the hierarchy of information. Note that often we're only interested in selective branches of the tree; hence, we can use the START WITH clause to pinpoint the desired branch, as shown here: SQL> select lpad(' ', level) || employee indent, level from employees connect by prior manager = employee start with employee = 'John' SQL> / INDENT LEVEL ---------------------------------------- ---------- John 1 Mary 2 Sue 3 In this example, we only wanted to show John's managers. Note that we can further filter data with the WHERE clause; however, the WHERE clause is applied AFTER the CONNECT BY, meaning that the tree structure is first created, and then the WHERE clause is applied. SQL> select lpad(' ', level) || employee indent, level from employees where employee = 'Mary' connect by prior manager = employee start with employee = 'John' SQL> / INDENT LEVEL ---------------------------------------- ---------- Mary 2 Note that we first looked for all of John's managers, and then we checked to see whether Mary was a manager of John using the WHERE clause. Now that we're able to use the CONNECT BY clause to display managers, let's see how we can traverse the tree in the opposite direction. SQL> select lpad(' ', level) || employee indent, level from employees connect by prior employee = manager SQL> / INDENT LEVEL ---------------------------------------- ---------- John 1 Mary 1 John 2 Sue 1 Mary 2 John 3 6 rows selected. In the preceding example, for each retrieved record from the employees table, we searched for a new record whose manager happened to be the retrieved row. John didn't have anyone working for him, but Mary was John's boss, so a level 2 record was displayed; Sue was Mary's boss, and Mary was John's boss, and the entire chain of command was displayed. Once again, the START WITH clause can be used to find all individuals working for Sue, as shown here: SQL> select lpad(' ', level) || employee indent, level from employees connect by prior employee = manager start with employee = 'Sue' SQL> / INDENT LEVEL ---------------------------------------- ---------- Sue 1 Mary 2 John 3 As I mentioned at the beginning of this section, the CONNECT BY clause can be applied to any table in which a recursive data relationship exists. In fact, the SQL tuning utility table, PLAN_TABLE, is also organized in the same manner. It contains the SQL execution plan, illustrating how Oracle goes about executing a SQL statement. Here's how you can present the plan in a user- friendly format: select lpad(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name goodplan from plan_table start with id = 0 connect by prior id = parent_id; Let's try it out. First, run the EXPLAIN PLAN for the query. SQL> explain plan for 2 select lpad(' ', level) || employee indent, level 3 from employees 4 connect by prior employee = manager 5 start with employee = 'Sue'; Explained. Next, run the query to report information back from the plan table. SQL> select lpad(' ',2*(level-1)) || operation || ' ' || options 2 || ' ' || object_name plan 3 from plan_table 4 start with id = 0 5 connect by prior id = parent_id; PLAN ------------------------------------------------------------ SELECT STATEMENT CONNECT BY TABLE ACCESS FULL EMPLOYEES TABLE ACCESS BY USER ROWID EMPLOYEES TABLE ACCESS FULL EMPLOYEES With indentation, we can determine the order in which an individual SQL component gets processed. ************************************************************** Quest Software - Solutions for SQL Developers Find out what over 100,000 Oracle developers have already discovered…TOAD® rocks! With an intuitive user interface and optional integration with debugging, tuning and DBA modules, TOAD gives users everything they need to get the job done - and get on with life. Download a trial today: http://www.quest.com/oracle_prof_010901.asp ************************************************************* --------------------------------------------------------- 5) YOUR FEEDBACK --------------------------------------------------------- As a new year has just started (and a belated happy holidays to you all!), I'd like to get your feedback on the eNewsletter so that I'll know what to emphasize in the coming year. I'd like to know what it is that you like about this eNewsletter and what you'd like to see in future issues. Here are some of my ideas: 1) Focus on the industry buzz: discussing Oracle product development news, database industry initiatives, etc. 2) Focus on SQL tips: greater emphasis on SELECT, INSERT, DELETE, UPDATE, and other SQL commands. 3) Focus on DBA activities: discussing database initialization parameters, database tuning, SQL tips, etc. 4) Focus on application development: examining PL/SQL programming, Oracle supplied packages, etc. 5) Focus on specific Oracle products: Oracle iAS, Oracle Designer, etc. 6) Invite guest "speakers" to contribute articles to the eNewsletter. Note that this isn't going to be a scientific survey because I might not be able to read every single response, but I'll definitely try my best to incorporate your feedback as much as I can. Thanks in advance for your input. --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2001 http://www.pinnaclepublishing.com All rights reserved.