4 Replies Latest reply on Jan 17, 2019 1:59 PM by 3868714

    Hash Indexes and NULL Comparisons

    3868714

      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

        • 1. Re: Hash Indexes and NULL Comparisons
          ChrisJenkins-Oracle

          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

          • 2. Re: Hash Indexes and NULL Comparisons
            3868714

            Hi Chris.

             

            Here are the details you asked for:

            [timesten@noas-timesten-vm ~]$ ttVersion

            TimesTen 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              DATE
            
            Table 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

            • 3. Re: Hash Indexes and NULL Comparisons
              ChrisJenkins-Oracle

              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

               

              • 4. Re: Hash Indexes and NULL Comparisons
                3868714

                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