This discussion is archived
1 2 Previous Next 17 Replies Latest reply: May 3, 2013 11:26 PM by Tubby RSS

Can Oracle convert literals to bind variables?

Thomas Morgan Newbie
Currently Being Moderated
Good morning everyone.

I have a procedure similar to this in our database:
    FUNCTION Get_Tag_Element_Id_For_Set(Element_Attr_Id_Table_In   in    ATTRIBUTE_TABLE_ID_TAB) RETURN NUMBER IS
      v_Select_Statement                    VARCHAR2(32767);
      v_Element_Id                             ELEMENT.Element_Id%TYPE;
    BEGIN
      FOR I IN 1..Element_Attr_Id_Table_In.COUNT LOOP
        IF v_Select_Statement IS NULL THEN
          v_Select_Statement := 'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Tab_In(i);
        ELSE
          v_Select_Statement := v_Select_Statement || ' intersect ' ||
                                'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Table_In(i);
        END IF;
      END LOOP;
      --
      EXECUTE IMMEDIATE v_Select_Statement INTO v_Element_id;
      --
      RETURN v_Element_Id;
    END;
What this does is to create a query similar to this:

SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 1 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 2 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 3 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 4 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 5;


I am using Dynamic SQL because the number of intersect pieces can vary from 1 to 15 and in the future it can grow.

The problem that I have is that because the literals are part of the string, the query is being hard parsed every single time. In our production environment this means 100s of thousands of times per day which according to our DBA has added 15% plus to our average load. I know that I can come up with a rewrite to avoid this, but I was wondering if there is a parameter I can set at the session or at the database level where the database itself can convert this kind of query into one with bind variables.

Thanks,

Tom

Edited by: Thomas Morgan on May 3, 2013 8:21 PM
  • 1. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    The problem that I have is that because the literals are part of the string, the query is being hard parsed every single time. In our production environment this means 100s of thousands of times per day which according to our DBA has added 15% plus to our average load. I know that I can come up with a rewrite to avoid this, but I was wondering if there is a parameter I can set at the session or at the database level where the database itself can convert this kind of query into one with bind variables.
    There is a parameter you could set, but the consequences of doing so would not be justified by a single piece of poorly written code that you already know how to fix :)

    If you had an entire application built like this and you needed it to survive until the root problem could be addressed you'd look at using that parameter, but it's only a band-aid ... not a fix.

    Cheers,
  • 2. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    On another note, this query seems quite strange.

    If you'd care to post some sample data (create table and some sample data to populate it) and explain the results you need I'm quite confident one of the forum members can aid you in creating a more "friendly" query.

    Cheers,
  • 3. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    I do not know yet how to fix but there are a couple of ideas in my head which I am executing now.

    Thanks for your reply. Just out of curiosity though and in case I cannot come up with a fix quickly, what is such parameter?
  • 4. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    I do not know yet how to fix but there are a couple of ideas in my head which I am executing now.

    Thanks for your reply. Just out of curiosity though and in case I cannot come up with a fix quickly, what is such parameter?
    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5180609822543

    The most important takeaway from this post
    "
    in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a poorly written program survive for the period of time the developers are hard at work CORRECTING their bug and putting bind variable support into their application
    "

    As previously mentioned, you really don't want to do this.

    Spend 10 minutes putting together a sample data set for us (create table and insert statements) and you'll probably have dozens of replies on a more efficient query that doesn't need any dynamic SQL (people like puzzles).

    Cheers,
  • 5. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    I'll put together DDL and data set as soon as I can.

    BTW, do you what tag I use to ensure my code maintains format (identation)? I do not see it in the markup to the right.

    I will also post a solution if I find one.

    Thanks,

    Thomas
  • 6. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    I'll put together DDL and data set as soon as I can.
    Cool!
    Thomas Morgan wrote:
    BTW, do you what tag I use to ensure my code maintains format (identation)? I do not see it in the markup to the right.
    https://wikis.oracle.com/display/Forums/Forums+FAQ

    I use the
     tags (surround the block you want with a begin/end tag). 
    
    
    Thomas Morgan wrote:
    I will also post a solution if I find one.
    Sounds good. Cheers,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 7. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    Good old DBMS_SQL to the rescue. It allows you to bind a variable number of variables into your statement.

    Here is what the solution looks like (My apologies if I changed table name(s)):
        FUNCTION Get_Element_Id_For_Set(Element_Attr_Id_Tab_In   in    ELEMENT_ATTRIBUTE_ID_TAB) RETURN NUMBER IS
          v_Statement                           VARCHAR2(32767);
          v_CursorID                            INTEGER;
          v_Dummy                               INTEGER;
          v_Element_Id                             ELEMENT.Element_Id%TYPE;
          v_Bind_Variable_Index                 PLS_INTEGER := 0;
        BEGIN
          FOR I IN 1..Tag_Element_Attr_Id_Tab_In.COUNT LOOP
            IF v_Statement IS NULL THEN
              v_Statement := 'SELECT distinct Element_Id FROM ELEMENT_ELEMENT_ATTR_XREF WHERE Element_Attribute_Id = :B' || I;
            ELSE
              v_Statement := v_Statement || ' intersect ' ||
                            'SELECT distinct Element_Id FROM ELEMENT_ELEMENT_ATTR_XREF WHERE Element_Attribute_Id = :B' || I;
            END IF;
          END LOOP;
          --
          v_Statement := 'BEGIN ' || 'SELECT Element_Id INTO :Element_Id FROM(' || v_Statement || '); END;';
          --
          v_CursorID := DBMS_SQL.OPEN_CURSOR;
          --
          DBMS_SQL.PARSE(v_CursorID, v_Statement, DBMS_SQL.V7);
          -- 
          DBMS_SQL.BIND_VARIABLE(v_CursorID, ':Element_Id', v_Element_Id);
          --
          FOR I IN 1..Element_Attr_Id_Tab_In.COUNT LOOP
            DBMS_SQL.BIND_VARIABLE(v_CursorID, ':B' || I, Element_Attr_Id_Tab_In(i));
          END LOOP;
          --
          v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
          DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':Element_Id', v_Element_Id);
          DBMS_SQL.CLOSE_CURSOR(v_CursorID);
          --
          RETURN v_Element_Id;
        END;
    Edited by: Thomas Morgan on May 3, 2013 8:21 PM
  • 8. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    See last posting for solution.
  • 9. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    Good old DBMS_SQL to the rescue. It allows you to bind a variable number of variables into your statement.
    Glad you found a way to kick in the bind variables.

    The SQL you are producing still looks much more complex than need be, but impossible to tell without knowing exactly what you're doing :)

    Cheers,
  • 10. Re: Can Oracle convert literals to bind variables?
    APC Oracle ACE
    Currently Being Moderated
    Thomas Morgan wrote:
    FYI {noformat}
    {noformat} is markup but not XML.  Consequently the closing tag does not have a slash.  That's why your code sample is not formatted correctly. 
    
    Cheers, APC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 11. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    Basically I have an XML document.

    The document has ELEMENTS.

    ELEMENTS can have attributes.

    There is a many to many relationship between ELEMENT and ATTRIBUTES so I have a XREF table to represent this relationship.

    When I receive a new document, I need to determine if a group of ATTRIBUTES already exist together in the XREF table so I have to find their common ELEMENT_ID or NULL in which case I would create a new ELEMENT and associate the new set of attributes to the new ELEMENT.

    Since the number of attributes can vary from 1 to around 15 for now, I had to dynamically build the query that find the common ID(intersect) if it exists.

    Here is some DDL and inserts:

    CREATE TABLE ELEMENT(ELEMENT_ID NUMBER NOT NULL);

    CREATE TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF(Element_Id NUMBER NOT NULL, Element_Attribute_Id NUMBER NOT NULL);

    ALTER TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF ADD CONSTRAINT ELEMENT_ATTR_MANY_2_MANY_PK PRIMARY KEY(Element_Id, Element_Attribute_Id);

    ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY(Element_Id);

    INSERT INTO ELEMENT VALUES(1);
    INSERT INTO ELEMENT VALUES(2);
    INSERT INTO ELEMENT VALUES(3);
    INSERT INTO ELEMENT VALUES(4);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 1);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 2);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(2, 1);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 1);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 3);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 4);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 5);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 6);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 7);

    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,

    Thanks,

    Tom
  • 12. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    Thanks APC, I'll make a note for future.

    Any possibility it could be added to the Markup displayed to the right?

    Thanks,

    Thomas
  • 13. Re: Can Oracle convert literals to bind variables?
    Tubby Guru
    Currently Being Moderated
    Thomas Morgan wrote:
    Basically I have an XML document.

    The document has ELEMENTS.

    ELEMENTS can have attributes.

    There is a many to many relationship between ELEMENT and ATTRIBUTES so I have a XREF table to represent this relationship.

    When I receive a new document, I need to determine if a group of ATTRIBUTES already exist together in the XREF table so I have to find their common ELEMENT_ID or NULL in which case I would create a new ELEMENT and associate the new set of attributes to the new ELEMENT.

    Since the number of attributes can vary from 1 to around 15 for now, I had to dynamically build the query that find the common ID(intersect) if it exists.

    Here is some DDL and inserts:

    CREATE TABLE ELEMENT(ELEMENT_ID NUMBER NOT NULL);

    CREATE TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF(Element_Id NUMBER NOT NULL, Element_Attribute_Id NUMBER NOT NULL);

    ALTER TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF ADD CONSTRAINT ELEMENT_ATTR_MANY_2_MANY_PK PRIMARY KEY(Element_Id, Element_Attribute_Id);

    ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY(Element_Id);

    INSERT INTO ELEMENT VALUES(1);
    INSERT INTO ELEMENT VALUES(2);
    INSERT INTO ELEMENT VALUES(3);
    INSERT INTO ELEMENT VALUES(4);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 1);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 2);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(2, 1);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 1);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 3);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 4);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 5);

    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 6);
    INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 7);

    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,

    Thanks,

    Tom
    Awesome, thanks!

    I'm not sure this works 100% for what you need, but I thought I'd give it a shot. I've used a built in array (sys.odcinumberlist) that you should have access to on your installation (assuming you have a relatively recent version of Oracle).

    I left out the part where you would loop through the array you are currently passing in to your procedure to build up this new array that you can use in a SQL statement.

    Cheers,
    create or replace procedure maybe_this
    (
       in_list           sys.odcinumberlist
    )
    is
       l_commonality     number;
    begin
    
       begin
          select
             distinct
                xref.element_id
          into
             l_commonality
          from
                table(cast(in_list as sys.odcinumberlist))  list
            ,  element_element_attribute_xref   xref
          where
             xref.element_attribute_id = list.column_value;
     
          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));
    no match
     
    PL/SQL procedure successfully completed.
     
    Elapsed: 00:00:00.01
    ME_TUBBZ?exec maybe_this(sys.odcinumberlist(6,7));
    match on element_id = 4
     
    PL/SQL procedure successfully completed.
     
    Elapsed: 00:00:00.01
  • 14. Re: Can Oracle convert literals to bind variables?
    Thomas Morgan Newbie
    Currently Being Moderated
    Thanks Tubby,

    I'll give it a try when I comeback to the office on Monday.

    Thomas
1 2 Previous Next

Legend

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