Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.20 October 4, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle News 3) Did You Know... --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- My fellow Managing Partner at Procase, Lev Moltyaner (mailto:lmoltyaner@ProcaseConsulting.com), sent us an SQL quiz. I'm sharing it with you. Introduction ============ Here is a simple and common business problem. It can be solved in several different ways. If you are interested, please submit a solution. As always, the solution should be efficient, structured, easy to maintain, and well- documented. Let's see who designs the best algorithms. Please limit the time you spend on this to one hour or so. Problem ======= We have three source tables that store data about a person (t1, t2, t3). Each table has a person id (pid). Each table has a start date and end date. The start date is entered by the user, while the end date is derived by a trigger such as (start date of next record -- 1) or (a high date in the future). Assume this trigger exists and works to produce the source tables as you see them below. The high date is Jun 23, 2004, in my example. This means that in each table the timeline is continuous, and there are no gaps or overlaps in dates. However, the earliest record for each person in each table could be on the same date or on different dates. The primary key of each table is (pid, start date). Each table has one or more mutually exclusive columns which all need to be in the final table. All three tables also have a change-by column (cby), to indicate who changed the source table. Source Tables ============= create table t1 (pid number, sd date, ed date, x number, cby varchar2(10)); create table t2 (pid number, sd date, ed date, y varchar2(1), cby varchar2(10)); create table t3 (pid number, sd date, ed date, z number, cby varchar2(10)); alter table t1 add constraint t1pk primary key (pid,sd); alter table t2 add constraint t2pk primary key (pid,sd); alter table t3 add constraint t3pk primary key (pid,sd); Objective ========= Produce a derived table that merges the data from the three tables into one: create table d (pid number, sd date, ed date , x number, y varchar2(1), z number , cby varchar2(10)); * There should be a record for each period created by a change in any of the three tables. * The table should only contain periods for which there is data in ALL three tables. This means that if one source table has no records for a person, the person will not appear in the derived table. Sample Data =========== select * from t1; PID SD ED X CBY ---------- --------- --------- ---------- ---------- 1 28-SEP-01 08-OCT-01 12 ann 1 09-OCT-01 23-JUN-04 20 joe 2 28-SEP-01 03-OCT-01 12 ann 2 04-OCT-01 23-JUN-04 20 joe select * from t2; PID SD ED Y CBY ---------- --------- --------- - ---------- 1 27-SEP-01 03-OCT-01 a frank 1 04-OCT-01 06-OCT-01 b mike 1 07-OCT-01 23-JUN-04 c joe 2 28-SEP-01 03-OCT-01 a frank 2 04-OCT-01 06-OCT-01 b mike 2 07-OCT-01 23-JUN-04 c joe select * from t3; PID SD ED Z CBY ---------- --------- --------- ---------- ---------- 1 01-OCT-01 23-JUN-04 10 frank 2 28-SEP-01 03-OCT-01 10 lucy 2 04-OCT-01 23-JUN-04 30 dan Derived Table Output ==================== select * from d; PID SD ED X Y Z CBY ---------- --------- --------- ---------- - ---------- ---------- 1 01-OCT-01 03-OCT-01 12 a 10 frank 1 04-OCT-01 06-OCT-01 12 b 10 mike 1 07-OCT-01 08-OCT-01 12 c 10 joe 1 09-OCT-01 23-JUN-04 20 c 10 joe 2 28-SEP-01 03-OCT-01 12 a 10 ann 2 04-OCT-01 06-OCT-01 20 b 30 joe 2 07-OCT-01 23-JUN-04 20 c 30 joe Inserts for Sample Data (For sysdate = Sep 28, 2000) ==================================================== -- data for pid=1 insert into t1 values (1, trunc(sysdate), trunc(sysdate)+10, 12, 'ann'); insert into t1 values (1, trunc(sysdate)+11, trunc(sysdate)+999, 20, 'joe'); insert into t2 values (1, trunc(sysdate)-1, trunc(sysdate)+5, 'a', 'frank'); insert into t2 values (1, trunc(sysdate)+6, trunc(sysdate)+8, 'b', 'mike'); insert into t2 values (1, trunc(sysdate)+9, trunc(sysdate)+999, 'c', 'joe'); insert into t3 values (1, trunc(sysdate)+3, trunc(sysdate)+999, 10, 'frank'); -- data for pid=2 insert into t1 values (2, trunc(sysdate), trunc(sysdate)+5, 12, 'ann'); insert into t1 values (2, trunc(sysdate)+6, trunc(sysdate)+999, 20, 'joe'); insert into t2 values (2, trunc(sysdate), trunc(sysdate)+5, 'a', 'frank'); insert into t2 values (2, trunc(sysdate)+6, trunc(sysdate)+8, 'b', 'mike'); insert into t2 values (2, trunc(sysdate)+9, trunc(sysdate)+999, 'c', 'joe'); insert into t3 values (2, trunc(sysdate), trunc(sysdate)+5, 10, 'lucy'); insert into t3 values (2, trunc(sysdate)+6, trunc(sysdate)+999, 30, 'dan'); Solutions ========= You should use the above commands to create the tables and insert sample data. You can assume that the production tables each have 50- 100k records. The table with the most rows is t1. You can use any method you like (for example, temp tables, create new indexes, etc.) We will try to benchmark the best solutions on actual data. We'll discuss our solutions -- and yours -- in two weeks. Have fun! --------------------------------------------------------- 2) ORACLE NEWS --------------------------------------------------------- Oracle has organized a Webcast to discuss their e- business strategy. Jeff Henley, EVP & CFO of Oracle Corporation, will share his experience in adopting the strategy for Oracle and discuss the challenges he has faced. The event will be held on October 8, 2001. Please go to the following site for further details: http://oracle.com/iseminars. --------------------------------------------------------- 3) DID YOU KNOW... --------------------------------------------------------- * that Oracle may offer a discount of $200 under certain conditions if you sign up for the Oracle OpenWorld 2001 event? The event will be held in San Francisco, California, from December 2-7, 2001. Find out more at the following site: http://www.oracle.com/openworld/us/conference/. --------------------------------------------------------- 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.