3 Replies Latest reply on May 26, 2014 6:24 AM by Junger He-Oracle

    OEP Local cache multiple key-properties

    mnoscars

      Hello All,

       

       

      I have a new question about OEP local cache definition.

       

       

      I try to load several tables into OEP local caches for a demo.

       

       

      I understand that the key specified in the key-properties attribute should be the same key as the one used for the cache HashMap, and that each column specified in the where clause of the CQL query should also be specified in the key-properties attributes.

       

      But how should I define the keys in the java cache loader if there are several keys?

       

       

      Here is an example :

       

       

      I have 2 tables and 1 relation.

       

      TAB1 - REL1 - TAB2

       

       

      REL1 contains 2 foreign keys. 1 to TAB1 and 1 to TAB2.

       

       

      I want to have that CQL query working :

       

      select *

      from c[NOW], tab1, rel1, tab2

      where c.key = tab1.key

      and tab1.idT1 = rel1.idT1

      and tab2.idT2 = rel1.idT2

       

       

      So I try to define the caches (and loader)

       

       

      <wlevs:cache id="Cachetab1" key-properties="key"

              value-type="EventTab1">

      ...

      <wlevs:cache id="Cacherel1" key-properties="idT1,idT2"

              value-type="EventRel1">

      ...

       

      But here is the problem, the query does not work here because I do not know how to use idT1 AND idT2 as keys in the Loader code.

       

      I have tried to use a hashmap or array containing both keys, but that does not seem to work.

       

       

      Any idea?

       

       

      Thanks in advance.

       

       

      Max

        • 1. Re: OEP Local cache multiple key-properties
          Junger He-Oracle

          Hi, Max,

           

          Yeah, it seems this is not well documented. I have two suggestions: 1) you can inspect the Map(cache) object to see how it construct the key. 2) use a key class instead.

           

          Thanks

          Junger

          • 2. Re: OEP Local cache multiple key-properties
            mnoscars

            Hello Junger,

             

            Thank you for your quick answer. Sure, a clear example in the documentation would be really useful there.

             

            What do you mean by "inspect the Map(cache)" ? can you explain more? Do you have an example?

             

            For the second suggestion, I have already tried that but I keep getting the same "predicate require full scan" error and as far as I understand, the key class is only used to specify the key columns, exactly as it is for the key-properties attribute. This does not solve the problem of how it is handled in the Java code. Or did I miss something?

             

            Also, I am not sure that my query is really ok :

             

            select *

            from c[NOW], tab1, rel1, tab2

            where c.key = tab1.key

            and tab1.idT1 = rel1.idT1

            and tab2.idT2 = rel1.idT2

             

            I have seen in the documentation that the order of the where clause matters, so tried everything but still have the same error.

             

            I guess that normally, the exact query should be :

             

            select *

            from c[NOW], tab1, rel1, tab2

            where c.key = tab1.key

            and tab1.idT1 = rel1.idT1

            and rel1.idT2 = tab2.idT2

             

            But the error is still the same. Moreover, I first thought that only my rel1 cache needed a composite key, but I guess now that the tab1 cache also need a composite key knowing that the used key to join the channel is not the same as the key to join the cache rel1. But the datatype of tab1.key and tab1.idT1 is not same (String - long). Could that be a problem?

             

            Thank you for your help.

             

            Max.

            • 3. Re: OEP Local cache multiple key-properties
              Junger He-Oracle

              Max,

               

              I missed your update. Sorry for late reply. I suppose you were using OEP 11.1.1.7.0.

              Here are some instructions on joining the cache:

              1) There should be only one stream in the statement to join with the cache

              2) The stream needs to be bound with NOW window

              3) Only one property can be used in the joining( a limitation on OEP prior to 12c release)

              4) The property types in stream and cache should be compatible. So you can't do s.strField = c.intField.

              5) Only equal operator is allowed.

               

              Anyway, in 12c, we have done a great enhancement to release the limitation of 3),5).

               

              BTW, I meant checking the key's data type and content by "inspect the cache".

               

              Junger