CODEWITHSUNDEEP
oracle-databaseplsqlddl
x.509
x.509

Reputation: 2235

Altering primary key

I have seen 2 approaches to alter a primary key. the approach 1, is something in which i delete the primary key (it deletes corresponding index as well) and then create the primary key with a new index with in it i.e.

alter table TABLE_NAME drop constraint PK_TABLE_NAME drop index;
alter table TABLE_NAME
add constraint PK_TABLE_NAME PRIMARY KEY ("COL1") 
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
           PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
  TABLESPACE "USERS" ENABLE;
COMMIT;
/

The second approach is doing all steps indiviually i.e.

alter table TABLE_NAME drop constraint PK_TABLE_NAME;
drop index PK_TABLE_NAME;

CREATE UNIQUE INDEX PK_TABLE_NAME ON TABLE_NAME
(COL1)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

alter table TABLE_NAME add constraint PK_TABLE_NAME PRIMARY KEY ("COL1") USING INDEX PK_TABLE_NAME;
COMMIT;
/

So now my questions are

  1. are these 2 approaches has anything different at the backend except for the fact that the steps are splitted up?
  2. I see that we can mention column name wihtin quotes i.e. "COL1" or without quotes i.e. COL1. Are these 2 approaches would make any difference?
  3. Consider these steps are being executed on a table which has TRILLION OF DATA, do any of these has any performance gain on other?

Upvotes: 3

Views: 16560

Answers (1)

Jon Heller
Jon Heller

Reputation: 36817

With the DDL exactly as you have it, as far as I know there is no difference between the two. (This is based on comparing select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') from dual;, some simple performance tests, and my previous experience dealing with these two approaches.)

But if you have trillions of rows or bytes, then you should probably use parallelism, and then there is a significant difference. The first approach does not allow you to create the index in parallel (ORA-03001: unimplemented feature), but the second method does. Even if you do not want the index to be parallel, you should probably create it in parallel and then change it to noparallel with a command like ALTER INDEX <index> NOPARALLEL;

As for the double quotes, they make no difference when the column is all upper-case. But if you use mixed case then it means that the name is case sensitive and you must always use quotation marks to refer to the name. That is really annoying, so I usually remove quotation marks to prevent accidentally creating a case-sensitive name.

A few other notes. You may want to consider using NOLOGGING. And there is no need for the COMMIT;, DDL will automatically cause a commit.

Upvotes: 2

Related Questions