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 <[EMAIL PROTECTED]> 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. > [EMAIL PROTECTED] > > > -----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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
