Home » RDBMS Server » Performance Tuning » 9i cbo mode first_rows (Oracle 9i on hp-ux)
9i cbo mode first_rows [message #410730] Tue, 30 June 2009 00:26 Go to next message
TRaj
Messages: 82
Registered: September 2006
Member

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 #410733 is a reply to message #410730] Tue, 30 June 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your question is irrelevant. The query returns all rows it must.

Regards
Michel

[Updated on: Tue, 30 June 2009 00:37]

Report message to a moderator

Re: 9i cbo mode first_rows [message #410739 is a reply to message #410733] Tue, 30 June 2009 01:12 Go to previous messageGo to next message
TRaj
Messages: 82
Registered: September 2006
Member

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 messageGo to next message
Michel Cadot
Messages: 64102
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
Re: 9i cbo mode first_rows [message #411153 is a reply to message #410730] Thu, 02 July 2009 03:15 Go to previous message
TRaj
Messages: 82
Registered: September 2006
Member

Thanks Michel.
Previous Topic: choose which index to be create (merged)
Next Topic: Index on normal and random column data
Goto Forum:
  


Current Time: Fri Dec 02 22:59:51 CST 2016

Total time taken to generate the page: 0.09735 seconds