mysterious unnesting

From: amit poddar <amit.poddar_at_yale.edu>
Date: Wed, 12 Jan 2005 15:51:20 -0500
Message-ID: <41E58DC8.30305@yale.edu>

Hi list,

This query is being unnested by oracle resulting into self join of gl_interface.
What I am not able to understand is the filter in id 1. Step 4 and 5 are hash joined resulting in one data set on which oracle does a sort aggregate
So what is the filter condition since after sort aggregate we have only data set left.

  1 explain plan for
  2 SELECT A.GROUP_ID
  3 FROM GL_INTERFACE A
  4 WHERE A.ROWID = (SELECT MIN(B.ROWID)

  5                       FROM GL_INTERFACE B
  6                      WHERE  B.USER_JE_SOURCE_NAME = 
A.USER_JE_SOURCE_NAME AND
  7                             B.SET_OF_BOOKS_ID = A.SET_OF_BOOKS_ID AND
  8                             B.GROUP_ID = A.GROUP_ID
  9                  ) AND
 10        A.USER_JE_SOURCE_NAME = :B2 AND
 11*       A.SET_OF_BOOKS_ID = :A1

SQL> / Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT     |                  |     1 |    59 |  
3928   (1)|


|*  1 |  FILTER              |                  |       |       

|            |
|   2 |   SORT GROUP BY      |                  |     1 |    59 |  
3928   (1)|


|*  3 |    HASH JOIN         |                  |   734K|    41M|    

15   (0)|


|*  4 |     INDEX RANGE SCAN | GL_INTERFACE_N1  |  2099 | 54574 |    

15   (0)|


|*  5 |     INDEX RANGE SCAN | GL_INTERFACE_N1  |  2099 | 69267 |

15 (0)|

Predicate Information (identified by operation id):

   1 - filter("A".ROWID=MIN("B".ROWID))
   3 - access("B"."USER_JE_SOURCE_NAME"="A"."USER_JE_SOURCE_NAME" AND
              "B"."SET_OF_BOOKS_ID"="A"."SET_OF_BOOKS_ID" AND

"B"."GROUP_ID"="A"."GROUP_ID")

   4 - access("B"."USER_JE_SOURCE_NAME"=:Z AND
              "B"."SET_OF_BOOKS_ID"=TO_NUMBER(:Z))
   5 - access("A"."USER_JE_SOURCE_NAME"=:Z AND
              "A"."SET_OF_BOOKS_ID"=TO_NUMBER(:Z))

22 rows selected.

thanks
amit

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 14:52:18 CST