9i cbo mode first_rows [message #410730] |
Tue, 30 June 2009 00:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
TRaj
Messages: 82 Registered: September 2006
|
Member |
![thiagesh%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi
What's the percentage of rows that the cbo mode first_rows will return for a query. Suppose if a table has 200000 rows then, setting optimizer_mode to 'FIRST_ROWS' will return how many rows initially.
Thanks in advance.
|
|
|
|
Re: 9i cbo mode first_rows [message #410739 is a reply to message #410733] |
Tue, 30 June 2009 01:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
TRaj
Messages: 82 Registered: September 2006
|
Member |
![thiagesh%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi Michel,
"Your question is irrelevant. The query returns all rows it must."
My question was much more relevant. I think, you didn't understand my question properly!
Yes, ofcourse the query has to return all rows, I know pretty well.
See the below paragraphs within quotes.
"In simple terms it ensures best response time of first few rows (n rows).
This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.
Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running."
I just want to know how much (% or in number) of rows initially (first few rows) will be returned to the user for a query which returns totally 200000 records?
|
|
|
Re: 9i cbo mode first_rows [message #410746 is a reply to message #410739] |
Tue, 30 June 2009 01:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As I said it is irrelevant to the optimizer goal, the "first few rows" is relevant to your array fetch size which is not known of the optimizer, so "first few rows" is first few rows without any specification.
This sentence just means that Oracle choose the best plan to return as fast as possible the first rows, how many does not matter, it is just first few ones, 1, 2, 3 or...
If you want to be more precise you should use FIRST_ROWS_X mode instead.
Regards
Michel
|
|
|
|