Topic
  • 7 replies
  • Latest Post - ‏2014-06-23T15:12:11Z by krmilligan
bjbbarker
bjbbarker
18 Posts

Pinned topic Unique index with a "where" condition.

‏2014-06-18T17:10:56Z |

Has anyone creating a unique index over a table and included a where condition?  The command does run and seems to work.  I just want to make sure there won't be any issues with it.  

example:

CREATE unique INDEX AWFILES.XTENTITY_u_W1
ON AWFILES.XTENTITY ( XTRNLTYPID ASC , "ID" ASC )   
where xtrnltypid = 22

 

  • krmilligan
    krmilligan
    450 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-18T20:53:36Z  

    Having a WHERE clause on the index definition involves the same process regardless if it's a unique or non-unique index.

  • B.Hauser
    B.Hauser
    282 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-19T08:56:55Z  

    As an aside, I'd suggest never create unique indexes (independent whether where conditions are included or not).

    Implement all unique keys as unique key constraints. Multiple unique key constraints / table are allowed.

    For accessing tables with native I/O you may add (not unique) indexes with the same key fields within the same or different sequence.

    In this way you'll never define accidently unique indexes, for keys that are not unique.

    Birgitta

  • krmilligan
    krmilligan
    450 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-19T19:37:37Z  
    • B.Hauser
    • ‏2014-06-19T08:56:55Z

    As an aside, I'd suggest never create unique indexes (independent whether where conditions are included or not).

    Implement all unique keys as unique key constraints. Multiple unique key constraints / table are allowed.

    For accessing tables with native I/O you may add (not unique) indexes with the same key fields within the same or different sequence.

    In this way you'll never define accidently unique indexes, for keys that are not unique.

    Birgitta

    If this index is being created for performance purposes, then your advice doesn't apply because a unique constraint definition can't include a WHERE clause.

  • B.Hauser
    B.Hauser
    282 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-20T04:31:39Z  

    If this index is being created for performance purposes, then your advice doesn't apply because a unique constraint definition can't include a WHERE clause.

    Kent,

    sure an unique key constraint cannot include where conditions.

    ... but if I define a unique key constraint and an index (without unique) but with the same key columns in the same sequence and where conditions, isn't the access path shared and the unique adopted?

    Or is an additional access path created with additional information from the where conditions?

    Birgitta

    Updated on 2014-06-20T04:38:56Z at 2014-06-20T04:38:56Z by B.Hauser
  • krmilligan
    krmilligan
    450 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-20T15:16:32Z  
    • B.Hauser
    • ‏2014-06-20T04:31:39Z

    Kent,

    sure an unique key constraint cannot include where conditions.

    ... but if I define a unique key constraint and an index (without unique) but with the same key columns in the same sequence and where conditions, isn't the access path shared and the unique adopted?

    Or is an additional access path created with additional information from the where conditions?

    Birgitta

    No, the access path used to enforce the unique constraint cannot be shared.  The index with WHERE condition creates a new access path so that the index only contains key values that meet the WHERE condition - this subset of key values is what provides the performance improvement to some queries. 

  • B.Hauser
    B.Hauser
    282 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-21T08:59:00Z  

    No, the access path used to enforce the unique constraint cannot be shared.  The index with WHERE condition creates a new access path so that the index only contains key values that meet the WHERE condition - this subset of key values is what provides the performance improvement to some queries. 

    Kent,

    Does this mean, an index with the where clause includes additional (key) information, for getting faster access to the data?

    If so, these additional key information within the access path, will prevent the access path to be shared with any other/existing access path with the same key fields in the same sequence?

    Consequently creating indexes with the same keys in the same sequence but different where conditions will cause separate access paths to be generated, that must be maintained with each insert, update or delete operation in the base table.

    Specifiying unique or not within the CREATE INDEX statement does not make any difference with regard to the performance?

    Inserts and updates will be checked against both the key constraints AND the unique indexes.

    Birgitta

  • krmilligan
    krmilligan
    450 Posts

    Re: Unique index with a "where" condition.

    ‏2014-06-23T15:12:11Z  
    • B.Hauser
    • ‏2014-06-21T08:59:00Z

    Kent,

    Does this mean, an index with the where clause includes additional (key) information, for getting faster access to the data?

    If so, these additional key information within the access path, will prevent the access path to be shared with any other/existing access path with the same key fields in the same sequence?

    Consequently creating indexes with the same keys in the same sequence but different where conditions will cause separate access paths to be generated, that must be maintained with each insert, update or delete operation in the base table.

    Specifiying unique or not within the CREATE INDEX statement does not make any difference with regard to the performance?

    Inserts and updates will be checked against both the key constraints AND the unique indexes.

    Birgitta

    No additional information is stored in the key, the index just contains a subset of the key values.  Specification of where clauses & expressions on key definitions changes the access path sharing rules.  In general, the index maintenance overhead is not a big concern.

    Not telling the optimizers that keys are unique can indirectly impact performance since it may result in a different query access plan being built.