DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Solution to get count(*) from Big tables quickly

  1. 02-07-2012, 06:31 AM #1
    sumit
    sumit is offline Member
    Join Date
    Dec 2001
    Posts
    203

    Solution to get count(*) from Big tables quickly

    Oracle Version: Either 10g OR 11gR2

    Objective: Finding total count(number of records) in each table under any schema.

    Avoiding: trying to avoid count(*) OR referring num_rows in dba_tables (even if stats in on)

    Tools: Either exp OR expdp

    Questions
    (1) Is there any way I could generate an Oracle export log file with number of records per table (exactly like our normal export log) WITHOUT actually exporting data?

    (2) Or is there any suggestion to retrieve total number of records [ count(*)] from big tables quickly?


    Please Note: BTW, I am aware of ESTIMATE_ONLY, but that is not what I am looking for.

    Regards
    Sumit
    sumit
    Reply With Quote Reply With Quote
  2. 02-07-2012, 11:10 AM #2
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Other than having a PK or index on a "not null" column plus doing the count(*) in parallel... nothing I can think about.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
    Reply With Quote Reply With Quote
  3. 02-16-2012, 03:03 PM #3
    skhanal
    skhanal is offline Junior Member
    Join Date
    Dec 2002
    Posts
    74
    If you gather stats with full compute option, you can get number of records from the statistics view.
    Reply With Quote Reply With Quote
  4. 02-17-2012, 01:43 PM #4
    gandolf989's Avatar
    gandolf989
    gandolf989 is offline Senior Advisor
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If you only want an estimate, and you keep stats on your schema,
    i would look at num_rows in the dba_tables view.

    Or you could try a ouija board...
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules

Click Here to Expand Forum to Full Width