Re: duplicate-index-names danger

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.

Jared

On Wed, 15 Dec 2004 19:11:30 -0600, Dan Tow <dantow_at_singingsql.com> 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
> www.singingsql.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l