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”)
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?|
- Which executive vice-president of product development at Oracle began as the PL/SQL product manager? (page 23)
- Which senior vice-president of server technologies at Oracle wrote the B-Tree indexing code back in the day? (page 23)
- What is the evil twin of relational algebra? (page 17)
- If you have SELECT privilege on a table, can you also “SELECT FOR UPDATE”? (page 19)
- Who coined the term “compulsive tuning disorder”? (page 13 footnote)
- What percentage of women leave high technology at the mid-career mark? (page 4)
- How can you empower application developers with access to live performance data from the production database? (page 6)
- What kind of optimization is the root of all evil according to Professor Donald Knuth? (page 10)
- Can UNION ALL materialized views be fast-refreshed on commit? (page 18)
- 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/.
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.
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.
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.
Now in its 28th year, the NoCOUG Journal is the oldest Oracle user group publication in the world. No other small user group in the world has a printed journal. Most large user groups do not have printed journals either. But little NoCOUG does. I am the editor of the NoCOUG Journal and—I must confess—I get sad when I see a discarded copy of the NoCOUG Journal at a NoCOUG conference. But the person who discarded it probably didn’t realize how much it costs to produce the Journal—$15 per copy—and how much volunteer effort goes into each issue. A very special mention goes to Brian Hitchcock, who has written dozens of book reviews for the Journal over a 12-year period.
And the production qualities of the Journal are simply awesome. The Journal is professionally copyedited and proofread by veteran copyeditor Karen Mead of Creative Solutions. Karen polishes phrasing and calls out misused words (such as the noun “reminiscence” instead of the verb “reminisce”). She dots every i, crosses every t, checks every quote, and verifies every URL. Then, the Journal is expertly designed by graphics duo Kenneth Lockerbie and Richard Repas of San Francisco-based Giraffex. And, finally, Jo Dziubek at Andover Printing Services deftly brings the Journal to life on an HP Indigo digital printer. The professional pictures on the front cover are supplied by Photos.com.
The content of the Journal is beyond awesome. But I’ll let you judge that for yourself. Click on the icons below to download the last four issues of the Journal.
FOR IMMEDIATE RELEASE
Cupcake Wars at NoCOUG Spring Conference on May 15 at UCSC Extension Silicon Valley
SILICON VALLEY (APRIL 1, 2014) – In a bold experiment aimed at increasing attendance at its awesome educational conferences, the Northern California Oracle Users Group (NoCOUG) is considering changing the format of its spring conference to that of Food Network’s “Cupcake Wars.”
Distinguished Oracle Product Manager Bryn Llewellyn will lead the PL/SQL team, OraPub founder Craig Shallahamer will lead the DBA team, Hadoop maven Gwen Shapira will lead the Big Data team, and Database Specialists Director of Managed Services Terry Sutton will lead the RAC team. NoCOUG president Hanan Hit will stride from one room to another shouting “TEN MINUTES, BAKERS! YOU HAVE TEN MINUTES LEFT!
“NoCOUG has been serving the Oracle community for 28 years but our conferences are best known for their awesome educational content. We want our conferences to also be a place where people can come together on a social level” said NoCOUG president Hanan Hit when asked for comment.
Registration for the spring conference is now open. Click here to view the complete agenda and register.
Also in today’s news:
- Want to make easy money? “Airbrb,” based on the apartment-renting app Airbnb, lets you rent out your office desk while you hang out at the water cooler or take a bio break.
- Convert any website into emoticon characters: Google now lets you emojify the web.
See also : No! to SQL and No! to NoSQL
The inventor of the relational model, Dr. Edgar “Ted” Codd believed that the suppression of physical database design details was the chief advantage of the relational model. He made the case in the very first sentence of the very first paper on the relational model saying “Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation).” (“A Relational Model of Data for Large Shared Data Banks,” reprinted with permission in the 100th issue of the NoCOUG Journal.)
How likely is it that application developers will develop highly performant and scalable applications if they are shielded from the internal representation of data? The de-emphasis of physical database design was the biggest mistake of the relational camp and provided the opening for NoSQL and Big Data technologies to proliferate.
A case in point is that the language SQL which is universally used by application developers was not created with them in mind. As explained by the creators of SQL (originally called SEQUEL) in their 1974 paper, there is “a large class of users who, while they are not computer specialists, would be willing to learn to interact with a computer in a reasonably high-level, non-procedural query language. Examples of such users are accountants, engineers, architects, and urban planners [emphasis added]. It is for this class of users that SEQUEL is intended. For this reason, SEQUEL emphasizes simple data structures and operations [emphasis added].” (http://faculty.cs.tamu.edu/yurttas/PL/DBL/docs/sequel-1974.pdf)
If you were the manager of a bookstore, how would you stock the shelves? Would you stand at the door and fling books onto any shelf that had some free space, perhaps recording their locations in a notebook for future reference. Of course not! And would you scatter related books all over the bookstore? Of course not! Then why do we store rows of data in random fashion? The default Oracle table storage structure is the unorganized heap and it is chosen 99.9% of the time.
The de-emphasis of physical database design was an epic failure in the long run. Esther Dyson referred to the “join penalty” when she complained that “Using tables to store objects is like driving your car home and then disassembling it to put it in the garage. It can be assembled again in the morning, but one eventually asks whether this is the most efficient way to park a car.” 
It doesn’t have to be that way. Oracle Database has always provided a way to cluster rows of data from one or more tables using single-table or multi-table clusters in hashed or indexed flavors and thus to completely avoid the join penalty that Esther Dyson complained about. However, they must be the longest and best kept secret of Oracle Database—as suggested by their near-zero adoption rate—and have not been emulated by any other DBMS vendor. You can read more about them at http://iggyfernandez.wordpress.com/2013/07/28/no-to-sql-and-no-to-nosql/.
It doesn’t have to be that way. But it is.
1. Esther Dyson was the editor of a newsletter called Release 1.0. I’ve been unable to find the statement in the Release 1.0 archives at http://www.sbw.org/release1.0/ so I don’t really know the true source or author of the statement. However, the statement is popularly attributed to Esther Dyson and claimed to have been published in the Release 1.0 newsletter. I found a claim that the statement is found in the September 1988 issue but that didn’t pan out.
See also : No! to SQL and No! to NoSQL
Solve the Oracle Database murder mystery and win a free ticket for yourself and a friend to the NoCOUG conference
You may remember this children’s song from kindergarten or you can listen to this YouTube video:
“Ten green bottles hanging on the wall
Ten green bottles hanging on the wall
And if one green bottle should accidentally fall
There’ll be nine green bottles hanging on the wall.”
In this Oracle Database murder mystery, there were no green bottles left hanging on the wall after the first bottle fell. Send your solution to firstname.lastname@example.org and receive a free ticket for yourself and a friend to the NoCOUG conference on Thursday, August 15 featuring performance guru Craig Shallahamer, a full track of Oracle Database 12c presentations, and alternative technology presentations on MySQL, NoSQL, and Big Data. Click here to review the detailed agenda.
It was a beautiful spring day. Popcorn was popping on the apricot tree. What does this have to do with databases? Nothing, but I’m trying to write a novel!
As I said, it was a beautiful Wednesday morning in spring. The time was exactly 9:12:00 AM PST. A database user noticed that her favorite database was down and called her favorite DBA—let’s call him Jack—for help.
For the record, I am not related to Jack.
Young Jack jumped to it and restarted the database lickety-split. Then disaster struck! The nine other databases on that database server—a Linux box with NetApp storage—crashed like bowling pins!
An unseen hand restarted all the databases immediately but the damage was done. Jack was dragged to the DBA interrogation chamber—the DBA manager’s office—and made to sit on the hot seat.
It was a sunny day and the sun was streaming in through the plate glass windows, which explains why the seat was so hot. Besides, the air-conditioning was not working that day.
“WHAT HAVE YOU DONE,” bellowed the furious DBA manager. “I was only trying to help,” said poor Jack.
“HELP!? DO YOU CALL THAT HELPING!?” bellowed the furious DBA manager. The database alert logs were examined. The first database log showed that someone had used the command “STARTUP FORCE” at precisely 9:12:00 AM PST.
“DID YOU DO THAT!? DID YOU DO THAT!?” bellowed the furious DBA manager. “Yes, I did that,” said poor Jack, “but I was only trying to help.”
A single tear slowly streamed down young Jack’s cheek.
“HELP!? DO YOU CALL THAT HELPING!?” bellowed the furious DBA manager, unmoved by Jack’s obvious distress. The remaining database alert logs were examined. Each of them showed that someone had used the command “SHUTDOWN IMMEDIATE” followed by “STARTUP” right after the first database was restarted by Jack. “DID YOU DO THAT!? DID YOU DO THAT!?” bellowed the furious DBA manager.
“I didn’t do any of that,” said poor Jack.
If you believe Jack’s protestations of innocence, figure out how and why nine databases were mysteriously stopped and restarted. Send your solution to email@example.com and receive a free ticket for yourself and your friend to the summer conference on Thursday, August 15 at Chevron in San Ramon. RSVP here.
P.S. For more than 25 years, NoCOUG has helped Oracle professionals like you continuously improve and enhance your skill sets through our conferences and Journal. Our conferences are held on the third Thursday of February (winter conference), May (spring conference), August (summer conference), and November (fall conference) and are filled with practical and cutting-edge content for application developers as well as database administrators. Please help spread the word about NoCOUG by forwarding this message to your friends and colleagues. They can join our email list at http://www.nocoug.org/.
This colleague of Dr. Edgar “Ted” Codd was featured in the latest NoCOUG Journal