When you are working on a complex query, it is important to try to pare the query down to its core. The core query involves the minimum number of columns and tables required to identify the final result set. Think of it as the skeleton of the query. The number of rows the core query returns must be identical to the number of rows returned by the query you are trying to improve; the number of columns the core query returns will probably be much smaller.

Often, you can classify the columns you need to handle into two distinct categories: core columns that are vital for identifying your result set (or the rows you want to act on), and "cosmetic” columns that return information directly derivable from the core columns. Core columns are to your query what primary key columns are to a table, but primary key columns are not necessarily core columns. Within a table, a column that is a core column for one query can become a cosmetic column in another query. For instance, if you want to display a customer name when you enter the customer code, the name is cosmetic and the code is the core column. But if you run a query in which the name is a selection criterion, the name becomes a core column and the customer code may be cosmetic if it isn’t involved in a join that further limits the result set. So, in summary, core columns are:

Sometimes no column from the table from which you return data is a core column. For instance, look at the following query, where the finclsref column is the primary key of the tdsfincbhcls table:

select count(*),
       coalesce(sum(t1.boughtamount)
                - sum(t1.soldamount), 0)
from tdsfincbhcls t1,
     fincbhcls t2,
     finrdvcls t3
where t1.finclsref = t2.finclsref
  and t2.finstatus = 'MA'
  and t2.finpayable = 'Y'
  and t2.rdvcode = t3.rdvcode
  and t3.valdate = ?
  and t3.ratecode = ?

Although the query returns information from only the tdsfincbhcls table, also known as t1, what truly determines the result set is what happens on the t2/t3 front, because you need only t2 and t3 to get all the primary keys (from t1) that determine the result set. Indeed, if in the following query the subquery returns unique values (if it doesn’t, there is probably a bug in the original query anyway), we could alternatively write:

select count(*),
       coalesce(sum(t1.boughtamount)
                - sum(t1.soldamount), 0)
from tdsfincbhcls t1
where t1.finclsref in 
       (select t2.finclsref
        from fincbhcls t2,
             finrdvcls t3
        where t2.finstatus = 'MA'
          and t2.finpayable = 'Y'
          and t2.rdvcode = t3.rdvcode
          and t3.valdate = ?
          and t3.ratecode = ?
          and t2.finclsref is not null)

The first step we must take when improving the query is to ensure that the subquery, which really is the core of the initial query, runs fast. We will need to consider the join afterward, but the speed of the subquery is a prerequisite to good performance.

As the previous example shows, you should check the role of tables that appear in the from clause when you identify what really matters for shaping the result set. There are primarily three types of tables:

Figure 5-6 shows how a typical query might look with respect to the various types of tables involved. Tables are represented by rectangles, and the joins are shown as arrows. The data that is returned comes from tables A and D, but a condition is also applied to columns from table E. Finally, we have two tables, B and C, from which nothing is returned; their columns appear only in join conditions.

Figure 5-6. How tables can be classified in a typical query

Therefore, we obtain a kind of chain of tables, linked to one another through joins. In some cases, we might have different branches. The tables to watch are those at the end of the branches. Let’s start with the easiest one, table E. Table E belongs to the query core, because a condition is applied to it (unless the condition is always true, which can happen sometimes). However, it doesn’t really belong to the from clause, because no data directly comes “from” table E. The purpose of table E is to provide an existence (or nonexistence) condition: the reference to table E expresses " . . . and we want those rows for which there exists a matching row in E that satisfies this condition.” The logical place to find a reference to table E is in a subquery.

There are several ways to write subqueries, and later in this chapter I will discuss the best way. We can also inject subqueries at various points in the main query, the select list, the from clause, and the where clause. At this stage, let’s keep the writing simple. Let me emphasize the fact that I am just trying to make the query more legible before speeding it up. I can take table E out of the from clause and instead write something like this:

and D.join_column in (select join_column from E where <conditions>)

For table A, if the query both returns data from the table and applies search criteria to its columns, there is no doubt that it belongs to the query core.

However, we must study harder if some data is returned from table A but no particular search criterion is applied to it except join conditions. The fate of table A in such a case depends on the answer to two questions:

Distinguishing between joins that only provide data to adorn an already defined result set and joins that contribute to reducing that result set is one of the key steps in identifying what matters. In the process, you should take a hard look at any distinct clause that you encounter and challenge its usefulness; once you have consigned to subqueries tables that are present to implement existence conditions, you will realize that many distinct clauses can go, without any impact on the query result. Getting rid of a distinct clause isn’t simply a matter of having eight fewer characters to type: if I empty my purse in front of you and ask you how many distinct coins I have, the first thing you’ll do is to sort the coins, which will take some time. If I give you only distinct coins, you’ll be able to answer immediately.

Like you, an SQL engine needs to sort to eliminate duplicates; it takes CPU, memory, and time to do that.

Sometimes you may notice that during this cleaning process some of the joins are perfectly useless. ^[37] While writing this book, I was asked to audit the performance of an application that had already been audited twice for poor performance. Both previous audits had pointed at queries built on the following pattern that uses the Oracle-specific syntax (+) to indicate an outer join (the table that is outer-joined is the one on the (+) side). All of these queries were taking more than 30 seconds to run.

select count(paymentdetail.paymentdetailid)
from paymentdetail,
     payment,
     paymentgrp
where paymentdetail.paymentid = payment.paymentid (+)
and payment.paymentgrpid = paymentgrp.paymentgrpid (+)
and paymentdetail.alive = 'Y'
and paymentdetail.errorid = 0
and paymentdetail.mode in ('CARD', 'CHECK', 'TRANSFER')
and paymentdetail.payed_by like 'something%'

Recommendations included the usual mix of index creation and haphazard database parameter changes, plus, because all three “tables” in the from clause were actually views (simple unions), the advice to use materialized views instead. I was apparently the first one to spot that all criteria were applied to paymentdetail, and that the removal of both payment and paymentgrp from the query changed nothing to the result, except that the query was now running in three seconds (without any other change). I hurried to add that I didn’t believe that this counting of identifiers was of any real use anyway (more about this in Chapter 6).

At this early stage of query refactoring, I am just trying to simplify the query as much as I can, and return the most concise result I can; if I could, I’d return only primary key values. Working on a very lean query is particularly important when you have aggregates in your query—or when the shape of the query suggests that an aggregate such as the computation of minimum values would provide an elegant solution. The reason is that most aggregates imply sorts. If you sort on only the minimum number of columns required, the volume of data will be much smaller than if you sort on rows that are rich with information; you will be able to do much more in memory and use fewer resources, which will result in much faster processing.

After we have identified the skeleton and scraped off rococo embellishments, we can work on structural modifications.

A complex query is usually made of a combination of simpler queries: various select statements combined by set operators such as union, or subqueries. There may also be an implicit flow of these various simpler queries: some parts may need to be evaluated before others because of dependencies. It is worth noting that the optimizer may copyedit the SQL code, and in fact change or even reverse the order of dependencies. In some cases, the DBMS may even be able to split a query and execute some independent sections in parallel. But whatever happens, one thing is true: a big, slow query generally is a combination of smaller queries. Needless to say, the time required to execute the full query can never be shorter than the time that is required to execute the longest step.

You will often reach a good solution faster if you “profile” your query by checking the “strength” of the various criteria you provide. That will tell you precisely where you must act, and moreover, in the process it may indicate how much improvement you can expect.

It is now time to hunt down tables that are referenced several times. We can find a reference to the same table in numerous places:

When you have spotted a table that seems to appear with some insistence in a query, you need to ask yourself two questions, in this order:

To give you an example, I once encountered in a query the following condition, where x is an alias for a table in the from clause (not shown):

...   and ((select min(o.hierlevel)
            from projvisibility p,
                 org o
            where p.projversion = x.version
              and p.id = x.id
              and p.accessid = o.parentid
              and o.id = ?)
                = (select min(o.hierlevel)
                   from projvisibility p,
                        org o
                   where p.projversion = x.version
                     and p.id = x.id
                     and p.accessid = o.parentid
                     and o.id = ?
                     and p.readallowed = 'Y'))

Here we have an obvious case of a repeated pattern, because after having verified that the two values that are substituted with the two ? place markers are indeed identical, we see that the only difference between the two subqueries is the last line in the preceding code snippet. What is this part of the query operating on? Basically, we are processing a set of values that is determined by three values, two of them inherited from the outer query (x.version and x.id), and the third one passed as a parameter. I will call this set of values S.

We are comparing a minimum value for S to the minimum value for a subset of S defined by the value Y for the readallowed column of the projvisibility table; let’s call the subset S_R. When the SQL engine is going to examine S, looking for the minimum value, it will necessarily examine the values of S_R at the same time. Therefore, the second subquery clearly appears as redundant work.

The magical SQL construct for comparing subsets within a set is often case. Collecting the two minimum values at once isn’t too difficult. All I have to do is to write:

select min(o.hierlevel)  absolute_min,
       min(case p.readallowed
             when 'Y' then o.hierlevel
             else null
           end) relative_min
from ...

null is ignored by a function such as min(), and I get my two values in a single pass. I just have to slightly modify the original query into the following:

... and exists (select null
                from projvisibility p,
                     org o
                where p.projversion = x.version
                  and p.id = x.id
                  and p.accessid = o.parentid 
                  and o.id = ?
                having min(o.hierlevel) =  min(case p.readallowed
                                                 when 'Y' then o.hierlevel
                                                 else null
                                                end))

and the repeated pattern is eliminated with the redundant work (note that I have removed the two aggregates from the select clause because I need them only in the having clause). I could even add that the query is simplified and, to me at least, more legible.

Let’s take a second, more complicated example:

select distinct
       cons.id,
       coalesce(cons.definite_code, cons.provisional_code) dc,
       cons.name,
       cons.supplier_code,
       cons.registration_date,
       col.rco_name
from weighing w
     inner join production prod
             on prod.id = w.id
           inner join process_status prst
                   on prst.prst_id = prod.prst_id
     left outer join composition comp
             on comp.formula_id = w.formula_id
     inner join constituent cons
             on cons.id = w.id
     left outer join cons_color col
             on col.rcolor_id = cons.rcolor_id
where prod.usr_id = :userid
  and prst.prst_code = 'PENDING'
union
select distinct
       cons.id,
       coalesce(cons.definite_code, cons.provisional) dc,
       cons.name,
       cons.supplier_code,
       cons.registration_date,
       cons.flash_point,
       col.rco_name
from weighing w
     inner join production prod
             on prod.id = w.id
           inner join process_status prst
                   on prst.prst_id = prod.prst_id
     left outer join composition comp
                  on comp.formula_id = w.formula_id
           inner join constituent cons
                   on cons.id = comp.cons_id
              left outer join cons_color col
                           on col.rcolor_id = cons.rcolor_id
where prod.usr_id = :userid
  and prst.prst_code = 'PENDING'

Here we have a lot of repeated patterns. The tables involved in the query are the same ones in both parts of the union, and the select lists and where clauses are identical; the only differences are in the joins, with cons being directly linked to w in the first part of the query, and linked to comp, which is itself outer-joined to w, in the second part of the query.

Interestingly, the data that is returned comes from only two tables: cons and col.

I will start with a schematic representation of the two parts of the query, to identify its core. I am using the same shade-coding as in Figure 5-6; that is, the tables from which some data is ultimately returned are shown as black boxes, and tables to which filtering conditions are applied but from which nothing is returned are shown as gray boxes. “Glue” tables are in white, joins are represented by arrows, and outer joins are represented by dotted arrows. Figure 5-7 shows the first part of the union.

Figure 5-7. A representation of the first part of the union

There are two interesting facts in this part of the query: first, prod and prst logically belong to a subquery, and because w is nothing but a glue table, it can go with them.

The second interesting fact is that comp is externally joined, but nothing is returned from it and there is no condition whatsoever (besides the join condition) on its columns. That means that not only does comp not belong to the core query (it doesn’t contribute to shaping the result set), but in fact it doesn’t belong to the query at all. Very often, this type of error comes from an enthusiastic application of the cut ‘n’ paste programming technique. The join with comp is totally useless in this part of the query. The only thing it might have contributed to is to multiply the number of rows returned, a fact artfully ^[38] hidden by the use of distinct, which is not necessary because union does away with duplicate rows, too.

The second part of the union, which I have represented in Figure 5-8, tells a different story. As in the first part of the union, prst, prod, and w can go to a subquery. In this case, though, comp is necessary because I can say that it links what is returned (data from cons and col) to the tables that allow us to define exactly which rows from cons and col we want. But there is something weird in the join: the outer join between comp and w tells the SQL engine “complete what you return from w with null values in lieu of the columns from comp if you cannot find a match in comp.” So far, so good, except that comp is also linked—and with a regular, inner join—to cons on the other side. It is a sound practice to challenge any construct that doesn’t fall in the “plain and basic” category, and outer joins are neither plain nor basic. Let’s suppose that for one row provided by w we don’t find a match in comp. Then a null value is returned for comp.cons_id, which is used for the join with cons. A null value is never equal to anything else, and no row from cons will ever match it. Rows that will be returned in this part of the query can correspond only to cases when there is a match between w and comp. The conclusion is immediate: the join between w and comp should be an inner join, not an outer one. The outer join adds no value, but as it is a potentially more tolerant join than the regular inner join, it may well send a wrong signal to the optimizer and contribute to poor performance.

Figure 5-8. A representation of the second part of the union

Now, prst, prod, w, and comp can all be moved to a subquery, as in the first part of the union. I can rewrite the query as follows:

select cons.id,
       coalesce(cons.definite_code, cons.provisional_code) dc,
       cons.name,
       cons.supplier_code,
       cons.registration_date,
       col.rco_name
from constituent cons
     left outer join cons_color col
             on col.rcolor_id = cons.rcolor_id
where cons.id in (select w.id
                  from weighing w
                       inner join production prod
                               on prod.id = w.id
                       inner join process_status prst
                               on prst.prst_id = prod.prst_id
                  where prod.usr_id = :userid
                   and prst.prst_code = 'PENDING')
union
select  cons.id,
       coalesce(cons.definite_code, cons.provisional) dc,
       cons.name,
       cons.supplier_code,
       cons.registration_date,
       cons.flash_point,
       col.rco_name
from constituent cons
     left outer join cons_color col
             on col.rcolor_id = cons.rcolor_id
where cons.id in (select comp.cons_id
                  from composition comp
                       inner join weighing w
                               on w.formula_id = comp.formula_id
                             inner join production prod
                                     on prod.id = w.id
                             inner join process_status prst
                                     on prst.prst_id = prod.prst_id
                  where prod.usr_id = :userid
                 and prst.prst_code = 'PENDING')

If I now consider the subquery as a whole, instead of having the union of two mighty joins, I can relegate the union to a subquery in a kind of factorization:

select cons.id,
       coalesce(cons.definite_code, cons.provisional_code) dc,
       cons.name,
       cons.supplier_code,
       cons.registration_date,
       col.rco_name
from constituent cons
     left outer join cons_color col
             on col.rcolor_id = cons.rcolor_id
where cons.id in (select w.id
                  from weighing w
                       inner join production prod
                               on prod.id = w.id
                       inner join process_status prst
                               on prst.prst_id = prod.prst_id
                  where prod.usr_id = :userid
                   and prst.prst_code = 'PENDING
                  union
                  select comp.cons_id
                  from composition comp
                       inner join weighing w
                               on w.formula_id = comp.formula_id
                             inner join production prod
                                     on prod.id = w.id
                             inner join process_status prst
                                     on prst.prst_id = prod.prst_id
                  where prod.usr_id = :userid
                 and prst.prst_code = 'PENDING')

In the real case from which I derived this example, I had excellent performance at this point (runtime had dropped from more than a minute to about 0.4 seconds) and I stopped here. I still have a repeating pattern, though: the repeated pattern is the join between w, prod, and prst. In the first part of the new union, the id column from w is returned, whereas in the second part, the formula_id column from the same table is used to join to comp.

If performance had still been unsatisfactory, I might have tried, with Oracle or SQL Server, to use the with clause to factorize the repeated query:

with q as (select w.id, w.formula_id
           from weighing w
           inner join production prod
                   on prod.id = w.id
           inner join process_status prst
                   on prst.prst_id = prod.prst_id
           where prod.usr_id = :userid
             and prst.prst_code = 'PENDING')

In that case, the union would have become:

select q.id
from q
union
select comp.cond_id
from composition comp
         inner join q
                 on q.formula_id = comp.formula_id

I’d like to point out that at this stage all of the modifications I applied to the query were of a purely logical nature: I gave no consideration to the size of tables, their structures, or the indexes on them. Sometimes a logical analysis is enough. Often, though, once the logical structure of the query has been properly identified and you have a clean, straight frame, it is time to look more closely at the data to reconstruct the query. The problem with SQL is that many constructs are logically equivalent (or almost logically equivalent, as you will see). Choosing the right one may be critical, and all DBMS products don’t necessarily handle all constructs identically. Nowhere is this as flagrant as with subqueries.

You can use subqueries in many places, and it is important to fully understand the implications of all alternative writings to use them correctly and to avoid bugs. In this section, I will review the most important places where you can use subqueries and discuss potential implications.

select a.c1, a.c2, (select some_col from b where b.id = a.c3) as sub
from a ...

select a.c1, a.c2, 
       (select some_col from b where b.id = a.c3) as sub1,
       (select some_other_col from b where b.id = a.c3) as sub2,
       ...
from a ...

select a.c1, a.c2, 
       b.some_col as sub1,
       b.some_other_col as sub2,
       ...
from a 
     left outer join b
                  on b.id = c.c3

select a.c1, a.c2, 
       (select some_col
        from b
        where b.id = a.c3
          and b.attr = 'ATTR1') as sub1,
       (select some_other_col
        from b
        where b.id = a.c3
          and b.attr = 'ATTR2') as sub2,
       ...
from a ...

select id,
       max(case attr
             when 'ATTR1' then some_col
             else null
           end) some_col,
       max(case attr
             when 'ATTR2' then some_other_col
             else null
           end) some_other_col
from b
where attr in ('ATTR1', 'ATTR2')
group by id

select a.c1, a.c2,
       b2.some_col,
       b2.some_other_col,
       ...
from a
     left outer join (select id,
                             max(case attr
                                   when 'ATTR1' then some_col
                                   else null
                                 end) some_col,
                             max(case attr
                                   when 'ATTR2' then some_other_col
                                   else null
                                 end) some_other_col
                      from b
                      where attr in ('ATTR1', 'ATTR2')
                      group by id) b2
                   on b2.id = a.c3

select m.member_name, ...
from members m
where exists (select null
              from forum f
              where f.post_date >= ...
                and f.posted_by = m.member_id)

select m.member_name, ...
from members m
where m.member_id in (select f.posted_by
                      from forum f
                      where f.post_date >= ...)

select m.member_name ...
from members m
     inner join (select distinct f.posted_by
                 from forum f
                 where f.post_date >= ...) q
              on q.posted_by = m.member_id

and my_primary_key in (subquery that returns plenty of rows)

...
and workset.worksetid in
                (select tasks.worksetid
                 from tasks
                 where tasks.projectversion = ?)
...

and exists (select null
            from tasks
            where tasks.worksetid = workset.worksetid
              and tasks.projectversion = ?)

Another important principle, filtering, must be brought into the process as soon as possible. This means you should eliminate rows before joining, sorting, or aggregating, and not after. I already mentioned the classic where versus having case, in which it sometimes makes a tremendous difference (particularly if the optimizer doesn’t fix your mistakes behind your back) to eliminate rows before aggregation when conditions don’t bear on the result of aggregation. But there are other common cases. Take, for instance, a schema that looks like the one in Figure 5-9, where a master table contains some basic information about a number of items, and peripheral tables contain attributes for these items that may or may not be present. All tables contain the item’s identifier as well as two columns that specify when the row was created (mandatory column) or last modified (which can be null if the row was never modified since creation). The task was to provide a list of all items that had been modified as of a given date.

Figure 5-9. Listing objects for which one attribute was recently modified

I once found this task performed by the following code: ^[40]

select ...
from master_table m
     left outer join t1
                  on t1.id = master_table.id
     left outer join t2
                  on t2.id = master_table.id
     left outer join t3
                  on t3.id = master_table.id
     left outer join t4
                  on t4.id = master_table.id
where (case 
         when coalesce(m.modified, m.created) > some_date then 1
         when coalesce(t1.modified, t1.created) > some_date then 1
         when coalesce(t2.modified, t2.created) > some_date then 1
         when coalesce(t3.modified, t3.created) > some_date then 1
         when coalesce(t4.modified, t4.created) > some_date then 1
         else 0
       end) = 1

The tables involved were not huge; all of them stored between thousands and tens of thousands of rows. But the query was very slow and ultimately returned few rows. The small result set meant that few records were modified during the period under scrutiny, and that the condition on the date was very selective. But to be able to deal with all the created and modified dates within a single expression, the SQL engine must first perform all joins—which is a lot of work on five tables of this size when no efficient filtering condition is applied before the join.

The answer in such a case is to identify the core query as the (small) set of identifiers that relate to rows recently modified. As such, we can rewrite the core query as follows:

select id
from master_table
where  coalesce(m.modified, m.created) > some_date
union
select id
from t1
where  coalesce(t1.modified, t1.created) > some_date
union
select id
from t2
where  coalesce(t2.modified, t2.created) > some_date
union
select id
from t3
where  coalesce(t3.modified, t3.created) > some_date
union
select id
from t4
where  coalesce(t4.modified, t4.created) > some_date

Even when each select translates to a table scan, we end up scanning a volume that comprises hundreds of thousands of rows—in other words, not very much compared to filtering after the join. The implicit distinct and the resultant sort called for by union will operate on a small number of identifiers, and you quickly get a very short list of identifiers. As a result, you have managed to perform screening before the joins, and you can proceed from here to retrieve and join the few rows of interest, which will be an almost instantaneous process.

Once you have checked that no function is applied to a column that prevents using an efficient index on that column, there isn’t much you can do to improve regular equality or inequality conditions. But we aren’t done with subqueries. Even when we have chosen whether a subquery should be correlated or uncorrelated, we may realize that we have many conditions that are now expressed as subqueries (possibly as a result of our cleaning up the from clause).

Therefore, the next step is to minimize the number of subqueries. One of your guiding principles must be to visit each table as few times as possible (if you have studied operational research, think of the traveling salesman problem ^[41]). You can encounter a number of patterns where hitting each table only once is easy:

Many other optimizations are often possible. Don’t misunderstand my clumping them together as their being minor improvements to try if you have time; there are cases where they can really save a poorly performing query. But they are variations on other ideas and principles exposed elsewhere in this chapter.

with my_subquery as (select distinct blah from ...)

where somecol in (select blah from ...)

where somecol = my_subquery.blah

select ...
from a, b, t1
where ...
union
select ...
from a, b, t2
where ...

select ...
from a, b,
     (select ...
      from t1
      where ...
      union
      select ...
      from t2
      where ...)
where ...

select ...
from a,
     (select ...
      from b, t1
      where ...
      union
      select
      from b, t2
      where ...)
where ...

The method that probably comes most naturally to mind is the nested loop: scanning the first table (or the result set defined by restrictive conditions on this table), and then for each row getting the value of the join column, looking into the second table for rows that match this value, and optionally checking that these rows satisfy some other conditions. In the previous example, the SQL engine might:

Note that the SQL optimizer has a number of options, which depend on indexing and what it knows of the selectivity of the various conditions: if b.col1 isn’t indexed but a.c1 is, it will be much more efficient to start from table b rather than table a because it has to scan the first table anyway. The respective “quality” of the conditions on c3 and col3 may also favor one table over the other, and the optimizer may also, for instance, choose to screen on c3 or col3 before or after the matching operation on c1 and col1.

But there is another method, which consists of processing, or preparing, the two tables involved in the join before matching them.

Suppose that you have coins to store into a cash drawer, where each type of coin goes into one particular area of the insert tray. You can get each coin in turn, look for its correct position, and put it in its proper place. This is a fine method if you have few coins, and it’s very close to the nesting loop because you check coins one by one as you would scan rows one by one. But if you have many coins to process, you can also sort your coins first, prepare stacks by face value, and insert all your coins at once. This is sort of how a merge join works. However, if you lack a workspace where you can spread your coins to sort them easily, you may settle for something intermediate: sorting coins by color rather than face value, then doing the final sort when you put the coins into the tray. This is vaguely how a hash join works.

With the previous example, joining using this method would mean for the SQL engine:

Sometimes nested loops outperform hash joins; sometimes hash joins outperform nested loops. It depends on the circumstances. There is a very strong affinity between the nested loop and the correlated subquery, and between the hash or merge join and the uncorrelated subquery. If you execute a correlated subquery as it is written, you do nothing but a nested loop. Nested loops and correlated subqueries shine when volumes are low and indexes are selective. Hash joins and uncorrelated subqueries stand out when volumes are very big and indexes are either inefficient or nonexistent.

You must realize that whenever you use inline views in the from clause, for instance, you are sending a very strong “hash join” signal to the optimizer. Unless the optimizer rewrites the query, whenever the SQL engine executes an inline view, it ends up with an intermediate result set that it has to store somewhere, in memory or in temporary storage. If this intermediate result set is small, and if it has to be joined to well-indexed tables, a nested loop is an option. If the intermediate result is large or if it has to be joined to another intermediate result for which there can be no index, a merge or hash join is the only possible route. You can therefore (re)write your queries without using heavy-handed “directives,” and yet express rather strongly your views on how the optimizer should run the query (while leaving it free to disagree).