RE: duplicate-index-names danger
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <dantow@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Dec 2004 10:09:04 +0100
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.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dan Tow
Sent: Thursday, December 16, 2004 02:12
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
