Oracle Pro Tips, Trends & Technology eXTRA Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.20 December 4, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Books 3) SQL Tips 4) Oracle News 5) Correction 6) Answer to the Pop Quiz -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you determine which stored programs -- e.g., database package, triggers, etc. -- need to be recompiled? --------------------------------------------------------- 2) BOOKS --------------------------------------------------------- Steven Feuerstein, a renowned Oracle expert and regular contributor to Pinnacle's Oracle Professional journal, has recently released a book on PL/SQL. Titled "Oracle PL/SQL Programming, 3rd Edition," the book is published by O'Reilly. Along with Bill Pribyl, Feuerstein provides unique insight into the use of PL/SQL for developing enterprise Oracle applications. The book examines the core PL/SQL functionality. Further, it provides extensive coverage of the PL/SQL runtime architecture, and PL/SQL interface to Java. Find out more at the following link: http://www.oreilly.com/catalog/oraclep3/ A sample chapter of the book can be found at: http://www.oreilly.com/catalog/oraclep3/chapter/index.html --------------------------------------------------------- 3) SQL TIPS --------------------------------------------------------- My colleague Andrew Okimi (mailto:aokimi@procaseconsulting.com) has an interesting insight to share on the use of UNION ALL. Basically, under certain situations it may be better to use UNION ALL than to use OR. Below are two tables, one storing company data and the other storing department data for each company: SQL> desc comp Name Null? Type --------------------------- -------- ------------------------ C_ID NOT NULL NUMBER C_NAME VARCHAR2(20) C_TYPE VARCHAR2(20) SQL> desc dept Name Null? Type --------------------------- -------- ------------------------ D_ID NOT NULL NUMBER D_NAME VARCHAR2(20) D_TYPE VARCHAR2(20) C_ID NUMBER The following shows indexes created on the above tables: OWNER INDEX_NAME TABLESPACE UNIQUENES POS COLUMN_NAME ------- ---------------- --------------- --------- ---- ------------- DUMMY CTYPE_IDX IND_TS NONUNIQUE 1 C_TYPE DUMMY SYS_C0033804 IND_TS UNIQUE 1 C_ID DUMMY CID_IDX IND_TS NONUNIQUE 1 C_ID DUMMY DTYPE_IDX IND_TS NONUNIQUE 1 D_TYPE DUMMY SYS_C0033805 IND_TS UNIQUE 1 D_ID Let's say we're developing a procedure to return some data. The procedure may take an input parameter, say department type. Sometimes the input parameter may contain a value; other times it may not. The query inside the procedure may look something like this: select * from comp c, dept d where d.c_id = c.c_id and (d.d_type = :dtype or :dtype is null); Note that we need to handle the case where the department type may be null as well as when it contains a value. The execution plan may look like the following: Query Plan --------------------------------------- SELECT STATEMENT Cost = NESTED LOOPS TABLE ACCESS FULL DEPT TABLE ACCESS BY INDEX ROWID COMP INDEX UNIQUE SCAN SYS_C0033804 Another way of writing the query is to use the UNION ALL option, as shown here: select * from comp c, dept d where d.c_id = c.c_id and d.d_type = :dtype and :dtype is not null UNION ALL select * from comp c, dept d where d.c_id = c.c_id and :dtype is null; The corresponding execution plan is shown here: Query Plan ---------------------------------------------------- SELECT STATEMENT Cost = UNION-ALL FILTER NESTED LOOPS TABLE ACCESS BY INDEX ROWID DEPT INDEX RANGE SCAN DTYPE_IDX TABLE ACCESS BY INDEX ROWID COMP INDEX UNIQUE SCAN SYS_C0033804 FILTER NESTED LOOPS TABLE ACCESS FULL DEPT TABLE ACCESS BY INDEX ROWID COMP INDEX UNIQUE SCAN SYS_C0033804 Although the plan appears longer, the interesting fact is that only half the plan will be executed. In other words, if the parameter is null, the bottom half of the plan will be executed. In this case, it is identical to the one shown earlier; hence, there is no gain in performance. On the other hand, if the parameter does contain a value, then you use the top plan, which is more efficient than before. You may wonder -- why not just do this with an IF-THEN- ELSE statement in PL/SQL? You could definitely do that, but sometimes it's much cleaner to have a single statement with simpler program control flow. --------------------------------------------------------- 4) ORACLE NEWS --------------------------------------------------------- If you're a supporter of Oracle, you may be encouraged to know that Oracle might have turned the corner, and brighter days are ahead. An article published in BusinessWeek talks about how Oracle has remained strong despite the technology downturn and intense competition from IBM. The article traced Oracle's downturn to mid-2000, when its newly released ERP application was fraught with bugs. Coupled with the bust in the technology industry, Oracle's share prices and revenues dropped. Then it chronicled Oracle's repositioning after losing the market-share leadership to IBM. Oracle continued to maintain profitability by trimming costs, and automating its operations with its own enterprise software applications such as the Internet File System. Going forward, there seems to be quite a bit of upside for Oracle. This is due to the apparent warm reception of Oracle's new products, e.g. collaboration suite by the industry, as well as the number of potential customers needing Oracle upgrades. Find out more at the following link: http://yahoo.businessweek.com/magazine/content/02_47/b3809100.htm --------------------------------------------------------- 5) CORRECTION --------------------------------------------------------- In my last issue I incorrectly called the Oracle event "Oracle OpenWorld 2003." It is actually called "OracleWorld." Also in case it wasn't clearly stated, Larry Ellison did not deliver the keynote address in person. He delivered his speech via satellite from New Zealand. --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- Use the dba_objects (or all_objects or user_objects) view. All objects requiring compilation will have a status of 'INVALID'. You can identify these objects with the following query: SQL> select * from dba_objecs where status = 'INVALID'; --------------------------------------------------------- Well, that's it for this issue. 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) 2002 http://www.pinnaclepublishing.com All rights reserved.