6 Replies Latest reply: Jan 1, 2013 7:44 AM by newbee RSS

    sql hint

    newbee
      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
          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
            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
              Sayan Malakshinov.
              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
                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
                  Billy~Verreynne
                  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
                    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 .