Home » RDBMS Server » Performance Tuning » Simple SELECT statement taking too long
Simple SELECT statement taking too long [message #65039] Mon, 05 April 2004 09:52 Go to next message
Cocoy
Messages: 4
Registered: April 2004
Junior Member
hi oracle experts,

simple SQL statement:

select count(*) from xx_table_xx

returns 800k rows over 5 minutes!

index on 1 column.

what do you think is the problem??? this is really odd.

server spec: 8way 1GHZ 1GB memory.

thanks!
Re: Simple SELECT statement taking too long [message #65040 is a reply to message #65039] Mon, 05 April 2004 10:33 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Have you analyzed the table ?
Post us
- the exact query
- the execution plan and statistics
- user_ind_columns where index_name='index_name'

-Thiru
Re: Simple SELECT statement taking too long [message #65043 is a reply to message #65040] Tue, 06 April 2004 06:50 Go to previous messageGo to next message
Vasantha
Messages: 2
Registered: April 2004
Junior Member
Try: Select count(primary_key) from table;

This is usually faster than just count(*)
Re: Simple SELECT statement taking too long [message #65045 is a reply to message #65043] Tue, 06 April 2004 07:05 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Not true. See here Count(*)

-Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65057 is a reply to message #65039] Fri, 09 April 2004 01:04 Go to previous messageGo to next message
perumal
Messages: 3
Registered: March 2004
Junior Member
Yes,
SELECT Count(ROWNUM) FROM table_name will work
faster tha SELECT COUNT(*) FROM table_name.
give us full Query.
Analyze the table.
Check for Optimazation RULE CBO/RBO.
Finally,if no way, you can go go for HINTS
this is the usual method for any query.
With out analyze the query, general info never solve your problem
Re: Simple SELECT statement taking too long [message #65060 is a reply to message #65057] Sun, 11 April 2004 09:03 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
How many times do we have to disprove this myth!!
Re: Simple SELECT statement taking too long [message #65108 is a reply to message #65045] Fri, 30 April 2004 09:51 Go to previous messageGo to next message
D Mera
Messages: 1
Registered: April 2004
Junior Member
I also found that a select count(*) from table fast scnas an ndex. I didn't think about it before, but isn't the number of records already info already stored? A simple count should return just a number. Is there any explanation?
TIA
Re: Simple SELECT statement taking too long [message #65112 is a reply to message #65039] Mon, 03 May 2004 18:53 Go to previous messageGo to next message
Cocoy
Messages: 4
Registered: April 2004
Junior Member
hi,

the query is now fast after i dropped/recreate the table. dropping just the index did not help. what do you think is the root cause why after drop and recreate (loaded with same amount of records) had improved the performance? is there such a term as TABLE CORRUPTION?
Re: Simple SELECT statement taking too long [message #65113 is a reply to message #65108] Mon, 03 May 2004 19:37 Go to previous message
Cocoy
Messages: 4
Registered: April 2004
Junior Member
hi,

I did a table drop and recreate and the query performce had increased! what do you think could have caused the slowness? reindexing did not help btw.

thanks.
Previous Topic: Cost-based optimization slows down my query
Next Topic: sample data to test Oracle database performance
Goto Forum:
  


Current Time: Fri Mar 29 10:44:27 CDT 2024