Re: Unbelievable Outer Join
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