7 Replies - 219706 Views - Last Post: 23 November 2010 - 06:49 AM

#1 fyrestorm  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 10
  • View blog
  • Posts: 3,113
  • Joined: 04-April 02

Row Count in Oracle

Posted 14 January 2007 - 04:27 PM

I need to get a row count of about 20 tables in an oracle db. Some of these tables have millions of records and it would take about 30 min just to get the row count of a table with about 5 million records if I did count(*).

Is there a simpler way to get a table rowcount besides doing "SELECT COUNT(*) FROM tbl"?
Is This A Good Question/Topic? 0
  • +

Replies To: Row Count in Oracle

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Row Count in Oracle

Posted 14 January 2007 - 07:04 PM

Try the tbl.count syntax...depends which version of Oracle you are using.

Do you have access to the system table for that db? If so, the row count for each table should be stored in it.
Was This Post Helpful? 0
  • +
  • -

#3 fyrestorm  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 10
  • View blog
  • Posts: 3,113
  • Joined: 04-April 02

Re: Row Count in Oracle

Posted 14 January 2007 - 09:42 PM

does the system table have a specific name and do you know what column in it gives the row count?
Was This Post Helpful? 0
  • +
  • -

#4 fyrestorm  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 10
  • View blog
  • Posts: 3,113
  • Joined: 04-April 02

Re: Row Count in Oracle

Posted 15 January 2007 - 06:54 AM

I found out that I can just do a

SELECT MAX(ROWNUM) FROM tbl

and that would return the number of rows.
Was This Post Helpful? 0
  • +
  • -

#5 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Row Count in Oracle

Posted 22 January 2007 - 11:55 AM

View Postfyrestorm, on 15 Jan, 2007 - 06:54 AM, said:

I found out that I can just do a

SELECT MAX(ROWNUM) FROM tbl

and that would return the number of rows.


Not quite. That will give you the number of rows used in the storage of the table. If any rows have been deleted, then you will have a higher number than the actual rows used, since any deleted rows will be empty. (Higher numbered rows are not renumbered when previous rows are deleted.)

Furthermore, subsequent inserts will use those empty rows before adding new rows. So, if you insert 100 rows into a new table and then delete rows 25-75 and then insert 15 rows, max(rownum) will give you 100 even though there are only 65 rows. And the most recently added row has a rownum of 40.

Do the tables have a primary key, like "id"? If so, count(id) might be a lot faster than count(*). Also, if you have access to it, take a look at the system table. (It's called 'system'.) Just DESC it and go from there. It will provide some good info for you.

Wayne
Was This Post Helpful? 1

#69 gwungfu  Icon User is offline

  • New D.I.C Head

Reputation: 9
  • View blog
  • Posts: 1
  • Joined: 06-May 08

Re: Row Count in Oracle

Posted 06 May 2008 - 10:35 AM

View Postfyrestorm, on 14 Jan, 2007 - 04:27 PM, said:

I need to get a row count of about 20 tables in an oracle db. Some of these tables have millions of records and it would take about 30 min just to get the row count of a table with about 5 million records if I did count(*).

Is there a simpler way to get a table rowcount besides doing "SELECT COUNT(*) FROM tbl"?


SELECT table_name, nvl(num_rows,1)
FROM dba_tables
Was This Post Helpful? 2

#70 Guest_Ankit*


Reputation:

Re: Row Count in Oracle

Posted 16 March 2010 - 10:50 PM

yes,

You can directly give the column name instead of using "*" in count function.
To get the exact total records count, you must use a non-null column in count fuction. You will surely got the better result.
Was This Post Helpful? 0

#71 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5835
  • View blog
  • Posts: 12,690
  • Joined: 16-October 07

Re: Row Count in Oracle

Posted 23 November 2010 - 06:49 AM

I moved the necro post to this necro'd post here.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1