2 Replies Latest reply on Dec 2, 2019 1:54 PM by 2725875

    JSON index not used when select uses IN clause

    2725875

      if I have JSON column (with check constraint) and an index, then the index is ONLY used if I use the '=' operator with multiple values

       

      So a query like this does full scans (query is not perfect - just sample to show the problem)

      select jt1.duns as duns_number, jt1.countrycode

        from TEST_JSON b,

             JSON_TABLE(b.data, '$.organization'

                COLUMNS (

                  duns number,

                  countrycode PATH primaryAddress.addressCountry.isoAlpha2Code

                )

             ) jt1                

      where  b.data.organization.duns in ('947563235', '951123421' )

       

      But this uses the index and performance is hundreds of times faster!!

      select jt1.duns as duns_number, jt1.countrycode

        from TEST_JSON b,

             JSON_TABLE(b.data, '$.organization'

                COLUMNS (

                  duns number,

                  countrycode PATH primaryAddress.addressCountry.isoAlpha2Code

                )

             ) jt1                

      where  ( b.data.organization.duns = '947563235' or b.data.organization.duns = '951123421' )