This discussion is archived
3 Replies Latest reply: Feb 6, 2013 9:53 AM by UserMB RSS

specifying index tablespace while enabling disabling primary key constraint

UserMB Explorer
Currently Being Moderated
hello,
during ETL i want to disable the PK/UK constraints to speed up the load;
on the enabling of the constraint, the corresponding indexes are created in a different tablespace then they were originaly on the creation of the table itself
i tried to disable the PK/UK's with option 'keep index' , set corresponding indexes to 'unusuable' status, and use 'alter session set skip_unusable_indexes = true' , but then i cannot insert rows with the error
Error: ORA-01502: index 'xxx' or partition of such index is in unusable state
can anyone advise if there is a way to either enable the PK/UK constraint and specify the tablespace for the corresponding indexes, or to keep the index and make the inserts work?
id like to avoid dropping the constraints / indexes and recreating both from the scratch after the load
i appreciate any tips
thanks
rgds
  • 1. Re: specifying index tablespace while enabling disabling primary key constraint
    riedelme Expert
    Currently Being Moderated
    :> >
    hello,
    during ETL i want to disable the PK/UK constraints to speed up the load; Good idea. This can help performance.
    on the enabling of the constraint, the corresponding indexes are created in a different tablespace then they were originaly on the creation of the table itself
    i tried to disable the PK/UK's with option 'keep index' , set corresponding indexes to 'unusuable' status, and use 'alter session set skip_unusable_indexes = true' , but then i cannot insert rows with the error
    Error: ORA-01502: index 'xxx' or partition of such index is in unusable state
    can anyone advise if there is a way to either enable the PK/UK constraint and specify the tablespace for the corresponding indexes, or to keep the index and make the inserts work?
    id like to avoid dropping the constraints / indexes and recreating both from the scratch after the load
    Since the disable is not doing what you want I don't see an alternative to dropping and recreating :(, unless ...

    I am wondering why the indexes are being recreated in another tablespace. How is that tablespace being chosen? Can you control the tablespace somehow? Could it be the default tablespace of the schema re-enabling the index?

    Edited by: riedelme on Feb 5, 2013 10:36 AM
  • 2. Re: specifying index tablespace while enabling disabling primary key constraint
    rp0428 Guru
    Currently Being Moderated
    >
    can anyone advise if there is a way to either enable the PK/UK constraint and specify the tablespace for the corresponding indexes, or to keep the index and make the inserts work?
    id like to avoid dropping the constraints / indexes and recreating both from the scratch after the load
    >
    Have you tried specifying the tablespace in the USING clause when you enable the constraint?
    ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX TABLESPACE myTS;
    See the DBA guide
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm
    >
    Creating an Index Associated with a Constraint

    Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.
    >
    Note the last sentence two sentences:
    >
    you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.
    >
    You do realize, I hope, that the only way Oracle can KNOW that the index contains valid values is to rebuild it?
  • 3. Re: specifying index tablespace while enabling disabling primary key constraint
    UserMB Explorer
    Currently Being Moderated
    hello
    statement:
    ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX TABLESPACE myTS;
    
    alter table emp enable constraint my_PK_constraint_name using index tablespace myTS;
    
    alter table emp enable constraint my_UK_constraint_name using index tablespace myTS;
    worked fine
    thanks very much
    rgds

Legend

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