Boneist's Oracle Blog

Over the past couple of years or so, I’ve been hanging round the OTN SQL and PL/SQL forum, trying to help out where I can. Originally, I started doing this as a way of gaining experience of different “real world” examples, so that I could consolidate my knowledge (such as how to work with analytic functions!) and also expose myself to a lot of different issues. And also, where possible, to help people!

Since then, I’ve learnt much more than I could ever have hoped to, from various tips and tricks that I use often, to things that I haven’t yet needed to use in my day-to-day job. Of this latter group, there are things that I haven’t got the set up or access to try out, but even though I haven’t used them, I *know* that they exist and can refer back to them if I ever do come across a situation that requires them.

I cannot recommend enough to people that if you want to consolidate your SQL and PL/SQL knowledge and improve your skills, start helping out on the OTN SQL and PL/SQL forum!

Anyway, I thought that I would start writing about some of the things that I have picked up from my fellow forum helpers (they know who they are! *waves*) in the hopes that more people will come across them.

Subquery Factoring

Subquery factoring has been around since Oracle 9i, and is an incredibly useful tool. This is one thing that I picked up from the forums that I use at least once every day whilst at work!

In essence, it’s a way of giving each subquery you use within a SELECT statement a name and giving the scope of that subquery to the whole query. Whilst you can name inline views, you have also restricted the scope of that subquery to the bit of the query that it’s in.

The format looks like:

with subq1 as (select ....),
     subq2 as (select ....),
     ...
     subqn as (select ....)
select ...
from   subq1,
       subq2,
       ...
       subqn
where  ....;

You can have one or multiple subqueries defined, and you can also refer to previous subqueries within later subqueries (eg. subq2 could select from subq1, etc). All subqueries must be selected from at least one somewhere in the overall query.

The beauty of subquery factoring is that if you have a subquery repeated throughout a query, like:

select 'first_query' name_col, col1, col3
from   (select col1, col2, sum(col3) col3
        from   a_table)
where  col2 != 'hello'
union all
select 'second_query' name_col, col1, col3
from   (select col1, col2, sum(col3) col3
        from   a_table)
where  col2 = 'hello';

where the subquery gets run twice, when you factor it out, it only gets run once and then accessed twice:

with my_sub_query as (select col1, col2, sum(col3) col3
                      from   a_table)
select 'first_query' name_col, col1, col3
from   my_sub_query
union all
select 'second_query' name_col, col1, col3
from   my_sub_query;

Oracle will quite happily create a Global Temporary Table behind the scenes to hold the data from the subquery if it thinks it is beneficial to do so.

As well as performance benefits, it helps to make the query easier to read by modularising the SQL statement. And as we all know, modularising your code and making it resuable is Good Practice (TM)!

As well as all those benefits, subquery factoring has become very popular as a means of simulating a table with data, like so:

with my_tab as (select 1 col1, 'A' col2, 'hi' col3 from dual union all
                select 2 col1, 'B' col2, 'bye' col3 from dual)
select *
from   my_tab;

This comes in very, very handy when needing to set up test cases that other people can run, as there’s no longer a need for them to create a test table – the data’s already there!