Home » SQL & PL/SQL » SQL & PL/SQL » retrieval time
retrieval time [message #195525] Fri, 29 September 2006 04:00 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
'select count(*) from table where something = something' takes about
the same time as 'select * from table where something = something'.


Can anybody suggest a way to retreive them fastly?
Re: retrieval time [message #195527 is a reply to message #195525] Fri, 29 September 2006 04:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In general, 'SELECT count(*)...' cannot be relied on to use the same execution plan as 'SELECT column_1...'

This is because Count(*) can be satisfied with indexes much more often than a query that has to access the tables to retrieve data.
Re: retrieval time [message #195530 is a reply to message #195527] Fri, 29 September 2006 04:36 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
dear friend
for quick result change

count(*) or count*
to
count(1)

in the * case query have to check all the columns
but in count(1) case
query have to check only first column

therefore result are quick

regards

Muhammad Shahid Mughal
Faisalabad Pakistan
Re: retrieval time [message #195533 is a reply to message #195530] Fri, 29 September 2006 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pay no attention to @shahidmughal.
What he says is completely untrue.
There is absolutely no difference between COUNT(*) and COUNT(1)

There is a long debate about it on AskTom.
Re: retrieval time [message #195541 is a reply to message #195533] Fri, 29 September 2006 06:34 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If your statistics are up-to-date and you only need a rough estimate on the number of rows in your table, you could query NUM_ROWS in ALL_TABLES. This contains (an estimate) of the number of rows in your table, the last time it was analyzed.

About the count(1) vs count(*): JRowBottom is right. There is absolutely no difference between the two. (except that using count(1) shows you don't know your database)
Previous Topic: TO_CHAR & TO_DATE
Next Topic: Exception Handling
Goto Forum:
  


Current Time: Fri Dec 09 15:47:10 CST 2016

Total time taken to generate the page: 0.56378 seconds