HINTS for acces method

[fido]

I have systax and execute problem with this syntax

SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;

I found this coonstruction in Oracle8 Tuning.
Problem is that PL SQL Devoleper take construction /* */ as comment

 

[hadfieldjohn]

Fido,

A hint in Oracle is a kind of 'special comment'. If you're using the syntax exactly as you've entered it above Oracle will be paying attention to you.

You can confirm this by using PL/SQL Developer's Explain Plan. Type your SQL in a new Explain Plan window and execute (F5).

The first line in the explain plan description should include some text to prove your hint has been considered. You may need to stretch the first field to see it all, but it will look something like:

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS

Hope this helps.

John

P.S. It is very important to not put a space between the * and + of your hint. /* +FIRST_ROWS for example will NOT work!

 

[fido]

but in SQL Windows return all records not only first as I need

 

[hadfieldjohn]

Ah. FIRST_ROWS is an optimizer hint that tells Oracle that you'd rather have it perform your query in such a way that may be less efficient over the whole result set, but gets you the first results more quickly.

I think what you want to use is the psuedo column ROWNUM. For example, to return only the first row, you would use:

SELECT *
FROM emp
WHERE ROWNUM < 2

John

 

[fido]

thanks