Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: No automatic indexes

Re: No automatic indexes

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 14 Jun 2006 17:43:41 -0700
Message-ID: <1150332221.345493.189190@h76g2000cwa.googlegroups.com>

Brian Peasland wrote:
> Brex wrote:
> > Hi!
> >
> >
> > Can someone please explain to me how not to generate indexes
> > automatically when I create a table with PK in Oracle?
> >
> > Thank you in advance!
> >
> > Brex
> >
>
> Sorry, but you cannot. Oracle uses a unique index to help it enforce the
> PK constraint. You cannot have a PK constraint without an index.
>
> HTH,
> Brian
>
> --

Actually I believe using 10gR2 it is possible to have a PK without an index, but the PK has to be in a disabled state. I do not have a system to test with right now but you can disable the PK on R2 and the index by default is no longer is dropped but rather is kept. You should then be able to perform a drop index. However, the PK constraint still exists so technically you have a table with a PK without an index to enforce it. If you enable the PK without first creating the index to support Oracle will create the index for you as it did in the past.

If you have a 10gR2 system you can test this. Ours has to be rebuilt since we were dumb enought to follow an Oracle support recomendation to fix an upgrade problem. Their fix not only uninstalled R2 but it took R1 with it.

Brian if you or Daniel has the time and interest to test this I would appreciate your posting the results to the thread (even if I turn out to be wrong).

HTH -- Mark D Powell -- Received on Wed Jun 14 2006 - 19:43:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US