Index hint ignored for driving table of Outer Join[ID 316518.1]-CSDN博客

Index hint ignored for driving table of Outer Join[ID 316518.1]

Index hint ignored for driving table of Outer Join [ID 316518.1]

		修改时间 11-NOV-2010 类型 BULLETIN 状态 PUBLISHED


Outer join will create a NULL row on the right hand side of a join for
every row (including NULL rows) on the left hand side.



Non-outer joins will eliminate the NULL rows.


(since NULL != NULL (or anything else))



In the following queries against oj_test and oj_test2 which has 2 rows (one of which is NULL), 
you would get the following results:



drop table oj_test;
dropt table oj_test2;

create table oj_test (col1 number);
create table oj_test2 (col1 number);

insert into oj_test values(1);
insert into oj_test2 values(1);
insert into oj_test values(NULL);

commit;

Query without outer join:


=========================

select nvl(a.col1,999), b.col1
from oj_test a, oj_test2 b
where a.col1=b.col1
/

NVL(A.COL1,999)       COL1
--------------- ----------
              1          1

1 row selected

Only 1 row is selected becuase the join eliminates all NULL values


(since NULL != NULL (or anything else))


NB The "nvl(a.col1,999)" is simply there to more clearly illustrate 
that there is a NULL row returned

Query with outer join

=====================

NULLs are not eliminated with outer joins

select nvl(a.col1,999), b.col1
from oj_test a, oj_test2 b
where a.col1=b.col1 (+)
/

NVL(A.COL1,999)       COL1
--------------- ----------
              1          1
            999

2 rows selected

So in this query 2 rows are retrieved from oj_test a and then
the outer join provides a 'NULL' for this to be joined to.

Index useage

============

Outer join can affect the usage of indexes if the index is not 'NULL safe'

Query with outer join

=====================


If ther is an index on col1 of oj_test, then it cannot be used to 
drive the outer join query because it is not 'NULL safe'. Single column
indexes do not store NULLs so if this index was used then the outer join result 
would be missing the NULL row from oj_test.

 create index oj_test_ind1 on oj_test(col1);

select /*+ leading(a) use_nl(b) index(A) */ nvl(a.col1,999), b.col1
from oj_test a, oj_test2 b
where a.col1=b.col1 (+)
/


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=166 Card=82 Bytes=2132)
   1    0   NESTED LOOPS (OUTER) (Cost=166 Card=82 Bytes=2132)
   2    1     TABLE ACCESS (FULL) OF 'OJ_TEST' (Cost=2 Card=82 Bytes=1066)
   3    1     TABLE ACCESS (FULL) OF 'OJ_TEST2' (Cost=2 Card=1 Bytes=13)

Note that there is no Index access of OJ_TEST even though there is a hint.

-------------------------------------------------------------------------------------

*************************************************************************************

-------------------------------------------------------------------------------------

 
SQL> select /*+ leading(a) use_nl(b) index(a oj_test_ind1) */ nvl(a.col1,999), b.col1
  2  from oj_test a, oj_test2 b
  3  where a.col1=b.col1 (+)
  4  /
NVL(A.COL1,999)       COL1
--------------- ----------
              1          1
            999

执行计划
----------------------------------------------------------
Plan hash value: 2621508677
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     2 |    10 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|          |     2 |    10 |     7   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| OJ_TEST  |     2 |     4 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| OJ_TEST2 |     1 |     3 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."COL1"="B"."COL1"(+))

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
 
SQL>
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
 

Query with no outer join

 
========================
 

 
In the non-outer join example, the NULLs are eliminated by the join, so it does
not matter that the index is not 'NULL safe' and therefore the index can be used.

select /*+ leading(a) use_nl(b) index(A) */ nvl(a.col1,999), b.col1
from oj_test a, oj_test2 b
where a.col1=b.col1
/

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190 Card=82 Bytes=2132)
   1    0   NESTED LOOPS (Cost=190 Card=82 Bytes=2132)
   2    1     INDEX (FULL SCAN) OF 'OJ_TEST_IND1' (NON-UNIQUE) (Cost=26 Card=82 Bytes=1066)
   3    1     TABLE ACCESS (FULL) OF 'OJ_TEST2' (Cost=2 Card=1 Bytes=13)

from:metalink [ID 316518.1]

SQL> select /*+ leading(b) use_nl(a) index(A) */ nvl(a.col1,999), b.col1
2 from oj_test a, oj_test2 b
3 where a.col1=b.col1
4 /

NVL(A.COL1,999) COL1
--------------- ----------
1 1

执行计划
----------------------------------------------------------
Plan hash value: 2197482950

-----------------------------------------------------------------------------------
| Id | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     5 |     3   (0)| 00:00:01 |
|   1 | NESTED LOOPS      |              |     1 |     5 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| OJ_TEST2     |     1 |     3 |     3   (0)| 00:00:01 |
|* 3 |   INDEX RANGE SCAN | OJ_TEST_IND1 |     1 |     2 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

3 - access("A"."COL1"="B"."COL1")
filter("A"."COL1" IS NOT NULL)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          8 consistent gets
          0 physical reads
          0 redo size
        392 bytes sent via SQL*Net to client
        377 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select /*+ leading(a) use_nl(b) index(A) */ nvl(a.col1,999), b.col1
2 from oj_test a, oj_test2 b
3 where a.col1=b.col1
4 /

NVL(A.COL1,999) COL1
--------------- ----------
1 1

执行计划
----------------------------------------------------------
Plan hash value: 1470321623

-----------------------------------------------------------------------------------
| Id | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     5 |     4   (0)| 00:00:01 |
|   1 | NESTED LOOPS      |              |     1 |     5 |     4   (0)| 00:00:01 |
|* 2 |   INDEX FULL SCAN | OJ_TEST_IND1 |     1 |     2 |     1   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| OJ_TEST2     |     1 |     3 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

2 - filter("A"."COL1" IS NOT NULL)
3 - filter("A"."COL1"="B"."COL1")

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          8 consistent gets
          0 physical reads
          0 redo size
        392 bytes sent via SQL*Net to client
        377 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>