Hi

what is purpose of these two parameters ?

regards
s.kalyan

calyanram@hotmail.com wrote:
> Hi
>
> what is purpose of these two parameters ?
>
> regards
> s.kalyan

I'll answer since I was one of the users pressing for something like
OPT_GOAL to be implemented back in the 7.24 days.

OPT_GOAL tells the optimizer whether to optimize for the minimum total cost
of a query or for the query plan that will return the first few rows the
fastest. The latter can be important to the responsiveness of an
interactive application that has to present the results of a query which
returns hundreds of rows but the user will only see a page of a dozen or two
at a time. For example, with OPT_GOAL set to FIRST_ROWS the optimizer will
TEND to avoid sorting in favor of using an index in the order of the ORDER
BY CLAUSE if one exists or may select that index instead of one that would
improve the speed of joining to another table. By default (OPT_GOAL =
ALL_ROWS), the optimizer will normally elect to get the rows by minimizing
total cost which usually involves sorting the results because IDS's sort
routines and parallel sort capability make sorting lightning fast. The
downside for interactive queries is that the indexed query will present a
few rows at a time as soon as they are read from disk and can pass the
filters while the sorted query must collect all 500 or so rows that would
pass the filters and then sort them in order to be able to present even the
first row to the user.

With ALL_ROWS optimization the engine will ALWAYS sort the results of
parallel query threads collecting data from multiple table fragments EVEN if
the index used to query the fragments is created in the same order as the
ORDER BY clause because the overhead of a merge of the sorted fragment
result sets is a bit higher than the cost of the sort. With FIRST_ROW
optimization set the optimizer will perform the merge and avoid the sort
again improving perceived responsiveness of the database server for
interactive applications.

Similarly, OPTCOMPIND lets you tell the optimizer to favor HASH joins, favor
NESTED LOOP joins or to use pure cost of query to determine which to use.
In most OLTP environments, hash joins are undesirable for the same reason
that sorting is anathema to interactive applications: the time needed to
create the hash table will often delay presenting the first output even in
queries without ORDER BY requirements.

Art S. Kagel