Problem Solving, Oracle-Style

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.

Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It’s gone through several iterations since its original offering and we’ll consider the most recent of those here. The problem:

	Two mathematicians met at their college reunion.  Wanting to keep current they
	started discussing their lives as only mathematicians can:

	Mathematician #1: So, do you have any children?
	Mathematician #2: Yes, three girls.
	Mathematician #1: Wonderful!  What are their ages?
	Mathematician #2: I'll give you a clue: the product of their ages is 36.
	Mathematician #1: Hmmm, good clue but not nearly enough information.
	Mathematician #2: Well, the sum of their ages is the number of people in this room.
	Mathematician #1: (After looking about the room) That's still not enough information.
	Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
	Mathematician #1: I have it now -- say, are the twins identical?

	Given that all of the information needed to solve the problem is in the problem,
	what are the ages of the three girls?

The problem seems unsolvable at first glance but there is more information available than is originally seen. Let’s state what we know from the problem:

	1 -- There are three girls
	2 -- Their ages, multiplied together, have a product of 36
	3 -- The sum of their ages is (to us, anyway) an undisclosed number
	4 -- The oldest daughter has a hamster with a wooden leg

A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let’s start with the product of the ages:

  
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  )
  6  select *
  7  from age_list;

       AGE                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                      
         5                                                                      
         6                                                                      
         7                                                                      
         8                                                                      
         9                                                                      
        10                                                                      
        11                                                                      

       AGE                                                                      
----------                                                                      
        12                                                                      
        13                                                                      
        14                                                                      
        15                                                                      
        16                                                                      
        17                                                                      
        18                                                                      
        19                                                                      
        20                                                                      
        21                                                                      
        22                                                                      

       AGE                                                                      
----------                                                                      
        23                                                                      
        24                                                                      
        25                                                                      
        26                                                                      
        27                                                                      
        28                                                                      
        29                                                                      
        30                                                                      
        31                                                                      
        32                                                                      
        33                                                                      

       AGE                                                                      
----------                                                                      
        34                                                                      
        35                                                                      
        36                                                                      

36 rows selected.

SQL> 
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  )
 18  select *
 19  from product_check
 20  order by 1,2,3;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         1          1         36         38         36                          
         1          2         18         21         36                          
         1          3         12         16         36                          
         1          4          9         14         36                          
         1          6          6         13         36                          
         2          2          9         13         36                          
         2          3          6         11         36                          
         3          3          4         10         36                          

8 rows selected.

SQL> 

Notice we return not only the product of the ages but also the sums of the various combinations, as we’ll need this information later on in the problem. Now another ‘fact’ emerges:

	5 -- Knowing the sum of the ages doesn't help matters much

This reveals that there is more than one combination of ages which produce the same sum:

  
SQL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19  	     select youngest, middle, oldest, sum, product
 20  	     from (
 21  		     select youngest, middle, oldest, sum, product,
 22  		     count(*) over (partition by sum) ct
 23  		     from product_check
 24  	     )
 25  	     where ct > 1
 26  )
 27  select *
 28  from summed_check;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          
         1          6          6         13         36                          

SQL> 

Now we know the number of people in the room and why the sum wasn’t enough information to solve the problem. The final ‘nail in the coffin’ (so to speak) is the owner of the hamster with the wooden leg; the problem states:

	Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.

It’s not the hamster, it is the fact that the oldest daughter (there’s only one) exists. Knowing that last piece of information provides the final answer:

  
SQL> 
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19  	     select youngest, middle, oldest, sum, product
 20  	     from (
 21  		     select youngest, middle, oldest, sum, product,
 22  		     count(*) over (partition by sum) ct
 23  		     from product_check
 24  	     )
 25  	     where ct > 1
 26  )
 27  select *
 28  from summed_check
 29  where oldest > middle;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          

SQL> 

The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.

The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.

So you don’t encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:

Hi, 


I have 3 columns of data 


Column 1:subscription 
Column 2: invoice number 
Column 3: Service 


I need to seperate the subscription types into new, renewals and additional 
which is fine but the next bit i am having trouble 


Each invoice number can have 1 or more service 


e.g. 
Invoice Number                    Service 
123                               Photocopying 
123                               Printing 
123                               Scan & Store 
234                               Photocopying 
234                               Scan & Store 
345                               Photocopying 
345                               Printing 


I apply a rate for each service e.g. 
photocopying = 1.5 
printing = 1.7 


but if Scan and store is in an invoice with photocopying we charge an extra 
1.5 


but if printing is a service with the scan and store a different rate 
applies 1.7 


so i can't just count scan and store and apply a rate i have to figure out 
if it is with photocopying or with printing and then apply the rate 


What I want to be able to do is creat a table with columns that calculates 
this 
so i get a 4 columns: 


Service                       usage       rate      total 
photocopying                      3        1.5        4.5 
Printing                          2        1.7        3.4 
Scan & Store                      1        1.5        1.5 
Scan & Store w/Print              1        1.7        1.7 


The problem comes in when i'm trying to count scan and store wit/without 
printing. I can't figure it out. 


I import the report from an excel spreadsheet into acces and want to run a 
query that does all this... 


thanks in advance, 
ainese 

With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:

  
SQL> Create table subscription(
  2          subscr_type varchar2(15),
  3          invoice number,
  4          service varchar2(40),
  5          service_cd number
  6  );

Table created.

SQL>
SQL> insert all
  2  into subscription
  3  values('RENEWAL',123,'Photocopying',0)
  4  into subscription
  5  values('RENEWAL',123,'Printing',2)
  6  into subscription
  7  values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
  8  into subscription
  9  values('ADDITIONAL',234,'Photocopying',0)
 10  into subscription
 11  values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
 12  into subscription
 13  values('NEW',345,'Photocopying',0)
 14  into subscription
 15  values('NEW',345,'Printing',2)
 16  select * From dual;

7 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:

  
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5


SQL>

Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:

  

SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                  PHOTOCOPY   PRINTING SCAN_AND_STORE SCAN_AND_STORE_W_PRT
--------------- ---------- ---------------------------------------- ---------- ---------- -------------- --------------------
NEW                    345 Photocopying,Printing                           1.5        1.7              0                    0
RENEWAL                123 Photocopying,Printing,Scan & Store              1.5        1.7              0                  1.7
ADDITIONAL             234 Photocopying,Scan & Store                       1.5          0            1.5                    0
                                                                    ---------- ---------- -------------- --------------------
sum                                                                        4.5        3.4            1.5                  1.7




SQL>

All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service.

One last problem is one found often on the web:

	Display the second highest salary in the employee table
	Display the employee id, first name, last name and salary for employees earning the second highest salary

Depending on which question is asked several solutions present themselves. The first is the ‘obvious’ solution:

  
SQL> select salary
  2  from
  3  (select salary from employees order by 1 desc)
  4  where rownum = 2;

no rows selected

SQL>

Which doesn’t work because ROWNUM is never set to 1 so it can’t get to 2. A modest rewrite produces:

  
SQL> select salary
  2  from
  3  (select rownum rn, salary from
  4  (select salary from employees order by 1 desc))
  5  where rn = 2;

    SALARY
----------
     17000


1 row selected.

SQL>

Which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:

  
SQL> select salary from
  2  (select salary, dense_rank() over (order by salary desc) rk
  3          from employees)
  4  where rk=2;

    SALARY
----------
     17000
     17000


2 rows selected.

SQL>

To produce more information the above query needs a small modification:

  
SQL> select employee_id, first_name, last_name, salary
  2  from
  3  (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
  4   from employees)
  5  where rk =2;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000


2 rows selected.

SQL>

RANK() and DENSE_RANK() do just what they’re named — rank the requested values — but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:

  
SQL> select salary, rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          4
     13500          5
     13000          6
     12000          7
     12000          7
     12000          7
     11500         10
...

Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():

  
SQL> select salary, dense_rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          3
     13500          4
     13000          5
     12000          6
     12000          6
     12000          6
     11500          7
     11000          8
     11000          8
     11000          8
     10500          9
     10500          9
     10000         10
     10000         10
     10000         10
     10000         10
...

Which is why DENSE_RANK() was used to solve the problem.

Problem solving, when given a little thought, isn’t a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the ‘right’ direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn’t consider). The more you practice, the more you learn.

A train leaves station A at 3:30 PM and travels west at 50 miles per hour …

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles