This discussion is archived
1 2 Previous Next 17 Replies Latest reply: May 3, 2013 11:26 PM by Tubby Go to original post RSS
  • 15. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    Sorry Tubby, but unfortunately this would not work, because in the case when we search for the 1,2 combo, it should return the element ID that they share, namely 1.

    Thomas
  • 16. Re: Can Oracle convert literals to bind variables?
    rp0428 Guru
    Currently Being Moderated
    >
    So for instance if I passed an array (Attribute ID Tab) containing ELEMENT_ATTRIBUTE_ID list 6 and 7 I would get 4 as the answer.

    Hope this helps,
    >
    Yes - that helps.

    Here is a solution that MIGHT be a good fit for your use case. It can provide superior performance for appropriate use cases. An appropriate use case is when that master set of 'attributes' (in your xref) table is highly static; that is does NOT have a high volume of DML.

    The reason is that this solution uses a bit-map approach whereby the list of attributes for a given element are bitmapped. I will provide the basic DDL based on yours and show how it works. Then if you think your use case is suitable and have any questions just ask.

    If you don't understand binary you will have trouble following this but bear with me.
    -- same ELEMENT table as yours
    CREATE TABLE ELEMENT(ELEMENT_ID NUMBER NOT NULL);
    
    -- modified ATTRIBUTE table - ALL attributes are stored in a NUMBER column as bitmapped values instead of a separate record for each --attribute.
    CREATE TABLE ELEMENT_ATTRIBUTES(Element_Id NUMBER NOT NULL, Attributes NUMBER NOT NULL);
    
    -- just a modified PK
    ALTER TABLE ELEMENT_ATTRIBUTES ADD CONSTRAINT ELEMENT_ATTR_PK PRIMARY KEY(Element_Id, Attributes);
    
    -- same as yours
    ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY(Element_Id);
    
    -- same data as yours
    INSERT INTO ELEMENT VALUES(1);
    INSERT INTO ELEMENT VALUES(2);
    INSERT INTO ELEMENT VALUES(3);
    INSERT INTO ELEMENT VALUES(4);
    
    -- here is where the fun starts - create a NUMBER that really represents a bit string. If a bit is set it means that attribute is set.
    -- this assumes your attributes are ALWAYS numbered sequentially from 1 to n (the number of bits for a NUMBER is limited to the equivalent of a 38 digit decimal number)
    --
    -- your example data uses attributes 1 and 2 so we want to set bits 0 and 1
    -- bits 1 & 2 (2^0 + 2^1 = 1 + 2 = 3) - the value of '3' means that both bits 0 and 1 are set: 0b00000011 in binary
    INSERT INTO ELEMENT_ATTRIBUTES VALUES(1, 3);
    
    -- your example data uses attribute 1 - so set only bit 0 (2 ^ 0 = 1): 0b00000001
    INSERT INTO ELEMENT_ATTRIBUTES VALUES(2, 1);
    
    -- your example uses attributes 1, 3, 4, 5 so we want those bits set: binary value is 0b00011101 = 29
    INSERT INTO ELEMENT_ATTRIBUTES VALUES(3, 29); 
    
    -- your example data uses attributes 6 & 7 so set bits 5 & 6: binary value is 0b01100000 = 96 
    INSERT INTO ELEMENT_ATTRIBUTES VALUES(4, 96);
    Now to query the data
    >
    -- So for instance if I passed an array (Attribute ID Tab) containing ELEMENT_ATTRIBUTE_ID list 6 and 7 I would get 4 as the answer.
    >
    So we want to convert 6 and 7 to a NUMBER value. Binary exponents and bits start at 0 so
    2^5 + 2^6 = 32 + 64 = 96 so our query mask is 96.
    Now we can query the data
    SELECT Element_Id, Attributes, bitand(attributes, 96) bit_match_value
    from element_attributes where bitand(attributes, 96) > 0
    
    ELEMENT_ID     ATTRIBUTES     BIT_MATCH_VALUE
    4     96     96
    That simple query does what your serial process does in a fraction of the time.

    Currently you are passing in a collection of attribute values. Only with the bitmap approach you turn that colllection of values into a single NUMBER that you then use to query the data from a standard table. No intersects, one parameter that can be a bind parameter, no more volume parsing.
    CREATE OR REPLACE FUNCTION Get_Tag_Element_Id_For_Set(Element_Attr_Id_Table_In in sys.odcinumberlist)
        RETURN NUMBER IS
          v_Select_Statement VARCHAR2(32767);
          v_Element_Id       ELEMENT.Element_Id%TYPE;
          v_attributes NUMBER;
        BEGIN
          v_attributes := 0; -- init to zero
          FOR I IN 1..Element_Attr_Id_Table_In.COUNT LOOP
            v_attributes := power(2, Element_Attr_Id_Table_In(i) - 1);
          END LOOP;
          v_Select_Statement := 'SELECT Element_Id FROM ELEMENT_ATTRIBUTES ' ||
                        ' WHERE BITAND(ATTRIBUTES, :v_attributes) = :v_attributes and rownum < 2';
          --
          EXECUTE IMMEDIATE v_Select_Statement INTO v_Element_id USING v_attributes, v_attributes;
          --
          RETURN v_Element_Id;
        END;
    
    select Get_Tag_Element_Id_For_Set(sys.odcinumberlist(1,2)) my_result from dual
    
    select Get_Tag_Element_Id_For_Set(sys.odcinumberlist(6, 7)) my_result from dual
    Now for a PIPELINED version that can return multiple results (your code ASSUMES that there is only one element_id that matches)
    -- PIPELINED function
    CREATE OR REPLACE FUNCTION Get_Tag_Element_Id_For_Set1(Element_Attr_Id_Table_In in sys.odcinumberlist)
        RETURN sys.odcinumberlist PIPELINED IS
          v_Select_Statement VARCHAR2(32767);
          v_attributes NUMBER;
          v_number NUMBER;
          TYPE my_ref_cursor IS REF CURSOR;
          v_cursor my_ref_cursor;
        BEGIN
          v_attributes := 0; -- init to zero
          FOR I IN 1..Element_Attr_Id_Table_In.COUNT LOOP
            v_attributes := power(2, Element_Attr_Id_Table_In(i) - 1);
          END LOOP;
          v_Select_Statement := 'SELECT Element_Id FROM ELEMENT_ATTRIBUTES ' ||
                        ' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') > 0';
    --                    ' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') = ' || v_attributes;
          --
          OPEN v_cursor for v_Select_Statement;
          LOOP
            FETCH v_cursor INTO v_number;
            EXIT WHEN v_cursor%NOTFOUND;
            PIPE ROW (v_number);
          END LOOP;
          --
        END;
    
    select * from table(Get_Tag_Element_Id_For_Set1(sys.odcinumberlist(1)))
    -- there are THREE elements that have attribute 1
    COLUMN_VALUE
    1
    2
    3
    
    select * from table(Get_Tag_Element_Id_For_Set1(sys.odcinumberlist(1,2)))
    -- only ONE element has both attributes 1 and 2
    COLUMN_VALUE
    1
    
    select * from table(Get_Tag_Element_Id_For_Set1(sys.odcinumberlist(6, 7)))
    -- onlly ONE with both 6 and 7
    COLUMN_VALUE
    4
    If you use the line in the function that is commented out instead of the other one it works a little different
    --                    ' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') = ' || v_attributes;
    The line above returns results where the ONLY attributes set are the ones you ask for. That is if you ask for 6,7 any results means that ONLY attributes 6 and 7 are present.

    If you use this line (which the code currently uses) and ask for 6,7
                        ' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') > 0';
    The results will be elements where attributes 6 OR 7 are set but no OTHER attributes are set. So an element with 6 will return.

    If you don't get result then you just add the value to the list. You would really create a function that converts your number table to a number and returns that number. Then your PIPELINED function would first call the conversion function and use the result in the query.

    You would add code in the pipelined function (or regular function) so that if no results were found you would use that value from the conversion function (e.g. 96) in a new record you add to the attributes table.

    PROS - VERY, VERY fast - orders of magnitude faster than what you are now doing

    CONS
    1. not suitable if the master attribute table gets a lot of DML where you keep changing the attribute combinations. The reason is the same reason that bitmap indexes are not suitable for OLTP tables that get a lot of DML.

    2. The attribute values are not very meaningful for humans. An attribute values (where 96 means attributes 6 & 7) represent binary bits that are set. Many people don't understand binary. Even if you do it isn't intuitive what 96 means -you need to do the math. The solution for that is to write the inverse function to the conversion loop I used in the code. You provide '96' and the pipelined function returns the list of attributes that it represents.

    3. Using an Oracle NUMBER limits you to 38 decimal digits, or about 125 different attribute values. You are only using 15 so you have plenty of room before you run out. If you do run out you can use RAW to store the binary and the conversion function would convert attributes 6 & 7 to bits 5 & 6 of a raw value and then you would use the UTL_RAW.BIT_AND function instead of the SQL BITAND function.

    LOVE bit-fiddling. Not many applications for it anymore but when you have one you can get some really amazing results.
  • 17. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    Sorry Tubby, but unfortunately this would not work, because in the case when we search for the 1,2 combo, it should return the element ID that they share, namely 1.

    Thomas
    Ah, my bad. I didn't realize you needed a "perfect match" on the set.

    This should address that.
    create or replace procedure maybe_this
    (
       in_list           sys.odcinumberlist
    )
    is
       l_commonality     number;
       l_array_items     number   := in_list.count;
    begin
    
       begin
          select
             xref.element_id
          into
             l_commonality
          from
             element_element_attribute_xref   xref
          where
             xref.element_attribute_id in (select column_value from table(cast(in_list as sys.odcinumberlist)))
          group by
             xref.element_id
          having count(*) = l_array_items;
    
          dbms_output.put_line('match on element_id = ' || l_commonality);
    
       exception
          when too_many_rows or no_data_found
          then
             dbms_output.put_line('no match');
       end;
    
    end;
    /
    
    ME_TUBBZ?exec maybe_this(sys.odcinumberlist(1,2));
    match on element_id = 1
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.02
    ME_TUBBZ?exec maybe_this(sys.odcinumberlist(6,7));
    match on element_id = 4
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    ME_TUBBZ?exec maybe_this(sys.odcinumberlist(1));
    no match
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    Cheers,
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points