Posts tagged Database
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 >
Flashback started out as a feature in Oracle Database 9i. Although to be honest it is just the opening up of a mechanism that has been at the core of the Oracle Database from very early on: the ability to have concurrent sessions and transactions and allow transaction rollback and long running queries unaffected by transactions completed after the start of the query are all based on the same mechanism as flashback.
Flashback was great for demos and it was great for administrators. Developers however could not to very much with it. At least not until Oracle Database 11g when the Flashback Data Archive was introduced that provided fine grained control over which tables should have flashback data associated with it and which would not. At the time, the FDA was part of the Advanced Compression Database Option on top of the Enterprise Edition. So:great feature but for the happy few. In addition, there still were two main limitations with Flashback: history starts only at the day when the FDA is created. Nothing from before that day would be available. It’s a bit like the butterfly that does not have any of the memories of the caterpillar. The second limitation: Flashback did not record More >
Oracle Database 12c: quickly create a virtual machine with OEL 6.4 and Oracle Database 12c (for dummies)3
I am first and foremost a developer. I am not an administrator. I know more about IDEs and GUIs than about command line. I have always been a little scared of system administration and non-wizard (next-next-finish) based installations. I am not proud of it. I have other skills and interests. I know how to drive my car but I cannot do engine maintenance. Something like that. Now Oracle has released Oracle Database 12c. And the only way for me to run it today is to install it on Linux. So I have a big impetus to finally get started. First with the creation of a Virtual Machine (VirtualBox) with Oracle Enterprise Linux inside (I wrote about my baby steps in this article http://technology.amis.nl/2013/06/29/how-to-create-a-virtual-box-vm-with-oracle-enterprise-linux-inside-a-dummy-guide/) and next with the installation of the Oracle Database 12c in that machine.
This article describes how I went from a clean Oracle Enterprise Linux 6.4 environment in a VirtualBox VM to a VM with additional hard disk, volume group and logical volume with the Oracle Database 12c up and running. The steps are: (more…)
When a record is no longer active – it is logically discarded – you may want to delete it. However, the record may still be required – for compliancy and auditing reasons for example. Or to produce incidental reports. So actually deleting it may not be an option. In the past I have used approaches such as a VALID_YN column or an END_OF_LIFE Date column to mark rows that were really no longer alive. In my applications and using views or VPD policies I can make sure to exclude such rows. That has to be an explicit act and it is a little cumbersome and non-trivial.
Oracle Database 12c introduced the concept of in-database archiving, which is basically what I described overhead – provided by a standard database mechanism that of course the SQL engine knows how to interpret. It works very much like the VALID_YN column – but of course a little differently. The Oracle Documentations says the following: “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by More >
Oracle 8i (8.1.6 if I remember correctly) started with the introduction of a that wonderful new phenomenon in SQL: Analytical Functions. Functions that basically allow the result for one row in the result set to be calculated using the values in other rows. This allowed to look forward (lag) and back (lead) in result sets as well as calculate aggregates across partitions in every direction. Analytical Functions have helped to produce quite advanced analyses of data using very elegant, compact and surprisingly well performing queries.
With Oracle Database 12c, our SQL just got a little better equipped to perform analysis. The MATCH_RECOGNIZE operator has been introduced – one you may know if you have ever dabbled in CQL, the Continuous Query Language that is at the heart of several complex event processors. This operator goes beyond Analytical Functions in its capabilities to analyze a data set. The comparisons MATCH_RECOGNIZE allows us to make between rows in order to decide whether or not to produce a result are more advanced (and sometimes more fuzzy) than the straightforward comparison operators available with analytical functions. That sounds a little cryptic. Let’s look at More >
Joining is a key ingredient in most SQL queries. Using collections (aka nested tables) that are produced on the fly inside the SQL query or that are returned by a PL/SQL function that is invoked from a TABLE operator in the query is a powerful weapon as well. Oracle Database 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example: