HomeOracle

Oracle 11.2 Outer Join And Index Issue

David Fitzjarrell
By David Fitzjarrell

While investigating a question on returning unmatched rows between two tables I set up an example where both tables had indexes, which could be used to speed up the query and hopefully return the result set in less time than required for a full table scan. To my surprise when the index was used on the table with the missing record the query returned no rows. Not understanding this behavior (as I KNEW there was at least one row in the data range that wasn’t in the smaller table) I checked the execution plans for queries that worked (meaning they returned the correct ‘missing’ row) and the ones that didn’t. We’ll go through my investigation and reveal the reason for this unexpected result.

First we’ll provide the problem we need to solve, namely how to return unmatched rows from two tables. Setting up the example is simple and straightforward — create two tables and load them with data, ensuring, in this example, that table B is missing rows that are present in table A:


SQL>
SQL> create table a (
  2  	     id number,
  3  	     dt      date
  4  );

Table created.

SQL>
SQL> create table b (
  2  	     id number,
  3  	     dt      date
  4  );

Table created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     insert into a
  4  		     values(i, trunc(sysdate)+i);
  5  		     if mod(i,9317)  0 then
  6  			     insert into b
  7  			     values(i, trunc(sysdate)+i);
  8  		     end if;
  9  	     end loop;
 10
 11  	     commit;
 12
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

Compute statistics on the schema to prevent dynamic sampling:


SQL>
SQL> exec dbms_stats.gather_schema_stats('GRIBNAUT')

PL/SQL procedure successfully completed.

SQL>

Create the indexes we want, to make the select statements more efficient:


SQL>
SQL> create index a_id_idx on a(dt);

Index created.

SQL> create index b_id_idx on b(dt);

Index created.

SQL>

Now the fun begins. Queries are executed that use both indexes to speed up processing and, as a result, return the ‘wrong’ result set:


SQL>
SQL> set autotrace on timing on
SQL>
SQL> select a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:02.22

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_827rna5a37fsc89d874e3" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         49  consistent gets
         28  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL> select /*+ leading(b a) */ a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_6p83dmsh1ur9589d874e3" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL> select /*+ leading(b a) use_nl(b a) */ a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_7up4d2184h90h89d874e3" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

Notice the order of the tables in the join; this is due to the Optimizer converting the outer join to an inner join:


  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$64C6BB79")
      OUTER_JOIN_TO_INNER(@"SEL$9E43CB6E" "B"@"SEL$1")
      OUTLINE(@"SEL$9E43CB6E")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$64C6BB79" "B"@"SEL$1" ("B"."DT"))
      INDEX(@"SEL$64C6BB79" "A"@"SEL$2" ("A"."DT"))
      LEADING(@"SEL$64C6BB79" "B"@"SEL$1" "A"@"SEL$2")
      USE_NL(@"SEL$64C6BB79" "A"@"SEL$2")
      NLJ_BATCHING(@"SEL$64C6BB79" "A"@"SEL$2")
    END_OUTLINE_DATA
  */

These examples generated several comments which caused me to examine what I did and what I may have done incorrectly. Re-writing the first ‘non-working’ query by moving the index access for table B to the join condition caused Oracle to produce the correct result set:


SQL> select a.id
  2  from a left join b
  3  on b.id = a.id
  4  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

        ID
----------
      9317

Elapsed: 00:00:00.70

Execution Plan
----------------------------------------------------------
Plan hash value: 4150175149

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|*  2 |   FILTER                       |          |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000)
   2 - filter("B"."ID" IS NULL)
   3 - access("B"."ID"(+)="A"."ID")
   5 - access("A"."DT">=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - access("B"."DT"(+)>=TRUNC(SYSDATE@!)+1000 AND
              "B"."DT"(+)<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_af8n1569bu8u9dca7cba2" used for this statement


Statistics
----------------------------------------------------------
        232  recursive calls
         46  db block gets
        329  consistent gets
         48  physical reads
       4976  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Obviously, after seeing this example, putting the date restriction for table B in the WHERE clause was incorrect thinking on my part.

Now look at a series of ‘working’ queries:


SQL>
SQL> select a.id
  2  from a, b
  3  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  4  and b.id (+) = a.id
  5  and b.id is null;

        ID
----------
      9317

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 764351325

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_a6prrdg4ukbttd488787e" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       2606  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select id
  2  from
  3  (select a.id, a.dt
  4   from a left join b
  5   on b.id = a.id
  6   where b.id is null)
  7  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 764351325

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_42g6vdgv7hfm3d488787e" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       2606  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select id
  2  from a
  3  where id not in (select id from b where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000)
  4  and a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2827420310

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI NA           |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_792mbp1nk9zf69c0ae570" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Another comment stated the first working query was not the same as the first ‘nonworking’ query; they are not supposed to be. My goal was to determine why one set of conditions using the index on table B produced no rows and another set of conditions, in general absent that same restriction, produced the correct results. As stated earlier in this article my thought process was a bit flawed as my presumptions with respect to including a date restriction on table B in the WHERE clause may not have been correct.

In the above examples the outer join is preserved, resulting in the ‘correct’ answer. All of this because of an index. Notice, too, that the only table using an index is table A. To be fair and honest it is possible to use an index on table B and get the proper result, as the examples below illustrate:


SQL> select a_id
  2  from
  3  (select id a_id
  4  from a
  5  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000) a,
  6  (select id b_id
  7  from b
  8  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000) b
  9  where b_id (+) = a_id
 10  and b_id is null;

      A_ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4150175149

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|*  2 |   FILTER                       |          |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"=TRUNC(SYSDATE@!)+1000 AND "DT"(+)<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_0awgfy98n1g1rdca7cba2" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select id
  2  from a
  3  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  4  minus
  5  select id
  6  from b
  7  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3815546394

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |  9002 |   228K|   101  (52)| 00:00:01 |
|   1 |  MINUS                         |          |       |       |            |          |
|   2 |   SORT UNIQUE                  |          |  9002 |   114K|    50   (2)| 00:00:01 |
|*  3 |    FILTER                      |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |   SORT UNIQUE                  |          |  9001 |   114K|    50   (2)| 00:00:01 |
|*  7 |    FILTER                      |          |       |       |            |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_6h13amvqrnwdx3cdf639d" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

In the first example both tables are used to create in-line views of the desired data range; those individual result sets are then joined to return the ‘correct’ result. In the second example the two select statements use the MINUS operator to again return the ‘correct’ answer.

Please refer back to the beginning of this article to see yet another query that uses the index on table B and produces the correct results

Is this a bug in Oracle 11.2? I suspected that it was; it certainly seemed strange that the outer join was converted to an inner join when an index on the ‘smaller’ table, the one with the missing data, was used. It turns out that a misstep in my logic caused at least one of the empty result sets. Correcting my logic and query produced the correct answer.

With a product as complex as Oracle some bugs are bound to be present; of course sometimes the ‘bug’ is in the logic behind an apparently misbehaving query, which does teach you to pay careful attention to the results a query delivers. Even though the queries that produced the incorrect results are syntatically correct you can’t be certain that Oracle won’t do something ‘behind the scenes’ that can produce the wrong answer and reveal an error in your logic. My gratitude goes out to those who paid enough attention to make comments on this article and point out places where I went wrong so I could update it with corrected information.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.
Previous article
MongoDB 2.6 Improves Open-Source Database Performance
Next article
Big Data Quality Metrics

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.