Come, young one.  Come sit by the open fire; hold your cold hands to its warmth. Let me tell you a story.  Back when I was young, the world was a simpler place: we roamed the plains, hunted game with our spears – and Oracle Select statements consisted of only a SELECT, a FROM and, possibly, a WHERE.

Things done changed.

In truth you’ll have to go back 20 years to find a time when the SELECT statement wasn’t a Swiss army knife with numerous, weird, clauses jutting out of it – from subquery factoring clauses to hierarchical query clauses. The purpose of this series is to shine light on some of those clauses, and help Oracle developers – especially those graduating from my Oracle For Absolute Beginners series – get to grips with them.

The With Clause

The With Clause – or, if you prefer fancy words, the subquery factoring clause – is a (optional) limb that grew out of the basic select statement as part of Oracle 9.2, back in 2002. The WITH clause allows you, as part of your select statement, to assign a name to a subquery and utilise its results by referencing that name.

It is, on first glance, quite jarring. Because the subquery factoring clause brutally transforms the look of a query, making it no longer start with the SELECT keyword.

The advantage of using the WITH clause is that, once you have defined your subquery, you can subsequently reference it repeatedly in your main select statement.  Oracle will optimise your subquery, resolving it as an inline view or a temporary table.  Also, counterintuitively, once you’ve come to recognise the odd-looking syntax, the WITH clause does actually make complex SELECT statements easy to read.  However, let us start with a reasonably simple example:

What the above SELECT statement does, basically, is create a subquery that gets a count of employees by department (it names the subquery emp_count), and then calls that subquery in the FROM clause of the main select statement the way it would call a table or view.

Our query is, as I promised, a reasonably simple one, but you can probably already begin to see how, using the WITH clause, you can corral complex subqueries, leaving your main SELECT statement relatively simple.

But before we venture down that rabbithole, let’s revisit something I said earlier: you can reference your subquery repeatedly. This makes sense if you spare it some thought. After all, it’s a temporary table, and SELECT statements can reference the same table more than once.  I’ll show you what I mean.

This ability to reference our named subquery more than once lifts the with clause above ordinary inline subqueries. If we want to reproduce the above query using inline subqueries the solution would be longer and uglier. The with clause helps us with the complexity.

Indeed, taking advantage of the fact that you can also define multiple subqueries, you can tidy away a large amount of complexity, leaving your main select statement simple.

Optimizer Hints

Oracle, as I said earlier, decides at runtime whether to process your subqueries as inline views or as temporary tables. However, you can nudge it in your preferred direction by using a couple of optimizer hints. The MATERIALIZE optimizer hint tells Oracle to treat your subquery as a temporary table; the INLINE hint, conversely, tells it to treat it as an inline view.

Personally, I try as much as possible not to fiddle with optimizer hints (I leave such black magic to our DBAs), however you may want to force Oracle to treat your subquery as a temporary table if it is complex and unwieldy.

I have seen WITH clause SELECT statements that stretch to many hundreds of lines. They mostly run like three-legged dogs, so no, I am not advocating them. It is in these situations that you may seriously consider optimizer hints.

Functions

With the release of 12C in 2014, the WITH clause learned a funky new trick: functions. That’s right, you can now create a function within the WITH  clause and use it in the body of your main select statement.  (Actually, the funky new trick isn’t really just functions; you can create procedures too, although I cannot see why you would want to, since you cannot call a procedure in a SELECT statement.)

Here’s the syntax:

Being eagle-eyed you probably noticed that semi-colon in the middle of the statement and assumed that it was a typo. Uh-uh, it’s not. Let me prove it with an example.

That’s nice, you might be thinking; but why don’t you just create a stand-alone function and use that instead? Isn’t this just showing off?

No, it’s not, actually. Firstly, there’s a neatness to a function that lives only for the duration that it is needed.  (Because any function embedded within a WITH clause is only available to that statement.) Secondly, you may find that using inline functions in this way gives you a performance advantage over stand-alone or package functions.

Oh, and just in case you were wondering, you can use functions and subqueries in the same with clause.

Statement Towers

The previous example begins to lean towards the thing I like most about the WITH clause: the way you can stack subquery over function over subquery, like little building blocks, to create what I – and no one else – call a statement tower.  I have not yet mentioned this, but in your WITH  clause you can reference a subquery in subsequent subqueries.

What this means is that you could, conceivably, build a statement tower of inter-referencing subqueries, fine-tuning your data with each subsequent subquery, leaving your main SELECT statement pure and simple, and easy to read.

It’ll be difficult to demonstrate what I mean using just the EMP and DEPT tables, so let’s use a few dummy tables instead. Imagine we’re running some kind of a sales report in which we not only wish to list the sold items, but the price, a description, how much profit we’re making, and who made the sale. While the following example might not demonstrate the most efficient use of SQL, hopefully it gives you an idea of how you could build a statement tower, parsing your requirements with each block.

Conclusion

So that’s it. The with clause. Go ye into the world and use it.

Head on over to Part 2: The Hierarchical Query Clause.