How to count # of records in a table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: How to count # of records in a table

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    How to count # of records in a table

    Is there a better way to count # of records in a table than doing count(*)?

    Sometimes it takes forever and consumes lots of resources to count records in a huge table, say with more than 200+ millions records.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    nop, no better way to get an exact number of records

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    How do you access to this huge table? do you have no PK for it?

  4. #4
    Join Date
    Jan 2001
    Posts
    138
    I did the following to find primary key on the table and got nothing back.

    select * from user_constraints where constraint_type='P' and table_name='GL_BALANCES';

    The table is analyzed once a week.

    So I could do

    SELECT num_rows FROM user_tables where table_name=’GL.GL_BALANCES’;

    It comes back in a a few seconds. While select count(*) runs for a long time, before I kiiled it.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pcotten
    So I could do

    SELECT num_rows FROM user_tables where table_name=’GL.GL_BALANCES’;

    It comes back in a a few seconds. While select count(*) runs for a long time, before I kiiled it.
    That will giv you nearly correct results but not accurate .. as Pando said count(*) is the accurate way to get num of recs.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If an estimate is good enough, then use the sample clause ...
    Code:
    Select
       Count(*)*100
    From
       GL.GL_BALANCES Sample (1);
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    So is it correct that you have a table with 200+ millions rows but no index on it? If so I'm just wondernig how you query this big table? Are you doing fts each time you query it?

  8. #8
    Join Date
    Jan 2001
    Posts
    138
    Originally posted by mike9
    So is it correct that you have a table with 200+ millions rows but no index on it? If so I'm just wondernig how you query this big table? Are you doing fts each time you query it?
    The table is 400 million+ rows in size. I don't query it. The developers and online batch jobs do. We do have indexes on it.

    SQL> select index_name from dba_indexes where table_name='GL_BALANCES';

    INDEX_NAME
    ------------------------------
    GL_BALANCES_N4
    GL_BALANCES_N3
    GL_BALANCES_N1
    GL_BALANCES_N2

    SQL> desc gl_balances
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SET_OF_BOOKS_ID NOT NULL NUMBER(15)
    CODE_COMBINATION_ID NOT NULL NUMBER(15)
    CURRENCY_CODE NOT NULL VARCHAR2(15)
    PERIOD_NAME NOT NULL VARCHAR2(15)
    ACTUAL_FLAG NOT NULL VARCHAR2(1)
    LAST_UPDATE_DATE NOT NULL DATE
    LAST_UPDATED_BY NOT NULL NUMBER(15)
    BUDGET_VERSION_ID NUMBER(15)
    ENCUMBRANCE_TYPE_ID NUMBER(15)
    TRANSLATED_FLAG VARCHAR2(1)
    REVALUATION_STATUS VARCHAR2(1)
    PERIOD_TYPE VARCHAR2(15)
    PERIOD_YEAR NUMBER(15)
    PERIOD_NUM NUMBER(15)
    PERIOD_NET_DR NUMBER
    PERIOD_NET_CR NUMBER
    PERIOD_TO_DATE_ADB NUMBER
    QUARTER_TO_DATE_DR NUMBER
    QUARTER_TO_DATE_CR NUMBER
    QUARTER_TO_DATE_ADB NUMBER
    YEAR_TO_DATE_ADB NUMBER
    PROJECT_TO_DATE_DR NUMBER
    PROJECT_TO_DATE_CR NUMBER
    PROJECT_TO_DATE_ADB NUMBER
    BEGIN_BALANCE_DR NUMBER
    BEGIN_BALANCE_CR NUMBER
    PERIOD_NET_DR_BEQ NUMBER
    PERIOD_NET_CR_BEQ NUMBER
    BEGIN_BALANCE_DR_BEQ NUMBER
    BEGIN_BALANCE_CR_BEQ NUMBER
    TEMPLATE_ID NUMBER(15)
    ENCUMBRANCE_DOC_ID NUMBER(15)
    ENCUMBRANCE_LINE_NUM NUMBER(15)

    Can I get a count by doing something like this:

    select count(CODE_COMBINATION_ID) from gl_balances;

    I don't want the developers doing select count(*) from gl_balances as that query ends up taking long time and is typically the most resource intensive SQL run. That is why I am looking for a better way to get count of records in the table.

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pcotten
    I don't want the developers doing select count(*) from gl_balances as that query ends up taking long time and is typically the most resource intensive SQL run.
    To the matter of fact, Count(*) is the most efficient way than any thing esle .. coz if u have PK/Unique Key Indexes/Indexes On NOT NULL columns, Oracle use Index_FFS of the Index which has least blocks & I cant think of any other operation much efficient that this ..

    Any ways jus check the Plan
    Last edited by abhaysk; 03-29-2004 at 10:09 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ...I heard count(1) was faster...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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