OraFAQ Forum: Performance Tuning » Normally why count (*) taking so much of time when compare to select *

Home » RDBMS Server » Performance Tuning » Normally why count (*) taking so much of time when compare to select *

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Normally why count (*) taking so much of time when compare to select * [message #603793]

Thu, 19 December 2013 08:21

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi All,

Normally why select count (*) from table_name is taking so much of time when compare to select * from table_name

For example:

in the below sql query count is taking so much of time but select * is returning the data immediately

SELECT COUNT(*)
FROM s_extract_cash_trade, d_cash_trade_junk
WHERE s_extract_cash_trade.contractname = d_cash_trade_junk.contract_name

SELECT *
FROM s_extract_cash_trade, d_cash_trade_junk
WHERE s_extract_cash_trade.contractname = d_cash_trade_junk.contract_name

Report message to a moderator

Re: Normally why count (*) taking so much of time when compare to select * [message #603797 is a reply to message #603793]

Thu, 19 December 2013 08:38

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

are you checking how long the select * takes to return all rows or just some of them?

Report message to a moderator

Re: Normally why count (*) taking so much of time when compare to select * [message #603799 is a reply to message #603797]

Thu, 19 December 2013 08:43

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Yes, i am checking select * for all the rows.

select * -- immediately the result is returning
select count(*) -- taking so much of time is taking to return the count

Report message to a moderator

Re: Normally why count (*) taking so much of time when compare to select * [message #603820 is a reply to message #603799]

Thu, 19 December 2013 09:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>select * -- immediately the result is returning
Oracle can START returning rows as it determines that those rows meet all the filter criteria.
You are NOT measuring how long it take for ALL rows to be returned by SELECT above; which will be longer than for the SELECT below.

>select count(*) -- taking so much of time is taking to return the count
Oracle must wait until ALL rows have been processed before it can return the single correct value of COUNT.

Report message to a moderator

Re: Normally why count (*) taking so much of time when compare to select * [message #604368 is a reply to message #603799]

Sun, 29 December 2013 08:03

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

set timing on

create table t_cnt nologging as
select count(*) from ...
/

create table t_slct nologging as
select * from ...
/

Tell us what you get when you run this from SQLPLUS.

[Updated on: Sat, 08 March 2014 13:25] by Moderator

Report message to a moderator

Previous Topic:	Bitmap causing deadlock
Next Topic:	Query is taking huge time to display the record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 07:10:04 CST 2025