Home » SQL & PL/SQL » SQL & PL/SQL » Truncate table issue
Truncate table issue [message #223048] Wed, 07 March 2007 06:32 Go to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Hi Friends,
I have an empty table and when i issue the following statement

select count(*) from table_name;

It takes more than 2 to 3 minutes and returns no rows.
I truncated the table and tried.. The results emerged quickly.

What could be the possible issue here?
Re: Truncate table issue [message #223050 is a reply to message #223048] Wed, 07 March 2007 06:47 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
The truncate moves the table high water mark back to zero. The high water mark is set to indicate which blocks were previously used. Even blocks that no longer have data are counted as previously used. So when you did the count(*), Oracle checked all the blocks under the high water mark. That's why it took so long.
Re: Truncate table issue [message #223051 is a reply to message #223048] Wed, 07 March 2007 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you do a full table scan on a table (which your query was almost certainly doing), Oracle reads all the data blocks between the start of the table and the High Water Mark (the last block that has ever had data in it).
If the table has been large in the past, but now has no rows in it, it will still read all the way to the end.

When you truncate the table, you reset the high water mark back to the start of the file.
Re: Truncate table issue [message #223054 is a reply to message #223051] Wed, 07 March 2007 07:09 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Thank u....it is really useful
Re: Truncate table issue [message #223055 is a reply to message #223054] Wed, 07 March 2007 07:20 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Well, I was searching about high water marks in google but i couldn't get satisfactory result. Can anybody tell me what is HWM?
Re: Truncate table issue [message #223061 is a reply to message #223055] Wed, 07 March 2007 07:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
According to the "Oracle Database Concepts": "The high water mark is the boundary between used and unused space in a segment."

And the "Performance tuning guide" tells me that "During a full table scan, all blocks in the table that are under the high water mark are scanned."

Go to http://tahiti.oracle.com and browse the vast documentation of Oracle.

MHE
Re: Truncate table issue [message #223202 is a reply to message #223061] Thu, 08 March 2007 00:11 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

that was gr8...thanks a lot
Previous Topic: explain this
Next Topic: Get first row with aggregate function?
Goto Forum:
  


Current Time: Mon Dec 05 08:51:46 CST 2016

Total time taken to generate the page: 0.22161 seconds