Re: CBO not using the index even though it is faster when hinted

set optimizer_index_cost_adj to value that is less than (cost without hint * 100 / cost with hint). Waleed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 24, 2003 7:34 PM Lower the settings for optimizer_index_cost_adj. set

Re: CBO not using the index even though it is faster when hinted ....

Are any of the columns in the index declared to be NOT NULL - if not, then the hint is invalid because obeying it could produce the wrong answer. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see

RE: CBO not using the index even though it is faster when hinted ....

Oh heck, of course! Why didn't I think of that? BAHH! Gotta quick staying up so bloody late! RF -Original Message- Lewis Sent: Friday, January 24, 2003 2:49 AM To: Multiple recipients of list ORACLE-L hinted Are any of the columns in the index declared to be NOT NULL - if not,

RE: CBO not using the index even though it is faster when hinted

Title: RE: CBO not using the index even though it is faster when hinted How about a where clause for your query ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal

RE: CBO not using the index even though it is faster when 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

Re: CBO not using the index even though it is faster when 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

RE: CBO not using the index even though it is faster when 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

RE: CBO not using the index even though it is faster when hinted

Mike, 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. This is not to distract you from the original problem, but Direct Path Reads/Writes is I/O that bypass the Db block buffers to read directly

Re: CBO not using the index even though it is faster when hinted

What were the logical reads query + current during this same test. Physical reads could be different because the first query loaded the rows into memory. John [EMAIL PROTECTED] wrote: All the columns in the table are not null ... here is an interesting test I ran ... several

RE: CBO not using the index even though it is faster when hinted

John This is not to distract you from the original problem, but Direct Path Reads/Writes is I/O that bypass the Db block buffers to read directly into PGA, and is typical of sort I/O and PQ (among many other reasons). Thanks on this See note I sent just before this on what I did.

RE: CBO not using the index even though it is faster when hinted

Lower the settings for optimizer_index_cost_adj. set optimizer_index_cost_adj to value that is less than (cost without hint / cost with hint). This should help. Regards, Waleed -Original Message- Sent: Friday, January 24, 2003 3:39 PM To: Multiple recipients of list ORACLE-L hinted

Re: CBO not using the index even though it is faster when hinted ....

Michael, Your select query has no where clause and the select clause includes columns not in the index, therefore Oracle must go to the table... every row in the table. Are you sure your dummy query is exactly the same type of syntax as the real one? It would be assumed that a full table scan

Re: CBO not using the index even though it is faster when hinted ....

Johnson, Michael, Does use index really faster than using FTS? You are getting all the data from the table, and unless the sort is really that cost, I think full table scan is preferred. And the order of the data in the table(the clustering factor in index) is another important

Re: CBO not using the index even though it is faster when hinted ....

How do you know an index scan would be faster? Did you get the query to use an index? What hint? Do you have the tracing stats for both with and without an index? First, you dont have a where clause filter on an indexed column? If all the columns in the select were indexed, maybe u could have

RE: CBO not using the index even though it is faster when hinted ....

Re: My last comments... I may be thinking about group by's, not order by's... I'm confusing myself... :-) RF -Original Message- Michael Sent: Thursday, January 23, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Anyone have any problems with the CBO not using a index when

RE: CBO not using the index even though it is faster when hinted ....

I believe (pulling back my tuning memory which has been jump started of late) that the order by is applied AFTER all the rows have been returned by the query. Thus, your query is effectively this: Select col1, col2, col3, blah1, blah2 from table; Then the order by is applied. Since there is no