Re: Question re indexes

K Stahl <BlueSax_at_Unforgetable.com> schreef in berichtnieuws 38FCB7D4.5A03B660_at_Unforgetable.com...
> I came across something in a database today that puzzles me a bit.
>
> Suppose I have a table called MYTABLE in MYSCHEMA. Someone logs into
 oracle
> as SYSTEM and creates an index on this table called INDEXA. Since no
 schema
> was specified, the index is owned by SYSTEM. If an application accesses
 the
> table via a synonym while logged in as a user which has the proper
> SELECT/INSERT/UPDATE/DELETE grans and using SQL which would be
> appropriately handled by INDEXA, will that index be used despite the fact
> that the index is owned by SYSTEM? If so, what difference does it really
> make which userid creates indexes as long as they are defined properly?
>
> If I am able I do intend to rebuild the index properly, but at this point
> I'm just wondering if it is being used at all.

Yes, the index will be used.
As to the second question: I would consider lack of transparency and loosing control. Of course system is a special case as this user has create any index privilege. But wouldn't think if you grant other users the right to create an index all sort of unpredictable things would occur. It is a known fact the (especially the rulebased) optimizer often chooses an incorrect index when there are many different indexes on a table. I wouldn't allow the situation you describe, I would consider this as loosing control on my database.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Apr 18 2000 - 00:00:00 CDT