This discussion is archived
8 Replies Latest reply: Mar 7, 2013 6:34 PM by 995142 RSS

How to hack oracle not checking row of data while running alter pk command

995142 Newbie
Currently Being Moderated
Hi All,

Because of I want to alter pk to table, but this table have lot of rows (about 10 billions).
If I use normal alter command, Oracle must to check every row to make sure it is uniuqe.
This method must to use lot of times (at lease 60 days) untill done.

How can I pass this checking method ? and I am sure that data in all rows (colume to be pk) are uniuqe.
Are there any ideas about this ?

Very thanks.
My Oracle is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
  • 1. Re: How to hack oracle not checking row of data while running alter pk command
    BEDE Explorer
    Currently Being Moderated
    use the novalidate clause

    Something like :
    alter table my_table add constraint ck_x check (...) novalidate
    ;

    or

    create index my_tab_pk_i (col1,col2) on my_tab
    ;
    alter table my_tab add constraint my_tab_pk primary key (col1,col2) novalidate using index
    ;

    or

    alter table my_tab add constraint mya_tab_x_fk1 foreign key (...) references ... novalidate
    ;

    Edited by: BEDE on Mar 6, 2013 12:02 PM
  • 2. Re: How to hack oracle not checking row of data while running alter pk command
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi and welcome to the forum,
    How can I pass this checking method ? and I am sure that data in all rows (colume to be pk) are uniuqe.
    Passing these checks probably does not sovle your problem - it depends ....

    How do things look like now? What is the current PK like, what is the index supporting it like?

    How do you want to change it?

    Please post more details, as in real statements, not just words.

    Regards
    Peter
  • 3. Re: How to hack oracle not checking row of data while running alter pk command
    995142 Newbie
    Currently Being Moderated
    Thanks for your answer, I just try to do this now...
  • 4. Re: How to hack oracle not checking row of data while running alter pk command
    995142 Newbie
    Currently Being Moderated
    Hi Peter,

    My current table have no constrain, it is only table with 4 columns, but there are lot of records.
    This table is for testing, but in the real production, it is must to have PK for one column at this table.
    I have 2 ways to done this,
    1) Delete all records from table, add PK and then insert data into the table again (this is very bad cause of need a long time).
    2) Finding a method to add PK without Oracle validate unique data row by row ???

    Table like below,
    CREATE TABLE "TEST_SCHEMA"."TEST_ALTER_PK"
    (     "ID" VARCHAR2(20 BYTE),
         "COLUMN1" VARCHAR2(20 BYTE),
         "COLUMN2" VARCHAR2(20 BYTE),
         "COLUMN3" VARCHAR2(20 BYTE)
    )
    and I want to add PK to column ID.

    Thanks for your response.
  • 5. Re: How to hack oracle not checking row of data while running alter pk command
    rp0428 Guru
    Currently Being Moderated
    >
    My current table have no constrain, it is only table with 4 columns, but there are lot of records.
    >
    You should have a NOT NULL constraint on the primary key column.

    How is the table normally used? With only four columns you might consider using an INDEX ORGANIZED table.
    CREATE TABLE TEST_ALTER_PK 
    ( "ID" VARCHAR2(20 BYTE) NOT NULL, 
    "COLUMN1" VARCHAR2(20 BYTE), 
    "COLUMN2" VARCHAR2(20 BYTE), 
    "COLUMN3" VARCHAR2(20 BYTE),
     constraint test_pk primary key (id)
    ) organization index AS SELECT * FROM myTable;
    You can create the table and index and populate it from your existing table with the above statement.
    >
    1) Delete all records from table, add PK and then insert data into the table again (this is very bad cause of need a long time).
    >
    Terrible idea to add the data with the index already in place.
    >
    2) Finding a method to add PK without Oracle validate unique data row by row ???
    >
    Just let Oracle do what it needs to do. The data should be validated.

    Use code like I showed above to create and load the table with one query. Use NOT NULL for the primary key column. Don't add the primary key constsraint until AFTER the data is loaded.
  • 6. Re: How to hack oracle not checking row of data while running alter pk command
    995142 Newbie
    Currently Being Moderated
    Hi,

    Please notice that there are about 10 billions records in the table.
    My point is - how to add PK without Oracle validate because if Oracle do the validate, will be use a long time until done and I cannot wait.
    I also confirmed that all of PK records data is unique.

    Thanks for your suggestion.
  • 7. Re: How to hack oracle not checking row of data while running alter pk command
    rp0428 Guru
    Currently Being Moderated
    >
    Please notice that there are about 10 billions records in the table.
    >
    I did notice that. You can create the new table and use the PARALLEL option.
  • 8. Re: How to hack oracle not checking row of data while running alter pk command
    995142 Newbie
    Currently Being Moderated
    Hi All,

    Very thanks for your help and special for BEDE.
    I try to use below command with 1 million records,

    ALTER TABLE test_alter_pk ADD CONSTRAINT test_alter_pk_id PRIMARY KEY(id)
    USING INDEX(CREATE INDEX test_alter_pk_id ON test_alter_pk(id)) ENABLE NOVALIDATE;

    it done in 1.34 sec, but i am not try this in the issue table with 10 billions records.
    Will be come back here when i get the result.

    Regards...

    Edited by: VRay on 7 มี.ค. 2556, 18:27 น.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points