Re: mysterious unnesting

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: amit.poddar@xxxxxxxx
  • Date: Wed, 12 Jan 2005 22:22:02 +0100

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
>
>--
>//www.freelists.org/webpage/oracle-l
>
>  
>


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

Other related posts: