• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 385
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 277
Oracle Spring Flex
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic