4 Replies Latest reply: Jul 25, 2013 1:30 PM by Billy~Verreynne RSS

    should cursors  help to increase query performance ?

    GTS (DBA)

      Good Morning Experts ;

       

      I have small doubt on EXPLICIT CURSORS.

      Ex :

      I have a table  it contains  million records  nearly 40 GB.

      If i issue query  select (*)  - i  will get output.

      If i write explict cursor for same program , i will display same output.

       

      Apart ffrom taht - what are the advantages of CURSORS ?

       

      we can watch status of DML operations using some attributes ( %FOUND . %NOTFOUND ,  %ROWCOUNT)

      Except above mentioned things , what  ELSE ?

       

      thanks.

        • 2. Re: should cursors  help to increase query performance ?
          sb92075

          8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

           

          Good Morning Experts ;

           

          I have small doubt on EXPLICIT CURSORS.

          Ex :

          I have a table  it contains  million records  nearly 40 GB.

          If i issue query  select (*)  - i  will get output.

          If i write explict cursor for same program , i will display same output.

           

          Apart ffrom taht - what are the advantages of CURSORS ?

           

          we can watch status of DML operations using some attributes ( %FOUND . %NOTFOUND ,  %ROWCOUNT)

          Except above mentioned things , what  ELSE ?

           

          thanks.

           

          other sessions can NEVER see uncommitted DML  changes regardless if implicit or explicit cursor is used.

          • 3. Re: should cursors  help to increase query performance ?
            jgarry

            See this: Explicit & Implicit Cursor

            (and of course keep in mind that some things may change over time)

            • 4. Re: should cursors  help to increase query performance ?
              Billy~Verreynne

              8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

               

              I have small doubt on EXPLICIT CURSORS.

              Ex :

              I have a table  it contains  million records  nearly 40 GB.

              If i issue query  select (*)  - i  will get output.

              If i write explict cursor for same program , i will display same output.

               

              Apart ffrom taht - what are the advantages of CURSORS ?

               

              All SQLs are parsed and executed as cursors. You cannot execute a SQL in any other way.

               

              The PL/SQL language can create a cursor for you, implicitly:

              declare
                cnt integer;
              begin
                select count(*) into cnt from my_table;
              end
              

               

              You can in the PL/SQL language, create a cursor for explicitly:

              declare
               cursor c is select count(*) from my_table;
                cnt integer;
              begin
                open c;
               fetch c into cnt;
               close c;
              end;
              

               

              The SQL engine does not care. It does not know which method you used in PL/SQL. In both cases the very same identical SQL cursor is parsed and executed.

               

              So the question about implicit versus explicit cursors is not a SQL question. It is a PL/SQL language question. Which method meets the coding/logic/usage/re-use/modularisation/etc requirements the best? And that is decision to make - as the better one depends on the situation. Neither one is an overall better choice than the other.