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. Post your solution in the Comments below. The prize for the best solution that fits all the facts is two free tickets to the first ever YesSQL Summit at the Oracle conference center in Redwood City on January 26 and 27.
It was a beautiful spring day. Popcorn was popping on the apricot tree. 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.
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 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,” protested poor Jack.
Let us draw the curtain of charity over the rest of the scene. If you believe Jack’s protestations of innocence, figure out how and why nine databases were mysteriously stopped and restarted. Post your solution in the Comments below. The prize for the best solution that fits all the facts is two free tickets to the first ever YesSQL Summit at the Oracle conference center in Redwood City on January 26 and 27.
Happy sleuthing and best wishes for 2016.
Special announcement: Invite your friends and co-workers to the spring conference on May 21 and register them as your guests. If your guests attend the conference, you and your guests will each receive a complimentary copy of How to Win Friends and Influence People, the timeless book by Dale Carnegie which has sold more than 15 million copies.
The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave laughing.
Entries should be attached as a comment to this announcement. Please use the code tag to delimit SQL code. Judging criteria will include correctness, originality, efficiency, portability, readability, and order of receipt. The winner will receive an Apple Watch Sport or gift card of equal value. The August Order of the Wooden Pretzel will also be conferred on the winner, in keeping with the celebrated comment by Steven Feuerstein in the August 2006 issue of the NoCOUG Journal: “Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.” The current knights of the August Order of the Wooden Pretzel—Alberto Dell’Era (Italy), Andre Araujo (Australia), Rob van Wijk (Netherlands), Ilya Chuhnakov (Russia), and Lukasz Pluta (Poland)—are not eligible to participate. Additional prizes may be awarded at the discretion of NoCOUG. The contest will close at a time of NoCOUG’s choosing. NoCOUG and the judges reserve the right to publish and comment on any of the submissions with due credit to the originators. NoCOUG reserves the complete right to clarify, interpret, or modify the contest rules at any time. NoCOUG’s decisions are final.
About the NoCOUG SQL challenges
In an interview for the NoCOUG Journal, Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”
Steven replied: “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic. PL/SQL remains the fastest and easiest way to access and manipulate data in an Oracle RDBMS, and I am certain it is going to stay that way for decades.”
To prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.
First International NoCOUG SQL Challenge
An ancient 20-sided die was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. A mysterious symbol was inscribed on each face of the die. The great Wizard of Odds discovered that each symbol represents a number. The great wizard discovered that the die was biased and invited all practitioners of the ancient arts of Es-Cue-El to create an Es-Cue-El spell to display the probabilities of obtaining various sums when the die was thrown “N” times in succession in a game of chance. Nine solutions were found by participants in seven countries and three continents but the winner, Alberto Dell’Era from Italy, rose above the competition by implementing Discrete Fourier Transforms and becoming the first knight of the August Order of the Wooden Pretzel. You can read an explanation of his wonderful solution here. Alberto also implemented Fast Fourier Transforms but we won’t even go there.
Second International NoCOUG SQL Challenge
An ancient manuscript titled “Love Your Data” was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. The manuscript was covered with mysterious words and the great Wizard of Odds implored contestants to create an Es-Cue-El spell that revealed the secret message. Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia) submitted solutions and became the second, third, and fourth knights of the August Order of the Wooden Pretzel. Ilya submitted two solutions: one using the MODEL clause and one using recursive common table expressions. You can read their wonderful solutions in the 100th issue of the NoCOUG Journal.
Third International NoCOUG SQL Challenge
The Wicked Witch of the West had invited six friends to the Third Annual Witching & Wizarding Ball at Pythian Academy of Es-Cue-El & No-Es-Cue-El. Here friends made their attendance contingent upon the attendance ofothers. For examle, Burdock Muldoon, Elfrida Clagg, and Falco Aesalon all said they would come if Carlotta Pinkstone and Daisy Dodderidge both came. The Wicked Witch of the West needed an Es-Cue-El or No-Es-Cue-El spell to determine whom she needed to persuade to attend the wizarding ball in order to ensure that all her invitees attend. Master sorcerer Lukasz Plata of Poland not only solved the problem with a single SQL statement but provided a proof that his solution was correct. He became the fifth knight of the August Order of the Wooden Pretzel. You can read his wonderful solution here.
If you have had interesting experiences with SQL or PL/SQL in your career, here’s your opportunity to share them on stage, on camera, or in print. Send your stories to firstname.lastname@example.org and we’ll forward them to the SQL evangelists team headed up by Steven Feuerstein. You may be selected to tell your stories on stage at the YesSQL! presentation at the winter conference next Tuesday or your stories may be recorded on camera or they may even be printed in Oracle Magazine! C’mon, don’t be shy!
The editor of the NoCOUG Journal sent in the following stories:
“SQL has come a long way since I first began using it in the late ’80s. In my first job as an application developer, I had to summarize a huge amount of data in multiple ways. I wrote a Pro*C program and used binary search trees to maintain totals for different grouping columns so that I did not have to retrieve the same data multiple times. Today, that can be done in a single SQL statement with ROLLUP, CUBE, or GROUPING SETS. SQL rocks!”
“For most of my career I had not heard of the founder of relational theory, Dr. Edgar Codd, or read his papers. In fact, I did not fully understand relational theory until I read Codd’s papers a few years ago. We printed Codd’s first paper A Relational Model of Data for Large Shared Data Banks in the 100th issue of the NoCOUG Journal. Since the paper was not available in text form, we had to type it all over again. And, because it used a lot of mathematical notation, we had to learn how to use the ‘LaTeX’ typesetting system. We reproduced the paper meticulously, even going so far as to reproduce the one misspelling ‘Pheonix’ that we found in Codd’s paper.”
“SQL is sometimes used for the wrong reasons. The high priest of PL/SQL, Steven Feuerstein, said in an interview for the NoCOUG Journal: ‘Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.’ The winner of the First International NoCOUG SQL challenge, Alberto Dell’Era from Italy, implemented ‘Discrete Fourier Transforms’ (!!) and ‘Fast Fourier Transforms’ (!!!!) to help the great ‘Wizard of Odds’ at ‘Hogwash School of Es-Cue-El’ solve the riddle of the ancient jade icosahedron found in the secret chamber of mystery!”
** This just in: Bring your camera! There will be a guided tour of the Oracle campus at lunch time. Take your picture in front of the amazing 130-foot high sailboat that won the Americas Cup, marvel at the pastries made by Chef Ian in the coffee shop in Building 300, chase the geese that make Oracle their home, walk around “Larry Lake” (the old Marine World lagoon), and buy a memento at the Oracle store in Building 500.
Please review the agenda and register now so that we can provide a count for catering. Some free passes are available for first-time NoCOUG conference attendees. If you would like to invite a co-worker (or even your manager) to go to the conference with you, please send an email to email@example.com requesting a free pass.
The quality of the presentations at NoCOUG conferences is truly exceptional. Consider, for example, these exceptional presentations by the eBay/PayPal database engineering team at the last conference.
- Managing a Large OLTP Database (How PayPal copies with the complexities of its Oracle database environment)
- Database Platform Selection Tool (How PayPal decides which database technology is best suited for a particular use case)’
- Sharding Oracle Databases (How to shard your data even if Oracle Database does not have native support for sharding)
But reading a Powerpoint slide deck instead of listening to a presentation is like reading a recipe for chicken curry instead of eating it for lunch. Consider attending the winter conference which is just ten days away on Tuesday, January 27 at the Oracle conference center in Redwood City.
The conference is being held in conjunction with BIWA/Spatial/Graph Summit 2015 so there is a choice of more sessions than at any time in NoCOUG’s 28-year history: five parallel tracks on Data Warehouse, Business Intelligence, Spatial, and Graph topics as well as sessions created especially for NoCOUG attendees including:
- SQL is the best development language for Big Data by Tom Kyte (the patron saint of SQL)
- YesSQL! A celebration of SQL and PL/SQL by Steven Feuerstein (the patron saint of PL/SQL) with cameo appearance by Andy Mendelsohn
- The Data Revolution by Doug Cutting, the creator of Hadoop
- 10 Tips for social media success by Yann Ropars Journal Editor’s Pick
There will also be a networking reception at the end of the day. Click here to review the agenda and register for the conference.
Dear NoCOUG members and friends,
You’re going to enjoy the new issue of the NoCOUG Journal. Click here to download it.
- Lothar Flatz talks about the biggest issue in SQL tuning: the “salted banana.” We predict that the phrase “salted banana” will become as widely known in the Oracle community as “compulsive tuning disorder.” Click here to go directly to Lothar’s article.
- Janis Griffin—a.k.a. the Looney Tuner—explains why the Oracle Database 12c “adaptive optimizer” is the best thing since sliced bread. The Oracle Database 12c query optimizer makes all previous versions of the query optimizer—and the query optimizers of Oracle competitors—seem primitive and foolish in comparison. All previous versions of the query optimizer stick to the plan, even when it is painfully, painfully obvious—after a few records have been retrieved—that the plan is terribly, terribly wrong. But why stick to a full table scan or an index scan if it becomes painfully, painfully obvious—after a few records have been retrieved—that there has been a terrible, terrible miscalculation and that the current plan is terribly, terribly wrong and should be abandoned, even though query execution has already started. If you were looking for a reason to upgrade to Oracle Database 12c, the adaptive optimizer is it. Click here to go directly to Janis’s article.
- Guy Harrison talks up the five biggest trends in database technology. If you read his article, you will be able to respond intelligently when your CIO or enterprise architect asks you for your opinion. Click here to go directly to Guy’s article.
- Iggy Fernandez explains why he doesn’t have a huge problem with the NoSQL fad. According to him, the NoSQL architecture is entirely compatible with the relational model and he points out that RDBMS vendors have already started incorporating these features into their products. Click here to go directly to Iggy’s article.
- Kyle Hailey provides a detailed explanation of the hottest topic in IT: thin provisioning of the entire tech stack—database as well as application. Business is dependent upon IT to deliver required applications and services but these applications and services are dependent upon timely, high-quality refreshes. Using thin provisioning, IT can deliver results faster, quicker, and cheaper. Click here to go directly to Kyle’s article.
Download the latest issue of the NoCOUG Journal.
Renew your 2015 NoCOUG membership.
Register for the winter conference on January 27 featuring Tom Kyte (the patron saint of SQL), Steven Feuerstein (the patron saint of PL/SQL), and Doug Cutting (the patron saint of Hadoop).
Win a free membership for yourself and three friends by correctly identifying the colorful objects on the front cover of the NoCOUG Journal and where the picture was taken.
Forward this message to your colleagues.
The volunteers and board members of the longest running and most active Oracle users group in the world
Dear NoCOUG members and friends,
Happy, Happy, Happy New Year to you and you and you. It’s time to renew your NoCOUG membership. Log into your account at http://nocoug.wildapricot.org/ and click on the Renew button.
Win a free NoCOUG membership for yourself and three friends (a $380 value) by correctly identifying the colorful objects on the front cover of the NoCOUG Journal and where the picture was taken (or making a good guess). Also write a sentence (or two or three) that captures the meaning of the picture and your experiences with NoCOUG; for example, “NoCOUG conferences offer a lot of interesting content. I have attended 112 conferences so far and this will be the 113th.” Post your entry in the comments section for all the world to see. The NoCOUG board will select the best entry. The prize is transferable so feel free to enter if you live in beautiful Denmark. Kim Berg Hansen, that’s you!
Our winter conference will be held on Tuesday, January 27 in conjunction with BIWA/Spatial Summit 2015. Conference attendees will have a choice of more sessions than ever before, with five parallel tracks on Data Warehouse, Business Intelligence, Spatial, and Graph topics as well as sessions created especially for NoCOUG attendees including:
- SQL is the best development language for Big Data by Tom Kyte
- YesSQL! A celebration of SQL and PL/SQL by Steven Feuerstein with cameo appearance by Andy Mendelsohn
- Top 10 tips for social media success by Yann Ropars
- The Data Revolution by Doug Cutting, the esteemed creator of Hadoop
There will also be a networking reception at the end of the day. Review the agenda and register at https://nocoug.wildapricot.org/.
NoCOUG members are also being offered the unprecedented opportunity to attend Day 2 (January 28) and Day 3 (January 29) of BIWA/Spatial 2015 for the low, low price of $75 (regular price $175). Another example of your membership dollars working as hard as they possibly can for you.
The volunteers and board members of the oldest Oracle users group in the world
photo by Tsahi Levent-Levi
by Iggy Fernandez
excerpted from The Twelve Days of NoSQL (http://iggyfernandez.wordpress.com/)
NoSQL and Big Data are “disruptive innovations” in the sense used by Harvard professor Clayton M. Christensen. In The Innovator’s Dilemma: When New Technologies Cause Great Firms to Fail, Christensen defines disruptive innovations and explains how they eventually lead to the failure of the established players who ignored them:
“Generally, disruptive innovations were technologically straightforward, consisting of off-the-shelf components put together in a product architecture that was often simpler than prior approaches. They offered less of what customers in established markets wanted and so could rarely be initially employed there. They offered a different package of attributes valued only in emerging markets remote from, and unimportant to, the mainstream.”
Established players usually ignore disruptive innovations because they do not see them as a threat to their bottom lines. In fact, they are more than happy to abandon the low-margin segments of the market, and their profitability actually increases when they do so. The disruptive technologies eventually take over most of the market and the established players fail.
An example of a disruptive innovation is the personal computer. The personal computer was initially targeted only at the home computing segment of the market. Established manufacturers of mainframe computers and minicomputers did not see PC technology as a threat to their bottom lines. Eventually, however, PC technology came to dominate the market, and established computer manufacturers such as Digital Equipment Corporation, Prime, Wang, Nixdorf, Apollo, and Silicon Graphics went out of business.
So where lies the dilemma? Christensen explains:
“In every company, every day, every year, people are going into senior management, knocking on the door saying: ‘I got a new product for us.’ And some of those entail making better products that you can sell for higher prices to your best customers. A disruptive innovation generally has to cause you to go after new markets, people who aren’t your customers. And the product that you want to sell them is something that is just so much more affordable and simple that your current customers can’t buy it. And so the choice that you have to make is: Should we make better products that we can sell for better profits to our best customers. Or maybe we ought to make worse products that none of our customers would buy that would ruin our margins. What should we do? And that really is the dilemma.”
Exactly in the manner that Christensen described, the e-commerce pioneer Amazon.com created an in-house product called Dynamo in 2007 to meet the performance, scalability, and availability needs of its own e-commerce platform after it concluded that mainstream database management systems were not capable of satisfying those needs. The most notable aspect of Dynamo was the apparent break with the relational model; there was no mention of relations, relational algebra, or SQL.
One of the innovations of the NoSQL camp is “schemaless design.” Data is stored in “blobs” and documents and the NoSQL database management system does not police their structure.
Let’s do a thought experiment. Suppose that we don’t have a schema and let’s suppose that the following facts are known.
- Iggy Fernandez is an employee with EMPLOYEE_ID = 1 and SALARY = $1000.
- Mogens Norgaard is an employee with EMPLOYEE_ID = 2, SALARY = €1000, and COMMISSION_PCT = 25.
- Morten Egan is an employee with EMPLOYEE_ID = 3, SALARY = €1000, and unknown COMMISSION_PCT.
Could we ask the following questions and expect to receive correct answers?
- Question: What is the salary of Iggy Fernandez?
- Correct answer: $1000.
- Question: What is the commission percentage of Iggy Fernandez?
- Correct answer: Invalid question.
- Question: What is the commission percentage of Mogens Norgaard?
- Correct answer: 25%
- Question: What is the commission percentage of Morten Egan?
- Correct answer: Unknown.
If we humans can process the above data and correctly answer the above questions, then surely we can program computers to do so.
The above data could be modeled with the following three relations. It is certainly disruptive to suggest that this be done on the fly by the database management system but not outside the realm of possibility.
EMPLOYEE_ID NOT NULL NUMBER(6)
EMPLOYEE_ID NOT NULL NUMBER(6)
EMPLOYEE_ID NOT NULL NUMBER(6)
A NoSQL company called Hadapt has already stepped forward with such a feature:
“While it is true that SQL requires a schema, it is entirely untrue that the user has to define this schema in advance before query processing. There are many data sets out there, including JSON, XML, and generic key-value data sets that are self-describing—each value is associated with some key that describes what entity attribute this value is associated with [emphasis added]. If these data sets are stored in Hadoop, there is no reason why Hadoop cannot automatically generate a virtual schema against which SQL queries can be issued. And if this is true, users should not be forced to define a schema before using a SQL-on-Hadoop solution—they should be able to effortlessly issue SQL against a schema that was automatically generated for them when data was loaded into Hadoop.
Thanks to the hard work of many people at Hadapt from several different groups, including the science team who developed an initial design of the feature, the engineering team who continued to refine the design and integrate it into Hadapt’s SQL-on-Hadoop solution, and the customer solutions team who worked with early customers to test and collect feedback on the functionality of this feature, this feature is now available in Hadapt.” (http://hadapt.com/blog/2013/10/28/all-sql-on-hadoop-solutions-are-missing-the-point-of-hadoop/)
This is not really new ground. Oracle Database provides the ability to convert XML documents into relational tables (http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb01int.htm#ADXDB0120), though it ought to be possible to view XML data as tables while physically storing it in XML format in order to benefit certain use cases. It should also be possible to redundantly store data in both XML and relational formats in order to benefit other use cases.
In Extending the Database Relational Model to Capture More Meaning, Dr. Codd explains how a “formatted database” arises from an unorganized collection of facts:
“Suppose we think of a database initially as a set of formulas in first-order predicate logic. Further, each formula has no free variables and is in as atomic a form as possible (e.g, A & B would be replaced by the component formulas A, B). Now suppose that most of the formulas are simple assertions of the form Pab…z (where P is a predicate and a, b, … , z are constants), and that the number of distinct predicates in the database is few compared with the number of simple assertions. Such a database is usually called formatted, because the major part of it lends itself to rather regular structuring. One obvious way is to factor out the predicate common to a set of simple assertions and then treat the set as an instance of an n-ary relation and the predicate as the name of the relation.”
In other words, a collection of facts can always be organized into relations if necessary.
Big Data in a Nutshell
In 1998, Sergey Brin and Larry Page invented the PageRank algorithm for ranking web pages (The Anatomy of a Large-Scale Hypertextual Web Search Engine by Brin and Page) and founded Google.
The PageRank algorithm required very large matrix-vector multiplications (Mining of Massive Datasets Ch. 5 by Rajaraman and Ullman) so the MapReduce technique was invented to handle such large computations (MapReduce: Simplified Data Processing on Large Clusters).
Smart people then realized that the MapReduce technique could be used for other classes of problems, and an open-source project called Hadoop was created to popularize the MapReduce technique (http://gigaom.com/2013/03/04/the-history-of-hadoop-from-4-nodes-to-the-future-of-data/).
Other smart people realized that MapReduce could handle the operations of relational algebra such as join, anti-join, semi-join, union, difference, and intersection (Mining of Massive Datasets Ch. 2) and began looking at the possibility of processing large volumes of business data (a.k.a. “Big Data”) better and more cheaply than mainstream database management systems.
Initially programmers had to write Java code for the “mappers” and “reducers” used by MapReduce. However, smart people soon realized that SQL queries could be automatically translated into the necessary Java code, and “SQL-on-Hadoop” was born. Big Data thus became about processing large volumes of business data with SQL but better and more cheaply than mainstream database management systems.
However, the smart people have now realized that MapReduce is not the best solution for low-latency queries (http://gigaom.com/2013/11/06/facebook-open-sources-its-sql-on-hadoop-engine-and-the-web-rejoices/). Big Data has finally become about processing large volumes of business data with SQL but better and more cheaply than mainstream database management systems and with or without MapReduce.
That’s the fast-moving story of Big Data in a nutshell. ▲
The statements and opinions expressed here are the author’s and do not necessarily represent those of Oracle Corporation.
“And so the choice that you have to make is: Should we make better products that we can sell for better profits to our best customers. Or maybe we ought to make worse products that none of our customers would buy that would ruin our margins. What should we do? And that really is the dilemma.”