Forum Stats

  • 3,734,267 Users
  • 2,246,933 Discussions
  • 7,857,216 Comments

Discussions

Unable to create index as it already exists...

Hokins
Hokins Member Posts: 20
edited Nov 23, 2013 2:46PM in SQL & PL/SQL

Hi,

I am using 11g r2. My appl response was slow so i see the table and all partitioned indexes were missing from a table. When i created the index it gives error that the column is already indexed. But I cannot see the indexes. So I login with sys and astonished to see that tables was indexed with index owner sys. How there owner is changed from schema to sys. And if there owner was sys from the day one then why SQL tunning advisor is recommending now to index those columns.

Thanks

Tagged:

Answers

  • TSharma-Oracle
    TSharma-Oracle Member Posts: 1,900 Silver Trophy

    Surely somebody had created indexes as SYS as a owner which is a bad bad practice. It looks very odd that sql tuning advisor is asking you to create indexes on already indexes column. As you did not post anything to prove your thread, I cannot comment on this as well. It could be the oracle BUG.

    Are you sure Tuning advisor is asking you to create index on exact same number of columns?

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    SYS is a strange entity: there are a lot of operations (plans, statistics) changing their behaviour when SYS is involved. So it would not be a big surprise if indexes created by SYS are ignored by the tuning advisor.

This discussion has been closed.