What happens to index once the table got dropped?

sql indexing sql-drop
Vaandu picture Vaandu · Dec 8, 2011 · Viewed 33k times · Source

After dropping the table, found that the index created on the columns of the dropped table is gone. I just want to know what happens after that. Could someone please explain?

What all are the others getting dropped along with table drop?

Answer

Raihan picture Raihan · Dec 8, 2011

In Oracle when dropping a table

  • all table indexes and domain indexes are dropped
  • any triggers defined on the table are dropped
  • if table is partitioned, any corresponding local index partitions are dropped
  • if the table is a base table for a view or if it is referenced in a stored procedure, function, or package, then these dependent objects are invalidated but not dropped

Related questions

How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More
What is an index in SQL?

What is an index in SQL? Can you explain or reference to understand clearly? Where should I use an index?

Read More
How to use index in select statement?

Lets say in the employee table, I have created an index(idx_name) on the emp_name column of the table. Do I need to explicitly specify the index name in select clause or it will automatically used to speed …

Read More