TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) DBA Tip 4) Oracle OpenWorld 2005 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the initial value of a sequence’s CURRVAL? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the August issue of Oracle Professional, Steven Feuerstein describes a framework for detecting NO_DATA_FOUND exceptions when fetching single rows of data through a function interface that improves robustness of code. James Koopmann describes a method for designing dynamic entities that will empower end users to make the changes they need while maintaining the integrity of the data model. And Dan Clamage describes how to develop a stored procedure-based Crystal Report to add greater flexibility and power to your reports. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) DBA Tip --------------------------------------------------------- We were recently working on a database upgrade project for a client moving them from Oracle 8i to 10g. One piece of the application ran on an NT server that connected to the database through ODBC and inserted and updated stock quote data into a table that was partitioned. The code was using an ODBC datatype called SQL_TIMESTAMP_STRUCT for the main column that was used in the where clause of the UPDATE statement. This was ok in Oracle 8i, but caused large performance issues in 10g. The code was changed to use SQL_DATE_STRUCT and the performance bottleneck went away. Below is an example using a timestamp variable for the start_date in the UPDATE in question showing that Oracle scans all the partitions. If the start_date is a date (test 2) - it works fine and only goes to one partition: Examples ======== 1. declare v_start_date timestamp; v_end_date quote.end_date%type; v_seq quote.seq%type; v_listing_id quote.listing_id%type; cursor v_cq is select start_date,end_date,listing_id, seq from skelly.t where end_date = '31-DEC-2999' and rownum < 400; begin open v_cq; fetch v_cq into v_start_date,v_end_date,v_listing_id, v_seq; while v_cq%found loop update QUOTE set end_date = v_end_date where listing_id = v_listing_id and end_date = '31-DEC-2999' and start_date = v_start_date and seq = v_seq; fetch v_cq into v_start_date,v_end_date,v_listing_id, v_seq; end loop; close v_cq; end; Partition pruning dump...... ---------------------------------- Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [0, 432] index = 0 current partition: part# = 0, subp# = 65535, abs# = 0 ============ 2. declare v_start_date date; v_end_date quote.end_date%type; v_seq quote.seq%type; v_listing_id quote.listing_id%type; cursor v_cq is select start_date,end_date,listing_id, seq from skelly.t where end_date = '31-DEC-2999' and rownum < 400; begin open v_cq; fetch v_cq into v_start_date,v_end_date,v_listing_id, v_seq; while v_cq%found loop update QUOTE set end_date = v_end_date where listing_id = v_listing_id and end_date = '31-DEC-2999' and start_date = v_start_date and seq = v_seq; fetch v_cq into v_start_date,v_end_date,v_listing_id, v_seq; end loop; close v_cq; end; Partition pruning dump...... --------------------------------------- Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [166, 166] index = 166 current partition: part# = 166, subp# = 65535, abs# = 166 As you can see, the second test went to the specific partition in question, while the first test went through all of them. Is this a bug in Oracle 10g or was the client just lucky in Oracle 8i? Regardless of the answer, the client is happy now! Thanks to Susan Kelly of The Globe and Mail newspaper in Toronto, who relentlessly pursued this performance issue and provided the test cases above. -------------------------------------------------------- 4) Oracle OpenWorld 2005 -------------------------------------------------------- Oracle OpenWorld 2005 will be held, once again, at the Moscone Center in San Francisco, September 17 – 22. Due to recent acquisitions, OpenWorld, PeopleSoft Connect, and Retek World conferences have merged into a single event. So, Oracle is ensuring that this year’s conference has content for its entire existing and newly acquired customers, including JD Edwards. Keynote Speakers include Oracle's Larry Ellison, HP's Mark Hurd, Intel's Paul Otellini, and more. If you register before August 5th, you’ll save $400, so act fast. For more information, visit: http://www.oracle.com/openworld/sanfrancisco/conference/index.html -------------------------------------------------------- 5) In the News -------------------------------------------------------- As expected, the acquisitions continue for Oracle. After last month’s acquisition of retail software maker ProfitLogic Inc., Oracle announced this week that it is buying a majority interest in India’s largest applications software company, i-flex solutions, a deal expected to be worth close to $900 million. The software maker specializes in corporate banking, consumer banking, investment banking, Internet banking, asset management, and investor services. The have provided products and services to 575 banks in 115 countries and fits well into Oracle’s strategic plans, Larry Ellison stated. Find out why Oracle’s interest in India might not be over by reading the following article: http://yahoo.businessweek.com/technology/content/aug2005/tc2005083_4679 _tc024.htm * * * * Apple Computer is launching a new mouse, called Mighty Mouse, which will have 4 sensors and a small scroll bar. For years, Apple has built added functionality into its operating system to support multi-button mice, but users have had to purchase the mice from other manufacturers, such as Logitech. The new mouse will retail for $49. Those of you who are fans of the Terrytoons cartoon avenger will recognize the Mighty Mouse name and Apple has received permission to use the name for this new product. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- When you first connect to Oracle, the value of CURRVAL is undefined. See below: DBDGAM1_GX>@dbs1_gx Connected. DBS1_GX> select tmp_seq.currval from dual; ERROR: ORA-08002: sequence TMP_SEQ.CURRVAL is not yet defined in this session It isn’t until you actually increment the sequence, that CURRVAL will be defined: DBS1_GX> select tmp_seq.nextval from dual; NEXTVAL ---------- 2931 DBS1_GX> select tmp_seq.currval from dual; CURRVAL ---------- 2931 Now, some of you may already know this behaviour. But, many of you probably don’t know that CURRVAL is session-specific. Meaning that once I incremented my sequence above, if another user is connected to the database and attempts to view the CURRVAL, they will get the same undefined error as I got at the beginning. More importantly, if that other user increments the sequence, then in my own session, if I attempt to view CURRVAL again, I will still see 2931 as the value because my session isn’t impacted by other sessions incrementing the sequence; except when I increment the sequence as I’ll do below: First I try to view the CURRVAL after another session incremented it, then I’ll increment it myself and see what happens: DBS1_GX> select tmp_seq.currval from dual; CURRVAL ---------- 2931 DBS1_GX> select tmp_seq.nextval from dual; NEXTVAL ---------- 2933 DBS1_GX> select tmp_seq.currval from dual; CURRVAL ---------- 2933 Note that the other user incremented the sequence to 2932, yet my session didn’t see it. So, CURRVAL is truly session-specific as you can see for yourself.