Thanks for the question.

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

Last updated: October 30, 2019 - 4:24 am UTC

Version: 10.0

Viewed 50K+ times! This question is

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 Chris 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.

Rating

(6 ratings)

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

Comments

Better answer

John, February 09, 2016 - 3:12 am UTC

"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.

February 09, 2016 - 3:48 am UTC

Good point

another suggestion

Wijnand Engelkes, February 09, 2016 - 6:52 am UTC

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

Markus, February 09, 2016 - 12:44 pm UTC

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

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

David, February 09, 2016 - 4:40 pm UTC

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.

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

Tim, October 29, 2019 - 4:09 pm UTC

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.

October 30, 2019 - 4:24 am UTC

Good point

approx_count_distinct will not help

Konstantin Kivi, December 20, 2020 - 9:33 am UTC

I believe that approx_count_distinct is called approximate because of the manner in which it process distinct values.
It will have to scan entire table to it's intended job.

However the motivational part of this answer is correct.

PS. It's a pity to see what AskTom have become
without Tom Kyte. "Good point" is not what people used to find here.

Breadcrumb

Question and Answer