Tony’s Oracle Tips

December 9, 2014

UKOUG TECH QUIZ TECH14 Answers

Filed under: Uncategorized — tonyhasler @ 10:05 am

The questions in the quiz were, of course, all trick questions.

Question 1: Answer D

SELECT * FROM T1 LEFT NATURAL JOIN T2;

SELECT * FROM T1 NATURAL LEFT JOIN T2;

The second example shows the correct syntax for a natural outer join.  In the first example, LEFT is interpreted as a table alias for T1 and so an inner join is used.

QUESTION 2: Answer E

SELECT * FROM T1 INNER JOIN T2 ON C1=C2;

SELECT * FROM T1 OUTER JOIN T2 ON C1=C2;

This is the same sort of issue.  Any outer join needs to specify either LEFT or RIGHT but in the second example above OUTER is interpreted as a table alias for T1.  They keyword INNER is optional.  A third construct that also generates identical results is:

SELECT * FROM T1 OUTER INNER JOIN T2 ON C1=C2;

Question 3: Answer D

SELECT *
FROM t1, t2, t3
WHERE t1.c1 = t2.c2(+) AND t2.c2(+) = t3.c3;

SELECT *
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c2
RIGHT JOIN t3 ON t2.c2 = t3.c3;

Those of you attending my talk on Monday would know the answer to this one.  The ambiguity in the first statement meant that it was illegal in releases prior to 12c but has now become legal for performance reasons.  To express the results of the first query in ANSI syntax you would have to say:

SELECT * FROM T1 CROSS JOIN T3 LEFT JOIN T2 ON T1.C1=T2.C2 AND T3.C3=T2.C2;

Question 4: Answer E

SELECT c1, COUNT (*)
FROM t1
GROUP BY c1
HAVING COUNT (*) = 1;

SELECT c1, COUNT (*)
FROM t1
HAVING COUNT (*) = 1
GROUP BY c1;

I wouldn’t consider it best practice but the HAVING clause may precede the GROUP BY clause.

Tie breaker 1:

It is actually possible to solve the Paul and Sam problem using just two query blocks.  Here is my solution:


WITH base
AS ( SELECT TRUNC (LEVEL / 97) + 2 a,
MOD (LEVEL, 97) + 3 b,
TRUNC (LEVEL / 97) + MOD (LEVEL, 97) + 5 s,
(TRUNC (LEVEL / 97) + 2) * (MOD (LEVEL, 97) + 3) p,
COUNT (*)
OVER (
PARTITION BY (TRUNC (LEVEL / 97) + 2)
* (MOD (LEVEL, 97) + 3))
p_cnt1,
COUNT (*)
OVER (
PARTITION BY (TRUNC (LEVEL / 97) + 2)
+ (MOD (LEVEL, 97) + 3))
s_cnt1
FROM DUAL b
WHERE TRUNC (LEVEL / 97) <= MOD (LEVEL, 97)
CONNECT BY LEVEL <= 97 * 97)
-- Main query block
SELECT a,b,p,s
FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s
WHERE b.s_cnt1 > 1 AND p.s IS NULL
GROUP BY b.p
HAVING COUNT (*) = 1
MODEL UNIQUE SINGLE REFERENCE RETURN UPDATED ROWS
DIMENSION BY (COUNT (*) OVER (PARTITION BY MAX (b.s)) s_cnt)
MEASURES (MAX (b.a) a, MAX (b.b) b, b.p, MAX (b.s) s)
RULES
(a [1] = a[1]);

The first query block is easy enough to understand.  We generate all possible pairs of numbers where A is the lower and B is the higher.  P_CNT1 is the number of rows with the same product and S_CNT1 is the number of rows with the same sum.

To understand the main query block we need to build it up in stages.

Let us start with this:
.....
SELECT a,b,p,s
FROM base b
WHERE b.s_cnt1 > 1
AND b.s NOT IN (SELECT s
FROM base p
WHERE p.p_cnt1 = 1);

Here we select the just the rows where Sam doesn’t know and Sam knew Paul didn’t know because there is no pair of numbers with the same sum where Paul could know.  We can get rid of the subquery with an outer join.


SELECT b.a,
b.b,
b.p,
b.s
FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s
WHERE b.s_cnt1 > 1 AND b.p_cnt1 > 1 AND p.s IS NULL;

In the above example the last predicate selects just the rows preserved by the outer join by virtue of not matching.

We can now move on to identify the rows where Paul can now declare that he knows.  For example, if Paul had been given the product 18, he would know that the numbers couldn’t be 3 and 6 because then Sam would have been given the number 9 and Sam wouldn’t have known in advance that Paul hadn’t been given the number 14 and hence have known the answer.  Once Paul is able to eliminate 3 and 6 from consideration he knows that the numbers must be 2 and 9.

We can identify the 86 rows where Paul now knows by using a GROUP BY with a HAVING clause.

...
SELECT MAX (b.a) a,
MAX (b.b) b,
b.p,
MAX (b.s) s,
COUNT (*) OVER (PARTITION BY MAX (b.S)) s_cnt
FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s
WHERE b.s_cnt1 > 1 AND b.p_cnt1 > 1 AND p.s IS NULL
GROUP BY b.p
HAVING COUNT (*) = 1;

The MAX aggregate functions could be MIN or even AVG because in the result set we are aggregating just one row.  The analytic function identifies the number of possibilities that Sam can now consider: the number out of the remaining 86 rows with identical values for the sum.  A visual inspection of these 86 rows yields the one possibility where Sam now knows the answer.  But how can we select that row without another query block?

The answer is to use the MODEL clause.

First of all, let us get the MODEL clause setup without attempting to restrict the rows.  When a model clause is used the aggregate and analytic expressions move out of the SELECT list and into the PARTITION, DIMENSION, and MEASURES sub-clauses.  We ultimately want to look up using S_CNT so that goes in the DIMENSION clause.

...
SELECT a,b,p,s,s_cnt
FROM paul_knows b LEFT JOIN paul_knows p ON p.p_cnt1 = 1 AND b.s = p.s
WHERE b.s_cnt1 > 1 AND p.s IS NULL
GROUP BY b.p
HAVING COUNT (*) = 1
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (COUNT (*) OVER (PARTITION BY MAX (b.s)) s_cnt)
MEASURES (MAX (b.a) a, MAX (b.b) b, b.p, MAX (b.s) s)
RULES
();

The above construct generates the same 86 rows as the previous example.  The UNIQUE SINGLE REFERENCE bit is used to supress a check the SQL engine makes; a reference to a measure a[3] would appear to reference a single cell but would actually reference 6.  By specifying UNIQUE SINGLE REFERENCE we promise not to do anything naughty like that and the error is supressed.

To restrict the rows further we add the keywords RETURN UPDATED ROWS.  If that is all we did we would get no rows returned because there are no rules yet to update rows. The final thing to do is to update the single row that is our puzzle answer.  a[1] = a[1] is a dummy update to identify the one row (a= 4 and b = 13) that is our puzzle answer.  We could have used any measure such as b[1] = b[1]+0.

Advertisements

December 2, 2014

UKOUG TECH14 SQL Quiz

Filed under: SQL Fun — tonyhasler @ 8:03 pm
Tags:

If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Troubleshooting Oracle Performance (2nd edition) by Christian Antognini

TOP

 

Troubleshooting Oracle Performance, 2nd Edition provides a systematic approach to addressing the underlying causes of poor database application performance.


 

AND a you can also win a free copy of Expert Oracle SQL by Tony Hasler

cover

Expert Oracle SQL provides a systematic approach for tuning individual SQL statements and shows how to stabilize production performance with a radical approach to managing object statistics.


 

To get a chance to win a copy of both of these books just leave a comment on this blog with your answers to these questions:

For each of the following pairs of SQL statements indicate whether:

A) They are both illegal
B) The first is legal and the second illegal
C) The second is legal and the first illegal
D) They are both legal but may provide different results
E) They are both legal statements guaranteed to return identical results (ignoring order)

You should assume an Oracle 12cR1 database containing tables T1, T2 and T3 in the current schema. Table T1 has a column C1, table T2 has a column C2, and table T3 has a column C3.  All tables have a column called ID.

Question 1:

SELECT * FROM T1 LEFT NATURAL JOIN T2;

SELECT * FROM T1 NATURAL LEFT JOIN T2;

Question 2:

SELECT * from T1 INNER JOIN T2 ON C1=C2;

SELECT * FROM T1 OUTER JOIN T2 ON C1=C2;

Question 3:

SELECT *
FROM t1, t2, t3
WHERE t1.c1 = t2.c2(+) AND t2.c2(+) = t3.c3;

SELECT *
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c2
RIGHT JOIN t3 ON t2.c2 = t3.c3;

Question 4:

SELECT c1, COUNT (*)
FROM t1
GROUP BY c1
HAVING COUNT (*) = 1;

SELECT c1, COUNT (*)
FROM t1
HAVING COUNT (*) = 1
GROUP BY c1;

Tie breaker 1:

Consider this puzzle.

All the numbers from 2 to 99 are placed into a hat. John draws two numbers from the hat and whispers the product of the two numbers to Paul. John whispers the sum of the two numbers to Sam. Paul and Sam then have the following conversation:

Paul: I don’t know what the two numbers are.
Sam: I knew you didn’t. Neither did I.
Paul: I do now.
Sam: So do I.

Write an SQL query that runs on an Oracle database to identify the only possible pair of numbers that could have been extracted from the hat.

  • The only database object you should reference is DUAL.
  • Use the keyword SELECT as infrequently as possible.
  • No PL/SQL. For the avoidance of doubt, PL/SQL used in part of an SQL statement in 12cR1 is not allowed.
  • You cannot work out all or part of the answer yourself. So, for example, you may work out that if Sam knows the answer immediately then so does Paul. Your SQL cannot make this assumption.

Tie breaker 2:

The first three names drawn out of a hat of containing the names of all correct answers will win a copy of the two books.

Rules

  • This competition is only available to attendees of the UKOUG Tech14 conference
  • To stand a chance of wining you must post your answers to the quiz questions by 11:00 a.m. on Tuesday 9th December 2014
  • You must be present at the Optimizer Round Table session at 12:00 on Tuesday 9th December to collect your prize. Otherwise another name will be draw from the hat.

Blog at WordPress.com.