TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Steven Feuerstein’s Oracle PL/SQL Spotlight Series 3) Oracle Professional 4) Oracle Tip 5) TOAD User Groups 6) In the News 7) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What does the COALESCE function do? --------------------------------------------------------- 2) Steven Feuerstein’s Oracle PL/SQL Spotlight Series --------------------------------------------------------- Participate in three dynamic Webinars, offering in-depth training with world-renowned Oracle PL/SQL author Steven Feuerstein, on key features of the PL/SQL language. Webinar #1: “Spotlight on Collections” June 8, 2005: 8:00 am-10:00 am (CT), 2:00 pm-4:00 pm (CT) Webinar #2: “Spotlight on Native Dynamic SQL” June 23, 2005: 8:00 am-10:00 am (CT), 2:00 pm-4:00 pm (CT) Webinar #3: “Spotlight on Exception Handling” July 13, 2005: 8:00 am-10:00 am (CT), 2:00 pm-4:00 pm (CT) Register today! Visit www.pinpub.com or call 800-493-4867 x4404 to find out more. --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the May issue of Oracle Professional, Steven Feuerstein reviews some of the commonly used data dictionary views (including some very useful views added in Oracle9i and Oracle10g), and offers a variety of examples showing how the views can be applied to answer important questions about your code base. Darryl Hurley continues his discussion on Oracle Streams and the capture component and looks at the propagation components of Streams. And Al Hetzel introduces us to the DBMS_METADATA package, first introduced in Oracle 9i, and how we can use it to get the DDL for any object in our database. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 4) Oracle Tip --------------------------------------------------------- This Month’s tip comes from my colleague, Andrew Okimi, who can be reached directly at aokimi@procaseconsulting.com: Some routine maintenance of a client's database had some unexpected repercussions. The production database was taken out of service while some tables and their associated indexes were moved to new tablespaces. This maintenance was done using the following commands: ALTER TABLE t MOVE TABLESPACE new_data_tspace PARALLEL; ALTER INDEX t_idx REBUILD TABLESPACE new_index_tspace PARALLEL; The PARALLEL clause allows Oracle to utilize parallel slaves that can drastically shrink the amount of time these operations require, especially for large tables. However, when the database was brought back on-line, there were performance problems relating to the objects that had just been moved. The database was taken back out of production while the issue was investigated. It turned out that the performance issue was due to changes in the access path that Oracle used for queries involving the moved objects. There were actually two separate issues caused by the above MOVE and REBUILD statements. a) Although the intent of using the PARALLEL clause was to make the MOVE and REBUILD operations much faster, using PARALLEL on the ALTER INDEX REBUILD command also had the (unintended) effect of changing the default degree of parallelism for the index. The default degree of parallelism can be found in the DEGREE column of data dictionary views such as USER_TABLES and USER_INDEXES. Although parallelism in queries can be a good thing, for some queries it is not efficient. Plus, although one might want to split up the job of rebuilding an index, that doesn't mean that the query behaviour of the index should be changed. To reset the degree of parallelism of an index, use ALTER INDEX t_idx PARALLEL 1; b) The second issue was that the ALTER TABLE MOVE command actually deleted statistics on the underlying table. Recall that Oracle's ANALYZE command is used to gather statistics on tables and indexes that the Oracle optimizer considers when choosing an access path for a particular query. In order to get the same performance prior to the maintenance operations, the following statements were executed (assuming an 8-way processor box is being used): -- first do the maintenance operations ALTER TABLE t MOVE TABLESPACE new_data_tspace PARALLEL 8; ALTER INDEX t_idx REBUILD TABLESPACE new_index_tspace PARALLEL 8; -- recompute statistics on the table -- note that this also computes statistics on the index too! -- this is a good idea anyways, since rebuilding the index -- has likely changed the characteristic statistics -- of the index ANALYZE TABLE t COMPUTE STATISTICS; -- finally reset the degree of parallelism back to 1 ALTER TABLE t PARALLEL 1; ALTER index t_idx PARALLEL 1; Note that the ALTER TABLE MOVE statement does NOT affect statistics on any of the indexes of the MOVE'd table. Also note that the ALTER INDEX REBUILD statement DOES preserve statistics on the index. These behaviours have been observed in various Oracle versions from 8i to 9i to 10g! -------------------------------------------------------- 5) TOAD User Groups -------------------------------------------------------- The Quest Software 2005 Toad User Groups are coming to an area near you. Connect with the Toad Community at this free, half-day local user group. Whether you are a developer, DBA, beginning Toad user or a master, you'll get the latest tips, techniques and trends to help you work more efficiently. Guest speaker Steven Feuerstein will discuss how you can improve the maintainability and performance of your application by eliminating common mistakes in PL/SQL programming. And, you'll be among the first to see the new features and functionality in Toad for Oracle 8.5! Check out the dates for the one that’s near you: Austin* May 10th Dallas May 11th Atlanta May 12th Chicago May 17th Columbus* May 18th Toronto May 19th New York May 20th Sacramento May 24th Los Angeles May 25th San Jose May 26th For more information, visit: http://www.quest.com/events/list.aspx?searchoff=true&prod=1 -------------------------------------------------------- 6) In the News -------------------------------------------------------- Siebel Systems Inc. (SEBL) is in the news with takeover rumours driving the price of its shares up and down like a rollercoaster. It began with rumours that Oracle was renewing its interest in the company in late April. Then, more recently, financier, Carl Icahn was mentioned in the rumour mill as being interested in the flailing company. Speculation that Siebel was a takeover target began last month when it announced sagging sales of its CRM software. An industry expert stated that he anticipates Siebel will eventually be purchased, but doesn’t expect it to be by Mr. Icahn. For more details, read the article at: http://biz.yahoo.com/deal/050504/icahnsizingupsiebel.html?.v=2 * * * * Oracle has recently opened up registration for OpenWorld San Francisco 2005 to be held September 17-22 at Moscone Centre in San Francisco. This year, the Oracle OpenWorld, PeopleSoft Connect and Retek World conferences have merged into one, integrated event. It will feature six days of intensive educational programming for business, IT professionals and partners and will showcase Oracle’s full product line. Registration is accessible online at: http://www.oracle.com/openworld/ * * * * The International Oracle Users Group (IOUG), a leading Oracle database and applications user group; the Oracle Applications Users Group (OAUG), the world’s largest user knowledgebase for Oracle Applications users; and the Quest International Users Group, an independent, not- for-profit association serving PeopleSoft and JD Edwards users; recently announced an agreement for a collaborative annual user group conference in 2006. The new event will be called Collaborate 06: Technology and Applications Forum for the Oracle Community and will take place April 23-27, 2006 at the Gaylord Opryland Resort in Nashville, Tenn. Contact each respective group if you would like to get more details about this new event. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The COALESCE function is an extension to the NVL function and was first introduced in Oracle 9i. It returns the first non-null expression in a list. To show you an example of its use, let’s assume that you have a person table and you need to retrieve a contact number for each person. Your table looks something like: >desc employee EMPLOYEE_ID NUMBER CELL_PHONE VARCHAR2(15) HOME_PHONE VARCHAR2(15) WORK_PHONE VARCHAR2(15) PAGER_PHONE VARCHAR2(15) So, you write a query like this: Select employee_id , coalesce(work_phone ,home_phone ,cell_phone ,pager_phone) contact_no from employee; Thus, Oracle will return the first non-null value of those 4 phone numbers in the list. The ability to get a similar result pre-Oracle 9i can be done by nesting 4 NVL statements, but the result isn’t pretty.