| Empty table takes time to show count [message #419058] |
Wed, 19 August 2009 12:57  |
sujitm30
Messages: 10
Registered: September 2005
|
Junior Member |
|
|
I have a table T1 which during the course of the day gets inserts and deletes.
At the max, the number of rows it gets is 3 Million.
Operations on this table are very slow. Right now there was no records in this table and still to give a count (*) (=0) it took 20 seconds. I know that when I truncate this table instead of deleting, it gives me the count in a millisec. But I can not afford to truncate.
Can anyone suggest what are the possible reasons? And a solution?
TIA,
Sujit
|
|
|
|
|
|
| Re: Empty table takes time to show count [message #419062 is a reply to message #419059] |
Wed, 19 August 2009 13:12  |
sujitm30
Messages: 10
Registered: September 2005
|
Junior Member |
|
|
Right. So is there a way to clear up space occupied (without truncating)? Anything to do with table creation/block size (8KB), PCTUSED etc?
Note that my slowness continues even days after deleting. Not a recent event.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Empty table takes time to show count [message #419084 is a reply to message #419081] |
Wed, 19 August 2009 16:02 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Other common solutions to consider would be moving the table, followed by rebuilding the indexes. Look at online redefinition too. You could probably do partition swapping to keep the table online for DML while doing the "shrink". None of these options is too attractive unless you really need it.
As recommended earlier - try to avoid FTS. Maybe you can do select... where pk > 0 (or whatever).
|
|
|
|
|
|
|
|
| Re: Empty table takes time to show count [message #419141 is a reply to message #419094] |
Thu, 20 August 2009 00:51 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If your data indeed fluctuates over the day, you do not want to go the DDL-route of shrinking tables, rebuilding/moving anything.
Your main problem might be in the fact that it is hard for the optimizer to always get it right. If you compute your statistics based on a filled table, it will look like performance is bad when the table is empty, vice versa. You have to find a spot somewhere in the middle that makes performance acceptable in all cases.
|
|
|
|
|
|
| Re: Empty table takes time to show count [message #419159 is a reply to message #419058] |
Thu, 20 August 2009 02:35 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
The high water mark is relevant since Oracle will scan all blocks under the high water mark, even when they contain no data, during a full scan. This will impact the performance of a full scan ‐ especially if most of the blocks under the high water mark are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows). Do a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long to count zero rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the high water mark to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the high water mark of a table back to ʹzeroʹ. If you plan on deleting every row in a table, TRUNCATE would be the method of my choice for this reason.
Copied from Expert One on One Oracle by Thomas Kytes
[Updated on: Thu, 20 August 2009 02:38] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
