NoCOUG watchers protest despicable tactics being used by NoCOUG management

FOR IMMEDIATE RELEASE

NoCOUG watchers protest despicable tactics being used by NoCOUG management

SILICON VALLEY (NOVEMBER 13, 2014) – Veteran NoCOUG watchers all over Northern California have been protesting the despicable tactics being used by NoCOUG management to lure Oracle Database professionals to the NoCOUG conference at the beautiful eBay Town Hall next week. Instead of relying on the sheer quality of the technical education that NoCOUG has to offer, NoCOUG management is trying to tempt Oracle professionals with mouth-watering lunch options from the eBay cafeteria. The menu includes chicken tikka masala, palak paneer, aloo gobi(vegan), vegetable poriyal, chick pea salad, vegetable samosas, steamed basmati rice, lentil dahl, naan bread, mint chutney, tomato chutney, and cookies.

There’s even a taco bar for those who don’t care that much for curry.

David L. from San Francisco indignantly emailed: “CTM for lunch?  That’s not playing fair.  I might get in traffic for that.”

Mark S. from Davis cynically commented on LinkedIn: “Finally, there is something on the agenda that suits my tastes!”

When contacted for comment, the NoCOUG spokeperson proved unabashedly unapologetic: “There is no legal prohibition against offering mouth-watering lunch options to conference attendees. I urge all Oracle Database professionals to attend this conference. For database administrators, I recommend the Managing the Largest OLTP Database in the World session by the PayPal DBA team. For architects, I recommend Database Platform Selection Tool by the eBay data architects. And, for boys and girls of all ages, I recommend Toad Overview and Roadmap for DBAs, Developers, and Data Analysts. There will also be opportunities to take the eBay Commerce Innovation Showcase tour which is usually offered to industry analysts only. The signup sheet will be posted in the lobby; it’s first come, first served.”

Our reporter heard the NoCOUG spokesperson chuckle as he hung up.

From a NoCOUG insider who asked not to be named publicly, our reporter learned that registrations are going through the proverbial roof as shown in the graph below. On further investigation, he discovered that there is going to be an ice cream social immediately following the Beyond High Availability: Replication’s Changing Role session in the afternoon, further evidence that NoCOUG management will go to any lengths to increase conference attendance.

Conference registration is still open at http://www.nocoug.org/rsvp.html but potential conference attendees are strongly advised to beware of the blandishments and make a purely rational decision to take advantage of the opportunity to hear first-hand from the PayPal and eBay database administrators and data architects, not be tempted by samosas and ice cream.

I Scream, You Scream, We All Scream For Ice Cream (scene from “Down By Law”)

Oracle Trivia Quiz

All the answers can be found in the November 2014 issue of the NoCOUG Journal.

I am the editor of the NoCOUG Journal. What’s NoCOUG, you ask? Only the oldest and most active Oracle users group in the world. If you live in the San Francisco bay area and have never ever attended a NoCOUG conference in the last 28 years (that’s how long NoCOUG has been around), please contact me for a free pass to our upcoming conference at eBay on November 19, featuring presentations such as “Managing the Largest OLTP Database in the World” and “I Didn’t Know that Toad could do That!” Continental breakfast, hot lunch (can you say “Chicken Tikka Masala”), and never-ending refreshments. Who says there’s no such thing as a free lunch?

1. Which executive vice-president of product development at Oracle began as the PL/SQL product manager? (page 23)
2. Which senior vice-president of server technologies at Oracle wrote the B-Tree indexing code back in the day? (page 23)
3. What is the evil twin of relational algebra? (page 17)
4. If you have SELECT privilege on a table, can you also “SELECT FOR UPDATE”? (page 19)
5. Who coined the term “compulsive tuning disorder”? (page 13 footnote)
6. What percentage of women leave high technology at the mid-career mark? (page 4)
7. How can you empower application developers with access to live performance data from the production database? (page 6)
8. What kind of optimization is the root of all evil according to Professor Donald Knuth? (page 10)
9. Can UNION ALL materialized views be fast-refreshed on commit? (page 18)
10. What will be the winning lottery numbers for the next Powerball drawing (just checking if you’re awake)

P.S. A 10-year digital archive of the NoCOUG Journal is available at http://nocoug.wordpress.com/nocoug-journal-archive/.

Results of the NoCOUG SQL Mini-Challenge

Follow @Oratweets

As published in the November 2014 issue of the NoCOUG Journal

The inventor of the relational model, Dr. Edgar Codd, was of the opinion that “[r]equesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language” (“Relational Completeness of Data Base Sublanguages”). Therefore, with the exception of the union operation, the original version of SQL was based on relational calculus, although, over time, other elements of relational algebra like difference (minus), intersection, and outer join crept in.

Testing of existence in a set using subqueries is the hallmark of relational calculus. The following example has nested subqueries. It lists the locations containing a department that either contains an employee named Steven King or an employee who holds the title of President or an employee who has previously held the title of President.

SELECT l.location_id, l.city
FROM locations l
WHERE EXISTS (
  SELECT * FROM departments d, employees e, jobs j
  WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND j.job_id = e.job_id
  AND (
    (e.first_name = 'Steven' AND e.last_name = 'King')
    OR j.job_title  = 'President'
    OR EXISTS (
      SELECT * FROM job_history jh, jobs j2
      WHERE jh.employee_id = e.employee_id
      AND j2.job_id = jh.job_id
      AND j2.job_title = 'President'
    )
  )
)

The challenge was to rewrite the above query without subqueries and in the most efficient way possible, as measured by the number of consistent gets—the Buffers column in the query execution plan—when executed in the HR sample schema. The competitors published their submissions on the NoCOUG blog nocoug.wordpress.com. Oracle ACE Directors Kyle Hailey and Tim Gorman helped judge the competition.

Backgrounder

The secret sauce of relational database management systems is composed of what Dr. Edgar Codd referred to as “relational calculus” and “relational algebra.” He was a mathematician by training so he instinctively used complex-sounding mathematical terms that normal folk don’t use a lot. Relational calculus has nothing to do with college calculus; a relational calculus expression is simply an English-like non-procedural specification of the user’s query requirements. Relational algebra, on the other hand, is a step-by-step procedural recipe that details how to meet the user’s requirements. The whole reason why there is so much interest in EXPLAIN PLAN is that it documents the sequence of relational algebra operations that Oracle Database used at runtime to execute any particular query; that is, it documents the query execution plan used by Oracle Database.

More precisely, relational algebra is a collection of operations that can be used to combine tables. Just as you can combine numbers using the operations of addition, subtraction, multiplication, and division, you can combine tables using operations like “restriction,” “projection,” “union,” “difference,” and “cartesian join.” These five operations are provably sufficient to implement any requirement expressed in relational calculus. They are also primitive; that is, none of them is a combination of the others. Composite relational operations can be devised by combining these five operations; examples include “inner join,” “intersection,” “outer join,” “semi-join,” “anti-join,” and “division.” For example, inner join is obviously a combination of cartesian join and restriction. Another example is A INTERSECTION B = A MINUS (A MINUS B).

In “Relational Completeness of Data Base Sublanguages” Codd showed how to mechanically convert a relational calculus expression into a relational algebra expression. Refer to http://goo.gl/SHcWLE for a worked example. In the case of the challenge query, a little thought—or a lot of thought—will convince you that the EXISTS clauses can be eliminated by the simple expedient of selecting distinct values of location_id and city from the join of all the tables in the original query, as shown below. Note that the jobs table has to be joined twice because it occurs twice in the challenge query.

SELECT DISTINCT l.location_id, l.city
FROM locations l, departments d, employees e, jobs j, job_history jh, jobs j2
WHERE EXISTS (
  SELECT * FROM departments d, employees e, jobs j
  WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND j.job_id = e.job_id
  AND (
    (e.first_name = 'Steven' AND e.last_name = 'King')
    OR j.job_title  = 'President'
    OR EXISTS (
      SELECT * FROM job_history jh, jobs j2
      WHERE jh.employee_id = e.employee_id
      AND j2.job_id = jh.job_id
      AND j2.job_title = 'President'
    )
  )
)

However, such a mechanical conversion from relational calculus to relational algebra may not be your best bet. In general, there is more than one way to write a SQL query, and the query optimizer may choose a different query execution plan for each version. In other words, functionally equivalent SQL queries may not be equally efficient in practice. This is the biggest unsolved problem of the relational era.

Judging Criteria

The criteria used by the judges were correctness and efficiency. Efficiency was measured by the number of consistent gets, that is, the Buffers column in the query execution plan. The GATHER_PLAN_STATISTICS hint instructs Oracle Database to keep track of “rowsource execution statistics,” which can then be printed out by DBMS_XPLAN.DISPLAY_CURSOR. Judging correctness is a much harder task, because no tools are available for determining the equivalence of two queries. In fact, the reason why the query optimizer may choose different query execution plans for two functionally equivalent queries is that it cannot tell if they are equivalent. What is needed is a systematic method of converting a SQL query into a “canonical” version such that all functionally equivalent queries would be converted into the same canonical version. In the absence of automated tools, we have to resort to thinking very hard and writing test cases.

We Have a Winner!

Kim Berg Hansen from Denmark wins the mini-challenge. Not only did his solution survive the test cases written by the judges but he reduced the number of consistent gets to the theoretical minimum of 1 by using a UNION ALL materialized view. The first branch of the UNION ALL finds employees named Steven King or who hold the title of President, while the second branch finds employees who previously held the title of President. Materialized views can be indexed just like tables, so Kim created an index consisting only of the location_id and city columns. He also took pains to ensure that the materialized view was fast-refreshable on commit. Therefore, his solution continues to return correct results even after the tables are updated.

CREATE MATERIALIZED VIEW LOG ON employees WITH ROWID, COMMIT SCN
   (employee_id, job_id, first_name, last_name, department_id)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON departments WITH ROWID, COMMIT SCN
   (department_id, location_id)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON locations WITH ROWID, COMMIT SCN
   (location_id, city)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON jobs WITH ROWID, COMMIT SCN
   (job_id, job_title)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON job_history WITH ROWID, COMMIT SCN
   (job_id, employee_id)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW employees_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT 'CURRENT' record_type, l.location_id, l.city, j.rowid j_rowid, NULL jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
FROM locations l, departments d, employees e, jobs j
WHERE (
  d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND j.job_id = e.job_id
  AND (
    (e.first_name = 'Steven' AND e.last_name = 'King')
    OR j.job_title  = 'President'
  )
)
UNION ALL
SELECT 'HISTORY' record_type, l.location_id, l.city, j2.rowid j_rowid, jh.rowid jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
FROM locations l, departments d, employees e, job_history jh, jobs j2
WHERE (
  d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND (
      jh.employee_id = e.employee_id
      AND j2.job_id = jh.job_id
      AND j2.job_title = 'President'
  )
);

CREATE INDEX employees_mv_ix on employees_mv (
   location_id, city
);

Kim’s solution to the mini-challenge mimics the definition of the materialized view in the hope that “query rewrite” will kick in. Because Oracle Database does not index null values, the redundant clause “WHERE location_id IS NOT NULL” is added to ensure that the query optimizer uses the index.

SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT location_id, city
FROM (
  SELECT l.location_id, l.city
  FROM locations l, departments d, employees e, jobs j
  WHERE (
    d.location_id = l.location_id
    AND e.department_id = d.department_id
    AND j.job_id = e.job_id
    AND (
      (e.first_name = 'Steven' AND e.last_name = 'King')
      OR j.job_title  = 'President'
    )
  )
  UNION ALL
  SELECT l.location_id, l.city
  FROM locations l, departments d, employees e,
    job_history jh, jobs j2
  WHERE (
    d.location_id = l.location_id
    AND e.department_id = d.department_id
    AND (
        jh.employee_id = e.employee_id
        AND j2.job_id = jh.job_id
        AND j2.job_title = 'President'
    )
  )
)
WHERE location_id IS NOT NULL;

On checking the query execution plan, we find that the data was indeed obtained from the index on the materialized view. Only one consistent get operation was needed.

SELECT * FROM
TABLE(dbms_xplan.display_cursor(format => 'TYPICAL IOSTATS LAST'));

Plan hash value: 4093995400

--------------------------------------------------------------------------
| Id  | Operation          | Name            | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |      1 |      1 |       1 |
|   1 |  SORT UNIQUE NOSORT|                 |      1 |      1 |       1 |
|*  2 |   INDEX FULL SCAN  | EMPLOYEES_MV_IX |      1 |      1 |       1 |
--------------------------------------------------------------------------

Kim wins a $75 Amazon gift certificate for his most excellent effort. The entries of the other competitors have been published on the NoCOUG blog nocoug.wordpress.com.