|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 24th, 2004, 05:58 PM
|
|||
|
|||
10 seconds to query an empty table?
I'm starting to think there is something very wrong with the database installation I am using. I'm not an oracle pro but have worked a lot with MySQL and SQL Server.
I'm doing a very simple query Code:
select * from call_analysis on an empty table. Is there any reason y this should take 10 seconds. I would have expected it to return nothing almost instantly. The server this is running on is no slouch either. It's a Xeon 2.8Ghz with 2.5 Gig ram and RAID10. **EDIT** As a side note the same query on another table with more columns and 700 records executes in 15ms.
__________________
Like the answers I give? Why not ask me directly at my forum. I'm always glad to help. Javascript scripts and tips can be found at Dynamic Tools. Check out DynamicTable, the best javascript table sorter around. Get reliable and affordable hosting at www.thinksmarthosting.com Last edited by ProggerPete : October 24th, 2004 at 06:22 PM.
|
|
#2
October 24th, 2004, 07:19 PM
|
|||
|
|||
|
hmm that's odd. Did it have rows at one time? have you rebuilt the table statistics lately?
run this command within the table where the table is and see if it makes any difference Code:
exec dbms_utility.analyze_schema(user,'ESTIMATE'); The query optimizer uses these generated statistics to determine a plan, I run a job each night that recalcs the statistics. The only other thing I can think of is that another session is holding some row locks, ie. uncommitted updates/deletes. Oracle would then have to use the redo log to build a read consistent view for your session. Last edited by hedge : October 24th, 2004 at 07:21 PM.
|
|
#3
October 24th, 2004, 08:32 PM
|
|||
|
|||
|
It's a new table I'm playing with to collate data from various tables to improve speed. Basically the theory is I populate this table overnight so people can query various bits of info in realtime the next day.
I've inserted and deleted about 5 millions rows a few times while testing this. Each time I've deleted I have deleted everything. Is doing this really going to impact on performance? I'd have thought that deleting all data would be as good as starting from scratch with a new table. As for the statistics, how much optimising can really be done on select * from call_analysis. Surely it will always be a full_scan.
|
|
#4
October 24th, 2004, 09:18 PM
|
|||
|
|||
|
Quote:
hmm, you're right. I have no idea then.
|
|
#5
October 25th, 2004, 01:13 AM
|
|||
|
|||
Resolved
The answer lies in what oracle call the High Water Mark. Which basically marks the end of the assigned dataspace. As this table had contained over 5 million records at one point the HWM was pretty high. The following command fixed the problem.
TRUNCATE TABLE call_analysis **WARNING** Before trying the command to improve performance you should know that TRUNCATE will delete all data in the table. Last edited by ProggerPete : October 25th, 2004 at 01:20 AM.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > 10 seconds to query an empty table? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
