mysterious unnesting
- From: amit poddar <amit.poddar@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jan 2005 15:51:20 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
