How Oracle Database uses internal locks to make statement level constraint validation robust at the transaction level0
Data Constraints are essential in protecting the integrity of the data in any relational database. The Oracle Database provides four types of declarative constraints that help implement various types of data rules. These are: Primary Key, Unique Key, Foreign Key and Check Constraint. Although these constraints can be configured to be enforced at transaction time (when the transaction is committed) by setting them to be deferred, the default behavior of the constraints is to enforce the integrity rule at statement level. That means that when a transaction performs multiple DML statements, the constraints are validated during execution of the statement. When the statement is done, the integrity is ensured (otherwise the statement would have failed) and additional statements can be executed.
In the multi session environment that is the Oracle Database, you could be wondering – as was I – how the Oracle Database ensures that other sessions executing DML operations can not undermine the integrity of the data touched by the current transaction. And how it can do so without needlessly preventing other sessions to perform data manipulation using various forms of locking.
It turns out that More >
ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC0
The challenge I will address in this article is the following: I would like to provide a nice presentation of data aggregated by time period. For example: an overview of the number of employees that was hired in each year in each department (example is drawn from table EMP). The presentation could look like this:
To extend the challenge a little bit: I would like to be able to drill down. From the year level shown in this picture, to the Quarter level and even to the Month level. The Quarter level would look similar – but more fine grained:
This article shows how this challenge can be addressed using ADF DVT – Data Visualization components, more specially the Resource Utilization Gantt Chart. It will describe how ADF BC is used in conjunction with the SQL TRUNC function and a smart bind parameter to allow for dynamic zooming to different time aggregation levels. And the approach demonstrated in this article can easily be reused for other time based presentations.
While working on a Web application that required some page to report data aggregated on several levels of time period – by month, quarter or year – I ran into a very handy way to use the TRUNC function. I was not aware of this usage until now – and since I make it a point to encourage my colleagues to write a blog article on anything new they uncover – no matter how trivial it may seem or how strong the feeling that surely everyone already knows about that – I must play by my own rules and tell you about it.
The challenge I was facing can easily be translated into this question: write a SQL query that reports on the number of employees hired per month (or quarter or year). In fact, use a bind parameter that drives the aggregation level – i.e. the Month, Quarter or Year.
Taking the well known EMP table as the target system, this challenge means that all employees hired in the same Month (or Quarter or Year) should be aggregated in the same group. That in turn means that the aggregation should take place by the MONTH (or QUARTER or YEAR) component of their HIREDATE. Instead of fiddling with TO_CHAR using different date identifiers (which also have been an option) it turns out to be More >
On February 13th I will be giving a webinar on Edition Based Redefinition hosted by Red Gate Software.I will be talking about how EBR can be used. It will not be an in-depth session, rather an overview of how EBR works or actually what you should do so you can use it. I am aiming for a presentation of about 30 minutes. This should leave some time to try and answer some questions.
Van 3 tot en met 7 maart vindt in Irving, Texas, het internationale Oracle performance Hotsos Symposium plaats. Dit jaar belooft het symposium een garantstelling voor inhoudelijk hoogstaande presentaties en discussies, want naast presentaties van Tom Kyte, Cary Millsap, Maria Colgan en Steven Feuerstein over performance, worden er ook onderwerpen behandeld zoals Big Data, noSQL, XML, Statistische toepassingen met betrekking tot performance, beheer in de (Oracle) Cloud, Exadata en Oracle 12c onderwerpen.
Het event “Hotsos Revisited 2013″ gaat in de herhaling op 2 april met de presentaties van de Nederlandse sprekers… (more…)
The Oracle ACE directors and Java champions are organizing an evangelist event called ‘OTNYathra 2013’ between 15th & 27th February 2013. The event is sponsored by the Oracle Technology Network. I will be one of the speakers on this tour – presenting two or three presentations at each of the six host cities for this tour:
Other speakers on this tour will cover topics such as ADF, LDAP, OVM, Oracle Enterprise Linux, RAC and Clusterware, BigData, Essbase and OBIEE, APEX, EPM and Hyperion. Speakers include Java Champion and Oracle ACEs and ACE Directors such as Harshad Oak, Hans Forbrich, Edward Roske, Murali Vallath, Vivek Sharma.