Home » RDBMS Server » Performance Tuning » Normally why count (*) taking so much of time when compare to select *
Normally why count (*) taking so much of time when compare to select * [message #603793] Thu, 19 December 2013 08:21 Go to next message
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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?
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.

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 Go to previous message
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:
  


Current Time: Thu Mar 28 04:55:05 CDT 2024