1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to get large table rowcount quickly

Discussion in 'SQL PL/SQL' started by prasuna, Oct 8, 2012.

  1. prasuna

    prasuna Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    85
    Hi All,
    i have a table which has billions of records, i tried to get row count using count(1) but it is taking too much of time, is there any way to get count quickly.
    i can't get row count from dba_table,because my schema is not refreshed recently.

    Thanks.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Prasuna,

    Query works fine.

    Code (SQL):
    SQL> SET TIMING ON
    SQL> SELECT COUNT(1) FROM ALL_TABLES;

      COUNT(1)
    ----------
         29126

    Elapsed: 00:00:00.73
    SQL>
    takes only 73 ms
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Gather statistics on table and do select COUNT(*) from <table> .
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't mention if you have any indexes on this table; a unique index or primary key will cause Oracle to scan the index instead of the table and it should return fairly quickly. Of course you need to define the term 'too much time' -- it's going to take a while to returnn a count in the billions.
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Of course, as you mentioned defining properly the primary key and possibly indexes are quite important, but what I understand from the OP is that the purpose, is to count the total number of rows in the table, therefore a full scan. So I'm not sure whether in this context the index can enhance the performance (though a primary key is also a unique index :) ).


    Regards,
    Dariyoosh
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't understand the various mechanisms Oracle can use to return the results of count(*) (or count(1) which hasn't been proven to be any faster). A 'select count(*) from ...' can be done in one of two ways:

    1) A full table scan iin the absence of a primary key index or a unique index on a NOT NULL column.
    2) An index fast full scan or an index full scan of a primary key index or a unique index on a NOT NULL column, bypassng the table altogether.

    Given the size of the table both will take some time to complete, but the latter case does not touch the table, instead using the index to return the row count.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> SELECT COUNT(*) FROM emp;
     
      COUNT(*)
    ----------
            14

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2083865914
    -------------------------------------------------------------------
    | Id  | Operation          | Name | ROWS  | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------
    Note
    -----
       - dynamic sampling used FOR this statement (level=2)

    Statistics
    ----------------------------------------------------------
              5  recursive calls
              0  db block gets
             15  consistent gets
              0  physical reads
              0  redo SIZE
            422  bytes sent via SQL*Net TO client
            419  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
     
    SQL>
    SQL> ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
     
    TABLE altered.
     
    SQL>
    SQL> SELECT COUNT(*) FROM emp;
     
      COUNT(*)
    ----------
            14

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3381701820
    -------------------------------------------------------------------
    | Id  | Operation        | Name   | ROWS  | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |        |     1 |            |          |
    |   2 |   INDEX FULL SCAN| EMP_PK |    14 |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------
    Note
    -----
       - dynamic sampling used FOR this statement (level=2)

    Statistics
    ----------------------------------------------------------
             28  recursive calls
              0  db block gets
             29  consistent gets
              0  physical reads
              0  redo SIZE
            422  bytes sent via SQL*Net TO client
            419  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              4  sorts (memory)
              0  sorts (disk)
              1  ROWS processed
     
    SQL>
     
  8. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Many thanks for this clarification, :)


    Regards,
    Dariyoosh