DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Primary Key constraint has a mind of its own...!!

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Primary Key constraint has a mind of its own...!!

    I have a table called HORACE. I create a primary key.....
    Code:
    ALTER TABLE HORACE
    ADD CONSTRAINT PK_HORACE PRIMARY KEY (customer_org,customer_number)
    USING INDEX
    TABLESPACE  INDEXES;
    My underlying index appears....
    Code:
    select index_name,table_name,degree,logging
    from user_indexes
    where table_name = 'HORACE';
    
    INDEX_NAME                     TABLE_NAME                     DEGREE     LOG
    ------------------------------ ------------------------------ ---------- ---
    PK_HORACE                      HORACE                         1          YES
    I wanna speed up inserts a bit and reduce the redo, so.....
    Code:
    ALTER INDEX PK_HORACE PARALLEL NOLOGGING;
    Index altered.
    Verify the changes to the index.....
    Code:
    select index_name,table_name,degree,logging
    from user_indexes
    where table_name = 'HORACE';
    
    INDEX_NAME                     TABLE_NAME                     DEGREE     LOG
    ------------------------------ ------------------------------ ---------- ---
    PK_HORACE                      HORACE                         DEFAULT    NO
    I now disable and then enable the constraint...
    Code:
    ALTER TABLE HORACE DISABLE CONSTRAINT PK_HORACE;
    ALTER TABLE HORACE ENABLE CONSTRAINT PK_HORACE;
    Just recheck that index set up.......
    Code:
    select index_name,table_name,degree,logging
    from user_indexes
    where table_name = 'HORACE';
    
    INDEX_NAME                     TABLE_NAME                     DEGREE     LOG
    ------------------------------ ------------------------------ ---------- ---
    PK_HORACE                      HORACE                         1          YES
    HEY!! What happened to my parallelisation and nologging options??

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    AFAIK, If the index was created as part of creating the constraint, it is dropped if you disable the constraint and recreated with the enable.

    P.S: can't find it now, I think that if the index was non-unique and existsed before, it is not dropped (yes, you can maintain a PK with a non-unique index!)
    Last edited by DaPi; 03-31-2005 at 11:09 AM.

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    You'd think that the RDBMS would store the modification to the underlying index within the constraint definition, so it knows what to do when the constraint is ENABLED.

    It appears to create the INDEX as it pleases. I've tried putting the NOLOGGING PARALLEL clauses in the constraint DDL at creation time, but it doesn't allow it.
    Code:
    ALTER TABLE HORACE DROP CONSTRAINT PK_HORACE;
    
    Table Altered.
    
    alter table horace
    ADD CONSTRAINT PK_HORACE PRIMARY KEY (customer_org,customer_number)
    USING INDEX
    TABLESPACE INDEXES
    PARALLEL NOLOGGING;
    
    PARALLEL NOLOGGING
    *
    ERROR at line 5:
    ORA-03001: unimplemented feature
    Bloody rubbish!!

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    (yes, you can maintain a PK with a non-unique index!)
    Can you elaborate ol' boy......

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    There is an option to KEEP INDEX on the DISABLE:
    http://asktom.oracle.com/pls/ask/f?p...#7082216367226

    P.S. The above link starts off with a discussion of index use for PK's e.g. non-unique.
    Last edited by DaPi; 03-31-2005 at 11:18 AM.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    That link contains the following from Tom:
    If you have a column(s) that is unique -- use a constraint, do NOT use a unique
    index.

    Lets put it this way -- if you find yourself creating a unique index say "whoops
    -- I'm doing something very wrong here". (unless you are simply precreating it
    for a constraint that will be placed on there). You shouldn't have the need to
    creat a unique index.
    Interesting - I'm still digesting it . . .

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Primary Key constraint has a mind of its own...!!

    Originally posted by Horace

    I now disable and then enable the constraint...
    Code:
    ALTER TABLE HORACE DISABLE CONSTRAINT PK_HORACE;
    ALTER TABLE HORACE ENABLE CONSTRAINT PK_HORACE;

    At this point you have dropped the index and re-created it. It then takes on the default attributes of the tablespace.
    Jeff Hunter

  8. #8
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Dapi, your a genius!!

    Alter table HORACE disable constraint PK_HORACE KEEP INDEX;

    Solves my problem.

    Thankyou very much.........

  9. #9
    Join Date
    Jan 2001
    Posts
    3,134
    Ya can't fo wrong with a "genius" and "super genius" helping you.
    I remember when this place was cool.

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Are you chinese?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width