Forum Stats

  • 3,728,127 Users
  • 2,245,554 Discussions
  • 7,853,348 Comments

Discussions

Hash Indexes and NULL Comparisons

3868714
3868714 Member Posts: 3
edited January 2019 in TimesTen In-Memory Database

Hi,

We are currently introducing more indexes into our databases as instructed by the index advisor, but have noticed a change in behaviour when comparing null values.

Prior to the index the following query would return 0 results:

select * from network_set where priority3_network_element not in (select id from network_element);0 rows found.

(I know this should be enforced by a foreign key, but we currently have incompatible data types which we're trying to resolve first)

Adding the following index results in a change of behaviour where the above query will then return any entries where priority3_network_element is null - adding a is not null check first achieves the correct result:

create hash index HASH_NETWORK_ELEMENT_ID on NETWORK_ELEMENT(ID);select * from network_set where priority3_network_element not in (select id from network_element);122 rows found....select * from network_set where priority3_network_element is not null and priority3_network_element not in (select id from network_element);0 rows found.

What is the explanation for the change in query behaviour here? Is there any other potential cases that we should be aware of that also changes the behaviour of queries when adding indexes?

Many Thanks

Curtis

Best Answer

  • ChrisJenkins-Oracle
    ChrisJenkins-Oracle Member Posts: 3,402 Employee
    edited January 2019 Accepted Answer

    The correct result set is of course the one with 0 rows found. Rows where PRIORITY3_NETWORK_ELEMENT is NULl should not appear in the result set.

    This is a serious bug which is still present in the most recent patch (11.2.2.8.35) but does not reproduce in the new 18.1 release.

    I will raise a bug on this to get it addressed in 11.2.2 but in the meantime please use the 'is not null' workaround.

    Sorry for the inconvenience.

    Chris

Answers

  • ChrisJenkins-Oracle
    ChrisJenkins-Oracle Member Posts: 3,402 Employee
    edited January 2019

    This looks like a bug to me. Can you please provide:

    1.   The definition of the table NETWORK_ELEMENT.

    2.  The definition of the column NETWORK_SET.PRIORITY3_NETWORK_ELEMENT.

    3.  The exact version of TimesTen being used (the full output of the ttVersion command).

    Thanks,

    Chris

  • 3868714
    3868714 Member Posts: 3
    edited January 2019

    Hi Chris.

    Here are the details you asked for:

    [[email protected] ~]$ ttVersionTimesTen Release 11.2.2.8.22 (64 bit Linux/x86_64) (tt1122:53396) 2017-06-20T21:06:23Z  Instance admin: timesten  Instance home directory: /opt/timesten/TimesTen/tt1122  Group owner: timesten  Daemon home directory: /opt/timesten/data  PL/SQL enabled.

    Please note that PRIORITY3_NETWORK_ELEMENT maps to PRIORITY3_ASBC in the below data model:

    Table TEST.NETWORK_ELEMENT:  Columns:   *ID                              NUMBER (32) NOT NULL    OSS_ID                          VARCHAR2 (32) INLINE NOT NULL    TYPE                            NUMBER (16) NOT NULL    NAME                            VARCHAR2 (32) INLINE NOT NULL    IP                              VARCHAR2 (32) INLINE    PORT                            VARCHAR2 (8) INLINE    LAST_UPDATED_BY                 VARCHAR2 (32) INLINE    LAST_UPDATED_DTIME              DATETable TEST.NETWORK_SET:  Columns:   *ID                              NUMBER (8) NOT NULL    OSS_ID                          VARCHAR2 (32) INLINE NOT NULL    PRIORITY1_SLEE                  NUMBER (8) NOT NULL    PRIORITY2_SLEE                  NUMBER (8)    PRIORITY1_ASBC                  NUMBER (8) NOT NULL    PRIORITY2_ASBC                  NUMBER (8)    PRIORITY3_ASBC                  NUMBER (8)    PRIORITY4_ASBC                  NUMBER (8)    LAST_UPDATED_BY                 VARCHAR2 (32) INLINE    LAST_UPDATED_DTIME              DATE

    The reason why we added this query is that PRIORITYX_YYYY should be a foreign key mapped to NETWORK_ELEMENT.ID but the incompatible data types (NUMBER(8) vs NUMBER(32)) prevent the foreign key from being created. We plan on fixing this but there are a few constraints which prevent us from doing this immediately.

    Coming back to the original queries, which result set is the expected result?

    Curtis

  • ChrisJenkins-Oracle
    ChrisJenkins-Oracle Member Posts: 3,402 Employee
    edited January 2019 Accepted Answer

    The correct result set is of course the one with 0 rows found. Rows where PRIORITY3_NETWORK_ELEMENT is NULl should not appear in the result set.

    This is a serious bug which is still present in the most recent patch (11.2.2.8.35) but does not reproduce in the new 18.1 release.

    I will raise a bug on this to get it addressed in 11.2.2 but in the meantime please use the 'is not null' workaround.

    Sorry for the inconvenience.

    Chris

  • 3868714
    3868714 Member Posts: 3
    edited January 2019

    Chris,

    Thank you for getting back to me so quickly and confirming the correct result. I will keep an eye out for any future patch releases.

    Curtis

Sign In or Register to comment.