Re: Unbelievable Outer Join

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 13 Aug 2009 04:02:22 -0700 (PDT)
Message-ID: <fbdd28b7-077e-4e36-849d-8e00881fd57c_at_d32g2000yqh.googlegroups.com>



On Aug 13, 4:47 am, Jan <noreply...._at_gmail.com> wrote:
> Thanks for all the hints. I am really impressed with how helpfull you
> are being.
> I know I could try newer syntax but it's not easy to change the SQL
> withing the life system now, to have one select with different syntax
> from all the others is not neat either.
> Statistics are up to date, TI238_14935572 has dynamic sampling
> enforced as this table has for example 0 rows at time T1, 3 milion
> rows at time T2 and back to 0 at time T3 all within few minutes, like
> this the whole day.
>
> My question is "simple". How can oracle, by this not being a BUG for
> whatever reason, apply left outer join of "preserved" table
> TT203_10100000001372631 that has 6798 rows on the outer joined table
> TI238_14935572 (with one row) and estimate it will end up with 1 row.
> The joining column is actually the primary key of the TT203_% table
> (thus it is not possible 1 row in TI238_14935572 would eliminate all
> 6798 rows.
>
> This is the explain plan again:
> | Id | Operation |
> Name | Rows
> ----------------------------------------------------------------------------------
> | 0 | INSERT STATEMENT
> | |
> | 1 | HASH UNIQUE
> | | 1
> |* 2 | TABLE ACCESS BY INDEX ROWID |
> TI238_185727 | 1
> | 3 | NESTED LOOPS
> | | 1
> | 4 | NESTED LOOPS
> | | 1
> | 5 | NESTED LOOPS
> | | 1
> |* 6 | FILTER
> | |
> | 7 | NESTED LOOPS OUTER
> | | 1
> |* 8 | TABLE ACCESS FULL |
> TT203_10100000001372631 | 6798
> |* 9 | TABLE ACCESS BY INDEX ROWID |
> TI238_14935572 | 1
> |* 10 | INDEX RANGE SCAN |
> I_T201_14935572 | 1
> |* 11 | INDEX RANGE SCAN |
> IDX_T510_PARENT_TO_CHILD | 111
> | 12 | PARTITION RANGE ITERATOR
> | | 1
> |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID|
> TT203_3401330 | 1
> |* 14 | INDEX RANGE SCAN |
> UQ_3401330_BD | 48
> |* 15 | INDEX RANGE SCAN |
> I_VAL_185727 | 1
> ----------------------------------------------------------------------------------
>
> At this moment I have no means to "play" with the original query as
> this is life environment, content of TI238_14935572 has completely
> changed since then and to simulate this problem on test enviroment
> will require significant effort. Does it however matter for replying
> the puzzling question?
>
> I tried to simulate the problem on a test case (different 10.2.0.3.0
> environment, most likely identical settings) that should have
> identical joining logic to the original and in this simulation the
> optimizer behaves correctly. This is the explain plan of the test
> case: (I can provide the plsql code for this simulation if anyone is
> intrested)
>
> ------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows
> ------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 | 1
> | 1
> |* 2 | FILTER | | 1 |
> | 9998
> |* 3 | FILTER | | 1 |
> | 9998
> | 4 | NESTED LOOPS OUTER | | 1 | 9999
> | 9999
> |* 5 | TABLE ACCESS FULL | TABLE1 | 1 | 9999
> | 9999
> |* 6 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 9999 | 1
> | 1
> |* 7 | INDEX RANGE SCAN | T2_T3_ID | 9999 | 1
> | 9999
> ------------------------------------------------------------------------------
>
> Friends, I am reading and re-reading your tips. At this point I am not
> even thinking how to fix the problem yet. I just can't believe how the
> problem could occur in the first place and still BUG seems to be the
> only answer. Or is there another explanation?
>
> Regards,
> Jan Rod

Jan,

There is no need to switch to the ANSI style joins. The first of the above plans is showing only estimated row counts. The second of the above plans is showing actual row counts where the STARTS column shows the actual number of times the operation TABLE ACCESS BY INDEX ROWID on TABLE2 was started. If you had created the first plan specifying 'ALLSTATS LAST' for the third parameter of DBMS_XPLAN.DISPLAY_CURSOR you would have seen a similar plan. An example test case:
CREATE TABLE T1 (
  C1 NUMBER,
  C2 VARCHAR2(100),
  PRIMARY KEY (C1)); CREATE TABLE T2 (
  C1 NUMBER,
  C2 VARCHAR2(100),
  PRIMARY KEY (C1)); INSERT INTO T1
SELECT
  ROWNUM,
  LPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL <=100000;

INSERT INTO T2
SELECT
  ROWNUM*15,
  LPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL <=100000/15;

COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE) EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE) There are now 2 table, table T1 with 100,000 rows and table T2 with 100,000/15 rows. Now for the test case, using SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS to suppress the output lines and display a TYPICAL execution plan:
SPOOL PLAN_TEST.TXT SELECT /*+ USE_NL(T1 T2) GATHER_PLAN_STATISTICS */

  T1.C1,
  T1.C2,
  T2.C2

FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1(+);

SELECT /*+ USE_NL(T1 T2) GATHER_PLAN_STATISTICS */

  T1.C1,
  T1.C2,
  T2.C2

FROM
  T1
LEFT OUTER JOIN
  T2
ON
  T1.C1=T2.C1;

SPOOL OFF SELECT
  SQL_ID,
  CHILD_NUMBER
FROM
  V$SQL
WHERE
  PLAN_HASH_VALUE=574041105; SET AUTOTRACE OFF SPOOL PLAN_TEST2.TXT SELECT
  T.*
FROM
  V$SQL S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST')) T
WHERE
  S.PLAN_HASH_VALUE=574041105; SPOOL OFF The above includes an older Oracle style join and an ANSI style join. The output follows with trimmed execute plans to reduce the risk of line wrapping:
SQL> SELECT /*+ USE_NL(T1 T2) GATHER_PLAN_STATISTICS */

  2    T1.C1,
  3    T1.C2,
  4    T2.C2

  5 FROM
  6 T1,
  7 T2
  8 WHERE
  9 T1.C1=T2.C1(+);

100000 rows selected.

Execution Plan



Plan hash value: 574041105
| Id  | Operation                    | Name         | Rows  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100K|
|   1 |  NESTED LOOPS OUTER          |              |   100K|
|   2 |   TABLE ACCESS FULL          | T1           |   100K|
|   3 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0020502 |     1 |
-------------------------------------------------------------

Predicate Information (identified by operation id):


   4 - access("T1"."C1"="T2"."C1"(+))

Statistics


          0  recursive calls
          0  db block gets
     208330  consistent gets
          0  physical reads
          0  redo size
    1677877  bytes sent via SQL*Net to client
       1423  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

Execution Plan



Plan hash value: 574041105
| Id  | Operation                    | Name         | Rows  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100K|
|   1 |  NESTED LOOPS OUTER          |              |   100K|
|   2 |   TABLE ACCESS FULL          | T1           |   100K|
|   3 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0020502 |     1 |
-------------------------------------------------------------

Predicate Information (identified by operation id):


   4 - access("T1"."C1"="T2"."C1"(+))

Statistics


          0  recursive calls
          0  db block gets
     208330  consistent gets
          0  physical reads
          0  redo size
    1677877  bytes sent via SQL*Net to client
       1423  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

Trying out the EXPLAIN PLAN calls, this is the output of the TABLE (DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST')) calls:
SQL_ID b4bt4md3aq0fx, child number 0



SELECT /*+ USE_NL(T1 T2) GATHER_PLAN_STATISTICS */ T1.C1, T1.C2, T2.C2 FROM T1, T2
WHERE T1.C1=T2.C1(+) Plan hash value:
574041105
| Id  | Operation                    | Name         | Starts | E-Rows
|

| 1 | NESTED LOOPS OUTER | | 1 | 100K|
| 2 | TABLE ACCESS FULL | T1 | 1 | 100K|
|   3 |   TABLE ACCESS BY INDEX ROWID| T2           |    100K|      1
|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0020502 |    100K|      1
|
-----------------------------------------------------------------------

Predicate Information (identified by operation id):


   4 - access
("T1"."C1"="T2"."C1")

SQL_ID 1cujx3ygjvwfh, child number 0



SELECT /*+ USE_NL(T1 T2) GATHER_PLAN_STATISTICS */ T1.C1, T1.C2, T2.C2 FROM T1 LEFT
OUTER JOIN T2 ON T1.C1=T2.C1 Plan hash value: 574041105
| Id  | Operation                    | Name         | Starts | E-Rows
|

| 1 | NESTED LOOPS OUTER | | 1 | 100K|
| 2 | TABLE ACCESS FULL | T1 | 1 | 100K|
|   3 |   TABLE ACCESS BY INDEX ROWID| T2           |    100K|      1
|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0020502 |    100K|      1
|
-----------------------------------------------------------------------

Predicate Information (identified by operation id):


   4 - access
("T1"."C1"="T2"."C1")

I believe what you are seeing is normal for an explain plan showing estimated cardinality when a nested loops join is used.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Aug 13 2009 - 06:02:22 CDT

Original text of this message