Posts tagged sql
Oracle OpenWorld is a monster event – 10Ks of attendees, thousands of sessions and 100Ks of private conversations that all help convey and define the message about Oracle’s strategy and the roadmap for its close to 4000 thousand products. Concurrent with OOW is the JavaOne conference that – at a slightly smaller scale – does the same thing for the world of the Java platform, the JVM and the Java community.
AMIS each year sends a substantial delegation to attend and contribute to the conference. We speak in many sessions, ask questions in even more and do our utmost to gather information, digest it and distill the real meaning and relevant details. We have just completed our yearly review of the Oracle OpenWorld and JavaOne 2013 conferences: a 60-page PDF document that answers the ultimate question about life, the universe and everything [Oracle].
This white paper describes the major transitions that the industry at large and Oracle in specific is going through. It explains what these transitions mean to Oracle, cause in terms of product evolution and will result in for the users. The major product announcements are listed and commented on. The roadmaps for the most relevant More >
The accuracy, internal quality, and reliability of data is frequently referred to using the term ‘data integrity’. Without it, data is less valuable or even useless. This session takes a close look at what data integrity entails and how it can be enforced in multi-tier application architectures using distributed data sources and global transactions. The discussion will make clear which elements are required from any robust implementation of data oriented business rules aka data constraints and it will explain how most existing solutions are not as watertight as is frequently assumed. Steps for achieving reliable constraint enforcement are demonstrated.
The presentation I did last week for the JFall 2013 conference can be checked on SlideShare:
One of my colleagues had an interesting challenge. In an Oracle Database he has a table that contains an XMLType column. The column contains XML documents with people collections. This data is retrieved and transformed inside the database using an XSLT stylesheet, to a format – for example XHTML – that can be served directly to an end user in a browser. However, the XMLType contains a country code element where the displayed data should show the name of the country. There is a lookup table that contains the country data; this table can be used to enrich the data in the XMLType. The question now was: what is the best moment in the processing pipeline (query => transform => output) to perform this enrichment. The definition of ‘best’ should include performance, scalability, programming effort and (infrastructural) complexity.
One of the options we discussed was enriching in the first stage of the pipeline, as part of the retrieval step. This would be done using an XQuery operation against the XMLType, producing another [enriched] XMLType that would be fed into the XSLT transformation. This article shows how that could be done. Using the experienced insights of my colleague Marco More >
Part of the evolution of Java in release 8 consists of Lambda expressions. These ‘functional expressions treated as variables’ introduce a powerful Inversion of Control in Java – allowing a clear and elegant distinction between the what [should be done] and how [should it be done]. The Collection APIs have been extended with the notion of Streams to make great use of these lambda expressions. This article shows some examples of what this means, leading up to the revelation that under certain circumstances Java is very similar to SQL.
A Stream is an interface. It is a “[…] potentially infinite sequence of elements. A stream is a consumable data structure. The elements of the stream are available for consumption by either iteration or an operation. Once consumed the elements are no longer available from the stream.” Any collection – as well as several other sources – can be exposed as a Stream. On such as stream, a number of operations – aka a pipeline – can be performed. These operations are either intermediate or terminal:
- intermediate – such as map, filter, sorted, distinct, limit, skip, substream, concat that produce a stream from a stream
- terminal – such as forEach, reduce, More >
In a recent post (http://technology.amis.nl/2013/07/24/oracle-database-12c-find-most-valuable-player-using-match_recognize-in-sql/) I described how we can use the new Oracle Database 12c MATCH_RECOGNIZE operator to spot patterns in records and derive results from those patterns. I used the MATCH_RECOGNIZE to find the most valuable player in a football team (US readers: I mean the sports that you may refer to as Soccer). My definition of the MVP is the player who is most frequently part of a period of uninterrupted ball possession ending with a goal. Whether the play scores the goal, presents the assist or is involved earlier on does not matter: if he was part of the play leading up to the goal – we count the contribution. However, the ball possession ends when a player from the other team has possession of the ball.
On closer inspection, this is a type of pattern that we can also find using a Recursive Subquery – albeit not as elegantly and presumably not as well performing.
One of the very nice new features in SQL in Oracle Database 12c is the MATCH_RECOGNIZE operator. MATCH_RECOGNIZE adds a facility for spotting patterns in records. This allows us to locate records that are part of some kind of pattern relative to other records. It does sound similar to what Analytic Functions – most notably LAG and LEAD can do – but it is different. LAG and LEAD allow you to calculate the result of a record based on other records in the result set – but you are very limited in the ways in which you can indicate which other records in the result set are to be referenced by LAG and LEAD. Typically, it will be a fixed number of records before or after the record itself.
MATCH_RECOGNIZE allows us to have the database find a pattern – a regular expression expressed in terms of row conditions – in a far more flexible, dynamic and almost fuzzy way.
In this article, I will use this functionality to find the most valuable player in a football team (US readers: I mean the sports that you may refer to as Soccer). My definition of the MVP is the player who is most frequently part of a period of uninterrupted ball possession ending with a goal. Whether the play scores the goal, More >