The Hitchhiker’s Guide to the EXPLAIN PLAN Part 38: Utilizing Shakespearean wisdom in database upgrades

“ Those friends thou hast, and their adoption tried, Grapple them unto thy soul with hoops of steel; But do not dull thy palm with entertainment Of each new-hatch'd, unfledg'd comrade.” —the advice of Polonius, counselor to King Claudius, to his son Laertes who is leaving for France in Act 1, Scene 3 of The Tragedy of Hamlet, Prince of Denmark by William Shakespeare Every new version of Oracle Database comes—this should be no (more...)

OT: How to interview an Oracle DBA candidate (NOT)

Yesterday T.J. wrote on the Oracle-L mailing list : “ For those of you who have conducted job interviews, what sort of questions have you found to be effective in evaluating a candidate’s skill level?” I remember the interview at which I could not answer the question “how do you enable block change tracking in a database?” (The answer is ALTER DATABASE ENABLE BLOCK CHANGE TRACKING.) I no longer want to (more...)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part III)

Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge . Chris Goerg submitted the following MODEL-clause solution. The use of two measures is the key. with d as ( select unique extract(month from dateofbirth) m , extract(day from dateofbirth) d from dates ) select m, d from ( select * from d model dimension by (m,d) measures (0 cd, 0 cm) rules ( -- mark the unique...(read more)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part II)

Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge . The NoCOUG challenge does not come with a fixed data set such as the 10 dates in the Cheryl’s Birthday problem. Instead contestants are required to solve the problem for all data sets that fit the story. CREATE TABLE DateOfBirth (DateOfBirth DATE NOT NULL); Perhaps because of familiarity with the Cheryl’s...(read more)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part I)

Update on the Fourth International NoCOUG SQL Challenge in progress at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge.

The previous NoCOUG SQL challenges (First, Second, Third) were about devising clever algorithms and then trying to code them in a language (SQL) that was not designed for implementing algorithms. The fourth challenge is more about translating a functional specification into relational algebra or calculus  so it’s closer to what SQL is all about.

The fourth challenge (more...)

OT: Get your next job through Twitter?

From Gwen Shapira’s interview in the May 2015 issue of the NoCOUG Journal : “I got my current job at Cloudera through Twitter. At the time, I was working with a retailer in Japan, as a consultant through Pythian. I was there to help them migrate to Exadata, but their data warehouse system was tightly integrated with Hadoop, and I got to work on this integration too. I got a lot of exposure to Cloudera’s (more...)

Fourth International NoCOUG SQL Challenge (teaser announcement)

The Fourth International NoCOUG SQL Challenge will be published in the May issue of the NoCOUG Journal which will be available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf at 9 AM PDT on Monday, May 11 . Here's a sneak preview: How Romeo Won the Heart of Juliet Fourth International NoCOUG SQL Challenge Once upon a time, Romeo, the son of Montague, told his cousin Benvolio that he was in love with Rosaline but she was not returning (more...)

Eventual Consistency is NOT a violation of the relational model (says C. J. Date)

C. J. Date was kind enough to comment on my article in the last issue (February 2015) of the NoCOUG Journal (see The Rise and Fall of the NoSQL Empire ). The full text of his remarks will be published in the next issue (May 2015). Here’s a sneak preview: First, to say that a database (distributed or otherwise) is consistent merely means, formally speaking, that the database conforms to all stated integrity constraints. Now, (more...)

The Rise and Fall of the NoSQL Empire (2007–2013)

The NoSQL camp put performance, scalability, and reliability front and center but lost the opportunity to take the relational model to the next level because—just like the relational camp—it mistakenly believed that normalization dictates physical storage choices, that non-relational APIs are forbidden by the relational model, and that “relational” is synonymous with ACID (Atomicity, Consistency, Isolation, and Durability). The NoSQL camp created a number of innovations: functional segmentation, sharding, replication, eventual consistency, and schemaless design. (more...)

OT: New Oracle Database features spell doom and gloom for NoSQL

Oracle Corporation has released a slew of new features that allow Oracle Database implementations to catch up to, match up to, and leave in the dust NoSQL implementations in the areas of performance, scalability, and reliability (PSR).(read more)

OT: Oracle wants your votes

It used to be that if you had an enhancement suggestion for Oracle Database, you had to file an enhancement request in MOS and pray that the Oracle product management gods would hear your request someday. It is now possible to make your enhancement request in a public forum and let the community vote on your idea.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 37: POISED: A problem-solving method

P.O.I.S.E.D. is the acronym I invented for the performance tuning method described by Chris Lawson in The Art and Science of Oracle Performance Tuning; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. The Observation and Interpretation phases feed each other. The Interpretation phase concludes with a determination of root cause. If we are investigating poor performance a single SQL statement, the tool of choice for the observation phase (more...)

OT: Never buy an Oracle book again (if you live in California)

If you are a resident of California, you can become a member of the San Jose Public Library and gain access to Safari Books Online and Books24x7. At last count, there were 445 Oracle titles available through Safari Books Online(read more)

OT: A software tool that improves my writing

I would like a little more help with my writing than the Spelling & Grammar check in Microsoft Word can provide so I am checking out a tool called Word Rake.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns

The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query. This antipattern has two sisters who are almost as evil. These two sisters arise from the requirement to paginate through the records retrieved by a query.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 35: Robust v/s optimal query execution plans

A subset of all possible execution plans can be described as robust. While such solutions are not always quite optimum, they are almost always close to optimum in real-world queries, and they have desirable characteristics, such as predictability and low likelihood of errors during execution.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 34: SQL Aaargh!

The biggest problem with SQL query optimization is that semantically equivalent SQL queries are not guaranteed to perform equally well. When I encounter problems like this, I sympathize with the folks who got frustrated with RDBMS performance and created NoSQL.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 33: The mother of all SQL antipatterns?

The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query.(read more)

OT: Pictures from the NoCOUG winter conference at Oracle HQ

There were 130 attendees at the Oracle conference center on January 27 at the Oracle conference center in Redwood City. There was a fantastic breakfast spread. The registration desk was a marvel of neatness. Delphix sent its most photogenic representative. And so did Cloudera. Andy Mendelsohn’s body language says it all: “Oracle is king of the hill and we’re planning to stay here for a long time.” Andy started at Oracle as a junior (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 32: Pretzels created by someone experimenting with hallucinogens?

In an interview for the NoCOUG Journal, Steven Feuerstein said: “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.” As if to prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.(read more)