mysterious unnesting

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
----------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation            |  Name            | Rows  | Bytes | Cost 
(%CPU)|
------------------------------------------------------------------------------
|   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

Other related posts: