Hi Rachel,
I've actually tried it both ways, with no joy. best luck I've had so far is
turning max_purm's to about 1000000 which makes it run in about 2 minutes.
Still to slow though.
RF
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 2/12/2003 1:54 PM
Robert,
have you tried recoding the view with the hint in the view text? Or am
I misunderstanding your original post which looks like you've put the
hints on the select count(*) statement?
Rachel
- Freeman Robert - IL <FREEMANR_at_tusc.com> wrote:
> 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).
>
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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).
Received on Wed Feb 12 2003 - 14:44:29 CST
