![]() |
Support eCharcha.Com. Click on sponsor ad to shop online! |
|
Notices |
How to Stuck for an answer to "How to do .."? |
![]() |
|
Thread Tools | Display Modes |
#1
|
||||
|
||||
![]() 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. |
#2
|
||||
|
||||
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.
![]()
__________________
I am here because I am nowhere else. But, am I there where I wanted to be? |
#3
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
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.
|
#4
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Quote:
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience. |
#5
|
||||
|
||||
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?
|
#6
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Quote:
__________________
----------------------------------------------- "Hinduism brought a multitude of religions under one roof and survived for eons. Christianity and other religions will need to do the same or perish." - saneless |
#7
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
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. |
#8
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Quote:
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience. |
#9
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Quote:
Quote:
SEEK "XYZ" COUNT WHILE SOMECOLUMN = "XYZ" TO <variable> |
#10
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle 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.
__________________
|
#11
|
||||
|
||||
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. |
#12
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Oracle pura bhool gaya yaar,refresher course karna padega
![]() |
#13
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
Quote:
![]() ![]() ![]() |
#14
|
||||
|
||||
Re: How to get a count or rows in a very large Oracle table?
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. |
#15
|
||||
|
||||
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) ?? |
![]() |
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 | |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
A Table for Two | Rakhi | Recipe Corner | 27 | April 22nd, 2009 02:39 PM |
Oracle External Table to Load XML File | motowner | Computing | 2 | February 9th, 2007 10:25 AM |
How to get rid of Mutating table problem | Napolean | Computing | 2 | February 14th, 2004 12:56 PM |
I want this table | Diplomat | SoapBox | 1 | November 19th, 2003 11:50 AM |