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
  #16  
Old July 9th, 2010, 02:49 PM
krantikari's Avatar
krantikari krantikari is offline
Senior eCharchan
 
Join Date: Jul 2000
Posts: 4,614
krantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

i understand count(1) is faster than count (*).
Reply With Quote
  #17  
Old July 9th, 2010, 03:20 PM
kkkk kkkk is offline
Senior eCharchan
 
Join Date: Sep 2008
Posts: 5,132
kkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

We do a hot backup of the live DB to another offline DB 3 times every day. we can then afford to query on this backup without worrying about the performance of the queries too much.
Reply With Quote
  #18  
Old July 9th, 2010, 03:46 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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
do you have a primary key field?
Yes we have a PK in the table with so many rows.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #19  
Old July 9th, 2010, 03:48 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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
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.
Fuck .. uncanny!!!.. Solution 4 is what I have proposed!!! I just got back from the meeting and see solution 4!!
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #20  
Old July 9th, 2010, 04:09 PM
motowner's Avatar
motowner motowner is offline
Senior eCharchan
 
Join Date: May 2006
Posts: 1,647
motowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner has a reputation beyond reputemotowner 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
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
Doesnt running stats give an exact count as of the time of analysis?

I think this is the best solution. Have a program run the stats on the table every night (if any changes have occured) and you can query the all_tables view to get the count.
Reply With Quote
  #21  
Old July 9th, 2010, 05:08 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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?

Thanks for all the inputs. I will put them all in my email to my boss. I am not a db expert but was asked to think of a solution. So I asked you all, and as always, you guys are great at helping out. Thanks folks
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #22  
Old July 9th, 2010, 07:58 PM
Sane Less's Avatar
Sane Less Sane Less is offline
Dead On Arrival is back
 
Join Date: Jun 2005
Posts: 16,782
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 echarcha View Post
Thanks for all the inputs. I will put them all in my email to my boss. I am not a db expert but was asked to think of a solution. So I asked you all, and as always, you guys are great at helping out. Thanks folks
Where are our reps... saala, aisa hi itna -ve reps mila... my points have become so low
__________________
-----------------------------------------------

"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
  #23  
Old July 9th, 2010, 08:06 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,590
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?

EC, see this. It may be better solution since you don't have to count from midnight everytime.

Quote:
Originally Posted by echarcha View Post
Thanks for all the inputs. I will put them all in my email to my boss. I am not a db expert but was asked to think of a solution. So I asked you all, and as always, you guys are great at helping out. Thanks folks
Khali thank you se kaam nahi chalega. $100 hota hai .
__________________
Nietzsche (1887) : God is dead
God (1900) : Nietzsche is dead
-----------------------------------------
I will not be hurried and I will not be bullied

Reply With Quote
  #24  
Old July 9th, 2010, 08:46 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
Thanks for all the inputs. I will put them all in my email to my boss. I am not a db expert but was asked to think of a solution. So I asked you all, and as always, you guys are great at helping out. Thanks folks

Apun ko naukri milegi kya? Boss ko recommendation kar de na. Main resume bhejta hoon.

At least +ve reps to de de. Saala Chitrala merekoo -ve de rela hai for some reason.
__________________
Never argue with idiots. They drag you down to their level and then beat you with experience.
Reply With Quote
  #25  
Old July 9th, 2010, 09:07 PM
ShivSainik's Avatar
ShivSainik ShivSainik is offline
Senior eCharchan
 
Join Date: Apr 2001
Location: San Jose, CA
Posts: 5,253
ShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

I am assuming that since your table is so large you have some kind of partitioning? If yes is it daily partition or partition based on something else?

Can you please do "explain plan" and see what is the cost of "select count(1) from table"? If it's doing full table scan (if it's not indexed properly) then you'll have problem and you have to use some other solution. If you have bitmap index on your table, select count(1) or select count(column name) shouldn't be an issue but confirm it by explain plan.

Trigger solution is most obvious but if your DB in question is OLTP database and if you have high amount of writes then trigger always takes extra CPU cycles and proper capacity analysis is required whether your DB hosts has those cycles (just for counts!)
__________________
"Once you start thinking too much about your place in history, you're at a point where you're no longer trying as hard as you should be"
Reply With Quote
  #26  
Old July 9th, 2010, 09:09 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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 ShivSainik View Post
I am assuming that since your table is so large you have some kind of partitioning? If yes is it daily partition or partition based on something else?

Can you please do "explain plan" and see what is the cost of "select count(1) from table"? If it's doing full table scan (if it's not indexed properly) then you'll have problem and you have to use some other solution. If you have bitmap index on your table, select count(1) or select count(column name) shouldn't be an issue but confirm it by explain plan.

Trigger solution is most obvious but if your DB in question is OLTP database and if you have high amount of writes then trigger always takes extra CPU cycles and proper capacity analysis is required whether your DB hosts has those cycles (just for counts!)
I will get you that information on Monday.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #27  
Old July 9th, 2010, 09:11 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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?

I have repped all who replied here. Thank you very much.
__________________
eCharcha.com
-Loud and Proud Desi Opinions
Reply With Quote
  #28  
Old July 9th, 2010, 09:20 PM
kkkk kkkk is offline
Senior eCharchan
 
Join Date: Sep 2008
Posts: 5,132
kkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk has a reputation beyond reputekkkk 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
I have repped all who replied here. Thank you very much.
do you mean useful responses? I havent got it
Reply With Quote
  #29  
Old July 9th, 2010, 09:25 PM
JaiSpeaks's Avatar
JaiSpeaks JaiSpeaks is offline
The NEo of Echarcha
 
Join Date: Apr 2001
Location: USA
Posts: 6,827
JaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond reputeJaiSpeaks has a reputation beyond repute
Re: How to get a count or rows in a very large Oracle table?

Yeah kaunse greek bhasha mein baat kar rahe ho
__________________
If you win you need not explain.. But if you lose you should not be there to explain
Reply With Quote
  #30  
Old July 9th, 2010, 09:27 PM
echarcha's Avatar
echarcha echarcha is offline
Sutradhar {admin}
 
Join Date: Jul 2000
Location: CA, USA
Posts: 44,831
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 kkkk View Post
do you mean useful responses? I havent got it
Phir se check kar!!
__________________
eCharcha.com
-Loud and Proud Desi Opinions
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 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


All times are GMT -7. The time now is 12:42 AM.


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