Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: PK vs PK and unique index

    Our vendor has been using the following when creating a PK on a table:

    ALTER TABLE OPORTAL.OR_FEATURES ADD (
    CONSTRAINT OR_FEATURES_PK PRIMARY KEY (FEATURE_ID)
    USING INDEX
    TABLESPACE OP_INDEX);

    When I look at tables created by another vendor, I see the following (in script generated by TOAD):

    CREATE UNIQUE INDEX OPORTAL.OR_FEATURES_PK ON OPORTAL.OR_FEATURES
    (FEATURE_ID)
    LOGGING
    TABLESPACE OP_INDEX
    NOPARALLEL;

    ALTER TABLE OPORTAL.OR_FEATURES ADD (
    CONSTRAINT OR_FEATURES_PK PRIMARY KEY (FEATURE_ID)
    USING INDEX
    TABLESPACE OP_INDEX);


    What is the difference? Is there a difference?
    Thanks
    Chuck

  2. #2
    Join Date
    Oct 2004
    Posts
    145
    It is different method of creating primary key (index for primary key).

    You have have noticed similar type of item for foreign key as well. You can embedded the foreign key as part of create table script or seperate it out as alter table command.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So, is the same PK created in one step, vs 2 steps? The single command in the first command is identical to the second command in the next example. The only thing that differs is the 'pre' creation of the UNIQUE index. Is that not necessary?

    -Chuck

  4. #4
    Join Date
    Oct 2004
    Posts
    145
    Yes, in your example.

    You can assign different name to the unique index when done in two steps.

    I also just noticed, when you look up the dependencies, primary key index is not shown but name index is show as dependency to the table.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can assign different name to the unique index when done in two steps.
    You can also do it in one step, by including the CREATE INDEX in the ALTER TABLE (or directly in the CREATE TABLE if you add the PK there), so as to specify which index is used to enforce the PK :

    Code:
    ALTER TABLE OPORTAL.OR_FEATURES ADD 
    (
       CONSTRAINT OR_FEATURES_PK PRIMARY KEY (FEATURE_ID)
       USING INDEX
       (
          CREATE UNIQUE INDEX OPORTAL.OR_FEATURES_PK ON OPORTAL.OR_FEATURES(FEATURE_ID)
          LOGGING
          TABLESPACE OP_INDEX
          NOPARALLEL
       )
    );
    BTW, it surprises me that the second solution (in two steps) makes the PK use the index created in the first step... It might be since the index has the same name as the constraint, but do you confirm ? Normally, you should have specified the index name after USING INDEX, and in that case, why still the TABLESPACE clause ? AFAIK, it is just there to specify that the Unique index to be created should be in that tablespace, and the index would have been created in the first step... Are you sure 2nd step doesn't create a 2nd index ? Anyway, in two steps, I would have done so :
    Code:
    CREATE UNIQUE INDEX OPORTAL.OR_FEATURES_PK ON OPORTAL.OR_FEATURES
    (FEATURE_ID)
    LOGGING
    TABLESPACE OP_INDEX
    NOPARALLEL;
    
    ALTER TABLE OPORTAL.OR_FEATURES ADD 
    (
       CONSTRAINT OR_FEATURES_PK PRIMARY KEY (FEATURE_ID)
       USING INDEX OPORTAL.OR_FEATURES_PK
    );
    Regards,

    RBARAER

    PS : I attached the USING INDEX clause from the Oracle doc, HTH.
    Attached Thumbnails Attached Thumbnails Using_Index_Clause.JPG  
    Last edited by RBARAER; 12-09-04 at 05:59.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I have not verified the 2-step process. It was the output I received from TOAD when I generated a table script. Since it differed from what I have been used to seeing, I thought I'd ask.

    By the way RBARAER, how you you get a portion of your posting inset in a scrollable window like that?

    -Chuck

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    One advantage of breaking the PK definition into separate steps is that if you create the index first and then use it for the PK constraint, you can later disable and re-enable the constraint without having to drop and recreate the index. This could potentially be useful in a batch load environment.

    Inline definition:
    (Note how the PK column automatically becomes NOT NULL. Also a key gets a default name when I don't specify one.)
    Code:
    SQL> CREATE TABLE bananas
      2  ( id  INTEGER CONSTRAINT banana_pk PRIMARY KEY
      3  , name VARCHAR2(30) NOT NULL UNIQUE );
    
    Table created.
    
    SQL> desc bananas
     Name                                Null?    Type
     ----------------------------------- -------- ------------------------
     ID                                  NOT NULL NUMBER(38)
     NAME                                NOT NULL VARCHAR2(30)
    
    
    SQL> SELECT index_name, uniqueness FROM user_indexes 
      2  WHERE table_name = 'BANANAS';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    BANANA_PK                      UNIQUE
    SYS_C00109763                  UNIQUE
    
    2 rows selected.
    
    
    SQL> SELECT constraint_name, constraint_type, index_name
      2  FROM   user_constraints
      3  WHERE  table_name = 'BANANAS';
    
    CONSTRAINT_NAME                C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C00109761                  C
    BANANA_PK                      P BANANA_PK
    SYS_C00109763                  U SYS_C00109763
    
    3 rows selected.
    
    SQL> drop table bananas;
    
    Table dropped.
    Separate definitions:
    If an index exists (doesn't have to be unique), Oracle can use it for a PK or UK constraint.
    Code:
    SQL> CREATE TABLE bananas
      2  ( id   INTEGER
      3  , name VARCHAR2(30) );
    
    Table created.
    
    SQL> CREATE INDEX banana_pk ON bananas (id);
    
    Index created.
    
    SQL> CREATE INDEX banana_uk ON bananas (name);
    
    Index created.
    
    SQL> ALTER TABLE bananas ADD CONSTRAINT banana_pk PRIMARY KEY (id);
    
    Table altered.
    
    SQL> ALTER TABLE bananas ADD CONSTRAINT banana_uk_testit UNIQUE (name);
    
    Table altered.
    
    
    SQL> SELECT index_name, uniqueness FROM user_indexes
      2  WHERE  table_name = 'BANANAS';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    BANANA_PK                      NONUNIQUE
    BANANA_UK                      NONUNIQUE
    
    2 rows selected.
    
    
    SQL> SELECT constraint_name, constraint_type, index_name FROM user_constraints
      2  WHERE  table_name = 'BANANAS';
    
    CONSTRAINT_NAME                C INDEX_NAME
    ------------------------------ - ------------------------------
    BANANA_PK                      P BANANA_PK
    BANANA_UK_TESTIT               U BANANA_UK
    
    2 rows selected.
    
    
    SQL> ALTER TABLE bananas DISABLE CONSTRAINT banana_pk;
    
    Table altered.
    
    
    SQL> SELECT index_name, uniqueness, status FROM user_indexes
      2  WHERE  table_name = 'BANANAS';
    
    INDEX_NAME                     UNIQUENES STATUS
    ------------------------------ --------- --------
    BANANA_PK                      NONUNIQUE VALID
    BANANA_UK                      NONUNIQUE VALID
    
    2 rows selected.
    
    SQL> SELECT constraint_name, constraint_type, status, index_name
      2  FROM   user_constraints
      3  WHERE  table_name = 'BANANAS';
    
    CONSTRAINT_NAME                C STATUS   INDEX_NAME
    ------------------------------ - -------- ------------------------------
    BANANA_PK                      P DISABLED
    BANANA_UK_TESTIT               U ENABLED  BANANA_UK
    
    2 rows selected.
    Last edited by WilliamR; 12-09-04 at 13:14.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    By scrollable windows, you mean the "Code" parts ?

    During the post creation, simply use the '#' icon, or add CODE within [] and /CODE within [] tags respectively at the beginning and the end of your text (I can't write them here because they're always interpreted, even when I disable "Automatically parse links in text").

    Regards,

    RBARAER
    Last edited by RBARAER; 12-09-04 at 13:31.

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello WilliamR,

    One advantage of breaking the PK definition into separate steps is that if you create the index first and then use it for the PK constraint, you can later disable and re-enable the constraint without having to drop and recreate the index.
    Thanks, I didn't know that ! However, it's not completely true, depending on what you call "One step". It's true when you do it that way :
    Code:
    CREATE TABLE bananas
    ( 
       id  INTEGER CONSTRAINT banana_pk PRIMARY KEY,
       name VARCHAR2(30)
    );
    But not if you do it that way :
    Code:
    CREATE TABLE bananas
    ( 
       id  INTEGER CONSTRAINT banana_pk 
          PRIMARY KEY USING INDEX 
          (
             CREATE INDEX index_bananas_pk ON bananas(id)
          ),
       name VARCHAR2(30)
    );
    Or the one I prefer :
    Code:
    CREATE TABLE bananas
    ( 
       id  INTEGER,
       name VARCHAR2(30),
       CONSTRAINT banana_pk 
          PRIMARY KEY(id) USING INDEX 
          (
             CREATE INDEX index_bananas_pk ON bananas(id)
          )
    );
    This could potentially be useful in a batch load environment.
    Not necessarilly because the batch load would be faster if you drop the indexes before the batch, and re-create them afterwards. IMO, it depends if you still need the indexes for querying data during the batch.

    Regards,

    RBARAER

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I didn't know about the inline CREATE statement. Cool!

    I would normally disable constraints and mark indexes unusable prior to a large batch load, rather than dropping them. If the load procedure recreates constraints/indexes from scratch at runtime, then I have a maintenance problem next time I need to amend the definitions in my master script or case tool. (The procedure becomes in effect the owner of those definitions.)

    Disabling a unique/primary key in a way that leaves the index intact allows direct path inserts (INSERT /*+ APPEND */ reverts to regular inserts when constraints are present). That's not as efficient as marking indexes unusable before and rebuilding after the load, but it could still be useful in the situation where for some reason you need the index during the load.

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK WilliamR,

    You were right, it's not always useful, but "could potentially" be. In your case it is.

    Regards,

    RBARAER

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    I thought that the UNIQUE & NOT NULL constraints and the INDEX were created in all cases, and the issue here was differing routes to create them and adding a non system-generated name if you wanted.

    If that's the case, then you should always be able to disable any of the 3, correct?

    -Chuck

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    For the NOT NULL constraint, it is just a question of constraint name, since there is no underlying index. However, for PRIMARY KEY constraints, it is not only a matter of name, but also a matter of :

    - What the underlying index of the PK looks like (hence the USING INDEX clause)

    - Will it be dropped or not when the PK is disabled ? (BTW, I wasn't aware of this possibility, thanks WilliamR)

    - Will it be dropped when the constraint is dropped ? (this is the case when using only PRIMARY KEY, but not when using the two other solutions with USING INDEX)

    IMO, the USING INDEX clause is very important because it lets you specify the EXACT index you want for the PK (name, tablespace, storage parameters...), which, apart from naming it, might be interesting for performance tuning if you often query this table using the PK index. In that case, this index is not only here to enforce the PK, but also to optimize a query, so you'd better build it accordingly.

    Regards,

    RBARAER

Posting Permissions

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