0

I am trying the following query to list the first 10 rows from fisgen table:

SELECT /*+FIRST_ROWS(10)*/ salute, gender, fid
FROM fisgen;

The hint is not executed. All the rows from the table are returned. What should I do so that the hint is executed?

Improve this question
2
  • 1
    The hint will not limit the number of rows returned. It is merely a "suggestion" to the optimizer to adapt the execution plan to the fact that you are only interested in the first 10 rows. But it will not change the result of the query
    – user1822
    Mar 17, 2015 at 9:52
  • While this statement is true in this case, Oracle does have at least one hint which does change the result of a query. It's very nasty and yet another reason why I don't use Oracle anymore.
    – Colin 't Hart
    Mar 17, 2015 at 9:56

1 Answer 1

Reset to default
2

Your query doesn't ask for the first ten rows. For that you need to write something like:

SELECT salute, gender, fid
FROM fisgen
WHERE ROWNUM <= 10;

The hint first_rows(10) is only needed to instruct the optimizer that it should optimize for retrieving the first 10 rows as fast as possible rather than optimizing for whatever percentage of the table the optimizer thinks it will get.

Don't include the hint unless the optimizer gets it wrong.

Improve this answer
5
  • But Iam not able to achieve whatever I give inside the hint.Eg :SELECT /*+ NO_USE_HASH(j f)*/ * from journal j,fisgen fg WHERE j.fid=fg.fid; The explain plan still returns hash as the join technique.
    – Lakshmi
    Mar 17, 2015 at 9:51
  • Why do you want to use the hint? Don't use a hint if you don't need it.
    – Colin 't Hart
    Mar 17, 2015 at 9:53
  • Iam trying to display the query execution plans for various join techniques as part of my project..So Iam using hints to display join techniques
    – Lakshmi
    Mar 17, 2015 at 10:09
  • 1
    I suggest you go to Jonathan Lewis' site. He covers many of the hints and join techniques.
    – Colin 't Hart
    Mar 17, 2015 at 10:48
  • 1
    Get a copy of Cost Based Oracle Fundamentals. It rocks
    – Philᵀᴹ
    Mar 17, 2015 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.