2 Replies Latest reply: Jul 17, 2008 1:30 PM by 437765 RSS

    Indexing expressions with custom pl sql functions not working as expected.

    437765
      Hello Aravind:

      I post this in the forum instead in mail because I think is a funny behavior that can derive in two things: I'm doing something wrong, or is a little bug.

      1.- I have a multiple rules conditions in this way:

      ( V_SOME_CONSTANT = 'CREATE' AND MY_PACK.GET_SOMETHING(V_PARAM) IS NOT NULL AND MY_PACK.VALIDATE_SOMETHING(V_PARAM,'S1,S2,S4',V_YEAR) = 'FALSE' )

      2.- I have agregate the predicates of MY_PACK.GET_SOMETHING(V_PARAM) and MY_PACK.VALIDATE_SOMETHING(V_PARAM,'S1,S2,S4',V_YEAR) , to the expression filter as default index parameters and regenerate the indexes using:

      begin
      exfsys.dbms_rlmgr_utl.drop_expfil_indexes('CAR_RULE_CLASS');
      exfsys.dbms_rlmgr_utl.create_expfil_indexes('CAR_RULE_CLASS');

      end;
      /

      MY PROBLEM IS:

      A) when I use the MY_PACK.GET_SOMETHING(V_PARAM) in other rules in the form MY_PACK.GET_SOMETHING(V_PARAM) = 'SOMETHING' the record is created with this part as indexed, and the rest of the condition I'm able to see it in the EXF$SPARSEPRED column. The same for the other function call.

      B) When I use the MY_PACK.GET_SOMETHING(V_PARAM) with the IS NOT NULL or IS NULL operators, this is not "indexed" (as far as I understood) and all the conditions that include this form of using the function, are created exactly with no change in the EXF$SPARSEPRED column, Even when this same condition has another function indexed in other condition, in this particular combination the other function IS NOT INDEXED TOO.

      IN SHORT:

      COND. ----------------------------------- SPARSE PRED.

      funct1 = 'S' AND var = 'N' -----------var = 'N'

      funct2 = 'X' AND var = 'Y' -----------var = 'Y'

      funct1 IS NOT NULL and-------------funct1 IS NOT NULL and
      func2 = 'X'----------------------------------func2 = 'X'


      As you can see, everything is ok until I use the IS NOT NULL, in one of the functions.

      I' hope you can help me on this,

      thnks in advance.

      Alex.
        • 1. Re: Indexing expressions with custom pl sql functions not working as expect
          442199
          Alex,

          For the predicate groups that are indexed/stored, the exact operator types (as in equality, inequality, like etc) that are indexed are specified while assigning the default index parameters. In the following example, exf$indexoper is used to specify the list of indexed operators.
          BEGIN
            DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS('Car4Sale',
              exf$attribute_list (
                 exf$attribute (attr_name => 'HorsePower(Model, Year)',
                                attr_oper => exf$indexoper('=','<','>','>=','<='),
                                attr_indexed => 'FALSE')    --- stored predicate group
              )
            );
          END;
          /
          You can find more information about exf$indexoper at
          http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/t_expfilobj.htm#ARPLS153

          Could you confirm that you chose to index 'is null' and 'is not null' while assigning the default index parameters ? This information is available in OPERATOR_LIST column of the USER_EXPFIL_DEF_INDEX_PARAMS view.

          Hope this helps,
          -Aravind.
          • 2. Re: Indexing expressions with custom pl sql functions not working as expect
            437765
            Aravind:


            Thnks for your answer.

            I have checked the view, and the OPERATOR_LIST column has the "all" value.

            Is the same way I have created as the attr_oper parameter to all my index parameters.

            Do I have to explicitly indicate de NULL's operators?

            Thnks in advance Aravind.


            Alex.