Re: Unbelievable Outer Join
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 13 Aug 2009 05:57:49 -0700 (PDT)
Message-ID: <f4ad6497-80f5-4550-a595-bb14e536e7cb_at_b15g2000yqd.googlegroups.com>
On Aug 12, 1:44 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 12, 12:10 pm, Jan <noreply...._at_gmail.com> wrote:
>
> > Neither of the two tables is partitioned. The statistics are I believe
> > accurate.
>
> > The only way I can explain it is that it is a bug. But this is such a
> > basic functionality that I can't believe it would not be fixed yet. It
> > just seems too silly to be a bug.
>
> > TT203_10100000001372631 is filtered during TABLE ACCESS FULL
> > operation, but the way I undestand the plans is that 6798 rows will be
> > left (moved into the next part which is the nested loop) after filter
> > is applied. No filtering is applied during NESTED LOOPS OUTER
> > operation, it's really just "6798 rows " left outer join "1 row" the
> > way I understand it, which should result in "6798 rows " not "1 row"
> > as estimated.
>
> This is typical behavior for a nested loop join. If you create a
> DBMS_XPLAN for the SQL statement showing actual execution statistics
> (with the STATISTICS_LEVEL set to ALL at the session level or a /*+
> GATHER_PLAN_STATISTICS */ hint), you will see a couple additional
> columns in the plan - one of those columns is the STARTS column. If
> 6,798 is exactly the number of rows returned from the
> TT203_10100000001372631 table, you will see the number 6,798 in the
> starts column of the TABLE ACCESS BY INDEX ROWID | TI238_14935572 line
> in the plan.
>
> For instance, a hash join for a query produces a DBMS_XPLAN output
> like this (broken up to avoid word wrap problems):
> Plan hash value: 2205292220
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> 10000 |
> |* 1 | FILTER | | 1 | |
> 10000 |
> |* 2 | HASH JOIN | | 1 | 99 |
> 10000 |
> |* 3 | TABLE ACCESS FULL | T3 | 1 | 100 |
> 10000 |
> | 4 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 |
> 10000 |
> |* 5 | INDEX RANGE SCAN | IND_T4 | 1 | 100 |
> 10000 |
> ---------------------------------------------------------------------------
>
> ------------------------------------------------------------
> | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
> ------------------------------------------------------------
> |00:00:00.01 | 532 | 355 | | | |
> |00:00:00.01 | 532 | 355 | | | |
> |00:00:00.01 | 532 | 355 | 1517K| 1517K| 1842K (0)|
> |00:00:00.01 | 158 | 155 | | | |
> |00:00:00.01 | 374 | 200 | | | |
> |00:00:00.01 | 121 | 32 | | | |
> ------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(:1<=:2)
> 2 - access("T3"."C1"="T4"."C1")
> 3 - filter(("T3"."C1"<=:2 AND "T3"."C1">=:1))
> 5 - access("T4"."C1">=:1 AND "T4"."C1"<=:2)
>
> The nested loop version of the plan looks like this:
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> 10000 |
> |* 1 | FILTER | | 1 | |
> 10000 |
> | 2 | NESTED LOOPS | | 1 | |
> 10000 |
> | 3 | NESTED LOOPS | | 1 | 99 |
> 10000 |
> |* 4 | TABLE ACCESS FULL | T3 | 1 | 100 |
> 10000 |
> |* 5 | INDEX RANGE SCAN | IND_T4 | 10000 | 1 |
> 10000 |
> | 6 | TABLE ACCESS BY INDEX ROWID| T4 | 10000 | 1 |
> 10000 |
> ---------------------------------------------------------------------------
>
> ---------------------------------
> | A-Time | Buffers | Reads |
> ---------------------------------
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 623 | 187 |
> |00:00:00.06 | 257 | 155 |
> |00:00:00.01 | 366 | 32 |
> |00:00:00.01 | 253 | 168 |
> --------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(:1<=:2)
> 4 - filter(("T3"."C1"<=:2 AND "T3"."C1">=:1))
> 5 - access("T3"."C1"="T4"."C1")
> filter(("T4"."C1"<=:2 AND "T4"."C1">=:1))
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Date: Thu, 13 Aug 2009 05:57:49 -0700 (PDT)
Message-ID: <f4ad6497-80f5-4550-a595-bb14e536e7cb_at_b15g2000yqd.googlegroups.com>
On Aug 12, 1:44 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 12, 12:10 pm, Jan <noreply...._at_gmail.com> wrote:
>
> > Neither of the two tables is partitioned. The statistics are I believe
> > accurate.
>
> > The only way I can explain it is that it is a bug. But this is such a
> > basic functionality that I can't believe it would not be fixed yet. It
> > just seems too silly to be a bug.
>
> > TT203_10100000001372631 is filtered during TABLE ACCESS FULL
> > operation, but the way I undestand the plans is that 6798 rows will be
> > left (moved into the next part which is the nested loop) after filter
> > is applied. No filtering is applied during NESTED LOOPS OUTER
> > operation, it's really just "6798 rows " left outer join "1 row" the
> > way I understand it, which should result in "6798 rows " not "1 row"
> > as estimated.
>
> This is typical behavior for a nested loop join. If you create a
> DBMS_XPLAN for the SQL statement showing actual execution statistics
> (with the STATISTICS_LEVEL set to ALL at the session level or a /*+
> GATHER_PLAN_STATISTICS */ hint), you will see a couple additional
> columns in the plan - one of those columns is the STARTS column. If
> 6,798 is exactly the number of rows returned from the
> TT203_10100000001372631 table, you will see the number 6,798 in the
> starts column of the TABLE ACCESS BY INDEX ROWID | TI238_14935572 line
> in the plan.
>
> For instance, a hash join for a query produces a DBMS_XPLAN output
> like this (broken up to avoid word wrap problems):
> Plan hash value: 2205292220
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> 10000 |
> |* 1 | FILTER | | 1 | |
> 10000 |
> |* 2 | HASH JOIN | | 1 | 99 |
> 10000 |
> |* 3 | TABLE ACCESS FULL | T3 | 1 | 100 |
> 10000 |
> | 4 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 |
> 10000 |
> |* 5 | INDEX RANGE SCAN | IND_T4 | 1 | 100 |
> 10000 |
> ---------------------------------------------------------------------------
>
> ------------------------------------------------------------
> | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
> ------------------------------------------------------------
> |00:00:00.01 | 532 | 355 | | | |
> |00:00:00.01 | 532 | 355 | | | |
> |00:00:00.01 | 532 | 355 | 1517K| 1517K| 1842K (0)|
> |00:00:00.01 | 158 | 155 | | | |
> |00:00:00.01 | 374 | 200 | | | |
> |00:00:00.01 | 121 | 32 | | | |
> ------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(:1<=:2)
> 2 - access("T3"."C1"="T4"."C1")
> 3 - filter(("T3"."C1"<=:2 AND "T3"."C1">=:1))
> 5 - access("T4"."C1">=:1 AND "T4"."C1"<=:2)
>
> The nested loop version of the plan looks like this:
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> 10000 |
> |* 1 | FILTER | | 1 | |
> 10000 |
> | 2 | NESTED LOOPS | | 1 | |
> 10000 |
> | 3 | NESTED LOOPS | | 1 | 99 |
> 10000 |
> |* 4 | TABLE ACCESS FULL | T3 | 1 | 100 |
> 10000 |
> |* 5 | INDEX RANGE SCAN | IND_T4 | 10000 | 1 |
> 10000 |
> | 6 | TABLE ACCESS BY INDEX ROWID| T4 | 10000 | 1 |
> 10000 |
> ---------------------------------------------------------------------------
>
> ---------------------------------
> | A-Time | Buffers | Reads |
> ---------------------------------
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 876 | 355 |
> |00:00:00.06 | 623 | 187 |
> |00:00:00.06 | 257 | 155 |
> |00:00:00.01 | 366 | 32 |
> |00:00:00.01 | 253 | 168 |
> --------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(:1<=:2)
> 4 - filter(("T3"."C1"<=:2 AND "T3"."C1">=:1))
> 5 - access("T3"."C1"="T4"."C1")
> filter(("T4"."C1"<=:2 AND "T4"."C1">=:1))
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
What is most annoying about this is I had just had a discussion with a developer on the same issue not very long ago.
- Mark D Powell --