The previous installment of this series examined aggregate subquery removal and subquery coalescing, describing the latter as similar in some ways to an inverse for “Or Expansion” and “Join Factorization”. In this instalment, it’s time to take a closer look at Or Expansion and we’ll move on to Join Factorization in the next instalment.… Read more
Oracle has built-in functionality to analyze text. Sanil Mhatre demonstrates how to perform a sentiment analysis on data stored in a table using Oracle Text.… Read more
Oracle added several enhancements to the sequence object in version 12c, including an “identity” column feature. Jonathan Lewis explains how to work with these new features. … Read more
This article was originally published on mikesmithers.wordpress.com. “You can’t have your cake and eat it!” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations. They also seem to be a bit intolerant of “cherry picking”. I’ve never really understood the saying, “You can’t have your cake and eat it” – … Read more
Unit testing and even Test-Driven Development is possible for databases. In this article Samuel Nitsche makes the case for introducing unit tests to your database development, and suggests some lightweight ways to get started.… Read more
Most development teams have agreed upon some sort of naming convention as it typically increases the readability and comprehension of the source code. Defining these rules is one thing, but correctly and consistently applying them is another. Wouldn’t it be great if we were able to actually validate whether our team-specific naming rules have been applied the right way?… Read more
There’s a possibility you’ve parachuted into this series on JSON and Oracle at this point. If you are already familiar with what JSON is and how it is created, and are only interested in discovering how to wrestle JSON data from an Oracle database, then you’re welcome, grab a seat. However, if you see the words JSON and Oracle, and… Read more
This is the third instalment in a series of articles that I’m hoping will serve as a primer for Oracle developers interested in learning about Xquery. And – confession time – I’ve left the best part for last. However, you really should start with Part 1. Good things come to those who wait. In the first article we talked about… Read more
In this series of articles I’m hoping to provide a primer for newish Oracle developers who are curious about XQuery and are looking to dip their toes into its world. If that’s you, hi, nice to meetcha. Or rather, nice to meetcha again. This is the second article in the series; go back and read the first if you haven’t… Read more
In the first part of this series I introduced you to the analytic functions family, outlined its close relationship to aggregate functions, and illustrated my points with a few examples. I demonstrated how, by clever use of the analytic function clauses – partition by, order by, and the windowing clause – you could tune your functions to wring even mor… Read more
I use Enterprise Manager Cloud Control to mask Oracle databases so they can be shared with our ERP vendor. I intended to do the same with our legacy Informix database but I found that we did not have the Database Gateway licenses required for using Enterprise Manager Data Masking Pack on Informix. We also had a requirement to replace customer names and… Read more
In this part of the series we will have a look at different approaches to how Parallel Execution Skew can be addressed via manual re-writes of an affected query. Let’s have a look at a couple of options. Approach 1: Mimic the new 12c skew aware feature Looking at the new 12c special skew aware distribution feature that I’ve introduced… Read more
A wise man once said: start at the beginning and go on till you come to the end: then stop. We’ve done things a little differently; all this time we’ve been talking about examining and manipulating the data in our tables, but we have never paused to consider where these tables come from, or discuss how they are created. Tim… Read more
A wise man once said, insanity is doing the same thing over and over again but expecting different results. If that is the case then no one’s saner than a computer programmer*, because programming is executing the same code over and over again and expecting the same results. We’ve already talked about bundling up statements in anonymous PL/SQL blocks; now… Read more
Designing a highly available database system involves taking various elements and combining them to suit your business needs and requirements. At the beginning of architecting a new high available Oracle database system it’s important to understand the need to make design and infrastructure decisions in the context of meeting Service Level Agreements (SLA). The SLA targets can be usually achieved… Read more
It is only recently that the tools have existed to make source-control easy for database developers. Although entire build-scripts for databases could be stored in Subversion, tools like Source Control for Oracle (SoCO) now allow for a more effective source-control at object-level. … Read more
Jenkins, formerly called Hudson, is an open-source server-based Continuous Integration tool that works with all the major Source Control Management (SCM) tools including TFS, and can even script in PowerShell. It is clever software written by a clever geek, Kohsuke Kawaguchi.… Read more
Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database table in the FROM clause of the query is… Read more
