This content has been marked as final. Show 17 replies
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.
Now to query the data
-- 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);
-- 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
Now we can query the data
2^5 + 2^6 = 32 + 64 = 96 so our query mask is 96.
That simple query does what your serial process does in a fraction of the time.
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
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.
Now for a PIPELINED version that can return multiple results (your code ASSUMES that there is only one element_id that matches)
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
If you use the line in the function that is commented out instead of the other one it works a little different
-- 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
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.
-- ' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') = ' || v_attributes;
If you use this line (which the code currently uses) and ask for 6,7
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.
' WHERE BITAND(ATTRIBUTES, ' || v_attributes || ') > 0';
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
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.
Thomas Morgan wrote:Ah, my bad. I didn't realize you needed a "perfect match" on the set.
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.
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