fwiw, The CBO is not choosing the fastest path for whatever reason on this query. When you run the various explain plans with timing on using the hints versus without the hint, it is clear which way is the faster way, but its not going that way using the CBO with a straight query.
I turned the statistics back on and the apps programmer has used the index hint to force its use. Personally, I dont like this, but it works for now as we have a policy to avoid hints since Oracle 8i if at all possible. As for the question as to why the apps programmer wrote the query the way they did ? I do not have an answer as to why apps programmers think the way they do. I recommended they put some boundries on that query through a where clause and they went round and round about why they needed to return all the data. Thanks for your time and Jonathan your book is most excellent. Have a good weekend. Mike -----Original Message----- Sent: Friday, January 24, 2003 2:15 PM To: Multiple recipients of list ORACLE-L hinted Yes ... I can force the hint, but why wont it take the RBO path ? Its a difference of 7 seconds versus a split second. So why would the CBO take that path ? -----Original Message----- Sent: Friday, January 24, 2003 1:14 PM To: Multiple recipients of list ORACLE-L hinted Well, if it can do it under rule-based, then it shouldn't be able to ignore the hint under CBO - I don't suppose there's any chance that you have a typo in the hint ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March ____USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 24 January 2003 21:06 hinted >All the columns in the table are not null ... > >here is an interesting test I ran ... > >several Direct_Path_Write and Read waits with large elasped times >are showing up in the 10046 trace data on this query due to the >full table scan. > >Just deleted statistics on this and fwiw ... > >With Statistics .... >===================== > >SELECT Statement Optimizer=Choose (Cost=225 ...) > SORT (Order By) (Cost=225 ...) > Table Access (Full) of 'table1' > >Physical Reads = 433 > >Without Statistics .. >====================== > >SELECT Statement Optimizer=Choose > Table Access (By Index Row) of 'table1 > Index ( Full Scan ) of 'concatenated index' (Non-Unique) > >Physical Reads = 0 > >Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Johnson, Michael 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: Johnson, Michael 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).
