How to get a count or rows in a very large Oracle table?

[echarcha]

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.

[chitrala]

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




sorry coudnt resist.

[echarcha]

Originally Posted by chitrala

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.

[smellyfinger]

Originally Posted by echarcha

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?

[raniraja]

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?

[Sane Less]

Originally Posted by raniraja

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.

[echarcha]

Originally Posted by smellyfinger

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.

[smellyfinger]

Originally Posted by echarcha

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?

[raniraja]

Originally Posted by Sane Less

..and have a trigger update this field everytime a record is inserted into the main table.

Inserted, or deleted.

Originally Posted by echarcha

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>

[rameshp]

Originally Posted by Sane Less

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.

[smellyfinger]

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.

[swami]

Oracle pura bhool gaya yaar,refresher course karna padega

[raniraja]

Originally Posted by smellyfinger

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?

[smellyfinger]

Originally Posted by raniraja

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.

[raniraja]

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) ??