Yes, good idea, but if execution plan is going to be different it beats the purpose of being "prepared". When it's prepared it gets compiled into bytecode, and different bytecode could be generated for different cases. Some databases do have the sql syntax to "fetch first n rows only" or "limit n" for example (i don't know if they can be parametric). Since they have incorporated this functionality, perhaps they have thought these details. I would even imagine that facilities which make this kind of optimization may be available or feasible to use at prepare time but not execute time.
ōystein GrÝvlen <Oystein.Grovlen@Sun.COM>
>>>>> "SAD" == Suavi Ali Demir writes:
SAD> Another little detail about optimization is that
SAD> Statement.setMaxRows() kind of functions on the JDBC side may
SAD> not be sufficient since it is called after SQL statement is
SAD> prepared and returned as an object (after query plan is
SAD> built). Therefore, it may be necessary to have language
SAD> syntax to indicate the intention to fetch first 1000 rows
SAD> only, so that when the query is prepared, this intention can
SAD> be taken into account.
It would be much better if this could be changed at execute-time for
an already prepared statement. That is, the same prepared statement
could be used regardless of how many rows one is going to fetch.