RE: View HELP Please!

Robert,

Have you considered the current values of _complex_view_merging, _push_join_predicate, _push_join_union_view. Some of them may be obsolete in 9iR2, but there are some published and recommended setting for these for specific Apps 11i environments. You could also look at/play with the value of 'optimizer_max_permutations'. You might have this piece of code up your sleeve, but if you don't here ya go!

column ksppinm format a45 heading "Name" column ksppstvl format a30 heading "Value" column ksppdesc format a79 heading "Description" select x.ksppinm, y.ksppstvl, x.ksppdesc from x$ksppi x,

       x$ksppcv y
where (x.indx = y.indx) and

       (x.ksppinm like '\_&undoc_parm' escape '\') order by x.ksppinm
/

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Freeman Robert - IL [mailto:FREEMANR_at_tusc.com]
> Sent: Wednesday, February 12, 2003 10:39 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: View HELP Please!
>
>
> Done that Dennis. The difference in the two is how the plan
> is being crafted
> by the optimizer. Bottom line is that the excellent plan I'm
> getting from
> the query by itself is not being replicated when it's moved
> into a view. It
> appears that this is because the predicate is not being
> pushed into the view
> properly. Since it's not, the view ends up just being a two
> table join with
> two full table scans who's row set is returned and then
> filtered. What I
> want is the predicate to be pushed into the view, which will
> allow for an
> index scan based on that predicate.
>
> RF
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 2/12/2003 9:53 AM
>
> Robert - I always try EXPLAIN PLAN as a starting point.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, February 12, 2003 9:19 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I'm fighting a view.... Someone hand me a big dagger with
> which to kill
> it.
> I have a view that takes 6 minutes to run when I query it like this:
>
> select
> count(*) from TST_XVW a
> where claim_carrier_key=41721;
>
> Yet, if I take the SQL from the view, add the claim_carrier_key
> predicate to
> it, I get a run time of 6 seconds. I've tried several different hints
> (push_pred, use_nl, etc) and I'm just not seeming to be able
> to get the
> optimizer to give me a good plan. Any help on this would be
> appreciated.
>
> Robert
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Freeman Robert - IL
> INET: FREEMANR_at_tusc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Freeman Robert - IL
> INET: FREEMANR_at_tusc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).