Forum Stats

  • 3,855,376 Users
  • 2,264,500 Discussions
  • 7,905,979 Comments

Discussions

Bind Peeking only for "known" columns?

2»

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    I've just noticed you created the test index as unique:

      create  unique index cat_test on catentry(partnumber, member_id,1)


    Presumably because the current index is unique; but a unique index allows multiple entries where all columns are null - which means this new index could raise a "uniqueness" data-entry error where the old index doesn't. To be safe you need this index to be non-unique.


    If you want to drop the other index (if the columns are in the same order it's redundant) you could still create this index as a non-unique index then create a unique constraint based on just the first two columns.


    Regards

    Jonathan Lewis


  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge

    Jonathan,

    Just one final question.

    I still don't understand the mechanism of why the optimizer does now use the Index when that extra literal column ( the 1 or the 0 ) is added.

    Can you elaborate on this?

    Thanks

    Frank-Jan

  • GS613
    GS613 Member Posts: 73 Bronze Badge

    Both columns in your index (partnumber and member_id) can be null.

    In order for your query to use an index the entry in the index needs to exist - if both columns are null then there will be no index entry.

    An index entry does not exist if ALL the fields in the index are null.

    In order to ensure that those records for nullable columns are included in the index a work-around is to add a literal to the index key - in this case a 0. Now one of the fields in the index will always have a value (0 is not null) and therefore will be indexed and can be accessed as such.

    Correct me if I am wrong, Jonathan!

  • GS613
    GS613 Member Posts: 73 Bronze Badge

    To add to my answer:

    there is a explanation in the following article - read from code listing 3.

    Ask Tom: On Constraints, Metadata, and Truth

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    Gary,

    No correction needed.

    (Though someone may eventually get the urge to point out that bitmap indexes and cluster indexes are allowed to hold entries for completely column sets)

    Regards

    Jonathan Lewis

    GS613
This discussion has been closed.