Re: No automatic indexes

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Thu, 15 Jun 2006 14:48:27 GMT
Message-ID: <J0wp4u.8z8@igsrsparc2.er.usgs.gov>

> 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.

See a hair? Split it....

If you have a disabled constraint, then do you really have the constraint? Rather, I would state that what you have is the *definition* of the constraint, but the constraint will not truly exist until you enable it.

> 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).

I'll bite on the test...Oracle 10.2.0.2 running on Red Hat Linux:

SQL> create table test (id number);

Table created.

SQL> alter table test add constraint test_pk primary key (id);

Table altered.

SQL> select index_name from user_indexes

   2 where table_name='TEST';

INDEX_NAME

---

TEST_PK Test table created...PK constraint added, and an index with the same name exists. We now disable the constraint:

SQL> alter table test disable constraint test_pk;

Table altered.

SQL> drop index test_pk;
drop index test_pk

            *
ERROR at line 1:
ORA-01418: specified index does not exist

SQL> select index_name from user_indexes

   2 where table_name='TEST';

no rows selected

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME

---

TEST_PK The index could not be dropped because it no longer exists! The constraint definition still remains in the Data Dictionary as shown by querying USER_CONSTRAINTS.

Enabling the constraint should rebuild the index:

SQL> alter table test enable constraint test_pk;

Table altered.

SQL> select index_name from user_indexes

   2 where table_name='TEST';

INDEX_NAME

---

TEST_PK So the test does show that the PK constraint definition can survive without the presence of the index. Bug then this leads to the question without an answer based in fact, but rather opinion...does the constraint still exist? I would say there is no PK constraint because it has been disabled. Until you enable (and the index is created), the PK constraint does not exist, but rather its definition exists (without the index).

It might all be semantics and I just might be picking a bone...

Cheers,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu Jun 15 2006 - 09:48:27 CDT