This discussion is archived
6 Replies Latest reply: Jan 1, 2013 5:44 AM by newbee RSS

sql hint

newbee Newbie
Currently Being Moderated
Hi
I use 11.2g XE database and PLSQLdeveloper 8 .

i create a test window in plsqldeveloper and add this code

Declare
i Integer;
Begin
For i In (Select /*+ FIRST_ROWS(5) */
t.Cod
From Cities t) Loop
Dbms_Output.Put_Line(I.COD);
End Loop;

End;

unfortunately instead of 5 rows , i saw all records of table . ?(
why ?
thanks
  • 1. Re: sql hint
    Solomon Yakobson Guru
    Currently Being Moderated
    newbee wrote:
    unfortunately instead of 5 rows , i saw all records of table . ?(
    why ?
    Why? Because you have to RTFM FIRST_ROWS Hint:

    The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. This doesn't mean query will stop after returning N rows. It means forst N rows will be returned as fast as possible even though such plan might result in longer time to return the rest of the rows. If you want just 5 rows, add WHERE ROWNUM <= 5 to your query.

    SY.
  • 2. Re: sql hint
    sb92075 Guru
    Currently Being Moderated
    newbee wrote:
    Hi
    I use 11.2g XE database and PLSQLdeveloper 8 .

    i create a test window in plsqldeveloper and add this code

    Declare
    i Integer;
    Begin
    For i In (Select /*+ FIRST_ROWS(5) */
    t.Cod
    From Cities t) Loop
    Dbms_Output.Put_Line(I.COD);
    End Loop;

    End;

    unfortunately instead of 5 rows , i saw all records of table . ?(
    why ?
    thanks
    more than 5 rows are returned because of the FOR LOOP.
    no PL/SQL is required
    just do as below

    Select /*+ FIRST_ROWS(5) */ t.Cod From Cities t;
  • 3. Re: sql hint
    xtender Pro
    Currently Being Moderated
    As Solomon said, if you want to limit number fetched rows you can:
    1) add predicate "rownum<=N" ( or analytic row_number() in inline view with outer predicate)
    2) execute "fetch" N times;
    3) fetch with limit N.

    With "rownum<=N" also optimizer tries to create plan with first_rows mode since "_optimizer_rownum_pred_based_fkr" is enabled.

    Regards,
    Sayan Malakshinov
    http://orasql.org
  • 4. Re: sql hint
    onedbguru Pro
    Currently Being Moderated
    Also note that if you want a particular 5 rows, you will need to make sure you properly code your query. ROWNUM values are assigned AFTER the select is complete.
  • 5. Re: sql hint
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Two issues.

    Do not use SQL hints.

    Secondly, the PL/SQL code is just plain silly. Why use PL/SQL to fetch SQL data and store that data in a PL/SQL static buffer, in expensive server memory? Then return control to the client where the client now reads this buffer and displays its contents?

    Yes, a lot of PL/SQL code examples use DBMS_OUTPUT to demonstrate basic concepts. However, something that almost every single one of them neglected to state is that DBMS_OUTPUT is a primitive interface that is dangerous to the health of the server when abused. And that the correct way to return SQL data from the database is via cursors. Not via non-scalar PL/SQL variables.
  • 6. Re: sql hint
    newbee Newbie
    Currently Being Moderated
    thanks for your attention .
    this staff code is for example .

    I thought /*+first_rows(n)*/ is a hint for limit result of query !!! whereas it affect ORACLE OPTIMIZER
    to fetch n first rows rapidly .

    I use rownum <= 5 for my purpose .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points