| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: duplicate-index-names danger
Hi Dan,
I think this has always been the case in Oracle; indexes are not owned by
tables but belong to schemas, and the name space for indexes is the schema.
to be honest, I never liked the need to specify index names in optimizer
hints in the first place -- because indexes are supposed to be transparent
to the application or user. that is, you should not need to know their
names.
therefore, I like the new index hint syntax, where you specify *column* names rather than *index* names; this resolves the ambiguity you refer to.
Cheers,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Dan Tow
Sent: Thursday, December 16, 2004 02:12
To: oracle-l_at_freelists.org
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 - 03:09:07 CST
 |
 |