| Understanding FIRST_ROWS [message #65194] |
Fri, 04 June 2004 12:42  |
David Peters
Messages: 6
Registered: April 2004
|
Junior Member |
|
|
Everything that I have read about the "FIRST_ROWS" optimization goal seems to say : "Works faster to return the first result of a query". This statement doesnt make sense to me - when you execute a sql query, you expect the entire result-set to return. In what cases do you get the result set incrementally?
FIRST_ROWS seems to work must faster for me. As opposed to ALL_ROWS where it supposedly returns the entire resultset faster. I use JDBC, so I connect to the database, execute the SQL, and it returns a resultset. In my application, for example, I explode down the node of a tree of parts in order to find the children of a given part. I execute a (complex) sql query, and it returns the children to me, which I use to show to the user.
Why would FIRST_ROWS be so much faster for me (on the order of 10 times faster), if I am executing a query and getting the entire resultset before sending it to the screen?
|
|
|
|
| Re: Understanding FIRST_ROWS [message #65195 is a reply to message #65194] |
Fri, 04 June 2004 13:47  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
From what I know, stored procedures/pkg/functions don't support FIRST_ROWS. It used to be useful in the old versions of SQL*Forms and can still useful in sqlplus.
You can think about it like this: Say you are doing a full table scan for some criteria - as records are found, they are immediately output. If however you take the same query and add and order-by, then FIRST_ROWS cant really work, because all the hits need to be found, then sorted and then returned.
Try adding an order-by to the SQL to see if you stillhave a performance difference. If there is a consistent difference then I think something else is causing the slow performance.
|
|
|
|
| Re: Understanding FIRST_ROWS [message #65198 is a reply to message #65195] |
Sun, 06 June 2004 09:57 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Hints are certainly supported within stored PL/SQL.
ALL_ROWS tends to favour full scans and hash joins and is usually suitable for datawarehouse type queries. FIRST_ROWS tends to favour indexes and nested loops and is usually better for interactive applications. If all the relevant tables are analyzed, you should not have to specify either as the cost-based optimizer should find the best path automatically.
Or if you are comparing /*+ FIRST_ROWS */ vs no hint at all, and the tables are not analyzed, the presence of any hint will make the optimizer switch from rule-based to cost-based.
The purpose of FIRST_ROWS is for applications that need to return a lot of rows to the client but only display a screenful at a time (and the user might not even choose to scroll all the way to the end), although it can sometimes be useful as a general "favour indexes" type of hint when for any reason the CBO seems determined not to, and they help (usually however, if the CBO chooses not to use indexes, it is right not to).
I would try to reproduce the performance difference on the SQL*Plus command line and check the explain plan for the two approaches.
|
|
|
|
| Re: Understanding FIRST_ROWS [message #65199 is a reply to message #65198] |
Mon, 07 June 2004 06:21 |
David Peters
Messages: 6
Registered: April 2004
|
Junior Member |
|
|
Thanks for the feedback. I think that, after examining the explain plan, like you said, the CBO (under ALL_ROWS) seems to be determined not to use the indexes (although they are definately analyzed) - which makes my query run much slower using ALL_ROWS. using the Rrule Based optimizer is also much faster.
Ive tweaked somne of the other common parameters - such as optimizer_index_cost_adj, db_file_multiblock_read_count, etc.. but it doesnt help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
