eCharcha.Com   Support eCharcha.Com. Click on sponsor ad to shop online!

Advertise Here

Go Back   eCharcha.Com > Science and Technology > How to

Notices

How to Stuck for an answer to "How to do .."?

Reply
 
Thread Tools Display Modes
  #1  
Old July 9th, 2010, 11:38 AM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,928
echarcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond repute
Question How to get a count or rows in a very large Oracle table?

Suppose an Oracle database has a table which has grown very large, i.e. millions of rows.

This table keeps growing as new records are added everyday. And all the existing rows must stay in this table because this table is required for a lookup.

Now for some process to run, say every x hours, we need to count the number of rows in this table. I know that the simple SQL query is count(*) but I wanted to know how to approach this problem for such a large table?

If you run the count(*) query on this table, it takes quite some time and the server gets loaded with just this query running. Obviously millions of records, so it takes time.

This counting of rows need not be done all at once i.e. it need not be a top priority task. However, it should not be spread out over such a long interval that the count returned is wrong, because this table gets new rows added quite frequently.

And mind you, this database is replicated i.e. there is a hot standby server which has the same database at the ready in case the primary server fails.

So how would you go about doing this task.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #2  
Old July 9th, 2010, 11:43 AM
chitrala's Avatar
chitrala chitrala is offline
Clown Prince
 
Join Date: Mar 2006
Location: Room 101
Posts: 8,199
chitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond reputechitrala has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

1 2 3 4 5 .....274 275 276 277 278... 1023 1024 1025.... 56784 56785 56786... contd.




sorry coudnt resist.
__________________
I am here because I am nowhere else. But, am I there where I wanted to be?

Reply With Quote
  #3  
Old July 9th, 2010, 11:46 AM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,928
echarcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by chitrala View Post
1 2 3 4 5 .....274 275 276 277 278... 1023 1024 1025.... 56784 56785 56786... contd.




sorry coudnt resist.
Arey yaar ... please .. I need to have some idea on this.. I am Googling too, but I thought someone here might have an idea... I need to know a bit before a meeting post lunch.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #4  
Old July 9th, 2010, 11:49 AM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by echarcha View Post
Suppose an Oracle database has a table which has grown very large, i.e. millions of rows.

This table keeps growing as new records are added everyday. And all the existing rows must stay in this table because this table is required for a lookup.

Now for some process to run, say every x hours, we need to count the number of rows in this table. I know that the simple SQL query is count(*) but I wanted to know how to approach this problem for such a large table?

If you run the count(*) query on this table, it takes quite some time and the server gets loaded with just this query running. Obviously millions of records, so it takes time.

This counting of rows need not be done all at once i.e. it need not be a top priority task. However, it should not be spread out over such a long interval that the count returned is wrong, because this table gets new rows added quite frequently.

And mind you, this database is replicated i.e. there is a hot standby server which has the same database at the ready in case the primary server fails.

So how would you go about doing this task.
You want exact count or approximate count will do?
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #5  
Old July 9th, 2010, 11:57 AM
raniraja's Avatar
raniraja raniraja is offline
The Y A W N E S T zzzz
 
Join Date: Jan 2001
Location: Surat
Posts: 9,624
raniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

I don't know how much of modifications will it require, and don't know anything about oracle, but then it would make sense to have a record in some control table, to store the number of rows. I am assuming that the database also contains some rows that are not included in the count (?deleted records or otherwise flagged), otherwise, don't the tables in oracle have a recordcount() function?
__________________
Nietzsche (1887) : God is dead
God (1900) : Nietzsche is dead
-----------------------------------------
I will not be hurried and I will not be bullied

Reply With Quote
  #6  
Old July 9th, 2010, 12:00 PM
Sane Less's Avatar
Sane Less Sane Less is offline
Dead On Arrival is back
 
Join Date: Jun 2005
Posts: 17,064
Sane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond reputeSane Less has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by raniraja View Post
I don't know how much of modifications will it require, and don't know anything about oracle, but then it would make sense to have a record in some control table, to store the number of rows. I am assuming that the database also contains some rows that are not included in the count (?deleted records or otherwise flagged), otherwise, don't the tables in oracle have a recordcount() function?
I agree with what Ranijana pai has said. If something is not already built-in, have a field in a different table and have a trigger update this field everytime a record is inserted into the main table.
__________________
-----------------------------------------------

"Kisi ne sahi kaha zindagi kutti cheez hai. You live life without a care in the world not realizing that life is building a heavy load of trash that it dumps on you one fine day, breaking your back." - saneless
Reply With Quote
  #7  
Old July 9th, 2010, 12:01 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,928
echarcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond reputeecharcha has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by smellyfinger View Post
You want exact count or approximate count will do?
See the requirement is .. well I cant get into very detailed description but here goes ..

Basically at any given time, the number of rows indicated how many 'things' are active. So one more parameter to the query would be "select count(*) where somecolumn = 'xyz'"

To answer your question, it is okay we miss by a few records here and there, but cannot miss by, say 100 or 1000.

So in a sense, yes we want the count to be as accurate as possible but are ready for some tradeoffs.

Unfortunately we cannot do this in an offline fashion by 'exporting' the rows to some other table and then counting it at a later time. The only concession we have is that this counting needs to be done once in, say, 24 hours. We can time it such that we know when the server load is not too high or a window of time when we have observed empirically that the server faces least load as there isn't much activity. For example, late night 1am to 2am.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #8  
Old July 9th, 2010, 12:02 PM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by echarcha View Post
See the requirement is .. well I cant get into very detailed description but here goes ..

Basically at any given time, the number of rows indicated how many 'things' are active. So one more parameter to the query would be "select count(*) where somecolumn = 'xyz'"

To answer your question, it is okay we miss by a few records here and there, but cannot miss by, say 100 or 1000.

So in a sense, yes we want the count to be as accurate as possible but are ready for some tradeoffs.

Unfortunately we cannot do this in an offline fashion by 'exporting' the rows to some other table and then counting it at a later time. The only concession we have is that this counting needs to be done once in, say, 24 hours. We can time it such that we know when the server load is not too high or a window of time when we have observed empirically that the server faces least load as there isn't much activity. For example, late night 1am to 2am.
do you have a primary key field?
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #9  
Old July 9th, 2010, 12:04 PM
raniraja's Avatar
raniraja raniraja is offline
The Y A W N E S T zzzz
 
Join Date: Jan 2001
Location: Surat
Posts: 9,624
raniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by Sane Less View Post
..and have a trigger update this field everytime a record is inserted into the main table.
Inserted, or deleted.

Quote:
Originally Posted by echarcha View Post
Basically at any given time, the number of rows indicated how many 'things' are active. So one more parameter to the query would be "select count(*) where somecolumn = 'xyz'"
Can't you index the table on the "somecolumn" ?? Then in Foxpro, we would have a simple :

SEEK "XYZ"
COUNT WHILE SOMECOLUMN = "XYZ" TO <variable>
__________________
Nietzsche (1887) : God is dead
God (1900) : Nietzsche is dead
-----------------------------------------
I will not be hurried and I will not be bullied

Reply With Quote
  #10  
Old July 9th, 2010, 12:08 PM
rameshp's Avatar
rameshp rameshp is offline
the Centre Right Indian!
 
Join Date: May 2004
Location: Tampa, Florida
Posts: 4,804
rameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond reputerameshp has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by Sane Less View Post
I agree with what Ranijana pai has said. If something is not already built-in, have a field in a different table and have a trigger update this field everytime a record is inserted into the main table.
I second this solution. This should be simple to implement as well. Although, if you add 1000s of records per minute, it will affect performance as each record addition will cause a trigger and overhead of changing record of another table.
__________________


Captain R Harshan
Ashok Chakra, 2 PARA SF,
Indian Army.
Reply With Quote
  #11  
Old July 9th, 2010, 12:11 PM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Basically you only have a few choices

1. If approximation will do, then run a statistics analysis on the table and see value for num_rows in dba_tables or all_tables

2. You can run select count(column_name) from XYZ if column name is a unique primary key non-null field (will be faster but not optimal)

3. Establish another table that keeps a count of how many inserts and deletes have happened to the main table using a trigger on the main table (which will be a performance hit to the main table transactions)

4. If you have a date stamp on that table (especially if the date stamp is indexed) you can run a qry just after midnight to count all records created the previous day - and store a daily count in another table. As long as you dont have deletes, this will work the best.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #12  
Old July 9th, 2010, 12:17 PM
swami's Avatar
swami swami is offline
Super eCharchan
 
Join Date: Mar 2009
Posts: 12,846
swami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond reputeswami has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Oracle pura bhool gaya yaar,refresher course karna padega
Reply With Quote
  #13  
Old July 9th, 2010, 12:23 PM
raniraja's Avatar
raniraja raniraja is offline
The Y A W N E S T zzzz
 
Join Date: Jan 2001
Location: Surat
Posts: 9,624
raniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by smellyfinger View Post
4. If you have a date stamp on that table (especially if the date stamp is indexed) you can run a qry just after midnight to count all records created the previous day - and store a daily count in another table. As long as you dont have deletes, this will work the best.
Bugger you man, I never thought of that!! Are you a programmer?
__________________
Nietzsche (1887) : God is dead
God (1900) : Nietzsche is dead
-----------------------------------------
I will not be hurried and I will not be bullied

Reply With Quote
  #14  
Old July 9th, 2010, 12:27 PM
smellyfinger's Avatar
smellyfinger smellyfinger is offline
Senior eCharchan
 
Join Date: Dec 2000
Location: Home
Posts: 8,851
smellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond reputesmellyfinger has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Quote:
Originally Posted by raniraja View Post
Bugger you man, I never thought of that!! Are you a programmer?
Kahaan yaar - programming aati hoti to is halat mein thodi na hota tha.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #15  
Old July 9th, 2010, 01:32 PM
raniraja's Avatar
raniraja raniraja is offline
The Y A W N E S T zzzz
 
Join Date: Jan 2001
Location: Surat
Posts: 9,624
raniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond reputeraniraja has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

I know that Cha must've already left for his meeting and already have a solution for this, but just for the sake of problem solving, if one is counting records from midnight, then why not count change from the previous count?

BTW, has anyone studied James Martin's "Principles of Database Management" (first published in 1977) ??
__________________
Nietzsche (1887) : God is dead
God (1900) : Nietzsche is dead
-----------------------------------------
I will not be hurried and I will not be bullied

Reply With Quote
Reply

Bookmarks

Tags
count(*), database, oracle, oracle db, rows, schema, sql, sql query, tables


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
A Table for Two Rakhi Recipe Corner 27 April 22nd, 2009 01:39 PM
Oracle External Table to Load XML File motowner Computing 2 February 9th, 2007 09:25 AM
How to get rid of Mutating table problem Napolean Computing 2 February 14th, 2004 11:56 AM
I want this table Diplomat SoapBox 1 November 19th, 2003 10:50 AM


All times are GMT -7. The time now is 12:57 PM.


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Site Copyright © eCharcha.Com 2000-2012.