ANSI Outer

Here’s an example of ANSI SQL that does something in a fashion that arguably looks a little tidier than the strategy you have to adopt in Oracle. As so often when I compare Oracle syntax and ANSI syntax it’s an example that relates to an outer join. We start with two tables – as usual I have locally managed tablespaces, 8KB blocks, 1MB uniform extents and freelist management. I’ve also disabled system statistics (CPU costing):

rem
rem     Script:         ansi_outer_selective.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2011
rem

create table t1 
as
select
        rownum - 1                      id,
        mod(rownum - 1,20)              n1,
        lpad(rownum - 1,10,'0')         v1,
        rpad('x',100)                   padding
from
        all_objects
where
        rownum <= 4000  -- > comment to avoid WordPress format issue
;

create table t2
as
select
        rownum - 1                      id,
        mod(rownum - 1,20)              n1,
        lpad(rownum - 1,10,'0')         v1,
        rpad('x',100)                   padding
from
        all_objects
where
        rownum <= 4000  -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(id);
create index t2_i1 on t2(id);


begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                estimate_percent => 100,
                method_opt       => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T2',
                estimate_percent => 100,
                method_opt       => 'for all columns size 1'
        );

end;
/

If you’re familiar with ANSI SQL you won’t need more than a couple of moments to interpret the following query – but I have to admit that I had to think about it carefully before I figured out what it was trying to achieve:

select
        /*+ gather_plan_statistics */
        t1.id,
        t1.n1,
        t1.v1,
        t2.n1
from
        t1
left join
        t2
on
        t2.id = t1.n1
and     t1.n1 in (7, 11, 13)
where
        t1.id = 15
;

        ID         N1 V1                 N1
---------- ---------- ---------- ----------
        15         15 0000000015

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER            |       |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN            | T1_I1 |      1 |      1 |      1 |00:00:00.01 |       3 |
|   4 |   VIEW                         |       |      1 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    FILTER                      |       |      1 |        |      0 |00:00:00.01 |       0 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2    |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |      INDEX RANGE SCAN          | T2_I1 |      0 |      1 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"=15)
   5 - filter(("T1"."N1"=7 OR "T1"."N1"=11 OR "T1"."N1"=13))
   7 - access("T2"."ID"="T1"."N1")
       filter(("T2"."ID"=7 OR "T2"."ID"=11 OR "T2"."ID"=13))

Look carefully at the query. The ON clause includes a reference to the t1 table that is NOT a join condition. This condition means that Oracle will try to find a matching row in t2 only if the n1 value in t1 is in 7,11, or 13.

Since the join is left outer, any t1 row where id = 15 will be returned, but the n1 column from t2 will be reported only if the t1.n1 value is in 7,11, or 13 and there is a t2 row with a matching id value.

How would you express the same requirment in standard Oracle syntax ? Here’s one possibility:

select
        /*+ gather_plan_statistics */
        t1.id,
        t1.n1,
        t1.v1,
        t2.n1
from
        t1, t2
where
        t1.id = 15
and     t2.id(+) = case
                when t1.n1 not in (7, 11, 13) 
                        then null
                        else t1.n1
        end
;


        ID         N1 V1                 N1
---------- ---------- ---------- ----------
        15         15 0000000015

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER          |       |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |      1 |      1 |      1 |00:00:00.01 |       3 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    INDEX RANGE SCAN          | T2_I1 |      1 |      1 |      0 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"=15)
   5 - access("T2"."ID"=CASE  WHEN (("T1"."N1"<>7) AND ("T1"."N1"<>11) AND
              ("T1"."N1"<>13)) THEN NULL ELSE "T1"."N1" END )

You will have noticed that I used the /*+ gather_plan_statistics */ hint in both queries, and the plans I’ve shown are the ones that I pulled from memory with their last execution statistics included.

The plans are clearly just a little different from each other – but are they equally efficient ?

Both plans start the same way – for each relevant row in t1 they call line 4 – and in both cases there is only one relevant row in t1, hence one call to line 4.

In the first plan we call the subroutine to create a rowsource (the VIEW operator), and this calls the FILTER operation. The filter operation is an example of a “conditional” filter – i.e. if the test in line 5 is true then line 6 is called – and in this case the test is false so line 6 is never called (Starts = 0) and Oracle doesn’t try to access table t2. So we fetch one row from t1, then call two subroutines that between them do a “case” test but access no data blocks.

In the second plan line 4 calls the table access operation, which calls the index range scan operation in line 5 – but line 5 starts with a call to the case statement that returns NULL – so even though we call the index range scan operation, we don’t access any data blocks, which means we don’t pass any rowids to the table access in line 4, which means that that operation doesn’t access any data blocks. The net workload is that lines 4 and 5 in the second plan also represent nothing more than two subroutine calls and a “case” test.

The two plans are virtually identical in resource usage – so your choice of which to use probably comes down to aesthetic appeal and comfort factor. Thanks to my previous habits I think the Oracle variant is “obviously” much easier to understand – but the client probably felt the opposite because they spent most of their time writing ANSI.

Is there another way to write the query – yes, there is, but for some reason it’s not supported. If you look at the 10053 trace file for the ANSI example you’ll find that Oracle has rewritten it with a LATERAL subquery before optimising it. So, presumably, we can appply the same transformation manually:

select
        /*+ gather_plan_statistics */
        t1.id,
        t1.n1,
        t1.v1,
        t2.n1
from
        t1,
        lateral (
                (
                select
                        t2.n1
                from
                        t2
                where
                        t1.n1 in (7, 11, 13)
                and     t2.id = t1.n1
                )
        )(+) t2
where
        t1.id = 15
;


        lateral (
                *
ERROR at line 9:
ORA-00933: SQL command not properly ended

On second thoughts perhaps we can’t – but it was a nice idea.

Oracle uses the table() operator with collections (or pipelined functions) as the equivalent of the ANSI LATERAL() operator, but doesn’t let you use it with queries (you get Oracle error “ORA-22905: cannot access rows from a non-nested table item” if you try).

The concept is simple – the table() or lateral() operator allows you to introduce a subquery in the FROM clause that references columns from a table that appears earlier in the same FROM clause.

I don’t know why Oracle doesn’t support the lateral() operator in end-user code – but actually, for experimental purposes only of course, if you want to play with it you can always set event 22829:

alter session set events '22829 trace name context forever';

-- execute lateral query, and get this plan

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER            |       |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN            | T1_I1 |      1 |      1 |      1 |00:00:00.01 |       3 |
|   4 |   VIEW                         |       |      1 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    FILTER                      |       |      1 |        |      0 |00:00:00.01 |       0 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T2    |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |      INDEX RANGE SCAN          | T2_I1 |      0 |      1 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"=15)
   5 - filter(("T1"."N1"=7 OR "T1"."N1"=11 OR "T1"."N1"=13))
   7 - access("T2"."ID"="T1"."N1")
       filter(("T2"."ID"=7 OR "T2"."ID"=11 OR "T2"."ID"=13))

The plan is identical to the plan for the ANSI after transformation. I’ll leave it to you to decide whether the code is easier to understand than the ANSI or ordinary Oracle versions – but I’d like to see it made legal, even if I didn’t find many cases where I needed it.