Home » SQL & PL/SQL » SQL & PL/SQL » Empty table takes time to show count (Oracle 9.2 SunOS 5.8)
Empty table takes time to show count [message #419058] Wed, 19 August 2009 12:57 Go to next message
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 #419059 is a reply to message #419058] Wed, 19 August 2009 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Delete do not release space (clear back HWM), so count have to scan ALL the table blocks.

Regards
Michel
Re: Empty table takes time to show count [message #419062 is a reply to message #419059] Wed, 19 August 2009 13:12 Go to previous messageGo to next message
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 #419063 is a reply to message #419062] Wed, 19 August 2009 13:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
truncating the table would be what you want to do. Why did you ask if there is a different method?
Re: Empty table takes time to show count [message #419064 is a reply to message #419062] Wed, 19 August 2009 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So is there a way to clear up space occupied (without truncating)?

Drop/recreate Smile

Regards
Michel
Re: Empty table takes time to show count [message #419065 is a reply to message #419058] Wed, 19 August 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>And a solution?
Stop doing Full Table Scan.
Re: Empty table takes time to show count [message #419080 is a reply to message #419065] Wed, 19 August 2009 15:09 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
-- shrink table

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:649507800346606066
Re: Empty table takes time to show count [message #419081 is a reply to message #419080] Wed, 19 August 2009 15:25 Go to previous messageGo to next message
sujitm30
Messages: 10
Registered: September 2005
Junior Member
Yup. I am looking for shrink table. But I don't think shrink table works in versions prior to 10g. Mine is 9.2.0.8

ORA 01735: "Invalid ALTER TABLE Option"
Re: Empty table takes time to show count [message #419084 is a reply to message #419081] Wed, 19 August 2009 16:02 Go to previous messageGo to next message
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 #419085 is a reply to message #419081] Wed, 19 August 2009 16:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What would interest me is why you can't "afford" to truncate, since truncate is what it looks like you need to to.

Do you have some funny licensing scheme where you have to pay for each truncate? Razz
Re: Empty table takes time to show count [message #419094 is a reply to message #419085] Wed, 19 August 2009 17:21 Go to previous messageGo to next message
sujitm30
Messages: 10
Registered: September 2005
Junior Member
Cann't afford to truncate because I do not always clean up the whole table. It's a delete of data based on certain dates. Which at times may make the table blank but not necessarily.
Re: Empty table takes time to show count [message #419141 is a reply to message #419094] Thu, 20 August 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
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 #419142 is a reply to message #419058] Thu, 20 August 2009 01:03 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
The question to be asked is What is purpose behind doing count(*) on this table?
Re: Empty table takes time to show count [message #419159 is a reply to message #419058] Thu, 20 August 2009 02:35 Go to previous message
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

Previous Topic: SQL Statement - Complex Conditions
Next Topic: sql Query Taking To much time to be execute (merged 4)
Goto Forum:
  


Current Time: Fri Dec 02 14:03:48 CST 2016

Total time taken to generate the page: 0.09115 seconds