Re: duplicate-index-names danger

Hi Dan,

the only place we're using this is with a third-party app. The vendor allows us to create extra indexes, but wants them in another schema "to avoid possible issues with upgrades etc." We have made sure to keep the index-names unique across owners; not specifically for the hinting issue you mention but just to avoid possible confusion.

mvg/regards

Jo

Dan Tow <dantow_at_singingsql.com>
Sent by: oracle-l-bounce_at_freelists.org
12/16/2004 02:11
Please respond to dantow  

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        duplicate-index-names danger

I just discovered an index-naming problem I wasn't aware of, and I wanted to
know if any of you knew of a reason why Oracle allows this:

It seems (in 9i, at least) that you can create two indexes having the same name,
on the same table (owned by the same table owner), but covering different columns, as long as the indexes have different *index* owners. The result is
certainly confusing, but more seriously, from my perspective, it makes index()
hints in the SQL ambiguous, since these appear to have no way to refer to the
index owner, only to the table and to the index name. This ability to create
indexes under owners other than the table owner smells to me like a case of
Oracle giving us rope to hang ourselves with, without any really compelling
need for that rope.

Have any of you ever run into a really good reason why you might actually *need*
to create an index owned by an owner other than the table owner???

I can certainly believe that it is convenient for some central, privileged owner
to create indexes on tables owned under other schemas, so I'm not saying that
should be prevented - I am really just asking of there is any reason for the
indexes created in this way to not just automatically be assigned ownership in
the table-owning account? What do cross-owned indexes (indexes owned by an account other than the table owner) buy us other than trouble, if anything?

Thanks,

Dan Tow
650-858-1557
www.singingsql.com

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 16 2004 - 02:19:38 CST