Re: Row in USER_INDEXES table not deleted when constraint is dropped

From: <capnwhit_at_yahoo.com>
Date: 11 Oct 2006 13:23:17 -0700
Message-ID: <1160598195.468416.55820@m73g2000cwd.googlegroups.com>

DA Morgan wrote:
> Can you create a demo we can try?
>
Unfortunately no because the error is not reproducible. But I have a test user schema with about 100 tables in which the error is manifesting itself and I'd be happy to test anything you may suggest (see initial tests below).

> My guess is because the index existed before the constraint was
> created but I'd like to try to duplicate what you are seeing.
>

I'm 100% certain that the index did not exist. The schema was created by a series of scripts. One script creates all tables, a second script creates all constraints (primary keys, unique constraints, foreign keys) and a third script is used to drop all constraints. I just created a test user today (named "BUGTEST") and recreated the schema using the scripts. If I drop a unique constraint then the associated index is dropped (as expected) but I have an older schema which was created in August using the same scripts (named "CORE23") and said schema is exhibiting the bug. I will demonstrate this via a cut-and-paste sqlplus session below.

This first example is for the new schema "BUGTEST" created today. Notice STEP 5 where the index is gone as expected:

BEGIN: BUGTEST sqlplus session

SQL> show user
USER is "BUGTEST"
SQL> select sysdate from dual;

SYSDATE

11-OCT-06

SQL> -- STEP 1: Look at constraints BEFORE
SQL> --
SQL> select constraint_name, table_name

  2 from user_constraints
  3 where table_name = 'SM_SUBSCRIBER'
  4 and constraint_name not like 'SYS_C00%'   5 order by table_name, constraint_name;

CONSTRAINT_NAME                TABLE_NAME


------------------------------ ------------------------------

FK_SM_SUB_SC_ID_CUST_ID        SM_SUBSCRIBER
FK_SM_SUB_SL_CUSTOMER_ID       SM_SUBSCRIBER
FK_SM_SUB_SL_ID                SM_SUBSCRIBER
FK_SM_SUB_SL_SC_ID             SM_SUBSCRIBER
PK_SM_SUBSCRIBER               SM_SUBSCRIBER
UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER
UK_SM_SUB_USER_NAME            SM_SUBSCRIBER

7 rows selected.

SQL> -- STEP 2: Look at indexes BEFORE
SQL> --
SQL> select index_name, table_name

  2 from user_indexes
  3 where table_name = 'SM_SUBSCRIBER'
  4 order by table_name, index_name;

INDEX_NAME                     TABLE_NAME


------------------------------ ------------------------------

PK_SM_SUBSCRIBER               SM_SUBSCRIBER
UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER
UK_SM_SUB_USER_NAME            SM_SUBSCRIBER

SQL> -- STEP 3: Drop constraint

SQL> --
SQL> alter table SM_SUBSCRIBER drop constraint UK_SM_SUB_USER_NAME;

Table altered.

SQL> -- STEP 4: Look at constraints AFTER
SQL> --
SQL> select constraint_name, table_name

  2 from user_constraints
  3 where table_name = 'SM_SUBSCRIBER'
  4 and constraint_name not like 'SYS_C00%'   5 order by table_name, constraint_name;

CONSTRAINT_NAME                TABLE_NAME


------------------------------ ------------------------------

FK_SM_SUB_SC_ID_CUST_ID        SM_SUBSCRIBER
FK_SM_SUB_SL_CUSTOMER_ID       SM_SUBSCRIBER
FK_SM_SUB_SL_ID                SM_SUBSCRIBER
FK_SM_SUB_SL_SC_ID             SM_SUBSCRIBER
PK_SM_SUBSCRIBER               SM_SUBSCRIBER

UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER 6 rows selected.

SQL>
SQL> -- STEP 5: Look at indexes AFTER
SQL> --
SQL> select index_name, table_name

  2 from user_indexes
  3 where table_name = 'SM_SUBSCRIBER'
  4 order by table_name, index_name;

INDEX_NAME                     TABLE_NAME


------------------------------ ------------------------------

PK_SM_SUBSCRIBER               SM_SUBSCRIBER

UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER SQL> END: BUGTEST sqlplus session

The example above shows the behavior I would expect: namely the index is deleted after the constraint is dropped.

The second example cut and pasted below is for user CORETST23. I was able to export the original user CORE23 and import into a new user CORETST23 and the bug still manifests itself. Notice STEP 5 where the index is left behind and is _NOT_ deleted after we drop the constraint! Compare to STEP 5 above where the index is gone as expected.

BEGIN: CORETST23 sqlplus session

SQL> show user
USER is "CORETST23"
SQL> select sysdate from dual;

SYSDATE

11-OCT-06

SQL> -- STEP 1: Look at constraints BEFORE
SQL> --
SQL> select constraint_name, table_name

  2 from user_constraints
  3 where table_name = 'SM_SUBSCRIBER'
  4 and constraint_name not like 'SYS_C00%'   5 order by table_name, constraint_name;

CONSTRAINT_NAME                TABLE_NAME


------------------------------ ------------------------------

FK_SM_SUB_SC_ID_CUST_ID        SM_SUBSCRIBER
FK_SM_SUB_SL_CUSTOMER_ID       SM_SUBSCRIBER
FK_SM_SUB_SL_ID                SM_SUBSCRIBER
FK_SM_SUB_SL_SC_ID             SM_SUBSCRIBER
PK_SM_SUBSCRIBER               SM_SUBSCRIBER
UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER
UK_SM_SUB_USER_NAME            SM_SUBSCRIBER

7 rows selected.

SQL> -- STEP 2: Look at indexes BEFORE
SQL> --
SQL> select index_name, table_name

  2 from user_indexes
  3 where table_name = 'SM_SUBSCRIBER'
  4 order by table_name, index_name;

INDEX_NAME                     TABLE_NAME


------------------------------ ------------------------------

PK_SM_SUBSCRIBER               SM_SUBSCRIBER
UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER
UK_SM_SUB_USER_NAME            SM_SUBSCRIBER

SQL> -- STEP 3: Drop constraint

SQL> --
SQL> alter table SM_SUBSCRIBER drop constraint UK_SM_SUB_USER_NAME;

Table altered.

SQL> -- STEP 4: Look at constraints AFTER
SQL> --
SQL> select constraint_name, table_name

  2 from user_constraints
  3 where table_name = 'SM_SUBSCRIBER'
  4 and constraint_name not like 'SYS_C00%'   5 order by table_name, constraint_name;

CONSTRAINT_NAME                TABLE_NAME


------------------------------ ------------------------------

FK_SM_SUB_SC_ID_CUST_ID        SM_SUBSCRIBER
FK_SM_SUB_SL_CUSTOMER_ID       SM_SUBSCRIBER
FK_SM_SUB_SL_ID                SM_SUBSCRIBER
FK_SM_SUB_SL_SC_ID             SM_SUBSCRIBER
PK_SM_SUBSCRIBER               SM_SUBSCRIBER

UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER 6 rows selected.

SQL> -- STEP 5: Look at indexes AFTER
SQL> --
SQL> select index_name, table_name

  2 from user_indexes
  3 where table_name = 'SM_SUBSCRIBER'
  4 order by table_name, index_name;

INDEX_NAME                     TABLE_NAME


------------------------------ ------------------------------

PK_SM_SUBSCRIBER               SM_SUBSCRIBER
UK_SM_SUB_CUST_ID_SL_ID_SUB_ID SM_SUBSCRIBER
UK_SM_SUB_USER_NAME            SM_SUBSCRIBER

SQL> END: CORETST23 sqlplus session

Notice in the example above that the UK_SM_SUB_USER_NAME index still exists even after we dropped the constraint! Can anybody help me figure out why the index was not dropped?

Thanks! Received on Wed Oct 11 2006 - 15:23:17 CDT