Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Azrael Noor
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Ashwin Sridhar
Ranch Hand
Posts: 277
Flex Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic