OraFAQ Forum: Performance Tuning » Simple SELECT statement taking too long

Home » RDBMS Server » Performance Tuning » Simple SELECT statement taking too long

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

Simple SELECT statement taking too long [message #65039]

Mon, 05 April 2004 09:52

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!

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65040 is a reply to message #65039]

Mon, 05 April 2004 10:33

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

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65043 is a reply to message #65040]

Tue, 06 April 2004 06:50

Vasantha
Messages: 2
Registered: April 2004

Junior Member

Try: Select count(primary_key) from table;

This is usually faster than just count(*)

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65045 is a reply to message #65043]

Tue, 06 April 2004 07:05

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

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

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65060 is a reply to message #65057]

Sun, 11 April 2004 09:03

Thiru
Messages: 1089
Registered: May 2002

Senior Member

How many times do we have to disprove this myth!!

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65108 is a reply to message #65045]

Fri, 30 April 2004 09:51

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

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65112 is a reply to message #65039]

Mon, 03 May 2004 18:53

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?

Report message to a moderator

Re: Simple SELECT statement taking too long [message #65113 is a reply to message #65108]

Mon, 03 May 2004 19:37

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:32:08 CST 2024