Milo -- Thanks for the question regarding "binding to dynamic SQL", version 8.1.7
Submitted on 9-Feb-2007 12:03 Central time zone
Last updated 16-Jul-2009 11:11
You Asked
Many web applications (including "Ask Tom") have an advanced search screen where the user can specify criteria in a number of fields. There are quite a few ways to build the SQL that goes with such a search screen, and I was wondering if you could give your input on the methods below. I'm particularly interested in the impact each method would have on caching, parsing, and execution plans. Also, if you know of a better way that is not listed below, please share it.
In the examples below, a search for books has many fields, but I'm only considering the search by "title" to simplify things. If a title is specified, it has to be an exact match. If no title is specified, all books are returned.
1) Native dynamic SQL using hardcoded values. (Creates a potentially infinite number of SQL statements.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
IF ( p_title IS NOT NULL ) THEN
l_sql := l_sql || ' AND title = ''' || p_title || '''';
END IF;
EXECUTE IMMEDIATE l_sql;
2) Native dynamic SQL using bind variables. (Creates one SQL statement per combination of criteria.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
IF ( p_title IS NOT NULL ) THEN
l_sql := l_sql || ' AND title = ?';
ELSE
-- Criteria will not contribute to the search.
-- It is only here to ensure that the bind variable is used.
l_sql := l_sql || ' OR ? = NULL';
END IF;
EXECUTE IMMEDIATE l_sql USING p_title;
3) DBMS_SQL using bind variables. (Creates one SQL statement per combination of specified criteria.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
IF ( p_title IS NOT NULL ) THEN
l_parm_index := l_parm_index+1;
l_parms(l_parm_index) := p_title;
l_sql := l_sql || ' AND title = :parm' || l_parm_index;
END IF;
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, l_sql, dbms_sql.native );
IF ( l_parms.COUNT > 0 ) THEN
FOR l_index IN l_parms.FIRST .. l_parms.LAST LOOP
dbms_sql.bind_variable( l_cursor, ':parm' || l_index, l_parms(l_index) );
END LOOP;
END IF;
l_result := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );
4) Native dynamic SQL using "Application Context". (Creates one SQL statement per combination of specified criteria.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
IF ( p_title IS NOT NULL ) THEN
DBMS_SESSION.set_context(
namespace=>'CTX_SEARCH',
attribute=>'p_title',
value=>p_title );
l_sql := l_sql || ' AND title = SYS_CONTEXT(''CTX_SEARCH'',''p_title'')';
END IF;
EXECUTE IMMEDIATE l_sql;
5) Native dynamic SQL using 'cleverer' bind variables. (Creates one SQL statement, period.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
l_sql := l_sql || ' AND ( ? IS NULL OR title = ? )';
-- Need to bind every parameter twice since it is used twice.
EXECUTE IMMEDIATE l_sql USING p_title, p_title;
6) DBMS_SQL using 'cleverer' bind variables. (Creates one SQL statement, period.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
l_parms(l) := p_title;
l_sql := l_sql || ' AND ( :parm1 IS NULL OR title = :parm1 )';
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, l_sql, dbms_sql.native );
FOR l_index IN l_parms.FIRST .. l_parms.LAST LOOP
dbms_sql.bind_variable( l_cursor, ':parm' || l_index, l_parms(l_index) );
END LOOP;
l_result := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );
7) DBMS_SQL using "Application Context". (Creates one SQL statement, period.)
l_sql := 'INSERT INTO SEARCH_RESULTS SELECT book_id FROM BOOK WHERE 1=1';
DBMS_SESSION.set_context(
namespace=>'CTX_SEARCH',
attribute=>'p_title',
value=>p_title );
l_sql := l_sql || ' AND ( SYS_CONTEXT(''CTX_SEARCH'',''p_title'') IS NULL OR title =
SYS_CONTEXT(''CTX_SEARCH'',''p_title'') )';
EXECUTE IMMEDIATE l_sql;
and we said...
See
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279
for how I accomplish this.
#1) NEVER NEVER NEVER do that. Not only does it not use bind variables - thus killing the system - it is subject to SQL Injection big time.
#2) No, not necessary.
#3) will be applicable in 11g when we can convert a dbms_sql 'cursor' to a ref cursor - but right now, if I don't have to use dbms_sql, I won't. I'd rather have a ref cursor
#4) my approach
#5) nope, leads to the worst possible plan being used for ANY set of inputs
#6) see #5
#7) see #3, but would still just do #4
why?
February 9, 2007 - 1pm Central time zone
Reviewer: Milo van der Leij
Thanks for the answer. I assumed the best way was #3 or #4, but I am surprised you picked SYS_CONTEXT over DBMS_SQL.
While I understand that native dynamic SQL is faster than DBMS_SQL, I'm assuming that SYS_CONTEXT also has some overhead associated with it. Is DBMS_SQL that bad?
Also, I understand that there is such a thing as "bind variable peeking". Can Oracle "peek" into the result of the SYS_CONTEXT call, or do you lose a potential benefit here? (Or does "bind variable peeking" not occur with DBMS_SQL either?)
And finally, for the sake of argument, consider doing SELECT rather than INSERT statements. Being on Oracle 8i, I can't bulk select using native dynamic SQL. In that case, would you consider using DBMS_SQL instead since it allows for bulk data retrieval?
That's why.
February 12, 2007 - 12pm Central time zone
Reviewer: Milo van der Leij
To save you the trouble, and to have all the answers in one place, I will answer my own questions.
Yes, DBMS_SQL is that bad. From Expert One-on-One: "On simple queries, where the processing of the query itself could be ignored, native dynamic SQL is almost
twice as fast at fetching the data as DBMS_SQL."
From the "CURSOR" thread you linked: "sys_context is treated as a bind variable (but no bind variable peeking)".
There are reports elsewhere that DBMS_SQL also doesn't do bind variable peeking: http://www.db-nemec.com/HappyNewPeek.html
And finally, again from Expert One-on-One when comparing DBMS_SQL with native dynamic SQL: "DBMS_SQL will be used when (...) You will be fetching or inserting thousands of rows and can employ array processing."
Note: one other reason to use DBMS_SQL is that, on Oracle 8i, SYS_CONTEXT values are limited to 255 characters. (4000 characters in 9i).
Tom's latest position + caveats with App Context
July 14, 2009 - 10am Central time zone
Reviewer: Milo
I see that in the July 2009 edition of Oracle Magazine you have changed your position on this (http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html ), now recommending a variation on #2 that uses "(1=1 OR ? IS NULL)" instead of "OR ? = NULL". I'm glad that people are still trying to find the best way to solve this problem, as I have to believe it's a common one.
In addition to the Application Context downsides you mention, Dominic Brooks points out that messing with the Application Context's value between binding and fetching has consequences that you may or may not expect. (http://orastory.wordpress.com/2009/07/14/gotcha-application-contexts ). This seems like another reason someone might not want to use Application Contexts.
Finally I'd like to add that since we've moved to 11g we are now using solution #3, using DBMS_SQL to build and bind to the statement, then convert it to a ref cursor, and then use a Dynamic SQL select (with bulk collect limit n) to retrieve the data. We feel it gives us the most flexibility and power without feeling like we're using any kind of hack.
Followup July 15, 2009 - 11am Central time zone:
... without feeling like we're using any kind of hack. ...
not sure why (1=1 or ? is null) is a 'hack'. Seems to me that the pure native dynamic sql approach is easier to code than the dbms_sql one. You have to inspect the inputs once to build the query (which the (1=1 or ? is null) does) and then inspect them all again to selectively bind.
July 15, 2009 - 4pm Central time zone
Reviewer: Milo van der Leij
Maybe not a "hack" by definition (if we could even agree on a definition) but it's definitely
"clever", which is not the same as "smart", and it relies on what appears to be undocumented
behavior. The PL/SQL Language Reference says that PL/SQL uses "Short-Circuit Evaluation", but I
can't find anything that says that SQL also does that.
If my colleagues ask "will that always work in all cases, even when we upgrade to Oracle 12?", I
wouldn't be able to answer "yes" with 100% certainty. I'm putting more value on trusting the
solution than on performance of the solution.
Followup July 15, 2009 - 4pm Central time zone:
... and it relies on
what appears to be undocumented behavior....
not at all?
select *
from t
where (1=1 or :x is null)
and (1=1 or :y is null)
/
what is undocumented about that sql syntax?
We rely one the optimizer to optimize things - to recognize "tautologies" - all of the time.
... I'm
putting more value on trusting the solution than on performance of the solution...
that doesn't seem to compute to me? When you upgrade to version 12, are you sure that the optimizer won't look at something like:
with p
as
(select :x x, :y y from dual)
select *
from t, p
where t.hiredate > p.y;
and go nutso for *some reason*?
It is a non-equijoin, a cartesian join of sorts. It cannot bind peek like "where t.hiredate > :y" can. It has far less opportunity to get the right estimated cardinality.
July 15, 2009 - 5pm Central time zone
Reviewer: Milo van der Leij
You're right, the SQL syntax is well documented. However, the optimizer's behavior is not. I'm simply saying that
select * from t
has a smaller chance of "going nutso" than
select * from t where (1=1 or :x is null)
Followup July 16, 2009 - 11am Central time zone:
agreed - but
with p (select ... from dual)
select * from t, p
has a much larger change of going nutso than
select * from t where (1=1 or :x is null)
does. Adding that non-equijoin in there - in a complex query of any sort - is infinitely more complex to optimize....
|