Solved

How to drop index?

Posted on 2001-08-29
22
2,506 Views
Last Modified: 2012-06-27
In my program I will insert a lot of data into the tables (Oracle). In order to speed up the processing, I am going to use DROP INDEX before insert the data and then CREATE index. But as I want to drop the index, such as DROP INDEX PARTNERID or DROP INDEX SYS_C00831, it failed. It comes an error message like:
ORA-02429: Index to forced unique/primarykey not deletebly.

Why?
0
Comment
Question by:chenwei
  • 9
  • 5
  • 5
  • +2
22 Comments
 
LVL 2

Expert Comment

by:jbever
ID: 6435867
The indexes you are trying to drop are part of a primary key constraint. (or unique key)
You should drop that instead.

j.

0
 
LVL 2

Expert Comment

by:RMZ
ID: 6436039
hi
u can't drop index is part of primary key
but u can drop primary key then insert data then recreate it again
---rmz----
0
 

Author Comment

by:chenwei
ID: 6436135
The table constrain lokks like:

Constraint         Type                     Column              Position      Status
-------------------------------------------------------------------------------------------------
SYS_C00818    Not NUll              PARTNERID                          ENABLED
SYS_C00819    Primary Key        PARTNERID        1               ENABLED

And the cripts is:

CREATE TABLE PARTNER (
  PARTNERID        NUMBER        NOT NULL,
  PARTNERNAME      VARCHAR2 (20),
  DISPLAYNAME      VARCHAR2 (50),
  ADDRESS          VARCHAR2 (100),
  ZIP              VARCHAR2 (10),
  CITY             VARCHAR2 (50),
  COUNTRY          VARCHAR2 (50),
  CONTACTNAME      VARCHAR2 (50),
  CONTACTPHONE     VARCHAR2 (30),
  MCCOMMENT        VARCHAR2 (255),
  ISGROUP          NUMBER,
  APPLICATIONID    NUMBER,
  CREATETIME       DATE,
  CHANGETIME       DATE,
  ADMINDLL         VARCHAR2 (255),
  CLIENTCONTEXTID  NUMBER,
  PRIMARY KEY ( PARTNERID )
    USING INDEX
     TABLESPACE USR PCTFREE 10
     STORAGE ( INITIAL 50K NEXT 64K PCTINCREASE 1 ))
   TABLESPACE USR NOLOGGING
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 51200
   NEXT 135168
   PCTINCREASE 1
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

How can I write the SQL?
0
 
LVL 2

Expert Comment

by:jbever
ID: 6436255
alter table PARTNER disable SYS_C00819    ;
or
alter table PARTNER drop primary key cascade;

jan.
0
 
LVL 3

Expert Comment

by:graf27
ID: 6436291
Be careful: If you disable the constraint: (SYS_C00819)
The related index will droped immediatly:

Tip:

ALTER TABLE PARTNER DROP CONSTRAINT SYS_C00819;   -- drop the constraint, index will also be dropped
-- do your loading work here
ALTER TABLE PARTNER ADD CONSTRAINT PARTNER_PK     -- give a name for the constraint
  PRIMARY KEY PARNTER_ID
 USING INDEX
 TABLESPACE USR PCTFREE 10
 STORAGE ( INITIAL 50K NEXT 64K PCTINCREASE 1 );

then you can allways use:

alter table partner disable constraint PARTNER_PK;                 -- disable

-- do your loading work here

alter table partner enable constraint PARTNER_PK                   -- enable
 using index tablspace USR pctfree 10
 storage (initial 50k next 64k pctincrease 1);

you can verify the exists of the index PARTNER_PK after the DISABLE CONSTRAINT clause.
0
 

Author Comment

by:chenwei
ID: 6436939
I got to go now. I give you the answer tomorrow. I am in Europe.
0
 

Author Comment

by:chenwei
ID: 6439853
Thanks for the info. How can I drop the foreign keys? I use :

ALTER TABLE wei_PARTTOCONTYPE DROP FOREIGN KEY;

but it shows error:
ORA-00905: Keyword is absend.

Why?
0
 
LVL 2

Accepted Solution

by:
jbever earned 30 total points
ID: 6439870
Use the name of the key.
ALTER TABLE wei_PARTTOCONTYPE  DROP PARTNER_FK    
or
drop the primary key on the field it is based on, with the cascade option.

j.
0
 

Author Comment

by:chenwei
ID: 6440117
Another problem:
since the primary key PARTNERID of table OARTNER is referenced by other tables, after I've drop the PK, the referenced exist not any more, even I ADD the primary key later. How can I rebuild the references?
0
 
LVL 2

Expert Comment

by:jbever
ID: 6440221
Altering all the tables that reference the PARTNER table.
j.
0
 

Author Comment

by:chenwei
ID: 6440384
I use the command you told me:
ALTER TABLE wei_PARTTOCONTYPE DROP PARTNERID;

but it shows the same error message:
ORA-00905: Keyword is absend.

Sure I can drop the PK it is based on. But I just want to drop the FK.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:jbever
ID: 6440758
Sorry, try this:

ALTER TABLE wei_PARTTOCONTYPE DROP CONSTRAINT PARTNERID;
j.
0
 

Expert Comment

by:yo_hou
ID: 6443829
hi
drop primary key can follows:
  alter table table_name drop primary key;
 or
  alter table table_name drop constraint        primary_key_constraint_name;

drop foreign key can follows:
   alter table table_name drop constraint foreign_key_constraint_name;

primary_key_constraint_name and foreign_key_constraint_name
you can get by using :
   select constraint_name from user_constraints where
table_name='table_name';
0
 

Author Comment

by:chenwei
ID: 6443900
I tryed this but it failed too. Here is the error message:
ORA-02443: Integrity rule can not be deleted - Rule not exist.

For convenient here is the script of table WEI_PARTTOCONTYPE:

CREATE TABLE WEI_PARTTOCONTYPE (
  PARTNERID         NUMBER        NOT NULL,
  CONNECTORTYPEID   NUMBER        NOT NULL,
  AVAILABILITY      NUMBER,
  AVAILABILITYTIME  VARCHAR2 (42),
  PRIMARY KEY ( PARTNERID, CONNECTORTYPEID )
    USING INDEX
     TABLESPACE USR PCTFREE 10
     STORAGE ( INITIAL 50K NEXT 72K PCTINCREASE 1 ))
   TABLESPACE USR NOLOGGING
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 51200
   NEXT 67584
   PCTINCREASE 1
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

ALTER TABLE WEI_PARTTOCONTYPE ADD
 FOREIGN KEY (CONNECTORTYPEID)
  REFERENCES MULTICOM.CONNECTORTYPE (CONNECTORTYPEID) ;

ALTER TABLE WEI_PARTTOCONTYPE ADD
 FOREIGN KEY (PARTNERID)
  REFERENCES MULTICOM.WEI_PARTNER (PARTNERID) ;

0
 

Author Comment

by:chenwei
ID: 6443949
Sorry, I put my last message before I read yo_hou's message. I iwll try yo_hou's one now.
0
 

Author Comment

by:chenwei
ID: 6444997
To yo_hou:

I think your SQL to drop foreign key is the same as jbever, right? But it failed. But if I use:

ALTER TABLE WEI_PARTTOCONTYPE DROP CONSTRAINT sys_c001074;

It runs. sys_c001074 is the index number of PARTNERID by table WEI_PARTTOCONTYPE. I don't want using sys_c00xxx since it's not a permanent value.
0
 

Expert Comment

by:yo_hou
ID: 6447060
You can specify constraint name when creating constraints.
If you don't specify them,oracle will provide them like sys_xxxxxxx.
For example:
    create table employee
     ( employee_name varchar2(100),
       employee_no   varchar2(20),
       employee_id   varchar2(20),
       constraint employee_fk foreign key (employee_no,employee_id) reforences department(employee_no,employee_id) on delete set null
      );

    Constraint_name  you can query from user_constraints
   is employee_fk,not sys_XXXXXX.
 
"Alter table table_name drop foreign key" is not invalided in oracle.But "alter table table_name drop primary key cascade" is valided.

As I mentioned , Drop foreign key must use
   alter table table_name drop constraint XXXXXXX.
 this usage can deployed for any constraint(ex.primary key,foreign key, check constraints.) But for primary key ,
if it is referenced by other foreign keys.It must follows:
alter table table_name drop primary key cascade. By doing so, you just drop foreign keys related to this primary key.

In another way, you can first disable all foreign keys related to this primary key. Then you can use :
  alter table table_name drop primary key.

Disable a constraint follows:
    alter table table_name disable constraint constraint_name.

0
 

Expert Comment

by:yo_hou
ID: 6447061
You can specify constraint name when creating constraints.
If you don't specify them,oracle will provide them like sys_xxxxxxx.
For example:
    create table employee
     ( employee_name varchar2(100),
       employee_no   varchar2(20),
       employee_id   varchar2(20),
       constraint employee_fk foreign key (employee_no,employee_id) reforences department(employee_no,employee_id) on delete set null
      );

    Constraint_name  you can query from user_constraints
   is employee_fk,not sys_XXXXXX.
 
"Alter table table_name drop foreign key" is not invalided in oracle.But "alter table table_name drop primary key cascade" is valided.

As I mentioned , Drop foreign key must use
   alter table table_name drop constraint XXXXXXX.
 this usage can deployed for any constraint(ex.primary key,foreign key, check constraints.) But for primary key ,
if it is referenced by other foreign keys.It must follows:
alter table table_name drop primary key cascade. By doing so, you just drop foreign keys related to this primary key.

In another way, you can first disable all foreign keys related to this primary key. Then you can use :
  alter table table_name drop primary key.

Disable a constraint follows:
    alter table table_name disable constraint constraint_name.

0
 

Expert Comment

by:yo_hou
ID: 6447063
sorry , because the computer reacts so slowly,so i click again.
0
 

Expert Comment

by:yo_hou
ID: 6447070
sorry , because the computer reacts so slowly,so i click again.
0
 
LVL 3

Expert Comment

by:graf27
ID: 6449803
chenwei:

What do you want ??
You ask a question, I give you the solution to handle Primary Keys. Right ?
Now you ask the next question to handle Foreign Keys ?
And next ?
0
 

Author Comment

by:chenwei
ID: 6449935
Sorry. graf27 is right. I have to give the points before I ask the second one. It's difficult for me to make the desicion to give whom the points since everyone's info is quite helpful for me. But I would like to say jbever's answer maybe the one I want to give the points. This dosen't mean that his answer is much netter than the other, I just mean his answer is enough good for me and is relatively earlier than the other.

I will put my question for drop FK as a new question again. Hope yo_hou can lock it so I can give yo_hou the points.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now