Perhaps that your problem is that you are running the wrong query. You
probably know like me that the rowid is a physical address, has no
relation whatsoever to any 'rank' of insertion and will change whenever
your DBA decides to move or reorganize the table in anyway.
Therefore, the only resonable reading which can be done of your query is
"Give me all distinct GROUP_IDs from GL_INTERFACE, for this
USER_JE_SOURCE_NAME
and this SET_OF_BOOKS_ID."
If I am not wrong in my understanding, methinks that there are simpler
ways to write it than your query, and perhaps they would deserve to be
explored before spraying three layers of hints ... Granted, I don't
like DISTINCT but I like it better than what is below.
--
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
amit poddar wrote:
>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
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 12 2005 - 15:18:04 CST
