Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: duplicate-index-names danger

Re: duplicate-index-names danger

From: Jared Still <>
Date: Thu, 16 Dec 2004 02:01:59 +0000
Message-ID: <>

Hi Dan,

Oracle has allowed this for a long time, at least as of 7.3, and probably earlier versions of 7, not sure about 6.

As for "why", I'm not sure why you would allow a user to have a private index into a table. I've known about the capability, but have never had a reason to use it.

I can see where it would cause a problem if a particular user were seeing a different execution path due to the index, and you weren't aware of the index. That could be confusing.


On Wed, 15 Dec 2004 19:11:30 -0600, Dan Tow <> wrote:
> 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
> --

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Received on Wed Dec 15 2004 - 20:02:54 CST

Original text of this message