Oracle OpenWorld 2014 – Datatype context…?!

The native JSON database functionality presentations are done. If you want to experience first hand what you can do with those new operators during Oracle OpenWorld this year, only the demo booth (search for JSON Document Store demo) or the Hands-on Lab’s, tomorrow or on Thursday, will be an option.

As a performance geek, first and foremost, database administrator, data driven nut and XMLDB hobbyist (aka a great domain for performance problems), I keep wondering if the current JSON approach will support future needs. Currently (see the presentation on this site for more details) among others VARCHAR2, CLOB are supported. Datatypes initially created with strings in mind.

We all know, or at least the development team knows, that for example XMLType CLOB stored XML is only good for document driven handling of the stored data when put in the context of performance. The moment content driven data handling is required (almost always), like “I only want value X”, in the case of XML, DOM based or Infoset methods kick in. In the case of strings as is, string comparison, find, replace, update methods are most of the time extremely resource intensive.

One of the methods in the XML string realm of the database was to, if stored, containerize the problem in such a way, via Object Relational XMLType or Post-Parse Binary XML, that piece wise updates or selectivity could be optimized by compartmentalize the performance / resource consumption issue to only the bits and pieces that actually had to be dealt with.

There is a reason why XMLType CLOB is deprecated nowadays and replaced as the default XMLType storage type since It almost kicked the XMLDB team in the but. All initial examples out there where based on this storage type and when actually later on implemented in production by everyone with probably “mixed” performance results at most.

Another lesson learned was that introducing a new datatype is not that easy and took multiple database versions to perfect and implement, if not only seen at a compatibility viewpoint with all functionality and tools outside and inside the database. Mistakes were made, but also solved over the years and I really wonder if there wasn’t a way to, for example, reuse the underlying code for XMLType into a JSON aware datatype (masking the fact that it is an XMLType internally). The approach “stepping on the shoulder of giants” (reuse good stuff and improve) might be more successful than keep on par with the rest of the world.

That said…Oracle has currently only made the first steps in building completely new operators for JSON and a JSON Path language. There is still a lot coming up in future releases. Need for PLSQL support is one I can think of, or generating JSON from relational content and also support for binary JSON formats. Hopefully the latter, guessing based on Securefile like Binary XML, will give us more effective content driven methods, that is applying a kind of post-parse JSON method before pushing the content into Securefile storage.

Maybe I am spoiled being able to handle single 100+ MB XML files, but I guess that it won’t take long for someone asks on the forum why his 10MB JSON file (stored in CLOB) is so horrible slow regarding performance while updating “only” a few values. The grumpy old men in me might reply, at that future time: “Why not use XML? It takes a little bit of time thinking about design, but it will be faster”, especially when figuring out that his “dates” are stored in a varchar2(35) column anyway.

Let’s see what the future brings. Time for a beer, I think. Still a lot to see, do and learn. Oracle OpenWorld 2014 is not yet “a wrap”.


Marco Gralike Written by: