This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills and have John Sonmez on-line!
See this thread for details.
The moose likes JDBC and the fly likes Unable to Truncate Table / Drop Storage and Slow access. Why? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unable to Truncate Table / Drop Storage and Slow access. Why?" Watch "Unable to Truncate Table / Drop Storage and Slow access. Why?" New topic
Author

Unable to Truncate Table / Drop Storage and Slow access. Why?

Azrael Noor
Ranch Hand

Joined: Jul 29, 2010
Posts: 382
Problem 1:
Unable to truncate following table.
Getting following error:


Following is table structure



Problem 2:
Very Slow Access, like if i select this table it takes 29, 30 seconds to access but other table in same schema select works out in fractions of seconds only

please guide where i am lacking?


Regards
Azrael Noor
Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

Problem 1

You have references from this table. So before deleting, those references needs to be deleted. In simple terms, before removing a department, you need to remove employees tagged to that department.

Problem 2

There is no guarantee that all tables in a schema should take equal time to fetch data. It all depends on the amount of data present, indexes present in tables. To improve query time, create index for your table.


Ashwin Sridhar
SCJP | SCWCD | OCA
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Ashwin Sridhar wrote:Problem 1

You have references from this table. So before deleting, those references needs to be deleted. In simple terms, before removing a department, you need to remove employees tagged to that department.

Not quite true. This error will occur even if the employees table is empty. What you wrote is true for delete operations, but truncate is a DDL operation, not a DML operation, and works differently.

The description of the error which Azrael himself posted tells exactly what needs to be done. All foreign key constrains that refer to the table being truncated must be dropped or disabled. They can be recreated or enabled after the truncate completes. This is simply how it works in Oracle.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to Truncate Table / Drop Storage and Slow access. Why?