1 2 3 Previous Next 36 Replies Latest reply: Mar 10, 2014 10:13 AM by rp0428 Go to original post RSS
  • 15. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    gandolf999 Journeyer
    Currently Being Moderated

    11 years ago I was working as a PL/SQL developer on a data warehouse application. While I was refactoring some 1500 lines queries I came accross a case where the logic needed to know if rows existed, not how many rows existed. To do that I wrote a function that opened an explicit cursor which returned one row. If I got data I closed the cursor and returned true otherwise I closed the cursor and returned false. Why count all of the rows if you only need to find one. I suppose the select into equivalent would be select col1 into v_col1 from table_a where ... and rownum=1; But to know which is faster you really need to test both ways both using set timing on and SQL tracing.

  • 16. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    user12240205 Explorer
    Currently Being Moderated

    gandolf999 wrote:

     

    11 years ago I was working as a PL/SQL developer on a data warehouse application. While I was refactoring some 1500 lines queries I came accross a case where the logic needed to know if rows existed, not how many rows existed. To do that I wrote a function that opened an explicit cursor which returned one row. If I got data I closed the cursor and returned true otherwise I closed the cursor and returned false. Why count all of the rows if you only need to find one. I suppose the select into equivalent would be select col1 into v_col1 from table_a where ... and rownum=1; But to know which is faster you really need to test both ways both using set timing on and SQL tracing.

    Good point. Yes, I too like to know only if rows (at least one row) exist in a table for some conditions.  But , shouldn't the query be like this:

     

    SELECT COUNT(*) INTO v_1

    FROM table

    WHERE

    <LOTS OF where conditions>

    AND

      ROWNUM < 2;

    With ROWNUM < 2, full table scan will not happen and the moment a single row is returned we get our count, which will be 1.

     

    I tried this method (i.e. with and without ROWNUM < 2) on a test table with 10000 records.

    Without ROWNUM<2 was 2.5 times slower than the one with ROWNUM < 2.

  • 17. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Either

    AND     ROWNUM  = 1

    or

    AND     ROWNUM  < 2

    will work.  They get the same results, and they're equally efficient.

    Personally, I find ROWNUM = 1 slightly easier to read and understand, but you may think otherwise.

  • 18. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    gandolf999 Journeyer
    Currently Being Moderated

    Frank Kulash wrote:

     

    Hi,

     

    Either

    AND     ROWNUM  = 1

    or

    AND     ROWNUM  < 2

    will work.  They get the same results, and they're equally efficient.

    Personally, I find ROWNUM = 1 slightly easier to read and understand, but you may think otherwise.

    I would agree that both are treated the same by the optimizer.

  • 19. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    Solomon Yakobson Guru
    Currently Being Moderated

    user12240205 wrote:

     

    With ROWNUM < 2, full table scan will not happen and the moment a single row is returned we get our count, which will be 1.

     

    I tried this method (i.e. with and without ROWNUM < 2) on a test table with 10000 records.

    Without ROWNUM<2 was 2.5 times slower than the one with ROWNUM < 2.

     

    Adding ROWNUM < 2 has nothing to do with FULL SCAN. It is just a stopkey:

     

    SQL> explain plan for select count(*) from emp where job = 'CLERK';

    Explained.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 2083865914

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
    |*  2 |   TABLE ACCESS FULL| EMP  |     4 |    32 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ---------------------------------------------------

       2 - filter("JOB"='CLERK')

    14 rows selected.

    SQL> explain plan for select count(*) from emp where job = 'CLERK' and rownum < 2;

    Explained.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 2641891062

    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     1 |     8 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |
    |*  2 |   COUNT STOPKEY     |      |       |       |            |          |
    |*  3 |    TABLE ACCESS FULL| EMP  |     1 |     8 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------


    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - filter(ROWNUM<2)
       3 - filter("JOB"='CLERK')

    16 rows selected.

    SQL>

     

    Now about your test. Adding ROWNUM < 2 simply allows you to stop after first matching row is found. So yes, adding ROWNUM < 2 will give performance increase (unless that matching row was found while reading last block). How big of increase is on case per case basis including execution plan and data distribution. So measuring savings on some test table simply can't produce meaningful results.

     

    SY.

  • 20. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    ApexPhil Journeyer
    Currently Being Moderated

    I don't know whether Oracle has changed the way it handles count(*) but it always used to be the case that select count(1).... was faster than select count(*)....

  • 21. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    gandolf999 Journeyer
    Currently Being Moderated

    ApexPhil wrote:

     

    I don't know whether Oracle has changed the way it handles count(*) but it always used to be the case that select count(1).... was faster than select count(*)....

    That was never true, they are the same. There is no advantage of one over the over. Here is a post on asktom that you should look at:

     

    Ask Tom &amp;quot;difference between count(1) and count(*)...&amp;quot;

  • 22. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    ApexPhil Journeyer
    Currently Being Moderated

    Good post Gandolf. That said I go back to the early 90s with Oracle and I saw a test case at some point in the last 2 decades which showed a difference. It may be that Oracle changed it after 2000. Anyway it looks like you can ignore count(1) nowadays.

  • 23. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    ApexPhil Journeyer
    Currently Being Moderated

    In this post:

    Ask Tom &amp;quot;Select Count(1): How it works&amp;quot;

     

    someone said:

    'The only reason I believed COUNT(*) is faster than COUNT(1) is because I read it in 'Oracle

    Performance Tuning', second edition, O'Reilly & Associates, Inc, page 175.  It says:

     

     

    "Contrary to popular belief COUNT(*) is faster than COUNT(1).  If the rows are returned via an

    index, counting the indexed column - for example, COUNT(EMP_NO) is faster still.  The optimizer

    realizes from the existence of the index that the column must also exist (non-null).  We tested the

    following statements on several different computers and found that COUNT(*) consistently runs

    between 15% and 20% faster than COUNT(1) and that COUNT(INDEXED_COLUMN) is 5% faster again.'

     

    It seem that things were different in the dim and distant past but Gandalf is correct, these days * or 1 is the same.

  • 24. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    rp0428 Guru
    Currently Being Moderated
    Which of the 2 methods is better:

    Probably neither.

     

    The more important, and unanswered question, is WHY do you need the count to begin with?

  • 25. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    rp0428 Guru
    Currently Being Moderated

    gandolf999 wrote:

     

    11 years ago I was working as a PL/SQL developer on a data warehouse application. While I was refactoring some 1500 lines queries I came accross a case where the logic needed to know if rows existed, not how many rows existed. To do that I wrote a function that opened an explicit cursor which returned one row. If I got data I closed the cursor and returned true otherwise I closed the cursor and returned false. Why count all of the rows if you only need to find one.

    That process has a major flaw.

     

    Oracle is a multi-user application.

     

    Just because you get zero rows for your query doesn't mean that the next query won't return rows.

     

    Any such count is only relevant to indicate COMMITTED data at the time the query STARTED. By the time your 'count' query actually finishes and returns the count there could be rows in the table.

     

    So it still gets back to what OPs purpose was in obtaining the count. Only if the table is locked will the count be guaranteed to accurately reflect the number of rows in the table that are returned by those 'count' queries.

  • 26. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    gandolf999 Journeyer
    Currently Being Moderated

    rp0428 wrote:

     

    gandolf999 wrote:

     

    11 years ago I was working as a PL/SQL developer on a data warehouse application. While I was refactoring some 1500 lines queries I came accross a case where the logic needed to know if rows existed, not how many rows existed. To do that I wrote a function that opened an explicit cursor which returned one row. If I got data I closed the cursor and returned true otherwise I closed the cursor and returned false. Why count all of the rows if you only need to find one.

    That process has a major flaw.

     

    Oracle is a multi-user application.

     

    Just because you get zero rows for your query doesn't mean that the next query won't return rows.

     

    Any such count is only relevant to indicate COMMITTED data at the time the query STARTED. By the time your 'count' query actually finishes and returns the count there could be rows in the table.

     

    So it still gets back to what OPs purpose was in obtaining the count. Only if the table is locked will the count be guaranteed to accurately reflect the number of rows in the table that are returned by those 'count' queries.

    So if you do a select count(*) into while someone has an uncommitted insert, then do select count(*) into immediately after they commit, the count will change. How is that different than using an explicit cursor to see if any rows are returned? Other than the fact that on operation is trying to find how how many rows exist and the other is just trying to see if there is any data. Given time any query, insert, update or delete will get different results depending on what is happening in the database. As you stated that is the nature of a multi-user application. I just don't see your point.

     

    Of course if you want a true multi-user application, then don't lock anything, because when you start locking rows and especially tables you create latency.

  • 27. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    rp0428 Guru
    Currently Being Moderated
    So if you do a select count(*) into while someone has an uncommitted insert, then do select count(*) into immediately after they commit, the count will change. How is that different than using an explicit cursor to see if any rows are returned?

      Other than the fact that on operation is trying to find how how many rows exist and the other is just trying to see if there is any data.

    It isn't different. Both are WRONG. They are just two different ways to get a count that is meaningless in a multi-user system.

     

    OP wrote and posted 'buggy' code. Then you shared a story that years ago you wrote similar 'buggy' code in a slightly different way.

    Given time any query, insert, update or delete will get different results depending on what is happening in the database. As you stated that is the nature of a multi-user application. I just don't see your point.

    Gee - I thought I made my point pretty clear but I'll try again.

     

    The point is: don't write JUNK code that won't work properly in a multi-user environment. Why write code that may not produce the required results? That makes NO sense.

     

    That is 'buggy' code. It was buggy code when you did it several years ago and it is still buggy code. I don't like buggy code and will NOT allow my developers to write such code. None of the code posted would pass code review.

     

    Any count produced by either of those methods may not (I didn't say WILL not) produce an accurate value. OP still hasn't stated what their purpose was in even wanting a count. The usual purpose is to base further action on whether the count indicates rows exist.

     

    If you can't rely on the count being correct that 'further action' may be taken inappropriately.

     

    Dave Rabone already said it: You're usually better off just "doing it" and handling the case where there's nothing to do, perhaps with an exception block.

  • 28. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated

    I'd go for a third option:

    function count_rows return number is
      rows_counted number := 0;
    begin
      for counter in (select count(*) current_count from whatever ) loop
        rows_counted:= counter.current_count+rows_counted;
      end loop;
      return rows_counted;
    end;

    This will never thow NO_DATA_FOUND or TOO_MANY_ROWS...

     

    bye

    TPD

  • 29. Re: SELECT COUNT(*) INTO        vs        CURSOR c1 IS SELECT COUNT(*)
    rp0428 Guru
    Currently Being Moderated

    This will never thow NO_DATA_FOUND or TOO_MANY_ROWS...

    And, like the other 'solutions' will likely never return a count that is actually useful for anything.

Legend

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