Friday, May 19, 2017

Oracle APEX and /*+ result_cache */ of form variables

I was having a tough time with a bug in an Oracle APEX application. I was filling a shuttle-list with a query, and I added the hint /*+ result_cache */ for performance, and thought that Oracle would be peeking at the bind values being used in the query. Wrong! In regular vanilla queries, Oracle will look at bind variables to see if it can use the result cache, but APEX magic prevents that.

Code sample causing issues:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like v('P215_PROJECT_ID_NUM_SEARCH') 
Well once I took out the hint, all worked as planned!

If you are not familiar with result caching on Oracle, it can make queries that return a small number of rows from large tables run extremely fast.

Actually, if I had coded the query like the following, using bind variable syntax (:P215_PROJECT_ID_NUM_SEARCH) instead of V('') syntax to refer to my form fields, I could probably have used the result_cache hint, and since the V('') syntax is actually calling a PL/SQL procedure, it could be slower than using the bind syntax:


select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like :P215_PROJECT_ID_NUM_SEARCH 

No comments:

Post a Comment