1 2 Previous Next 15 Replies Latest reply on Apr 18, 2018 11:04 AM by Solomon Yakobson

    count(*) fails while using 12c feature (offset)

    Most Wanted!!!!

      Dear Gurus ,

       

           Need your guidance , Count(*) fails while using "offset" (12C) ,I understand we give no of rows when using offset (eg: offset 0 rows fetch next 5 rows(here count is 5 but shows null ))  but when using fetch first I get  total record count (eg:fetch first 5 rows only (I get total record count in that table)),

       

       

       

       

      EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
      7839KINGPRESIDENT 17-NOV-19815000 10
      7698BLAKEMANAGER783901-MAY-19812850 30
      7782CLARKMANAGER783909-JUN-19812450 10
      7566JONESMANAGER783902-APR-19812975 20
      7788SCOTTANALYST756609-DEC-19823000 20
      7902FORDANALYST756603-DEC-19813000 20
      7369SMITHCLERK790217-DEC-1980800 20
      7499ALLENSALESMAN769820-FEB-1981160030030
      7521WARDSALESMAN769822-FEB-1981125050030
      7654MARTINSALESMAN769828-SEP-19811250140030
      7844TURNERSALESMAN769808-SEP-19811500030
      7876ADAMSCLERK778812-JAN-19831100 20
      7900JAMESCLERK769803-DEC-1981950 30
      7934MILLERCLERK778223-JAN-19821300 10

       

      here are the results , kindly correct me if its wrong.

       

      Select Count(*)From Emp

      /

        COUNT(*)

      ----------

              14

       

       

      Select * From Emp Fetch First 5 Rows Only

      /

      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

      7839 KING PRESIDENT  17-NOV-1981 5000  10

      7698 BLAKE MANAGER 7839 01-MAY-1981 2850  30

      7782 CLARK MANAGER 7839 09-JUN-1981 2450  10

      7566 JONES MANAGER 7839 02-APR-1981 2975  20

      7788 SCOTT ANALYST 7566 09-DEC-1982 3000  20

       

      Select Count(*),Count(1),Sum(1) From Emp Fetch First 5 Rows Only

      /

        COUNT(*)   COUNT(1)     SUM(1)
      ---------- ---------- ----------
              14         14         14

       

       

      Select * From Emp Offset 5 Rows Fetch Next 5 Rows Only

      /

      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

      7902 FORD ANALYST 7566 03-DEC-1981 3000  20

      7369 SMITH CLERK 7902 17-DEC-1980 800  20

      7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30

      7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30

      7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30

       

      Select count(*), Count(1),Sum(1) From Emp A Offset 1 Rows Fetch Next 5 Rows Only

      /

      no rows selected

       

       

       

       

      regards,

      friend

        • 1. Re: count(*) fails while using 12c feature (offset)
          Paulzip

          Post sample data, table scipt and query.  We can't guess what you executed based on a description.

          • 2. Re: count(*) fails while using 12c feature (offset)
            Most Wanted!!!!

            sorry it got posted before completing my question ,I have edited it you can see now

            • 3. Re: count(*) fails while using 12c feature (offset)
              Solomon Yakobson

              Count and how many rows to fetch have nothing in common. Compare:

               

              select  count(*) over() cnt

                from  emp

                fetch first 5 rows only

              /

               

                     CNT

              ----------

                      14

                      14

                      14

                      14

                      14

               

              SQL>

               

              And:

               

              select  count(*) over() cnt

                from  emp

                where rownum <= 5

              /

               

                     CNT

              ----------

                       5

                       5

                       5

                       5

                       5

               

              SQL>

               

              Former instructs oracle to:

               

              a) take all rows in EMP table

              b) for each EMP table row count number of rows in EMP table

              c) fetch first 5 rows

               

              while latter instructs oracle to:

               

              a) fetch first 5 rows of EMP table

              b) for each of the 5 rows count number of fetched rows

              c) display 5 fetched rows

               

              SY.

              1 person found this helpful
              • 4. Re: count(*) fails while using 12c feature (offset)
                Solomon Yakobson

                Ah, I see you posted query in question. Look at:

                 

                Select Count(*),Count(1),Sum(1) From Emp Fetch First 5 Rows Only

                /

                 

                Row limiting clause (fetch first in your case) is executed last, even after order by. So your query reads all rows in EMP table applies implicit group by and calculates counts and sum which results in a single row and only then applies fetch first 5 rows which, as you see now, is meaningless since implicit group by returns only one row. What you intended is :

                 

                SQL> with t as (

                  2             select  *

                  3               from  emp

                  4               fetch first 5 rows only

                  5            )

                  6  select  count(*),

                  7          count(1),

                  8          sum(1)

                  9    from  t

                10  /

                 

                  COUNT(*)   COUNT(1)     SUM(1)

                ---------- ---------- ----------

                         5          5          5

                 

                SQL>

                 

                Which is same (and more efficient) as:

                 

                SQL> select  count(*),

                  2          count(1),

                  3          sum(1)

                  4    from  emp

                  5    where rownum <= 5

                  6  /

                 

                  COUNT(*)   COUNT(1)     SUM(1)

                ---------- ---------- ----------

                         5          5          5

                 

                SQL>

                 

                SY.

                1 person found this helpful
                • 5. Re: count(*) fails while using 12c feature (offset)
                  Most Wanted!!!!

                  thank you ,

                      for offset condition the same should have happened but it did not.

                   

                  Solomon Yakobson wrote:

                   

                  Count and how many rows to fetch have nothing in common. Compare:

                   

                  select count(*) over() cnt

                  from emp

                  fetch first 5 rows only

                  /

                   

                  CNT

                  ----------

                  14

                  14

                  14

                  14

                  14

                   

                  SQL>

                   

                   

                   

                  Former instructs oracle to:

                   

                  a) take all rows in EMP table

                  b) for each EMP table row count number of rows in EMP table

                  c) fetch first 5 rows

                   

                   

                  SY.

                  • 6. Re: count(*) fails while using 12c feature (offset)
                    Cookiemonster76

                    Most Wanted!!!! wrote:

                     

                    thank you ,

                    for offset condition the same should have happened but it did not.

                    The same as what exactly?

                    • 7. Re: count(*) fails while using 12c feature (offset)
                      Solomon Yakobson

                      And to illustrate what is under the hood for row limiting clause:

                       

                      SQL> variable c clob

                      SQL> exec dbms_utility.expand_sql_text('select ename from emp offset 7 rows fetch next 5 rows only',:c)

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL> print c -- I'll format the output

                       

                      C

                      --------------------------------------------------------------------------------

                      SELECT  "A1"."ENAME" "ENAME"

                        FROM  (

                               SELECT  "A2"."ENAME" "ENAME",

                                        ROW_NUMBER() OVER( ORDER BY NULL) "rowlimit_$$_rownumber"

                                 FROM  "SCOTT"."EMP" "A2"

                              ) "A1"

                        WHERE "A1"."rowlimit_$$_rownumber" <= CASE

                                                                WHEN (7>=0) THEN FLOOR(TO_NUMBER(7))

                                                                ELSE 0

                                                              END +5

                          AND "A1"."rowlimit_$$_rownumber" > 7

                       

                      SQL>

                       

                      SY.

                      1 person found this helpful
                      • 8. Re: count(*) fails while using 12c feature (offset)
                        Most Wanted!!!!

                        the "quoted" portion in my replie

                        • 9. Re: count(*) fails while using 12c feature (offset)
                          Solomon Yakobson

                          Most Wanted!!!! wrote:

                           

                          thank you ,

                          for offset condition the same should have happened but it did not.

                          Again, row limiting is applied at the very end when EVERYTHING is already calculated. It simply states - out of everything that's calculated show me just certain rows. Don't confuse it with WHERE clause which limits what rows fit select criteria.

                           

                          SY.

                          1 person found this helpful
                          • 10. Re: count(*) fails while using 12c feature (offset)
                            Cookiemonster76

                            The only difference between your original query and the one  you quoted is that yours uses aggregate count and Solomon's uses analytic count.

                            Both count all the rows in the table.

                            Both have the number of rows fetched limited to 5.

                            But your version aggregates so can only return 1 row no matter what the fetch limit.

                            The analytic version would return as many rows as there are in the table but the fetch clause limits it 5.

                             

                            In either case, as Solomon has already explained / demonstrated, the fetch clause is applied after the count is calculated - and that is by design, and is the way it is documented to work.

                            Oracle is behaving exactly as it is supposed to here, you have just misunderstood what the fetch clause does.

                            1 person found this helpful
                            • 11. Re: count(*) fails while using 12c feature (offset)
                              padders

                              Ooops...not that I was about to call any columns that :-)

                               

                              Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                              With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

                               

                              SQL> SELECT *

                                2  FROM   (SELECT e.empno "rowlimit_$$_rownumber" FROM emp e)

                                3  FETCH FIRST 5 ROWS ONLY;

                              SELECT *

                              *

                              ERROR at line 1:

                              ORA-00918: column ambiguously defined

                               

                              SQL>

                              • 12. Re: count(*) fails while using 12c feature (offset)
                                Sven W.

                                Interesting. I guess it is not documented, that we shouldn't use a "rowlimit_$$_rownumber" as column names...

                                • 13. Re: count(*) fails while using 12c feature (offset)
                                  Solomon Yakobson

                                  padders wrote:

                                   

                                  Ooops...not that I was about to call any columns that :-)

                                   

                                   

                                  Well, oracle had to come up with some aliases, right? Also, another argument not to use $ in names.

                                   

                                  SY.

                                  • 14. Re: count(*) fails while using 12c feature (offset)
                                    Billy~Verreynne

                                    Solomon Yakobson wrote:

                                     

                                    Well, oracle had to come up with some aliases, right? Also, another argument not to use $ in names.

                                    .. because Larry wants all the $'s.

                                    1 2 Previous Next