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;
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