Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: View HELP Please!

RE: View HELP Please!

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 12 Feb 2003 11:59:02 -0800
Message-ID: <F001.0054B1FF.20030212115902@fatcity.com>


Robert,
 Is the view part of an application that you can make use of an OUTLINE? I think that is the proper terminology. Then you could force the desired optimization.
Ron

>>> FREEMANR_at_tusc.com 02/12/03 01:39PM >>> 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: Ron Rogers
  INET: RROGERS_at_galottery.org

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). Received on Wed Feb 12 2003 - 13:59:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US