Skip to Main Content
  • Questions
  • How to count no of records in table without count?

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 08, 2016 - 12:45 pm UTC

Answered by: Chris Saxon - Last updated: October 30, 2019 - 4:24 am UTC

Category: Developer - Version: 10.0

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Never rely on an assumed order

You Asked

2)How to count no of records in table without count?

---Actually,this question asked when i had attended an interview in Dell company.I don't know why they people are asked these type of questions,but i said an answer like in my own way.

--->select max(rownum) from <table name).

and....>select table_name,num_rows from user_tables....They were asked any better one comparing to both so,i could not get an idea with in the span of time on interview.
That is the reason to do asked a question...

and we said...

I'm not sure why people ask these kinds of trick questions in interviews either.

Num_rows in user_tables doesn't count how many rows there are in a table. It returns the number of rows last time stats were gathered. Assuming users have added and/or removed data since this was last done it could be wildly inaccurate.

SQL> create table t as
  2    select rownum x from dual connect by level <= 1;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows from user_tables
  2  where  table_name = 'T';

  NUM_ROWS
----------
         1

SQL>
SQL> insert into t
  2    select rownum from dual connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select num_rows from user_tables
  2  where  table_name = 'T';

  NUM_ROWS
----------
         1

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
      1001


Max(rownum) will return the number of rows. But it's more confusing than count(*). And it's slower:

SQL> select max(rownum) from t;

MAX(ROWNUM)
-----------
       1001

SQL>
SQL> set timing on
SQL> declare
  2    l pls_integer;
  3  begin
  4    for i in 1 .. 100000 loop
  5       select max(rownum) into l from t;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.85
SQL>
SQL> declare
  2    l pls_integer;
  3  begin
  4    for i in 1 .. 100000 loop
  5       select count(*) into l from t;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.87


I see no reason to use this over count(*).


==================

Addenda:

Often the "answer" that the interviewer wants is that you know of the SAMPLE clause. For example, you could do:

select count(*) * 100 from T sample block (1);

or similar.

Once again - an estimate only.

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Better answer

February 09, 2016 - 3:12 am UTC

Reviewer: John from Greenbelt MD USA

"COUNT(*) SAMPLE" is nice to know, but it violates a constraint in the original question - "... without using count".

I believe a better response to the question might be "count(*) is most effective method of counting rows in a table, because that is precisely what it is intended to do. Any other method without count(*) could be considered a trick or side-effect, and would not be appropriate for a production system." It is analogous to asking how to select rows from a table without using "select". Why would anyone even consider doing that, other than in an academic challenge.
Connor McDonald

Followup  

February 09, 2016 - 3:48 am UTC

Good point

another suggestion

February 09, 2016 - 6:52 am UTC

Reviewer: Wijnand Engelkes from Netherlands

Don't see the point of avoiding count(*) but if you really want to you could:

set pages 0
select null from big_table;
123456 records selected

(may take some time:) )

faster one

February 09, 2016 - 12:44 pm UTC

Reviewer: Markus from Switzerland

SQL> create table tc as select * from dba_objects;

Table created.

SQL> select count(*) from tc;

COUNT(*)
----------
20726

SQL> select sum (nvl2 (rowid, 1, 0)) from tc;

SUM(NVL2(ROWID,1,0))
--------------------
20726


Chris Saxon

Followup  

February 09, 2016 - 1:04 pm UTC

Sum(nvl2()) comes out much slower for me (12.1.0.2):

SQL> declare
  2   l pls_integer;
  3  begin
  4    for i in 1 .. 1000 loop
  5      select count(*) into l from tc;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.84
SQL>
SQL> declare
  2   l pls_integer;
  3  begin
  4    for i in 1 .. 1000 loop
  5      select sum (nvl2 (rowid, 1, 0)) into l from tc;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.87

February 09, 2016 - 4:40 pm UTC

Reviewer: David from Boulder, CO

You could create a custom function that accepts the table name as the argument and uses a loop to incrementally count the number of the records in the table.

or

select sum(nvl2(primary_key_column, 1, 0) from table;

And while I'm not familiar enough with the MODEL clause I wouldn't be surprised if there's a MODEL technique that will return a count of rows without using COUNT.

Sometimes a question like this is a "trick" question just to see how the candidate reacts so the interviewer can get a better sense of who they are. However, they could also be looking for a candidate that can quickly come up with several alternate solutions.
Chris Saxon

Followup  

February 09, 2016 - 5:28 pm UTC

Asking for alternate solutions is a good idea - when the problem calls for it! Counting rows isn't one of those.

I think John's answer in the first follow up is the perfect response for this.

Approx_count_distinct

October 29, 2019 - 4:09 pm UTC

Reviewer: Tim from Ogden, UT USA

You could use APPROX_COUNT_DISTINCT(ROWID) for a single table or with NVL() added to rowid for a full joined table. The reason an interviewer might ask this question is because count(*) can be very resource intensive for very large tables and it is good to have alternatives. Often when you do a count(*) you don't really need to know the exact count of records but you need to get an idea of how many rows are in the table without doing a full table scan.
Connor McDonald

Followup  

October 30, 2019 - 4:24 am UTC

Good point

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here