Archive for the ‘NoSQL’ Category

Editor’s Choice award at ODTUG Kscope14: NoSQL and Big Data for the Oracle Professional

My paper on NoSQL and Big Data won the Editor’s Choice award at ODTUG Kscope14. Here are some key points from the paper:

  • The relational camp made serious mistakes that limited the performance and usefulness of the relational model.
  • NoSQL is based on the incorrect premise that tables in the relational model must be mapped to separate buckets of physical storage.
  • All the innovations of the NoSQL camp could have been implemented within a relational framework.

Click here to download the paper. Most of it is based on my blog posts.



Categories: Big Data, DBA, NoSQL Tags: , ,

The Twelve Days of NoSQL: Day Twelve: Concluding Remarks

January 6, 2014 1 comment

Day One: Disruptive Innovation
Day Two: Requirements and Assumptions
Day Three: Functional Segmentation
Day Four: Sharding
Day Five: Replication and Eventual Consistency
Day Six: The False Premise of NoSQL
Day Seven: Schemaless Design
Day Eight: Oracle NoSQL Database
Day Nine: NoSQL Taxonomy
Day Ten: Big Data
Day Eleven: Mistakes of the relational camp
Day Twelve: Concluding Remarks

On the twelfth day of Christmas, my true love gave to me
Twelve drummers drumming.

Poster by Xavier Romero-Frias

Poster by Xavier Romero-Frias

The relational camp put productivity, ease-of-use, and logical elegance front and center. However, the mistakes and misconceptions of the relational camp prevent mainstream database management systems from achieving the performance levels required by modern applications. For example, Dr. Codd forbade nested relations (a.k.a.unnormalized relations) and mainstream database management systems equate the normalized set with the stored set.

The NoSQL camp on the other hand put performance, scalability, and reliability front and center. Understandably the NoSQL camp could not see past the mistakes and misconceptions of the relational camp and lost the opportunity to take the relational model to the next level. Just like the relational camp, the NoSQL camp believes that normalization dictates physical storage choices. Just like the relational camp, the NoSQL camp believes that non-relational APIs are forbidden by the relational model. And the NoSQL camp believes that relational is synonomous with ACID (Atomicity, Consistency, Isolation, Durability).

The NoSQL camp created a number of innovations that are disruptive in the sense used by Harvard Business School professor Clayton Christensen: functional segmentation, sharding, replication, eventual consistency, and schemaless design. Since these innovations are compatible with the relational model, I hope that they will eventually be absorbed by mainstream database management systems.

There are already proofs that performance, scalability, and reliability can be achieved without abandoning the relational model. For example, ScaleBase provides sharding and replication on top of MySQL storage nodes. Another good example to study is VoltDB which claims to be the world’s fastest OLTP database (though it has never published an audited TPC benchmark). A counter-example to Amazon is eBay which arguably has equal scale and equally high performance, scalability, and reliability requirements. eBay uses performance segmentation, sharding, replication, and eventual consistency but continues to use Oracle (and SQL) to manage the local database. I asked Randy Shoup, one of the architects of the eBay e-commerce platform, why eBay did not abandon Oracle Database and he answered in one word: “comfort.” Here are links to some of his presentations and articles on the eBay architecture:

Finally, I should point out that are very good reasons to criticize current NoSQL products; for example, lack of standards, primitive feature sets, primitive security, and primitive management tools, unproven claims, and traps for the unwary. MongoDB uses a database-wide lock for reads and writes …

I hope that you enjoyed reading this series of posts as much as I enjoyed writing it. Happy new year!

Also see: The Twelve Days of SQL: Day Twelve: Readers do not block writers; writers do not block readers

The Twelve Days of NoSQL: Day Eleven: Mistakes of the relational camp

January 5, 2014 1 comment

On the eleventh day of Christmas, my true love gave to me
Eleven pipers piping.

(Yesterday: Big Data in a Nutshell)(Tomorrow: Concluding Remarks)

Over a lifespan of four and a half decades, the relational camp made a series of strategic mistakes that made NoSQL possible. The mistakes started very early. The biggest mistake is enshrined in the first sentence of the first paper on relational theory by none other than its inventor, Dr. Edgar Codd: “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)

How likely is it that application developers will develop scalable high-performance applications if they are shielded from the internal representation of data? SQL was never intended for serious application development. As explained by the creators of SQL 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, [SQL] emphasizes simple data structures and operations [emphasis added].” (

A case in point: 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 yet it is chosen 99.9% of the time.

Productivity and ease-of-use were the goals of the relational model, not performance. In Normalized Data Base Structure: A Brief Tutorial (1971), Dr. Codd said “What is less understandable is the trend toward more and more complexity in the data structures with which application programmers and terminal users directly interact. Surely, in the choice of logical data structures that a system is to support, there is one consideration of absolutely paramount importance – and that is the convenience of the majority of users. … To make formatted data bases readily accessible to users (especially casual users) who have little or no training in programming we must provide the simplest possible data structures and almost natural language. … What could be a simpler, more universally needed, and more universally understood data structure than a table?”

Dr. Codd emphasized the productivity benefits of the relational model in his acceptance speech for the 1981 Turing Award: It is well known that the growth in demands from end users for new applications is outstripping the capability of data processing departments to implement the corresponding application programs. There are two complementary approaches to attacking this problem (and both approaches are needed): one is to put end users into direct touch with the information stored in computers; the other is to increase the productivity of data processing professionals in the development of application programs. It is less well known that a single technology, relational database management, provides a practical foundation to both approaches.”

The emphasis on productivity and ease of use at the expense of performance was the biggest mistake of the relational camp.

Mistake #2: Forbidding nested relations

Dr. Codd made a second serious mistake in his 1970 paper by forbidding nested relations in the interests of productivity and ease-of-use. He incorrectly argued (in an unpublished version of that paper) that nested relations would mean that “The second-order predicate calculus (rather than first-order) is needed because the domains on which relations are defined may themselves have relations as elements.” Not anticipating markup languages like XML, he also argued that The simplicity of the array representation which becomes feasible when all relations are cast in normal form is not only an advantage for storage purposes but also for communication of bulk data between systems which use widely different representations of the data.” This caused the detractors of the relational model to observe 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.” (incorrectly attributed to Esther Dyson, the editor of Release 1.0).

Even though he made a serious mistake by forbidding nested relations, Dr. Codd never said that

  • data should be stored in normalized form only;
  • each stored table should occupy a separate storage bucket;
  • a single data block should only contain data from a single table;
  • data should be stored in row-major order; and
  • stored tables have only one storage representation each.

Oracle Database has limited support for nested tables but they are not stored inline, they are “collections” not real tables, and you cannot define primary-key or foreign-key constraints on them.

Mistake #3: Favoring relational calculus over relational algebra

At the heart of the relational model are the operations of the “relational algebra”: equi-join, theta-join, outer-join, semi-join, anti-join, union, difference, intersection, etc. In the interests of productivity and ease-of-use however, Dr. Codd favored “relational calculus” over relational algebra. In Relational Completeness of Data Base Sublanguages, he proved the equivalence of relational calculus and relational algebra. In the interests of productivity and ease-of-use, Codd then made the decision for you and me: “Clearly, the majority of users should not have to learn either the relational calculus or algebra in order to interact with data bases. However, requesting 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.” SQL was therefore based on relational calculus instead of relational algebra and it became the job of the database management system to convert SQL statements into equivalent sequences of relational algebra operations. This is not a trivial problem. It is an extremely hard problem. But the relational camp is adamant that the optimizer always knows best (it doesn’t).

Mistake #4: Equating the normalized set with the stored set

Equating the normalized set with the stored set ensures that mainstream database management systems will be permanently handicapped in the performance race. For example, a nested relation can be normalized into “first normal form” (no nested relations). As another example, if B and C are colunm-subsets of A and A = B EQUIJOIN C, then we can perform a “lossless decomposition” of A into B and C. However, the relational model does not require that each normalized relation be mapped to a separate bucket of physical storage. Dr. Codd differentiated between the “stored” set, the “named” set, and the “expressible” set but mainstream database management systems conflate the stored set with the normalized set. Mainstream database management systems only allow integrity constraints to be specified on the stored set and only permit DML operations on the stored set. If B and C are colunm-subsets of A and A = B EQUIJOIN C, then it ought to be possible to store A only while defining primary and foreign key constraints on B and C and allowing DML operations on B and C but this is not permitted by mainstream database management systems.

Mistake #5: Incomplete implementation

Strange as it may sound, the relational model has not yet been fully implemented by mainstream database management systems. You’ve probably heard of Codd’s “twelve rules” published in a 1985 Computerworld article. More than a quarter-century later, mainstream database management systems still do not follow all the twelve rules. DML operations are still not permitted on views (Rule 6 and Rule 7). Arbitrary integrity constraints still cannot be declared and enforced (Rule 10). Integrity constraints still cannot span separate databases (Rule 11). If would be hard to abandon a database management systems that had these capabilities. It is easier to abandon a database management system that does not have these capabilities.

Mistake #6: The marriage of relational and transactional

ACID transactions nothing to do with the relational model per se although relational theory does come in very handy in defining consistency constraints. (See What’s so sacred about relational anyway?) Pre-relational database management systems such as IMS provided ACID guarantees and so did post-relational object-oriented database management systems. We should be able to store non-transactional data outside a transactional database management system while continuing to exploit the entire spectrum of indexing, partitioning, and clustering techniques. See We don’t use databases; we don’t use indexes.

Also see: The Twelve Days of SQL: Day Eleven: AWR and Statspack are a goldmine of historical performance data

Categories: DBA, NoSQL, Oracle, SQL Tags: , , ,

The Twelve Days of NoSQL: Day Ten: Big Data in a Nutshell

January 4, 2014 2 comments

On the tenth day of Christmas, my true love gave to me
Ten lords a-leaping.

(Yesterday: NoSQL Taxonomy)(Tomorrow: Mistakes of the relational camp)

The topic of Big Data is often brought up in NoSQL discussions so let’s give it a nod. 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 (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 by Rajaraman and Ullman) and began looking at the possibility of processing large volumes of business data (a.k.a. “Big Data”) better and cheaper 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 cheaper than mainstream database management systems. However, the smart people have now realized that MapReduce is not the best solution for low-latency queries (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 cheaper than mainstream database management systems and with or without MapReduce.

That’s the fast-moving story of Big Data in a nutshell.

Also see: The Twelve Days of SQL: Day Ten: Sometimes the optimizer needs a hint

The Twelve Days of NoSQL: Day Nine: NoSQL Taxonomy

On the ninth day of Christmas, my true love gave to me
Nine ladies dancing.

(Yesterday: Oracle NoSQL Database)(Tomorrow: Big Data in a Nutshell)

NoSQL databases can be classified into the following categories:

  • Key-value stores: The archetype is Amazon Dynamo of which DynamoDB is the commercial successor. Key-value stores basically allow applications to “put” and “get” values but each product has differentiators. For example, DynamoDB supports “tables” (namespaces) while Oracle NoSQL Database offers “major” and “minor” key paths.
  • Document stores: While key-value stores treat values as uninterpreted strings, document stores allow values to be managed using formats such as JSON (JavaScript Object Notation) which are conceptually similar to XML. This allows key-value pairs to be indexed by any component of the value just as XML data can be indexed in mainstream database management systems.
  • Column-family stores: Column-family stores allow data associated with a single key to be spread over multiple storage nodes. Each storage node only stores a subset of the data associated with the key; hence the name “column-family.” A key is therefore composed of a “row key” and a “column key” in a manner analogous to the major and minor key paths of Oracle NoSQL Database.
  • Graph databases: Graph databases are non-relational databases that use graph concepts such as nodes and edges to solve certain classes of problems: for example; the shortest route between two towns on a map. The concepts of functional segmentation, sharding, replication, eventual consistency, and schemaless design do not apply to graph databases so I will not discuss graph databases.

NoSQL products are numerous and rapidly evolving. There is a crying need for a continuously updated encyclopedia of NoSQL products but none exists. There is a crying need for an independent benchmarking organization but none exists. My best advice is to do a proof of concept (POC) as well as a PSR (Performance Scalability Reliability) test before committing to using a NoSQL product. Back in the day, in 1985 to be precise, Dr. Codd had words of advice for those who were debating between the new relational products and the established pre-relational products of his day. The advice is as solid today as it was in Dr. Codd’s day.

“Any buyer confronted with the decision of which DBMS to acquire should weigh three factors heavily.

The first factor is the buyer’s performance requirements, often expressed in terms of the number of transactions that must be executed per second. The average complexity of each transaction is also an important consideration. Only if the performance requirements are extremely severe should buyers rule out present relational DBMS products on this basis. Even then buyers should design performance tests of their own, rather than rely on vendor-designed tests or vendor-declared strategies. [emphasis added]

The second factor is reduced costs for developing new databases and new application programs …

The third factor is protecting future investments in application programs by acquiring a DBMS with a solid theoretical foundation …

In every case, a relational DBMS wins on factors two and three. In many cases, it can win on factor one also—in spite of all the myths about performance.”

—An Evaluation Scheme for Database Management Systems that are claimed to be Relational

Also see: The Twelve Days of SQL: Day Nine: Physical database design matters

Categories: DBA, NoSQL, Oracle, SQL, Uncategorized Tags: , , ,

The Twelve Days of NoSQL: Day Eight: Oracle NoSQL Database

On the eighth day of Christmas, my true love gave to me
Eight maids a-milking.

(Yesterday: Schemaless Design)(Tomorrow: NoSQL Taxonomy)

In May 2011, Oracle Corporation published a scathing indictment of NoSQL, the last words being “Go for the tried and true path. Don’t be risking your data on NoSQL databases.” Just a few months later however, in September of that year, Oracle Corporation released Oracle NoSQL Database. Oracle removed the NoSQL criticism from its website but since information published on the internet is immortal, archived copies can be easily found if you know what you are looking for. In the white paper that accompanied the release of Oracle NoSQL Database, Oracle Corporation claimed that the demands of certain applications could not be met by mainstream database management systems:

“The Oracle NoSQL Database, with its “No Single Point of Failure” architecture, is the right solution when data access is “simple” in nature and application demands exceed the volume or latency capability of traditional data management solutions. For example, click-stream data from high volume web sites, high-throughput event processing and social networking communications all represent application domains that produce extraordinary volumes of simple keyed data. Monitoring online retail behavior, accessing customer profiles, pulling up appropriate customer ads and storing and forwarding real-time communication are examples of domains requiring the ultimate in low-latency access. Highly distributed applications such as real-time sensor aggregation and scalable authentication also represent domains well-suited to Oracle NoSQL Database.”

Oracle NoSQL Database has two features that distinguish it from other key-value stores.

  • A key is the concatenation of a “major key path” and a “minor key path.” All records with the same “major key path” will be colocated on the same storage node.
  • Oracle NoSQL provides transactional support for modifying multiple records with the same major key path.

Here are some resources to get you started with Oracle NoSQL Database:

  • The white paper on Oracle NoSQL Database v2.0; an updated version of the original September 2011 paper.
  • Installation instructions and a five-minute quickstart for Oracle NoSQL Database are at It includes a “Hello World” teaching example illustrating the “put” and “get” function calls which are the basic operations in key-value stores.
final String keyString = "Hello";
final String valueString = "Big Data World!";
store.put(Key.createKey(keyString), Value.createValue(valueString.getBytes()));
final ValueVersion valueVersion = store.get(Key.createKey(keyString));
System.out.println(keyString + " " + new String(valueVersion.getValue().getValue()));
  • The Oracle NoSQL Database resource page on the Oracle Corporation website.

Also see: The Twelve Days of SQL: Day Eight: Statistics are a double-edged sword

Categories: DBA, NoSQL, Oracle Tags: , ,

The Twelve Days of NoSQL: Day Seven: Schemaless Design

December 31, 2013 Leave a comment

On the seventh day of Christmas, my true love gave to me
Seven swans a-swimming.

(Yesterday: The False Premise of NoSQL)(Tomorrow: Oracle NoSQL Database)

As we discussed on Day One, NoSQL consists of “disruptive innovations” that are gaining steam and moving upmarket. So far, we have discussed functional segmentation (the pivotal innovation), sharding, asynchronous replication, eventual consistency (resulting from lack of distributed transactions across functional segments and from asynchronous replication), and blobs.

The final innovation of the NoSQL camp is “schemaless design.” In database management systems of the NoSQL kind, data is stored in “blobs” and documents the database management system does not police their structure. In mainstream database management systems on the other hand, doctrinal purity requires that the schema be designed before data is inserted. 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 a commissioned employee with EMPLOYEE_ID=2, SALARY=€1000, and COMMISSION_PCT=25.
  • Morten Egan is a commissioned 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.




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.” (

This is not really new ground. Oracle Database provides the ability to convert XML documents into relational tables ( 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” is created from a 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.

Also see: The Twelve Days of SQL: Day Seven: EXPLAIN PLAN lies

%d bloggers like this: