Re: Use of hints

Satish Narasimha wrote at 21-Nov-97 06:55:44

>hello,

Hello!

> Can anybody say me how to hints in optimisation of a query with
> some small e.g.
 

> I know that hints can be used, but don't know how to use, can
> anybody help how to solve this problem ?
 

> Thanks in advance and shall be waiting for the reply.

Here are some examples:

select /*+ FIRST_ROWS */
from table where...

This instructs the optimizer to choose an execution plan that results in giving the first resulting rows faster at the cost of possible longer overall runtime.

The oposite is:

select /*+ ALL_ROWS */
from table where...

You can also tell the optimizer to use a special index:

select /*+ INDEX(index) */
from table where...

There is a possibility to specify the table and the index, but I don't remember exactly. I think the syntax is:

select /*+ INDEX(index table) */
from table
where...

Or suggest a special kind of join:

select /*+ USE_NL(a,b)
from table1 a,table2 b
where a.field=b.field
and...

This causes the join executed via 'nested loops'. Other options are USE_HASH for 'hash-join' and I think USE_MERGE for 'merge-join'.

In any case hints are just hints! If the are not correct, the optimizer ignores them and gives no error message. You should always check the execution plan!

There are some other hints that I don't remember right now!

For further information, RTFM! ;-)

>from
>satish :satishn_at_blr.sni.de
> somasatish_at_hotmail.com

CU,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |