COUNT ON index TABLES
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: COUNT ON index TABLES

  1. #1
    Join Date
    Feb 2006
    Posts
    37

    COUNT ON index TABLES

    Is this the fastest way to get a total count on a table with 30mil records:

    select /*+ ROWID */ count(*) as "TOTAL" from
    MINIAL.CHARGES

    This query took 10 minutes!!!

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    It may be quicker to the count against an indexed column, if you have one e.g.

    select count(primary_key_column) from minial.charges

    That way, it should be able to get the data from the index without looking at the table.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Nonsense. Just "Select count(*) from MINIAL.CHARGES" and let Oracle work out the fastest way. If there's an appropriate index then it will be used.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    Quote Originally Posted by slimdave
    Nonsense. Just "Select count(*) from MINIAL.CHARGES" and let Oracle work out the fastest way. If there's an appropriate index then it will be used.
    If only there were a silver bullet for select count???
    this space intentionally left blank

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.

    Take slimdave's advice:
    select count(*) is the best approach. (unless your using oracle 7 ).

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    CENSUSDBA ,

    Do you have an index on a not null column?

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by ixion
    Too bad Oracle doesn't keep the count in the root block of the index. It would only need a few bytes, and getting the count would be a trivial matter of reading a field from the index root block.
    I can't imagine the contention on that block...
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Well, if an approximate count matches your needs, then you can just get NUM_ROWS from USER_TABLES or ALL_TABLES : it's the number of rows calculated by the last statistics gathering.

    But if you need the exact count, then follow slimdave's advice.

    HTH & Regards,

    rbaraer

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by RBARAER
    Well, if an approximate count matches your needs, then you can just get NUM_ROWS from USER_TABLES or ALL_TABLES : it's the number of rows calculated by the last statistics gathering.
    You might even use the SAMPLE clause to do that -- Oracle will still scan an index if available, but if not you'd only be reading 1% (or your chosen fraction) of the rows or blocks.

    You don't want to "overcook" the sample percentage though.

    Code:
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
    PL/SQL Release 10.1.0.4.0 - Production
    CORE    10.1.0.4.0      Production
    TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
    NLSRTL Version 10.1.0.4.0 - Production
    
    SQL> drop table t
      2  /
    
    Table dropped.
    
    SQL>
    SQL> create table t (col1 not null, col2)
      2  as
      3  select rownum, lpad(rownum,4000)
      4  from dual connect by level <= 10000
      5  /
    
    Table created.
    
    SQL>
    SQL> set autotrace on
    SQL>
    SQL> exec dbms_stats.gather_table_stats(user,'t')
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select count(*) from t;
    
      COUNT(*)
    ----------
         10000
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=16 Card=10000)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          10034  consistent gets
           8051  physical reads
              0  redo size
            420  bytes sent via SQL*Net to client
            508  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> select 100*count(*) from t sample (1);
    
    100*COUNT(*)
    ------------
            9600
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=7)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=16 Card=100 B
              ytes=700)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            476  consistent gets
           3081  physical reads
              0  redo size
            424  bytes sent via SQL*Net to client
            508  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> select 100*count(*) from t sample block (1);
    
    100*COUNT(*)
    ------------
           12800
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
       1    0   SORT (AGGREGATE)
       2    1     TABLE ACCESS (SAMPLE) OF 'T' (TABLE) (Cost=2 Card=100 By
              tes=700)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            162  consistent gets
             91  physical reads
              0  redo size
            425  bytes sent via SQL*Net to client
            508  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> create index i on t (col1) compress;
    
    Index created.
    
    SQL>
    SQL> exec dbms_stats.gather_index_stats(user,'i')
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select count(*) from t;
    
      COUNT(*)
    ----------
         10000
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Card=10000
              )
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             33  consistent gets
             30  physical reads
              0  redo size
            420  bytes sent via SQL*Net to client
            508  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> select 100*count(*) from t sample (1);
    
    100*COUNT(*)
    ------------
           10500
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
              d=100 Bytes=700)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             33  consistent gets
              0  physical reads
              0  redo size
            425  bytes sent via SQL*Net to client
            508  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> select 100*count(*) from t sample block (1);
    
    100*COUNT(*)
    ------------
               0
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (SAMPLE FAST FULL SCAN) OF 'I' (INDEX) (Cost=2 Car
              d=100 Bytes=700)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
            423  bytes sent via SQL*Net to client
            508  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by CENSUSDBA
    Is this the fastest way to get a total count on a table with 30mil records:

    select /*+ ROWID */ count(*) as "TOTAL" from
    MINIAL.CHARGES

    This query took 10 minutes!!!
    whether you do count(*) or count(primary_key_column) the count timing difference is marginal.
    "What is past is PROLOGUE"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width