Forum Stats

  • 3,769,696 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

Does adding a PK constraint wo a "using index" automatically create an index

rjsosi
rjsosi Member Posts: 221 Bronze Badge

Hi ,

We're using Oracle 12c.

We dropped and recreated a Primary Key (PK) Constraint. using the following:

alter table table_name add constraint constraint_name primary key (col1, col2, col3)
enable novalidate;

There was already a PK Index there from the previous PK constraint. It looks like it created an additional PK index.

Was that because we didn't specify "USING INDEX" in the ALTER statement?

So in other words does creating a PK Constraint automatically create a PK Index, even if a PK Index already exists, if you don't specify "USING INDEX" in the ALTER statement?

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,157 Blue Diamond
    edited Sep 28, 2021 8:08PM Accepted Answer

    Let's use the terms "old PK" and "new PK" for the PK constraints.


    EDIT:

    As SY points out below, my "theory" is wrong. Ignore!

    END EDIT


    My theory: When the "old PK" was created, an index was already available, and the old PK was created with the USING INDEX option. If a PK is created in this manner, when the PK constraint is dropped, the index is not dropped.

    Then when you created the new PK, without telling it to use the existing index (left behind when you dropped the old PK), the new PK constraint resulted in the creation of a new, system-generated index.

    If the old PK had been created without the USING INDEX option (instead allowing the system to create the index automatically), the index would have been dropped when the old PK constraint was dropped - that's what Mr. Kulash saw when he tried to re-create your problem.

    If my theory is correct, after you dropped the old PK, if you wanted the index to be dropped as well, you would have needed to do that explicitly. Or, if the new PK is on the same columns and in the same order as the old PK, you could again use the same index - but you would need to do that explicitly, with USING INDEX.

    One more thing - you talk about "PK index". There is no such thing. PK is a constraint, which consists of two different things: NOT NULL and UNIQUE. The NOT NULL part is enforced independently of an index. What the PK uses is a unique index (or, in fact, it may even use a non-unique index, which may be useful in some cases - perhaps even in yours); but there is no such thing as a "PK index".

    rjsosi

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    Hi, @rjsosi

    I'm unable to re-create the problem. (I'm using version 18.4, but I don't think that's the reason). When I add a constriant like the one you posted, an index is automatically created. If I drop the constraint, the index is automatically dropped. Can you post a complete set of commands (CREATE TABLE ..., ALTER TABLE ..., SELECT ... FROM user_indexes ...) that show exactly what you're doing?

     does creating a PK Constraint automatically create a PK Index

    I'm not familiar with the term "PK index". What exactly does it mean? Is it used in the Oracle documentation?

  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge

    Hi Frank,

    PK stands for Primary Key PK. Here is the actual statement:

    ALTER TABLE SLDPROD.SLD_LOAN_CLTRL_EAD_MO
     DISABLE CONSTRAINT PK_SLD_LOAN_CLTRL_EAD_MP;
      
    ALTER TABLE SLDPROD.SLD_LOAN_CLTRL_EAD_MO
     DROP CONSTRAINT PK_SLD_LOAN_CLTRL_EAD_MO;
    
    ALTER TABLE SLDPROD.SLD_LOAN_CLTRL_EAD_MO ADD (
     CONSTRAINT PK_SLD_LOAN_CLTRL_EAD_MO
     PRIMARY KEY
     (D_MO, I_LOAN_ID, I_CLTRL_ID, I_ISS_ID_CLTRL, I_ISS_TRI_PTY, I_BSE_CLTRL_ID, I_EXCLV_ID
     ENABLE VALIDATE);
    

    The reason I'm asking this is because before we created this new Constraint there was only 1 PK index to go with the existing PK Constraint.

    When we ran this ALTER statement, on a 7 billion row partitioned table, it ran for over 6 hours. Eventually the job had to be cancelled. However the constraint got created anyway. Oracle must have let the last statement finish before stopping the job. (They said the process did run in the background through the night after cancellation).

    The regular build of that constraint historically took about 04:30 hh:mm.

    Meanwhile a new PK Index showed up. So now we have two PK Constraints.

    My guess is because we didn't specify USING INDEX in the ALTER statement Oracle must have created a new PK Index to go with the new constraint.

    This also explains why an ALTER statement, that should have taken only 4.5 hours, took almost a day.

    This is because is was building a new PK Index to go along with it's new PK Constraint.

    This is my theory, I'm just asking that it be verified.

    Thanks!

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,670 Silver Crown

    When you drop a PK constraint, if it has an index that was created with the constraint, it will drop it. But if the index was created before and you create the constraint, the index will be kept when the constraint is dropped.

    There are no such a thing like a PK index, there is a constraint and an index that supports it. You may read this to get a clear idea:


    If you have an index that includes all the columns in the PK, you can use it instead of letting the PK create a new one, you do that with the USING INDEX clause

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    Hi, @rjsosi

    PK stands for Primary Key PK.

    The question was: What exactly does "PK index" mean? How does it differ from other indexes?

    Here is the actual statement:

    That looks like some of the actual statements. There must have been a CREATE TABLE statement first, and perhaps a separate ALTER TABLE statement to add the constraint. What you posted is still not enough to re-create the problem.

    Meanwhile a new PK Index showed up. So now we have two PK Constraints.

    No kidding! When you query ALL_CONSTRAINTS, you see two separate constraints on the same table, both with constraint_type='P'?

  • mathguy
    mathguy Member Posts: 10,157 Blue Diamond
    edited Sep 28, 2021 8:08PM Accepted Answer

    Let's use the terms "old PK" and "new PK" for the PK constraints.


    EDIT:

    As SY points out below, my "theory" is wrong. Ignore!

    END EDIT


    My theory: When the "old PK" was created, an index was already available, and the old PK was created with the USING INDEX option. If a PK is created in this manner, when the PK constraint is dropped, the index is not dropped.

    Then when you created the new PK, without telling it to use the existing index (left behind when you dropped the old PK), the new PK constraint resulted in the creation of a new, system-generated index.

    If the old PK had been created without the USING INDEX option (instead allowing the system to create the index automatically), the index would have been dropped when the old PK constraint was dropped - that's what Mr. Kulash saw when he tried to re-create your problem.

    If my theory is correct, after you dropped the old PK, if you wanted the index to be dropped as well, you would have needed to do that explicitly. Or, if the new PK is on the same columns and in the same order as the old PK, you could again use the same index - but you would need to do that explicitly, with USING INDEX.

    One more thing - you talk about "PK index". There is no such thing. PK is a constraint, which consists of two different things: NOT NULL and UNIQUE. The NOT NULL part is enforced independently of an index. What the PK uses is a unique index (or, in fact, it may even use a non-unique index, which may be useful in some cases - perhaps even in yours); but there is no such thing as a "PK index".

    rjsosi
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,921 Red Diamond

    There was already a PK Index there from the previous PK constraint. It looks like it created an additional PK index.

    Not possible. Oracle, unlike SQL Server doesn't allow more than one index for same set of index expressions (provided in same order).

    SY.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,670 Silver Crown

    If the "old" index have all the same columns in the same order, then Oracle would not allow a new one to be created with the same columns, order.

    But if one column is in a different "place" or there is one additional column in the original index, then you can end with 2 indexes, the first the "old" one created before the first PK was defined, and the second one the one automatically created by adding the PK without using the USE INDEX option...

  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge
    edited Oct 1, 2021 9:27PM

    OK so , for Frank and others here is the list of indexes in the DB after the addition of the new PK constraint. First of all that was a typo in the comment I made to Frank. I meant to say two 'PK indexes' so that was my bad. This is what I was talking about.

    As you can see they're both there at the same time. That's why I proposed the theory that one was there before the addition of the PK Constraint and the newest one, PK_SLD_LOAN_CLTRL_EAD_MO, is there now.

    I do apologize for not being clearer on my nomenclature. When I wrote PK Index, I just meant the index that gets create along with the Primary Constraint or the index that covers that same columns as the Primary Constraint.

    Then when you created the new PK, without telling it to use the existing index (left behind when you dropped the old PK), the new PK constraint resulted in the creation of a new, system-generated index.

    Mathguy's answer wins. That confirms my theory. Although this is nobody else's fault. Because I fumbled with the terminology it did create confusion. I'll try to proof read my stuff more in the future. Thank you all.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,670 Silver Crown

    Yes, but as you can see the list of columns is completely different, that is why at the moment you created the "new" primary it created a new index (as it always do unless you tell to use an existing one) and that did not generate an error, since the "first" index is on a different set of columns

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,921 Red Diamond
    edited Oct 2, 2021 12:27PM

    Don't you see second index has two extra columns? So obviously new index must be created if new PK has more columns. Again, unless you explicitly specify create index in PK USING INDEX clause Oracle will check if there is covering index (not matching - covering) regardless if such covering index is unique or not. And you were already explained if Oracle finds such existing index it marks PK is using pre-existing index and such index will not be dropped when PK is dropped. So from what I understand you had pre-existing unique index on columns D_MO, I_LOAN_ID, I_CTRL_ID, I_ISS_TRI_PTY so when you created PK on these columns Oracle found there is pre-existing covering index and used it as PK supporting index. Then you dropped PK but because PK supporting index was pre-existing index that index wasn't dropped along with PK. And after dropping PK you created new PK with two additional columns. Now that pre-existing index isn't covering index anymore so Oracle is forced to crete PK supporting index on all PK columns. Just to illustrate:

    SQL> create table tbl(c1 number,c2 number,c3 number, c4 number);
    
    Table created.
    
    SQL> create index tbl_idx1 on tbl(c1,c2);
    
    Index created.
    
    SQL> alter table tbl add constraint tbl_pk primary key(c1,c2);
    
    Table altered.
    
    SQL> column index_name format a30
    SQL> select index_name,uniqueness from user_indexes where table_name = 'TBL';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    TBL_IDX1                       NONUNIQUE
    
    SQL>
    

    As you can see oracle found covering index and used it as PK supporting index even though that index isn't unique index. Now we drop PK:

    SQL> alter table tbl drop primary key;
    
    Table altered.
    
    SQL> select index_name,uniqueness from user_indexes where table_name = 'TBL';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    TBL_IDX1                       NONUNIQUE
    
    SQL>
    

    As you can see Oracle realized PK supporting index is pre-existing index and doesn't drop it when we drop PK. Now we add PK but with extra column c3:

    SQL> alter table tbl add constraint tbl_pk primary key(c1,c2,c3);
    
    Table altered.
    
    SQL> select index_name,uniqueness from user_indexes where table_name = 'TBL';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    TBL_IDX1                       NONUNIQUE
    TBL_PK                         UNIQUE
    
    SQL>
    

    As you can see Oracle didn't find covering index so it created one and now table has two indexes. And about covering index - index is PK covering index if index leading column subset matches (in any order) PK column list. I will drop PK, create index on columnc C1,C2,C3,C4 and create PK on C2,C3,C1:

    SQL> alter table tbl drop primary key;
    
    Table altered.
    
    SQL> create index tbl_idx2 on tbl(c1,c2,c3,c4);
    
    Index created.
    
    SQL> alter table tbl add constraint tbl_pk primary key(c2,c3,c1);
    
    Table altered.
    
    SQL> select index_name,uniqueness from user_indexes where table_name = 'TBL';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    TBL_IDX1                       NONUNIQUE
    TBL_IDX2                       NONUNIQUE
    
    SQL>
    

    As you can see Oracle realizes there is a index TBL_IDX2 on C1,C2,C3,C4 and PK column list C2,C3,C1 is leading subset (regardless of column order) of that index so index TBL_IDX2 is PK covering index and there is no need to create new index to support PK. But if PK column list isn't leading subset then we have no covering index and new index will be created:

    SQL> alter table tbl drop primary key;
    
    Table altered.
    
    SQL> alter table tbl add constraint tbl_pk primary key(c2,c3);
    
    Table altered.
    
    SQL> select index_name,uniqueness from user_indexes where table_name = 'TBL';
    
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    TBL_IDX1                       NONUNIQUE
    TBL_IDX2                       NONUNIQUE
    TBL_PK                         UNIQUE
    
    SQL>
    

    SY.