Re: Unbelievable Outer Join
Date: Wed, 12 Aug 2009 10:44:57 -0700 (PDT)
Message-ID: <f576ec4d-8e9e-4bb4-a6cb-0489f7ca6e32_at_k6g2000yqn.googlegroups.com>
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.
Received on Wed Aug 12 2009 - 12:44:57 CDT