export and importing indexes and pk

Thanks for the question, noga.

Asked: January 03, 2001 - 5:00 am UTC

Last updated: September 09, 2006 - 12:05 pm UTC

Version: 8.0.6

Viewed 10K+ times! This question is

You Asked

Hi,

I dropped some of the indexes in my schema, when I tried to load them back using the export rows=n I found that some pk were build partially, that mean I see the pk in user_constraints but not in user_indexes. I also found that this happened only when I had index that contained all the fields of the pk and more.
Only when I dropped the index and first created the pk, the pk was build correctly-
Who can I continoue using the export import utility if we are losing some indexes ?

Example-
-- See that the PK exsist partialy-
SQL> select index_name,status from user_indexes where table_name='Q2';

INDEX_NAME STATUS
------------------------------ --------
Q2_2IX VALID

SQL> select constraint_name,status from user_constraints where table_name='Q2';

CONSTRAINT_NAME STATUS
------------------------------ --------
Q2_PK ENABLED
Q2_SRV_TRX_S_NO_NN ENABLED
Q2_DVC_SFX_NO_NN ENABLED
SYS_C0087257 ENABLED
SYS_C0087258 ENABLED
Q2_PHD_ID_NN ENABLED

6 rows selected.

SQL> alter table q2 drop constraint Q2_PK;

Table altered.

SQL> ALTER TABLE Q2 ADD CONSTRAINT Q2_PK PRIMARY KEY
(SRV_TRX_S_NO, DVC_SFX_NO)
USING INDEX ;

Table altered.

SQL> select constraint_name,status from user_constraints where table_name='Q2';

CONSTRAINT_NAME STATUS
------------------------------ --------
Q2_PK ENABLED
Q2_SRV_TRX_S_NO_NN ENABLED
Q2_DVC_SFX_NO_NN ENABLED
SYS_C0087257 ENABLED
SYS_C0087258 ENABLED
Q2_PHD_ID_NN ENABLED

6 rows selected.

SQL> select index_name,status from user_indexes where table_name='Q2';

INDEX_NAME STATUS
------------------------------ --------
Q2_2IX VALID

Droping the index-
SQL> drop index Q2_2IX;
drop index Q2_2IX
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table q2 drop primary key;

Table altered.

SQL> drop index Q2_2IX;

Index dropped.

SQL> @q2_cre_pk (creates the PK as described before)

Table altered.

SQL> select constraint_name,status from user_constraints where table_name='Q2';

CONSTRAINT_NAME STATUS
------------------------------ --------
Q2_PK ENABLED
Q2_SRV_TRX_S_NO_NN ENABLED
Q2_DVC_SFX_NO_NN ENABLED
SYS_C0087257 ENABLED
SYS_C0087258 ENABLED
Q2_PHD_ID_NN ENABLED

6 rows selected.

SQL> select index_name,status from user_indexes where table_name='Q2';

INDEX_NAME STATUS
------------------------------ --------
Q2_PK VALID

SQL> CREATE INDEX Q2_2IX ON Q2 (SRV_TRX_S_NO,
2 DVC_SFX_NO,
PHD_ID);

Index created.

SQL> select index_name,status from user_indexes where table_name='Q2';

INDEX_NAME STATUS
------------------------------ --------
Q2_2IX VALID
Q2_PK VALID

Thanks

and Tom said...

A primary key constraint will use an EXISTING index (take it over actually) as you have observed. That is why:

SQL> drop index Q2_2IX;
drop index Q2_2IX
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of
unique/primary key

This is the expected behaviour. Nothing was partially built here -- the primary key was "borrowing" the other index to enforce itself.

Here is an example that shows the order of creation will result in different objects. A constraint does not imply an index will be created!!

tkyte@ORA806.WORLD> create table t ( x int, y int );
Table created.

tkyte@ORA806.WORLD> create index t_idx on t(x,y);
Index created.

tkyte@ORA806.WORLD> alter table t add constraint t_pk primary key(x);
Table altered.

tkyte@ORA806.WORLD> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------
T TABLE

T_IDX INDEX

There is no index created for this primary key -- there is none needed since we already have one that is good enough. To see that we in fact have a primary key we just need to insert:

tkyte@ORA806.WORLD> insert into t(x) values(1);

1 row created.

tkyte@ORA806.WORLD> insert into t(x) values(1);
insert into t(x) values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TKYTE.T_PK) violated

So, we have a primary key -- it is not "partial". To see that we 'hijacked' this index, we can:

tkyte@ORA806.WORLD> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

So, to see that a different order of object creation may result in a different set of schema objects we can:

tkyte@ORA806.WORLD> alter table t drop constraint t_pk;
Table altered.

tkyte@ORA806.WORLD> drop index t_idx;
Index dropped.

tkyte@ORA806.WORLD> alter table t add constraint t_pk primary key(x);

Table altered.

Now that created an index for us since one does not already exist that we can use.

tkyte@ORA806.WORLD> create index t_idx on t(x,y);

Index created.

tkyte@ORA806.WORLD> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------
T TABLE

T_IDX INDEX
T_PK

So, now we have the "implicitly" created (but redundant) index for the primary key as well as the additional index we created. This is the expected behaviour in Oracle8.0 and up.

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question about performance ....

Arindom Kanti Dam, February 25, 2002 - 1:44 pm UTC

Was trying to speed up the primary key constraint/index using parallel query. My results : SQL> SQL> desc url Name Null? Type ----------------------------------------- -------- ---------------------------- URLID NOT NULL NUMBER BIZID CHAR(10) URL VARCHAR2(256) URLTYPE VARCHAR2(6) FAILURECOUNT NUMBER CREATEDON NOT NULL DATE MODIFIEDON DATE CREATEDBY VARCHAR2(40) MODIFIEDBY VARCHAR2(40) SQL> SQL> select OBJECT_NAME, OBJECT_TYPE 2 from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ URL TABLE 1 row selected. Elapsed: 00:00:00.01 SQL> SQL> ALTER TABLE URL 2 ADD CONSTRAINT PK_URL PRIMARY KEY (URLID) 3 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 53248 4 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 5 FREELISTS 1 ); Table altered. Elapsed: 00:00:55.24 SQL> SQL> select OBJECT_NAME, OBJECT_TYPE 2 from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ PK_URL INDEX URL TABLE 2 rows selected. Elapsed: 00:00:00.01 SQL> SQL> rem --- > rem --- Instead of the above ; figured I would create index 'in parallel' SQL> rem --- and enable constraint(as prallel ddl is not possible in alter table) SQL> rem --- > SQL> SQL> SQL> alter session force parallel ddl; Session altered. Elapsed: 00:00:00.01 SQL> SQL> ALTER TABLE URL 2 drop CONSTRAINT PK_URL; Table altered. Elapsed: 00:00:00.31 SQL> SQL> drop index pk_url; drop index pk_url * ERROR at line 1: ORA-01418: specified index does not exist Elapsed: 00:00:00.02 SQL> SQL> select OBJECT_NAME, OBJECT_TYPE 2 from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ URL TABLE 1 row selected. Elapsed: 00:00:00.01 SQL> SQL> SQL> create index pk_url 2 on URL(URLID) /*+ parallel(url,7) */ 3 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 53248 4 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 5 FREELISTS 1 ); Index created. Elapsed: 00:00:52.69 SQL> SQL> ALTER TABLE URL 2 ADD CONSTRAINT PK_URL PRIMARY KEY (URLID) using index; Table altered. Elapsed: 00:00:59.84 SQL> SQL> select OBJECT_NAME, OBJECT_TYPE 2 from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ PK_URL INDEX URL TABLE 2 rows selected. Elapsed: 00:00:00.01 SQL> SQL> My Question is ; if the 'alter table add constraint' takes over the existing index ; why does it take almost as long as the create index itself? Is there a work around or suggestions to speed up the index/pk creation?

February 25, 2002 - 2:27 pm UTC

Well, couple things here. You need to read the documentation for the syntax of a parallel create index. You are using a hint (valid in DML, not DDL). You need to use the parallel keyword. Additionally, you created in NON-UNIQUE index. Hence when you added the constraint -- it had to VALIDATE all of the data since the index it used is not enough to say "hey, this data is OK". For example: REM drop table t; REM REM create table t as select * from all_objects; REM insert /*+ APPEND */ into t select * from t; REM commit; couple more insert/commits to load it up... ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id); Table altered. Elapsed: 00:00:08.42 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop constraint t_pk; Table altered. Elapsed: 00:00:00.68 ops$tkyte@ORA817DEV.US.ORACLE.COM> create unique index t_idx 2 on t(object_id) unrecoverable 3 parallel 2 4 / Index created. Elapsed: 00:00:06.99 ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id) 2 / Table altered. Elapsed: 00:00:00.03 ops$tkyte@ORA817DEV.US.ORACLE.COM> Now, even if you use a NON-Unique index, you can still do this IF you know for a FACT that the data is OK (unique). ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop constraint t_pk; Table altered. Elapsed: 00:00:00.28 ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx 2 on t(object_id) unrecoverable 3 parallel 2 4 / Index created. Elapsed: 00:00:07.86 ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id) rely enforce 2 / Table altered. Elapsed: 00:00:00.02 ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from t where rownum = 1; insert into t select * from t where rownum = 1 * ERROR at line 1: ORA-00001: unique constraint (OPS$TKYTE.T_PK) violated Elapsed: 00:00:00.06 ops$tkyte@ORA817DEV.US.ORACLE.COM>

Thanks.

Arindom Kanti Dam, February 25, 2002 - 2:37 pm UTC

Thanks.Helped a lot...
(Though Table 23-1 ; Oracle 8i Concepts ; 8.1.6 does state that alter session force parallel DDL overides the Parallel Clause for a create index parallel operation; also I did see parallel OS processes with a ps -aef)

February 25, 2002 - 2:50 pm UTC

Yes -- but your "hint" leads me to believe you believe it will get you an index created with parallel 7 -- it won't, it does nothing.

the alter session for you set the degree of parallelism to the default degree of parallelism. Use the PARALLEL clause in the create command to get it set to what you want explicitly.

Is this Normal?

Sunil Gururaj, July 11, 2003 - 3:39 am UTC

Hi Tom, Could you explain a bit about the below behaviour in Oracle 8.1.7.4? There is a table LOGICAL_DATE which has the below Indexes/Constraints: SQL>> select index_name from ind where table_name = 'LOGICAL_DATE'; Index Name ------------------------- LOGICAL_DATE_1UQ LOGICAL_DATE_PK 2 rows selected. SQL>> select constraint_name,constraint_type from user_constraints where table_name = 'LOGICAL_DATE'; Constraint Name C ----------------------------------- - LGCDT_LOGICAL_DATE_NN C LGCDT_LOGICAL_DATE_TYPE_NN C LOGICAL_DATE_1UQ U LOGICAL_DATE_PK P 4 rows selected. Now, I drop the UQ constraint and assume that the index associated with it would be dropped automatically as is the case with PKs SQL>> alter table logical_date drop constraint LOGICAL_DATE_1UQ; Table altered. SQL>> select constraint_name,constraint_type from user_constraints where table_name = 'LOGICAL_DATE'; Constraint Name C ----------------------------------- - LGCDT_LOGICAL_DATE_NN C LGCDT_LOGICAL_DATE_TYPE_NN C LOGICAL_DATE_PK P 3 rows selected. As seen above, the Constraint gets dropped but... SQL>> select index_name from ind where table_name = 'LOGICAL_DATE'; Index Name ------------------------- LOGICAL_DATE_1UQ LOGICAL_DATE_PK 2 rows selected. ... the index remains. When I try to drop the index, SQL>> drop index LOGICAL_DATE_1UQ; drop index LOGICAL_DATE_1UQ * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key SQL>> Why is this?

July 11, 2003 - 9:40 am UTC

osh kosh bgosh -- how's about the DDL for that example please, instead of me trying to figure out what permutations of DDL i might have to make up. I can replicate your example, I can also come up with tons of examples that do not. Here is one of each ops$tkyte@ORA817DEV> drop table logical_date; Table dropped. ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> create table logical_date 2 ( x int not null, 3 y int not null, 4 z int 5 ) 6 / Table created. ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> alter table logical_date add constraint LGCDT_LOGICAL_DATE_NN check (y>0); Table altered. ops$tkyte@ORA817DEV> alter table logical_date add constraint LGCDT_LOGICAL_DATE_TYPE_NN check (y>0); Table altered. ops$tkyte@ORA817DEV> create index LOGICAL_DATE_1UQ on logical_date(x,y); Index created. ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_1UQ unique(x,y); Table altered. ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_PK primary key(x); Table altered. ops$tkyte@ORA817DEV> create index LOGICAL_DATE_PK on logical_date(x); Index created. ops$tkyte@ORA817DEV> select index_name from user_indexes; INDEX_NAME ------------------------------ LOGICAL_DATE_1UQ LOGICAL_DATE_PK At this point BOTH constraints are using the SAME index for enforcement -- logical_date_1uq is used for BOTH the primary and unique constraint ops$tkyte@ORA817DEV> alter table logical_date drop constraint logical_date_1uq; Table altered. ops$tkyte@ORA817DEV> select index_name from user_indexes; INDEX_NAME ------------------------------ LOGICAL_DATE_1UQ LOGICAL_DATE_PK so the index 1uq stays cause IT IS FOR THE PRIMARY KEY as well -- the other index, well, that is just an index here ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> drop table logical_date; Table dropped. ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> create table logical_date 2 ( x int not null, 3 y int not null, 4 z int 5 ) 6 / Table created. ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> alter table logical_date add constraint LGCDT_LOGICAL_DATE_NN check (y>0); Table altered. ops$tkyte@ORA817DEV> alter table logical_date add constraint LGCDT_LOGICAL_DATE_TYPE_NN check (y>0); Table altered. ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_1UQ unique(x,y); Table altered. ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_PK primary key(x); Table altered. ops$tkyte@ORA817DEV> select index_name from user_indexes; INDEX_NAME ------------------------------ LOGICAL_DATE_1UQ LOGICAL_DATE_PK ops$tkyte@ORA817DEV> alter table logical_date drop constraint logical_date_1uq; Table altered. ops$tkyte@ORA817DEV> select index_name from user_indexes; INDEX_NAME ------------------------------ LOGICAL_DATE_PK ops$tkyte@ORA817DEV> now, since each constraint created its own index.... well, it gets dropped

Please Ignore the Previous Review

Sunil Gururaj, July 11, 2003 - 3:49 am UTC

Hi Tom,

Please ignore the previous review. I got the answer in the below link.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5668744528845, <code>

Thanks,
Sunil

Indexes and Uniqe/PK constraints

Logan Palanisamy, July 11, 2003 - 8:46 pm UTC

Tom,

In your second part of the example which I am quoting below, how come the PK constraint is not using the index created by the Unique key constraint? How does it know that the index LOGICAL_DATE_1UQ was actually created by the unique key constraint?

<Quote>
ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_1UQ
unique(x,y);

Table altered.

ops$tkyte@ORA817DEV> alter table logical_date add constraint LOGICAL_DATE_PK
primary key(x);

Table altered.

ops$tkyte@ORA817DEV> select index_name from user_indexes;

INDEX_NAME
------------------------------
LOGICAL_DATE_1UQ
LOGICAL_DATE_PK

ops$tkyte@ORA817DEV> alter table logical_date drop constraint logical_date_1uq;

Table altered.

ops$tkyte@ORA817DEV> select index_name from user_indexes;

INDEX_NAME
------------------------------
LOGICAL_DATE_PK

ops$tkyte@ORA817DEV>
< End Quote >

Can we surmise like this.

1. More than one Unique/PK constraint could use the same index (unique or not) with the same leading edge, if the index was created separately before the constraints.

2. A PK/Unique constraint won't use an index which was created as part of another PK/Unique constraint. In such a case it will create its own index.

Am I correct?

July 12, 2003 - 9:27 am UTC

it tends to not kidnap an index created implicitly to support a constraint in the first place. It'll kidnap "normal" indexes.

good summary.

Leading edge required?

Pravesh Karthik, September 08, 2004 - 5:49 am UTC

Tom,

I am using, 9ir1.

we are creating indexes as follows.

create index fact_i18 on fact_def(commodity_cs)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i19 on fact_def(invoice_nbr)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i20 on fact_def(part_nbr)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i21 on fact_def(po_nbr)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i22 on fact_def(source_system_ns)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i23 on fact_def(line_item_nbr_po)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);
create index fact_i24 on fact_def(line_item_nbr_voucher)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);

one of my friend said in 9ir1 optimizer will understand that indexes are created on a particular column ( yes, analyzed) and even if its not the leading edge, the optimizer will use the index. Is that true. If so, can i change my index creation to the following.

create index fact_i24 on fact_def(commodity_cs,invoice_nbr,part_nbr,po_nbr,source_system_ns,line_item_nbr_po,line_item_nbr_voucher)
tablespace fact_def_i1
storage (initial 50m next 10m pctincrease 0 maxextents unlimited);

Please let me know if above index creation would mean the same as creating different indexes now...9ir1.

do we need to enable any parameter settings or anything of that sort required?. how different are the above index creation statement if that is not true.

Thanks a lot for your consideration

Pravesh Karthik

September 08, 2004 - 10:01 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5671539468597 <code>

Dropping PK constraints and indexes

Mike, September 08, 2006 - 4:10 pm UTC

From the example provided, it seems that Oracle will drop (or not drop) an index supporting a primary-key constraint, depending on whether the index was implicitly created or was pre-existing.

> --
> -- Save existing objects into a table we can check against later
> --
> create table old_objects as select object_type, object_name from user_objects;

Table created.

> --
> -- Verify we are starting clean
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

no rows selected

> --
> -- Create a table
> --
> CREATE TABLE ELS_POOL_TB
2 (
3 POOL_NBR VARCHAR2(20 BYTE) NOT NULL,
4 POOL_EFCTV_DT DATE NOT NULL,
5 DLVRY_MERS_ID VARCHAR2(7 BYTE) NOT NULL
6 );

Table created.

> --
> -- Check USER_OBJECTS
> --
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
TABLE ELS_POOL_TB

> --
> -- Add a PK constraint
> --
> ALTER TABLE ELS_POOL_TB ADD (
2 CONSTRAINT ELS_POOL_PK PRIMARY KEY (POOL_NBR));

Table altered.

> --
> -- Check USER_OBJECTS, see the new index
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
INDEX ELS_POOL_PK
TABLE ELS_POOL_TB

> --
> -- Drop the PK constraint
> --
> alter table ELS_POOL_TB drop constraint ELS_POOL_PK;

Table altered.

> --
> -- Check USER_OBJECTS, the index is now gone
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
TABLE ELS_POOL_TB

> --
> -- Now create an index for the PK to use
> --
> CREATE UNIQUE INDEX ELS_POOL_PK ON ELS_POOL_TB (POOL_NBR);

Index created.

> --
> -- See it in USER_OBJECTS
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
INDEX ELS_POOL_PK
TABLE ELS_POOL_TB

> --
> -- Add the constraint
> --
> ALTER TABLE ELS_POOL_TB ADD (
2 CONSTRAINT ELS_POOL_PK PRIMARY KEY (POOL_NBR));

Table altered.

> --
> -- Nothing new in USER_OBJECTS
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
INDEX ELS_POOL_PK
TABLE ELS_POOL_TB

> --
> -- Drop the constraint
> --
> alter table ELS_POOL_TB drop constraint ELS_POOL_PK;

Table altered.

> --
> -- Check USER_OBJECTS, the index is still there
> --
> select object_type, object_name from user_objects
2 minus
3 select object_type, object_name from old_objects ;

OBJECT_TYPE OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
INDEX ELS_POOL_PK
TABLE ELS_POOL_TB

> --

My question is:
How can I predict (ahead of time) whether the index will drop when I drop the PK constraint? I need to prepare scripts that will run without errors like 'index does not exist'.

I have looked in USER_CONSTRAINTS and USER_INDEXES, and I see no clues there about whether an index was implicitly created or was otherwise tied to the constraint.

Any guidance on this would be appreciated.

September 09, 2006 - 12:05 pm UTC

depends on the release actually - the behavior changed over time.

In order to build the scripts - why not use something like - plsql, so you can catch and handle expected execptions?

Dropping PK constraints and indexes

Mike, September 11, 2006 - 9:10 am UTC

Sorry that I did not specify our version (10.2.0.1).

A colleague suggested (and I verified) that

ALTER TABLE ELS_POOL_TB DROP CONSTRAINT ELS_POOL_PK KEEP INDEX;
DROP INDEX ELS_POOL_PK;

should run without raising an error, in either situation, for 9i and later.

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Question about performance ....

Thanks.

Is this Normal?

Please Ignore the Previous Review

Indexes and Uniqe/PK constraints

Leading edge required?

Dropping PK constraints and indexes

Dropping PK constraints and indexes

More to Explore

Live SQL

Dev Gym