This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 15, 2013 10:44 AM by user522961 Go to original post RSS
  • 15. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Randolf I posted the View definitions.
    Thanks.
  • 16. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    Thank you Randolf.
    Sorry for delay. I was busy for some other deployment.
    Here are the view definitions
    These are not the complete view definitions. We are looking for the following views in my understanding:

    PS_SP_RCV1_NONVWLN
    PS_SP_RECV1_NONVW

    The latter seems to be missing from your reply.

    Randolf
  • 17. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    yes, my apologies.
     
    SQL> set long 10000 
    SQL> select view_name, text from dba_views where view_name like '%PS_SP_RECV1_NONVW%'; 
    
    VIEW_NAME 
    ------------------------------ 
    TEXT 
    -------------------------------------------------------------------------------- 
    PS_SP_RECV1_NONVW 
    SELECT DISTINCT A.BUSINESS_UNIT , B.DESCR FROM PS_RECV_HDR A , PS_BUS_UNIT_TBL_F 
    S B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RECV_STATUS different_from 'C' 
    Thanks.
  • 18. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    yes, my apologies.
    OK, thanks. So there is nothing fancy in these views, just plain SELECT DISTINCT. The problem seems to be that there are only a very few distinct values of BUSINESS_UNIT, so in case of the UNION ALL variant of the query the optimizer detects this and applies a "DISTINCT PLACEMENT" transformation that moves the DISTINCT to the PSCRECV_HDR table where only a single (or few) row(s) seems to result from that operation, which prevents the generation of those huge intermediate join results.

    In case of the UNION, for some reason, that DISTINCT PLACEMENT is not performed, very likely because the optimizer decides that it can go without any DISTINCT at all at that stage.

    It would probably need an optimizer trace file (event 10053 or "trace[RDBMS.SQL_Optimizer.*]" from 11g on) to get a better understanding why the optimizer decides to do so, but as already said, you better clarify this with Oracle Support.

    At present I don't see anything useful that could be added here that hasn't already been mentioned in this and the other related thread.

    Randolf
  • 19. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thanks Randolf.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points