Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, snowy.

Asked: April 27, 2009 - 10:22 pm UTC

Answered by: Tom Kyte - Last updated: July 10, 2019 - 10:24 am UTC

Category: Database - Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a web application connecting to a Oracle DB.
I have a search screen where user can enter any search criterion and submit the information.
The information is collected and passed to an Oracle Stored Proc. where a dynamic query is formed based on the search citerion passed.
Ex
if user entered emp no then
the dynamic query formed would be

select * from emp where empNo = p_empno;

If the user entered emp no and salary then
the dynamic query formed would be

select * from emp where empNo = p_empno and sal = p_salary;

What we are doing is forming the where predicate string dynamically based on user input and using execute immediate to execute above dynamically formed query.

I know this dynamic query would be inefficient as each query would be unique which means hard parsed which means low efficiency.
Actually my search screen has a lot of fields about 50 different fields.

(1)Since p_empno etc are parameters to stored proc are these turned into bind varaibles even though I am using execute immediate ?

(2)
I did search on the net for any help .
One article said a generic search could be formed like this

select * from emp 
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);

but would be inefficient as it would always lead to full table scans.
Is this true?

(2) I think using this would be better than above since it uses bind variables.

Form the query string like this

select * from emp 
where ename = :n
and deptno = :d
and empno = :e 
using p_ename,p_deptno, p_empno


and then execute above formed dynamic string using execute immediate.

(3) Would you suggest any better way of doing this generic search.

As always thank you for your valuable time in helping out the newbies.

Thank you a million times.


Regards


and we said...

Funny, the timing - I am just set to proof my next column for Oracle Magazine - and it is on this *very* topic!

Here is a pre-release you can read further:

https://blogs.oracle.com/oraclemagazine/on-popularity-and-natural-selection

(might be typos/layout issues, I haven't proofed it yet!)


Basically, you will use a ref cursor, and you will build the query dynamically, the query will resemble:


where (1=1 or :COLUMN_NAME1 is null) and ( column_name2 = :COLUMN_NAME2 )


you would use the first form:

(1=1 or :column_name1 is null)

when the user did not supply the input to constrain against column_name1 and the second form:

( column_name2 = :COLUMN_NAME2 )

when then do. The optimizer will see (1=1 or ...) and optimize that away, it will be as it is was never there. But, it will give you a bind place holder.

So, if you had three inputs (c1, c2, c3) you would build a query like:

select ... from ... where (1=1 or :c1 is null) and (c2 = :c2) and (c3 = :c3)


and then:


open C for that_string using p_c1, p_c2, p_c3;


and that would process a query whereby the end user supplied c2 and c3 - but not c1. And it would do it as efficiently as possible - using an index on c2 or c3 or c2+c3 if appropriate (you get the best plan based on the inputs that are provided)



by the way - BOTH of your examples used bind variables. But they are not equivalent (the second one would not return the right answer, and the first one would be slow - a full scan every time).



and you rated our response

  (92 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

query

April 28, 2009 - 9:31 am UTC

Reviewer: sam

Tom:

is this technique you describe similar to SET context you used here

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279


Where can i see all the articles you post in oracle magazine?
Tom Kyte

Followup  

April 28, 2009 - 10:44 am UTC

SMK - did you bother reading the article I linked to?


http://www.oracle.com/technology/oramag/oracle/asktom/index.html
that has all but the first year of asktom.

the first year can be found here
http://asktom.oracle.com/pls/ask/download_file?p_file=2124635631812021926

at the bottom

Very nice!

April 28, 2009 - 11:02 am UTC

Reviewer: Sal from Pittsburgh, PA

Tom,

The new method in your article is pretty neat! How can one measure the performance improvements with the new mechanism? Will there be less latching and CPU time?

Also, what mechanism would you recommend if the parameters spanned over multiple tables and the column list was also variable?

Enhancing this procedure or third party tool?

Thanks!
Tom Kyte

Followup  

April 28, 2009 - 11:26 am UTC

the ease of use of this (no to'ing to coerce sys_context to number and dates), no having to encode the format of numbers and dates..

the lack of conversions...

the fact that sys_context is a function - and does require a bit of latching...


This is just better all around.


matching nulls

April 28, 2009 - 12:02 pm UTC

Reviewer: nameless


A possible enhancement would be to have something similar to pro*c indicators to be able to handle matching nulls.
...
  p_sal     in number default null
  p_sal_ind in number default 0
...
  if p_sal_ind = 0 then
    if p_sal is not null then
      l_query := l_query || ' and sal > :p_sal';
    else
      l_query := l_query || ' and (1=1 or :p_sal is null)';
    end if;
  else
    l_query := l_query || ' and (sal is null and :p_sal is null)';
  end if;
...
Maybe.

April 29, 2009 - 1:02 am UTC

Reviewer: snowy from japan

You said
Tom sorry to ask again .My numbering was wrong
I had 3 queries above

(1)
 select * from emp where empNo = p_empno and sal = p_salary;

(2)
select * from emp 
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);

(3)
select * from emp 
where ename = :n
and deptno = :d
and empno = :e 
using p_ename,p_deptno, p_empno


Which one did you mean when you said

BOTH of your examples used bind variables. But they are not equivalent (the second one would not return the right answer, and the first one would be slow - a full scan every time).

the second one would not return the right answer, and why ?
Tom Kyte

Followup  

April 29, 2009 - 8:57 am UTC

Initially - there were only two queries of any interest, the two that you were talking about - the first "two" (there are actually four if you count them) were just for talking points. Your entire question was about the last two.


Anyway - regarding JUST the above last review here...


all of your queries above use binds.

all three of them are different from each other.

only the second one appears to be the one you think you want. but you don't, you want to read the link I gave you and do that.

Hard Parsing

April 29, 2009 - 1:59 am UTC

Reviewer: Vinay Paryani from UAE

Hi Tom,
Had a glance at the logic for using sys_context to generate the query dynamically in generic search and referred to the below article:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

My understanding is that the statement "open l_cursor for l_query;" where l_query is the string generated using sys_context, will go for a hard parse everytime since the result set is generated using OPEN ref_cursor syntax.

Kindly correct me if I am wrong.

Thanks for your valuable guidance.


Tom Kyte

Followup  

April 29, 2009 - 9:01 am UTC

your understanding is wrong.


a hard parse happens when the sql isn't in the shared pool. After the first hard parse, it will be.

The entire *goal* of using sys-context in that example was to simulate BIND VARIABLES so as to avoid hard parsing.

Doubt

April 29, 2009 - 2:25 am UTC

Reviewer: Vicky from India

Hi Tom.

Question 1)What is the point of having else clause in the code? Cant we get away with it? Because this will not be used by the optimizer at all.

if ( p_hiredate is NOT NULL )
then
l_query := l_query ||
` and hiredate > :p_hiredate `;
else |||| Why is
l_query := l_query || |||| this
` and (1=1 or :p_hiredate is null) `; |||| required?
end if;

Regards,
Vicky
Tom Kyte

Followup  

April 29, 2009 - 9:03 am UTC

did you read the article?

Tell you what, you have the code, you have the example, why don't you take the example - remove the else code and see what happens????



hint:

open C for that_string using p_c1, p_c2, p_c3;


we always open the cursor with all three (three is just what I used in my example, it could be 17, 42, 1 - whatever) binds. Therefore we need to always.......(fill in the blank) .....

Multiple Values

April 29, 2009 - 11:32 am UTC

Reviewer: Greg P. from Chicago, IL

Tom,

How could one extend this technique to pass parameters with multiple values? If I was building dynamic SQL I would do something like:

l_query := l_query||'
AND ename IN ('''||REPLACE(p_ename,',',''',''')||''')'
Tom Kyte

Followup  

April 29, 2009 - 12:43 pm UTC

good gosh, I hope you would NEVER do that. no binds, a security bug the size of the space shuttle, run away from that.

ops$tkyte%ORA11GR1> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace
  2  procedure generic_query
  3  ( p_cursor in out sys_refcursor,
  4    p_enames in sys.odcivarchar2list default null,
  5    p_sal in number  default null
  6  )
  7  as
  8      l_string long := 'select ename, job, sal, deptno from emp where ';
  9  begin
 10      if ( p_enames is not null )
 11      then
 12          l_string := l_string || ' ename in (select * from TABLE(:p_enames)) ';
 13      else
 14          l_string := l_string || ' (1=1 or :p_enames IS NULL) ';
 15      end if;
 16      if ( p_sal is not null )
 17      then
 18          l_string := l_string || ' and sal >= :p_sal ';
 19      else
 20          l_string := l_string || ' and (1=1 or :p_sal IS NULL) ';
 21      end if;
 22
 23      open p_cursor for l_string using p_enames, p_sal;
 24  end;
 25  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> variable x refcursor
ops$tkyte%ORA11GR1> set autoprint on
ops$tkyte%ORA11GR1> exec generic_query( :x, sys.odcivarchar2list( 'SCOTT', 'ALLEN' ) );

PL/SQL procedure successfully completed.


ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SCOTT      ANALYST         3000         20
ALLEN      SALESMAN        1600         30

ops$tkyte%ORA11GR1> exec generic_query( :x, p_sal => 3000 );

PL/SQL procedure successfully completed.


ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SCOTT      ANALYST         3000         20
KING       PRESIDENT       5000         10
FORD       ANALYST         3000         20

ops$tkyte%ORA11GR1> exec generic_query( :x, sys.odcivarchar2list( 'SCOTT', 'ALLEN' ), 3000 );

PL/SQL procedure successfully completed.


ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SCOTT      ANALYST         3000         20




and if you have to pass that delimited string, parse it into a collection variable and just do what I did above.

Still a doubt.

April 30, 2009 - 2:40 am UTC

Reviewer: Vicky from India

Thanks Tom for the response.

I cannot try the code as I do not have access to database in my company for performing RnD.

l_query varchar2(512) default 'select * from emp where 1=1 ';
cursor l_template is select * from emp;
l_rec l_template%rowtype;

begin

if ( p_ename is NOT NULL ) then
l_query := l_query || ' and ename like''%''||:p_ename||''%'' ';
end if;

if ( p_hiredate is NOT NULL ) then
l_query := l_query || ' and hiredate > :p_hiredate ';
end if;

if ( p_sal is NOT NULL ) then
l_query := l_query || ' and sal > :p_sal ';
end if;


I have taken out where 1=1 and added it into default and removed all the else condition.
Just trying to make it a bit more neat.

Regards,
Vicky

Tom Kyte

Followup  

April 30, 2009 - 10:00 am UTC

... I cannot try the code as I do not have access to database in my company for
performing RnD.
....

go immediately to otn.oracle.com/xe

download it

install it

play with it - that is how you'll learn many things and become much better at Oracle in general.

this is NOT "RnD" which I assume you mean to say "Research and Development". This is standard development practice - to try something out. (golden rule in our profession, never use an acronym or abbreviation without defining it. If I used the acronym HSM - would you know what it meant? Guess what, if you say 'yes, I would' - you are wrong - 100% wrong. You cannot possibly know what I meant - no matter what you say it stands for, I'll give you a different answer).

And if you don't have access to a database where you can try this concept out - well, frankly you don't need to try this concept out - you are apparently not in database development or DBA work - if you cannot run a test script, that means you don't have any development work in the database at all (for the act of development is..... running test scripts and such).




and guess what will happen to your "neat" (whatever that means???? This is like a flashback to Saturday Night Live with the tag line "It is better to look good than to feel good". "neat" code that doesn't work is ugly code) code when you execute:

open p_cursor for l_query using p_ename, p_hiredate, p_sal;

and all three where NULL?? think about it - you don't even need to compile that code, it should be obvious

(if all three are null inputs, the query is simply 'select * from emp where 1=1', what will happen with

open c for 'select * from emp where 1=1' USING p_ename, p_hiredate, p_sal;



ops$tkyte%ORA10GR2> declare
  2          l_cursor sys_refcursor;
  3          x varchar2(1);
  4          y varchar2(1);
  5          z varchar2(1);
  6  begin
  7          open l_cursor for 'select * from scott.emp where 1=1' using x,y,z;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at line 7

Doubt Cleared

May 04, 2009 - 4:55 am UTC

Reviewer: Vicky from India

Thanks Tom for the excellent Response.
I will ensure that code is tried out before posting any comment on it over the site.

select *

May 11, 2009 - 10:59 am UTC

Reviewer: James from Australia

Hi Tom. Good to see you have gone back and addressed this golden question.

There were 2 sections in your article, the 1=1 bind all approach and the select * warning. You used 'select * from emp' in your binding example, so I'm wondering if the following is more acceptable.

Would you be against using "select *" with views?

e.g. Assuming your application is only interested in 4 columns from emp:

create view emp_view as (select emp_id,ename,hiredate,sal from emp);

procedure prepare_emp_search(
p_cursor out sys_refcursor,
p_ename number default null,
p_hiredate date default null,
p_sal number default null)

is

l_query varchar2(512) := 'select * from emp_view';
cursor l_template is select * from emp_view;

begin
--...

open p_cursor for l_query
using p_ename,
p_hiredate,
p_sal;

end;



This will limit the exposure of the application to column alteration and well defines your application data requirements at a logical, database level.


Regards,
James
Tom Kyte

Followup  

May 11, 2009 - 6:48 pm UTC

... This will limit the exposure of the application to column alteration ...

no it doesn't.

In real production code that you have to maintain, you will not use select *

Why not use DBMS_SQL.bind_variable

May 15, 2009 - 2:47 am UTC

Reviewer: Patrick from Hong Kong

Tom,
Could you please clarify why you need to use dbms_session.set_context or the optional (1=1) techniques described?
We encountered a similar problem and I went for the solution below:
  IF l_order_number IS NOT NULL
  THEN
     l_sqltext := l_sqltext || ' AND order_num = :order_number';
  END IF;

  DBMS_SQL.parse (l_cur, l_sqltext, DBMS_SQL.native);

  IF p_order_number IS NOT NULL
  THEN
     DBMS_SQL.bind_variable (l_cur, ':order_number', p_order_number);
  END IF;

Is there any reason why that would not work in your example or are there performance issues?
Regards,
Patrick
Tom Kyte

Followup  

May 15, 2009 - 1:44 pm UTC

because we wanted a ref cursor - returning a result set to a client.

dbms_sql cursors would be the wrong thing to return to a client.

How about using a dynamic anonymous pl/sql block

May 19, 2009 - 11:35 am UTC

Reviewer: Marco Coletti from Trento, Italy

It seems to me that the whole point of the "new" technique is to simplify the task to provide fixed-position-bind-placeholders in the text of the dynamic query to be executed, while still allowing the optimizer to recognize that there are some void predicates that it can ignore.

If the underlying problem is that entirely removing the void predicates has the effect to shift the bind placeholders such that they are mismatched in respect to the variables list... then, what about this solution (I made the least possible modifications to your proposed my_new_procedure):
<pre>
create or replace procedure my_newer_procedure
( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
as
l_cursor sys_refcursor;
l_query varchar2(512)
default 'select * from emp';

cursor l_template is select * from emp;
l_rec l_template%rowtype;

begin

if ( p_ename is NOT NULL ) then
l_query := l_query || ' where ename like ''%''||:p_ename||''%'' ';
end if;

if ( p_hiredate is NOT NULL ) then
l_query := l_query || ' and hiredate > :p_hiredate ';
end if;

if ( p_sal is NOT NULL ) then
l_query := l_query || ' and sal > :p_sal ';
end if;

dbms_output.put_line ( l_query );

l_query := '
declare
c_result sys_refcursor := :1;
ename varchar2(999) := :2;
hiredate date := :3;
sal number := :4;
begin
open c_result for '||l_query||';
end;';

open l_cursor for select * from DUAL; -- Workaround for Oracle bug 2709343
execute immediate l_query using
in out l_cursor -- 1
,p_ename -- 2
,p_hiredate -- 3
,p_sal -- 3
;

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
dbms_output.put_line ( l_rec.ename || ',' || l_rec.hiredate || ',' || l_rec.sal );
end loop;
close l_cursor;

end my_newer_procedure;
/
</pre>

Admittedly, this is not "flour of my bag" as we say in Italy. I noticed this technique in some other thread of this same website, and then only polished it a bit.



Tom Kyte

Followup  

May 23, 2009 - 12:18 pm UTC

it has the downside effect of hitting the shared pool with two parses instead of one. Three if you could the select from dual query (only necessary in old old unpatched 9.2 releases).

do not like it for that reason.

May 19, 2009 - 1:09 pm UTC

Reviewer: Marco Coletti from Trento, Italy

I made some pretty obvious errors using placeholders instead of variables local to the anonymous block:
...
  if ( p_sal is NOT NULL ) then
    l_query := l_query || ' and sal > p_sal ';
  end if;

...

  l_query := '
    declare
      c_result   sys_refcursor := :1;
      p_ename    varchar2(999) := :2;
      p_hiredate date          := :3;
      p_sal      number        := :4;
    begin
      open c_result for '||l_query||';
    end;';
...


sys.odcivarchar2list

May 19, 2009 - 7:48 pm UTC

Reviewer: A reader

In your reply on April 29, 2009, you used sys.odcivarchar2list in the input parameter. How does JDBC handle this? Thanks.
Tom Kyte

Followup  

May 23, 2009 - 12:45 pm UTC

it is a collection, jdbc would handle it as it would any collection. Just like if you

create or replace type myType as table of varchar2(4000);

it would be handled "just the same"

Is this a complete solution?

May 19, 2009 - 7:56 pm UTC

Reviewer: A reader

So the new solution that you provided and demonstrated in your Oracle Magazine article is not really a complete replacement for using an application context, right? I mean if I have 10 different columns as input parameters and each column can have, say, up to 3 different operators (e.g. =, LIKE, NOT LIKE), using the new method could create a SQL of monstrous size. Wouldn't it be better to use an application context in that case?
Tom Kyte

Followup  

May 23, 2009 - 12:49 pm UTC

ummm

please explain your logic as to how an application context would be any different???

answer to last question of "wouldn't it be better" is NO

I don't see how the resulting sql would be *any* different or better using an application context???

You will have code of the form:

if ( p_variable is not null )
then
add one thing to where clause
else
add (1=1 OR (:variable is null))
end if


if you use an application context, you will

if ( p_variable is not null )
then
add one thing to where clause
end if;


the resulting SQL in the former case will be of finite size, as will the latter. They will be "about the same" in complexity and length.

May 20, 2009 - 6:06 am UTC

Reviewer: Aji from India

Hi Tom,

Can you let me know if the below query can be used for a generic search?

SELECT employee_id
      ,salary
FROM   hr.employees
WHERE (:emp_id IS NULL OR employee_id = :emp_id)
AND   (:salary IS NULL OR salary = :salary)


I tried a few cases with values for :emp_id and :salary. It gave the expected results.

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 20 15:09:57 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Loading login.sql file...
HR@testdb SQL>SELECT employee_id
  2        ,salary
  3  FROM   hr.employees
  4  WHERE employee_id = 206
  5  AND   salary      = 8300;

EMPLOYEE_ID     SALARY
----------- ----------
        206       8300

HR@testdb SQL>
HR@testdb SQL>VAR emp_id NUMBER
HR@testdb SQL>VAR salary NUMBER
HR@testdb SQL>EXEC :emp_id := 206;

PL/SQL procedure successfully completed.

HR@testdb SQL>EXEC :salary := 8300;

PL/SQL procedure successfully completed.

HR@testdb SQL>
HR@testdb SQL>SELECT employee_id
  2        ,salary
  3  FROM   hr.employees
  4  WHERE (:emp_id IS NULL OR employee_id = :emp_id)
  5  AND   (:salary IS NULL OR salary = :salary);

EMPLOYEE_ID     SALARY
----------- ----------
        206       8300

HR@testdb SQL>
HR@testdb SQL>EXEC :emp_id := 206;

PL/SQL procedure successfully completed.

HR@testdb SQL>EXEC :salary := NULL;

PL/SQL procedure successfully completed.

HR@testdb SQL>
HR@testdb SQL>SELECT employee_id
  2        ,salary
  3  FROM   hr.employees
  4  WHERE (:emp_id IS NULL OR employee_id = :emp_id)
  5  AND   (:salary IS NULL OR salary = :salary);

EMPLOYEE_ID     SALARY
----------- ----------
        206       8300

HR@testdb SQL>
HR@testdb SQL>EXEC :emp_id := NULL;

PL/SQL procedure successfully completed.

HR@testdb SQL>EXEC :salary := NULL;

PL/SQL procedure successfully completed.

HR@testdb SQL>
HR@testdb SQL>SELECT employee_id
  2        ,salary
  3  FROM   hr.employees
  4  WHERE (:emp_id IS NULL OR employee_id = :emp_id)
  5  AND   (:salary IS NULL OR salary = :salary);

EMPLOYEE_ID     SALARY
----------- ----------
        198       2600
        199       2600
        200       4400
        201      13000
        202       6000
        203       6500
        204      10000
        205      12000
        206       8300
*.....All Employees.....*
107 rows selected.

HR@testdb SQL>
HR@testdb SQL>EXEC :emp_id := 1000;

PL/SQL procedure successfully completed.

HR@testdb SQL>EXEC :salary := 100000000;

PL/SQL procedure successfully completed.

HR@testdb SQL>
HR@testdb SQL>SELECT employee_id
  2        ,salary
  3  FROM   hr.employees
  4  WHERE (:emp_id IS NULL OR employee_id = :emp_id)
  5  AND   (:salary IS NULL OR salary = :salary);

no rows selected


Thanks,
Aji
Tom Kyte

Followup  

May 23, 2009 - 12:50 pm UTC

now, load your table with 1,000,000 records and see how the performance is.


getting the right answer: good
getting the right answer right now: best


your query will always full scan, it has no real other choice

our goal was to develop and efficient search query given a variable number of inputs.

May 21, 2009 - 6:26 am UTC

Reviewer: Anamika from India

Hi Tom,

Thanks a lot for your excellent approach!

===============================
In response to Query from Aji:
Aji,
If you use this approach,
<<
SELECT employee_id
,salary
FROM hr.employees
WHERE (:emp_id IS NULL OR employee_id = :emp_id)
AND (:salary IS NULL OR salary = :salary)
>>
Oracle optimizer will not be able to generate optimal plan for your query. As you know, your statement is optimized(plan created, saved in shared pool) when its first submitted to Oracle. Afterwards, for subsequent executions, Oracle may reuse the same plan i.e. no hard parsing.
So, when you submit this SQL for the first time (does not matter you provide emp_id or salary or both), Oracle will create a plan which should work for all the possible searches (today,tomorrow and the day after..till the SQL is available in shared pool)..Now, that can only be a 'Full Table Scan'..!!

So, no indexes even if you search using employee_id..

Please refer to : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279


Thanks,
Anamika

"Is this a complete solution? May 19, 2009" Followup

May 24, 2009 - 2:38 am UTC

Reviewer: A reader

When there is no input parameter, the SQL using application context will be much shorter, cleaner, and easier to understand. No?

-- ------------------------------------------------------
-- The new solution
-- ------------------------------------------------------
SELECT ...
FROM   ...
WHERE  (1=1 OR (:variable1 IS NULL)) AND
       (1=1 OR (:variable2 IS NULL)) AND
       (1=1 OR (:variable3 IS NULL)) AND
       ...
       ...
       (1=1 OR (:variable29 IS NULL)) AND
       (1=1 OR (:variable30 IS NULL));


-- ------------------------------------------------------
-- application context
-- ------------------------------------------------------
SELECT ...
FROM   ...;


Tom Kyte

Followup  

May 26, 2009 - 8:36 am UTC

and did you read the article that shows:

SELECT ...
FROM ...
WHERE (1=1 OR (:variable1 IS NULL)) AND
(1=1 OR (:variable2 IS NULL)) AND
(1=1 OR (:variable3 IS NULL)) AND
...
...
(1=1 OR (:variable29 IS NULL)) AND
(1=1 OR (:variable30 IS NULL));


is optimized to

select ... from ...;

????

and the fact is - you are not going to be reading the result sql are you - it is *generated*, you actually NEVER see it.


Also, the code you DO SEE is the code that generated the SQL and guess which one I find easier to read and understand now? No conversions (you have to hide dates in strings, numbers in strings and be careful to convert them back to dates and numbers - then there are timestamps too now)....


Compare:
[old way]
if ( p_hiredate is NOT NULL ) 
then
   dbms_session.set_context
   ( 'MY_CTX', 'HIREDATE',
   to_char(p_hiredate,
              'yyyymmddhh24miss'));
   l_query := l_query ||
    ' and hiredate >
      to_date(
       sys_context( ''MY_CTX'',
                         ''HIREDATE'' ),
             ''yyyymmddhh24miss'') ';
end if;


[new way]
  if ( p_hiredate is NOT NULL )
  then
    l_query := l_query ||
    ' and hiredate > :p_hiredate ';
  else
    l_query := l_query ||
    ' and (1=1 or :p_hiredate is null) ';
  end if;



that is the code you have to read and understand (not so much the resulting entire sql statement)



May 26, 2009 - 6:57 am UTC

Reviewer: A reader

You used sys.ODCIVarchar2List is it by default usese memory for 32767 values? if yes then can we reduce it means specify that i have only 50 values.
Tom Kyte

Followup  

May 26, 2009 - 10:06 am UTC

varray's are upper bounds - NOT the initial amount. They are a limit, not the beginning.

you can create your own type, but it would take the same amount of "memory", varrays are sparse.




ops$tkyte%ORA11GR1> create or replace type myType as varray(50) of varchar2(4000)
  2  /

Type created.

ops$tkyte%ORA11GR1> declare
  2          l_data sys.odcivarchar2list;
  3  begin
  4          select rpad('*',30,'*') bulk collect into l_data
  5            from dual
  6           connect by level <= 50;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> declare
  2          l_data myType;
  3  begin
  4          select rpad('*',30,'*') bulk collect into l_data
  5            from dual
  6           connect by level <= 50;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> @mystat "session pga memory max"
ops$tkyte%ORA11GR1> set echo off

NAME                                               VALUE
--------------------------------------------- ----------
session pga memory max                           1792500

ops$tkyte%ORA11GR1> declare
  2          l_data sys.odcivarchar2list;
  3  begin
  4          select rpad('*',30,'*') bulk collect into l_data
  5            from dual
  6           connect by level <= 50;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> @mystat2
ops$tkyte%ORA11GR1> set echo off

NAME                                               VALUE DIFF
--------------------------------------------- ---------- ------------------
session pga memory max                           1923572          131,072

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> @mystat "session pga memory max"
ops$tkyte%ORA11GR1> set echo off

NAME                                               VALUE
--------------------------------------------- ----------
session pga memory max                           1792500

ops$tkyte%ORA11GR1> declare
  2          l_data myType;
  3  begin
  4          select rpad('*',30,'*') bulk collect into l_data
  5            from dual
  6           connect by level <= 50;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> @mystat2
ops$tkyte%ORA11GR1> set echo off

NAME                                               VALUE DIFF
--------------------------------------------- ---------- ------------------
session pga memory max                           1923572          131,072



notice the lack of differences in pga memory used....

Thanks.....

May 26, 2009 - 8:47 am UTC

Reviewer: Aji from India

Thanks to Tom and Anamika. I should have checked the explain plan.....:-)

comment

June 23, 2009 - 12:35 am UTC

Reviewer: A reader

That's great - but if you have an unknown number of values to match against a column then is doesn't help. That is x = p1 or x = p2 or x = p3 with a unknown number of parameters.

I've always wished oracle would let you dynamically create the "using" bit of a cursor...

Something like:

if p_x is not null then
l_query:= l_query|| and X = :p_x;
l_using:= l_using||','||p_x;

open l_cursor
for l_query
using l_using;
end if;

How much easier that would make life for everyone...are you listening oracle?
Tom Kyte

Followup  

June 26, 2009 - 8:51 am UTC

you can get what you want without using the "technique" you think you want to use.

I fail (entirely and completely) to see how your approach would be easier or something anyone would want. Or even how it would make sense - think about what your resulting statement would be?


sounds like you want a collection, quite easily done/ accommodated for.


ops$tkyte%ORA10GR2> create or replace procedure dyn_query
  2  ( p_cursor in out sys_refcursor,
  3    p_inputs in sys.odcinumberList
  4  )
  5  as
  6          l_query long := 'select * from all_users where 1=1 ';
  7  begin
  8          if ( p_inputs is not null )
  9          then
 10                  l_query := l_query || ' and user_id in (select * from TABLE(cast(:x as sys.odcinumberList))) ';
 11          else
 12                  l_query := l_query || ' and (1=1 or :x is null) ';
 13          end if;
 14          dbms_output.put_line( l_query );
 15          open p_cursor for l_query using p_inputs;
 16  end;
 17  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autoprint on
ops$tkyte%ORA10GR2> variable c refcursor;
ops$tkyte%ORA10GR2> exec dyn_query(:c,null);
select * from all_users where 1=1  and (1=1 or :x is null)

PL/SQL procedure successfully completed.


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
BIG_TABLE                              58 14-DEC-05
DIP                                    19 30-JUN-05
TSMSYS                                 21 30-JUN-05
LOTTOUSER                              65 30-DEC-05
MDDATA                                 50 30-JUN-05
FOO$TKYTE                              60 19-DEC-05
QUOTA                                  94 22-FEB-06
R                                      76 09-JAN-06
RT_TEST                               231 29-OCT-07
...
OUTLN                                  11 30-JUN-05
MGMT_VIEW                              53 30-JUN-05
MY_USER                               211 09-NOV-06

41 rows selected.

ops$tkyte%ORA10GR2> exec dyn_query(:c,sys.odcinumberList( 1,2,3,4,5 ));
select * from all_users where 1=1  and user_id in (select * from TABLE(cast(:x
as sys.odcinumberList)))

PL/SQL procedure successfully completed.


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 30-JUN-05


July 06, 2009 - 7:00 am UTC

Reviewer: snowy from Japan

Tom,


I tried your approach in the article , and it is giving very good performance. Thank you.

Could you please explain why this would be wrong approach.

select * from emp
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);

(1) I am still unable to understand the above.

Let me guess , this query predicate always contains all the colloumns so , no indexes on any colloumns will be used and it will always go for a full table scan scanning each row and all the colloumns.

Is this the reason or is there anything else.

(2)Assuming ename and dept no were not passed ,
Why can't Oracle optimize

select * from emp
where ename = ename
and deptno = deptno
and empno = NVL(:e,empno);

into

select * from emp where empno = NVL(:e,empno);

if it was able to optimize
select * from emp
where (1= 1 or :ENAME IS null)
and and (1=1 or :deptno IS NULL)
and(1=1 or :empno IS NULL);

Am I missing something here?


Tom Kyte

Followup  

July 06, 2009 - 8:20 pm UTC

ould you please explain why this would be wrong approach.

select * from emp 
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);


that always has to use a full scan - think about it, it would be one plan for everything and since none of :n, :d, :e have to have a value (that query doesn't make it so that one of them does) - the only plan that could be would be a full scan.

do you really want to full scan every time....


(2) I have no clue what you mean, where did that query come from? and why does it matter?

More readable approach?

July 08, 2009 - 1:07 pm UTC

Reviewer: Kevin K from St. Louis MO

A minor point, but couldn't the need for the "else" conditions be removed, and the code be further simplified, by starting with a default query of:

'select * from emp
where (1=1 or :p_ename||:p_hiredate||:p_sal is null)'

A further thought: might it be better practice to return a refcursor that contained not only the columns of emp, but also the criteria used to find them, e.g.:

'select :p_ename, :p_hiredate, :p_sal, emp.* from emp'

or even

'select :p_ename||'',''||:p_hiredate||'',''||:p_sal params, emp.* from emp'

This would eliminate the need for '(1=1 or ...)' conditions altogether. Sure, those conditions work out nicely, but they rely on hidden "magic" behavior of the CBO, and thus run a risk of confusing developers down the road.



Tom Kyte

Followup  

July 08, 2009 - 3:55 pm UTC

did you consider attempting your idea? did you see if it actually worked (it does not). You should probably do that (i always do, every time I don't actually test what I propose, I'm wrong)

ops$tkyte%ORA10GR2> declare
  2      p_ename varchar2(30) := 'SCOTT';
  3      l_query long := 'select * from emp where (1=1 or :p_ename is null)';
  4      C sys_refcursor;
  5  begin
  6      if ( p_ename is not null )
  7      then
  8          l_query := l_query || ' and ename = :p_ename ';
  9      end if;
 10
 11      open C for l_query using p_ename;
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 11


ops$tkyte%ORA10GR2> declare
  2      p_ename varchar2(30) := NULL;
  3      l_query long := 'select * from emp where (1=1 or :p_ename is null)';
  4      C sys_refcursor;
  5  begin
  6      if ( p_ename is not null )
  7      then
  8          l_query := l_query || ' and ename = :p_ename ';
  9      end if;
 10
 11      open C for l_query using p_ename;
 12  end;
 13  /

PL/SQL procedure successfully completed.



if you reference :p_ename twice, you need to bind it twice. Binds are purely positional in native dynamic sql here.


... might it be better practice to return a refcursor that
contained not only the columns of emp, but also the criteria used to find them,
.......

I doubt it, the invoker already knows the inputs. and why would you want to repeat this data hundreds or thousands of times in a result set stream from the server to the client?


As for confusion and magic, I don't believe either is true (that there would be confusion - and (1=1 or :x is null) - that is straight boolean logic, you know, I know, any coder should know - that is "TRUE".

And if you know and I know and they know, the optimizer knowing that isn't magic...

Just like our compilers do dead code elimination, loop unrolling, moving redundant assignments outside of a loop, removing unnecessary variables, etc - the optimizer is expected to optimize.

query

July 08, 2009 - 8:30 pm UTC

Reviewer: A reader


My apologies

July 09, 2009 - 3:00 pm UTC

Reviewer: Kevin K from St. Louis MO

Tom,

My apologies for wasting your and your readership's time: I absolutely should have tested the syntax prior to posting. Furthermore, even were the syntax viable, your criticisms are completely valid.

I'm not sure if this will be redemptive, or dig me into the hole deeper, but I think I've got one final (tested!) variant which accomplishes my original goals (simpler code block with no "else" clauses and no "1=1 or..." conditions).



declare
p_ename varchar2(30) := 'SCOTT';
p_hiredate date := sysdate;
l_query long := 'with params as '
|| ' (select :p_ename p_ename, '
|| ' :p_hiredate p_hiredate '
|| ' from dual) '
|| 'select emp.* '
|| 'from emp, params';
l_where_and varchar2(7) := ' where ';
C sys_refcursor;
begin
if ( p_ename is not null )
then
l_query := l_query || l_where_and || 'ename = p_ename';
l_where_and := ' and ';
end if;
if ( p_hiredate is not null )
then
l_query := l_query || l_where_and || 'hiredate > p_hiredate';
l_where_and := ' and ';
end if;
open C for l_query using p_ename, p_hiredate;
end;

Tom Kyte

Followup  

July 14, 2009 - 3:19 pm UTC

I'm not a fan.

First, I don't get why:

"(1=1 or :x is null)"

is deemed "complex" or "hard". It is after all a DYNAMIC SQL STATEMENT, no one is going to read any bit of straight forward code and "see" this statement. They will see a block of code that builds a string and the preface to this block of code is a good old fashioned comment that says "here is what we are about to do folks...."


And this one, with the query on dual and the non-equijoin, can cause the optimizer some degree of heartburn.


where x = :value

when :value is a bind generally is easier to figure out then

where x = params.something



Does it do a full table scan?

July 15, 2009 - 3:11 am UTC

Reviewer: Partha from Singapore

You mentioned:
>>
>>ould you please explain why this would be wrong approach.

>>select * from emp 
>>where ename = NVL(:n,ename)
>>and deptno = NVL(:d,deptno)
>>and empno = NVL(:e,empno);

>>that always has to use a full scan - think about it, it >>would be one plan for everything and since none of :n, >>:d, :e have to have a value (that query doesn't make it >>so that one of them does) - the only plan that could be >>would be a full scan.
>>
>>do you really want to full scan every time.... 

It does not do a full scan every time. If it has a Primary key index, then, there are 2 plans and depending on whether the primary key is selected or not, the appropriate plan is used. See below:

SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production          
PL/SQL Release 11.1.0.6.0 - Production                                          
CORE 11.1.0.6.0 Production                                                      
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production                         
NLSRTL Version 11.1.0.6.0 - Production                                          

SQL> begin dbms_stats.gather_table_stats('SCOTT', 'EMP', CASCADE=>TRUE); END;
  2  /

PL/SQL procedure successfully completed.

SQL> variable x number
SQL> variable y varchar2(30)
SQL> set linesize 100
SQL> set autotrace traceonly explain
SQL> select * from emp where empno = nvl(:x, empno) and ename = nvl(:y, ename);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 1977813858                                                                         
                                                                                                    
----------------------------------------------------------------------------------------            
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT              |        |     2 |    74 |     3   (0)| 00:00:01 |            
|   1 |  CONCATENATION                |        |       |       |            |          |            
|*  2 |   FILTER                      |        |       |       |            |          |            
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     2   (0)| 00:00:01 |            
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |            
|*  5 |   FILTER                      |        |       |       |            |          |            
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |            
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------            
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("ENAME"=NVL(:Y,"ENAME"))                                                              
   4 - filter("EMPNO" IS NOT NULL)                                                                  
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("ENAME"=NVL(:Y,"ENAME"))                                                              
   7 - access("EMPNO"=:X)                                                                           

SQL> variable z number;
SQL> select * from emp where empno = nvl(:x, empno) and ename = nvl(:y, ename) and deptno = nvl(:z, deptno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 1977813858                                                                         
                                                                                                    
----------------------------------------------------------------------------------------            
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT              |        |     2 |    74 |     3   (0)| 00:00:01 |            
|   1 |  CONCATENATION                |        |       |       |            |          |            
|*  2 |   FILTER                      |        |       |       |            |          |            
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     2   (0)| 00:00:01 |            
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |            
|*  5 |   FILTER                      |        |       |       |            |          |            
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |            
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------            
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("ENAME"=NVL(:Y,"ENAME") AND "DEPTNO"=NVL(:Z,"DEPTNO"))                                
   4 - filter("EMPNO" IS NOT NULL)                                                                  
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("ENAME"=NVL(:Y,"ENAME") AND "DEPTNO"=NVL(:Z,"DEPTNO"))                                
   7 - access("EMPNO"=:X)                                                                           

SQL> create index empdept_idx on emp(deptno);

Index created.

SQL> begin dbms_stats.gather_table_stats('SCOTT', 'EMP', CASCADE=>TRUE); END;
  2  /

PL/SQL procedure successfully completed.

SQL> select * from emp where empno = nvl(:x, empno) and ename = nvl(:y, ename) and deptno = nvl(:z, deptno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 1977813858                                                                         
                                                                                                    
----------------------------------------------------------------------------------------            
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT              |        |     2 |    74 |     3   (0)| 00:00:01 |            
|   1 |  CONCATENATION                |        |       |       |            |          |            
|*  2 |   FILTER                      |        |       |       |            |          |            
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     2   (0)| 00:00:01 |            
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |            
|*  5 |   FILTER                      |        |       |       |            |          |            
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |            
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------            
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("ENAME"=NVL(:Y,"ENAME") AND "DEPTNO"=NVL(:Z,"DEPTNO"))                                
   4 - filter("EMPNO" IS NOT NULL)                                                                  
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("ENAME"=NVL(:Y,"ENAME") AND "DEPTNO"=NVL(:Z,"DEPTNO"))                                
   7 - access("EMPNO"=:X)                                                                           

SQL> select * from emp where deptno = nvl(:z, deptno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 916335561                                                                          
                                                                                                    
---------------------------------------------------------------------------------------------       
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
---------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT              |             |    19 |   703 |     4   (0)| 00:00:01 |       
|   1 |  CONCATENATION                |             |       |       |            |          |       
|*  2 |   FILTER                      |             |       |       |            |          |       
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP         |    14 |   518 |     2   (0)| 00:00:01 |       
|*  4 |     INDEX FULL SCAN           | EMPDEPT_IDX |    14 |       |     1   (0)| 00:00:01 |       
|*  5 |   FILTER                      |             |       |       |            |          |       
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP         |     5 |   185 |     2   (0)| 00:00:01 |       
|*  7 |     INDEX RANGE SCAN          | EMPDEPT_IDX |     5 |       |     1   (0)| 00:00:01 |       
---------------------------------------------------------------------------------------------       
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:Z IS NULL)                                                                           
   4 - filter("DEPTNO" IS NOT NULL)                                                                 
   5 - filter(:Z IS NOT NULL)                                                                       
   7 - access("DEPTNO"=:Z)                                                                          

SQL> select * from emp where deptno = nvl(:z, deptno) and empno = nvl(:x, empno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 1977813858                                                                         
                                                                                                    
----------------------------------------------------------------------------------------            
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT              |        |     6 |   222 |     3   (0)| 00:00:01 |            
|   1 |  CONCATENATION                |        |       |       |            |          |            
|*  2 |   FILTER                      |        |       |       |            |          |            
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     5 |   185 |     2   (0)| 00:00:01 |            
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |            
|*  5 |   FILTER                      |        |       |       |            |          |            
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |            
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------            
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("DEPTNO"=NVL(:Z,"DEPTNO"))                                                            
   4 - filter("EMPNO" IS NOT NULL)                                                                  
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("DEPTNO"=NVL(:Z,"DEPTNO"))                                                            
   7 - access("EMPNO"=:X)                                                                           

SQL> select * from emp where deptno = nvl(10, deptno) and empno = nvl(:x, empno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 1977813858                                                                         
                                                                                                    
----------------------------------------------------------------------------------------            
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |            
----------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT              |        |     6 |   222 |     3   (0)| 00:00:01 |            
|   1 |  CONCATENATION                |        |       |       |            |          |            
|*  2 |   FILTER                      |        |       |       |            |          |            
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     5 |   185 |     2   (0)| 00:00:01 |            
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |            
|*  5 |   FILTER                      |        |       |       |            |          |            
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |            
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |            
----------------------------------------------------------------------------------------            
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("DEPTNO"=NVL(10,"DEPTNO"))                                                            
   4 - filter("EMPNO" IS NOT NULL)                                                                  
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("DEPTNO"=NVL(10,"DEPTNO"))                                                            
   7 - access("EMPNO"=:X)                                                                           

SQL> select * from emp where deptno = 10 and empno = nvl(:x, empno);

Execution Plan
----------------------------------------------------------                                          
Plan hash value: 906666078                                                                          
                                                                                                    
---------------------------------------------------------------------------------------------       
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
---------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT              |             |     4 |   148 |     3   (0)| 00:00:01 |       
|   1 |  CONCATENATION                |             |       |       |            |          |       
|*  2 |   FILTER                      |             |       |       |            |          |       
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     3 |   111 |     2   (0)| 00:00:01 |       
|*  4 |     INDEX RANGE SCAN          | EMPDEPT_IDX |     3 |       |     1   (0)| 00:00:01 |       
|*  5 |   FILTER                      |             |       |       |            |          |       
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    37 |     1   (0)| 00:00:01 |       
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP      |     1 |       |     0   (0)| 00:00:01 |       
---------------------------------------------------------------------------------------------       
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(:X IS NULL)                                                                           
   3 - filter("EMPNO" IS NOT NULL)                                                                  
   4 - access("DEPTNO"=10)                                                                          
   5 - filter(:X IS NOT NULL)                                                                       
   6 - filter("DEPTNO"=10)                                                                          
   7 - access("EMPNO"=:X)                                                                           

SQL> spool off


Tom Kyte

Followup  

July 15, 2009 - 12:13 pm UTC

Ok, sure, if you have a NOT NULL column and are doing a single table and the stars align - you have the ability to sometimes get something good.

But suppose you have an index on

a) empno
b) deptno
c) ename

and each returns no more then 3% of the table (making them good candidates to be used)

do you really want this plan:

select * from emp where empno = nvl(:x, empno) and ename = nvl(:y, ename) and deptno = nvl(:z, deptno);

-------------------------------------------------------------------------------- --------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------- --------
|   0 | SELECT STATEMENT              |        |     2 |    74 |     3   (0)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |   |
|*  2 |   FILTER                      |        |       |       |            |   |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |        |       |       |            |   |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------- --------



The subtree "2" is rather a bad idea (not sure why we are doing that, that looks *really* bad)

if I don't supply a primary key - this performs worse than a full scan
and if a supply a primary key - this performs good.


NVL or expansion is a trick that works well with a single column, but not so good with more.


This is not the way to do it.


What I would want is

if i supply empno (regardless of ename/deptno inputs) use the EMP_PK

if i supply ename & deptno but not empno - then pick one of the two (optimizer will choose the one likely the return the least number of hits based on statistics)

if i supply ONLY ename - then pick that index

if i supply ONLY deptn - then pick that index


I/You do not want a single plan for something like this.

We need a real solution please

July 17, 2009 - 4:46 am UTC

Reviewer: Van Heddegem Roeland from Brussels, Belgium

Hi Tom,

You mentioned in the article that this answer is holding the record for being most read.
Congrats for that.

This is your second officialy answer (that I know of) on this question.
Now I'm wondering, do you feel comfortable with this situation? Let me explain...

The answer that you (and by extention Oracle Corp) are given on this question is that we
have to chose between performance and safety.
If we go for performance, we lose typed cursors, we lose the parsing by compilation,
we lose even the readability of our code, ...
If we go for safety, we lose the performance, but what is performance without correctness? Nothing.

What are you saying against those poor souls that are using
"column_x = nvl(p_Param, column_x)" or "(p_Param is null or column_x = p_Param)"?
That they are doing it wrong. That they should choose performance.

I say that there is something wrong with this implementation. If even a man like you,
with tons of experience, have to come with this new solution, after all this years, and
the solution is "slightly better", do you still feel comfortable?

In what direction should you guys go, in my opinion?
I think that we will always need these kind of solutions that you are proposing, but not
on this scale. Only in some rare cases, where we need to change the "select" or "from" statements.
What was the original problem? We have a constant "select" and "from" statement.
So why shouldn't we able to use typed cursors? Why should it be dynamic?

Now for the "where" part.
If we must make such a constructs like your answer, why couldn't the compiler
reevaluated those query's before running them? Problem solved.
If Oracle Corp doesn't like this solution, for whatever reason, make it an option.

So we could achieve multiple goals:
- Clean and readable code: CorrectNess
- Evaluated and checked at compile time: CorrectNess Now and in the Future
- Reevaluated at runtime: Performance
- Safer code
- No code change necessary.

So, what do you think now?

Roeland

Tom Kyte

Followup  

July 24, 2009 - 8:08 am UTC

... Now I'm wondering, do you feel comfortable with this situation?....

I didn't read any further before writing this, the answer is "well, of course, else I would not have written it..."


... have to chose between performance and safety. ...

HOW THE HECK DID YOU COME TO THAT CONCLUSION??


.. we lose even the readability of our code, ... ...

wow, what planet did you arrive from?



Look - you have a requirement:

requirement: develop a query whose parameters are not known until runtime. Forget the implementation for a minute, think about what you are asking for:

Write something that you cannot write until it is actually executed

That demands, that dictates, that necessitates - dynamic sql. When you think about it, virtually everything 3gl interface to the database uses nothing BUT dynamic SQL. People do not use pro*c very often, people do not use SQLJ (the precompiler for Java) - they opt to use APIs (odbc, jdbc, oci, etc). All of the API's - 100% of them - do nothing but dynamic SQL.

PL/SQL is the exception to this rule, plsql allows you to do 99.99999% of your work using what PLSQL calls static SQL - known at runtime - and when necessary, gives you the flexibility to drop down to dynamic SQL.



... What are you saying against those poor souls that are using
"column_x = nvl(p_Param, column_x)" or "(p_Param is null or column_x =
p_Param)"?
That they are doing it wrong. That they should choose performance.


And what pray tell is wrong with that? Would you not like to know "you've done it wrong and if you think about it for a moment, you would understand very clearly why it is that your approach has to be suboptimal"

... If even a man
like you,
with tons of experience, have to come with this new solution, after all this
years, and
the solution is "slightly better", do you still feel comfortable?
....

You can teach an old dog new tricks - and - you should be taking it as a sign of "wow, you should constantly re-evaluate the approaches you have been taking for years - there is an excellent chance your approach of the past is SUBOPTIMAL in light of new information"

I am *constantly* changing my mind over the years - things change, the software changes, stuff happens. And if you think that you should blindly do what you've been doing for years - well, that isn't good.


... So why shouldn't we able to use typed cursors? Why should it be dynamic?

Look at the problem, the PREDICATE by definition is different, your criteria for SELECTING (filtering, where'ing, whatever) is DIFFERENT. Sometimes you want people whose name is like X%, sometimes you want people that make more than $5,000, somethimes..... Every time you run the query, the FILTER is completely different (potentially).


... why couldn't the compiler
reevaluated those query's before running them? ...


ummm, because that would sort of defeat shared sql entirely. You would lose all performance, we'd be back to version 5.1 of Oracle.


I think I'm very pleased with this approach, I think that it is the exception, not the rule. I think that the resulting code (which you would of course comment and document) is not only readable - it is extremely readable, extremely clear, concise even.

We need a real solution please

July 17, 2009 - 4:56 am UTC

Reviewer: Van Heddegem Roeland from Brussels, Belgium

Errata:

Now for the "where" part.
Why couldn't the compiler reevaluate query's like "col_x = nvl(p_parameter_x, col_x)" or "(p_parameter_x is null or col_x = p_parameter_x)" before running them?

That's in my opinion the same as that we are doing it now. But much clearer and cleaner.
Problem solved.
If Oracle Corp doesn't like this solution, for whatever reason, make it an option.

So we could achieve multiple goals:
- Clean and readable code: CorrectNess
- Evaluated and checked at compile time: CorrectNess Now and in the Future
- Reevaluated at runtime: Performance
- Safer code
- No code change necessary.

So, what do you think now?

Roeland
Tom Kyte

Followup  

July 24, 2009 - 8:21 am UTC

... Why couldn't the compiler reevaluate query's like "col_x = nvl(p_parameter_x, col_x)" or "(p_parameter_x is null or col_x = p_parameter_x)" before running them? ....


because it would entirely and utterly and completely defeat shared sql.

We need a real solution please

July 30, 2009 - 4:12 am UTC

Reviewer: Van Heddegem Roeland from Brussels

Hehe, still trying...

...have to chose between performance and safety...
When I compile a procedure/function/package without dynamic sql that's safer than
with dynamic sql. Why?
Because the code is not (spell)checked until runtime. I lose dependencies. (of course
I could still add them, but it's not a necessity anymore.)

.. we lose even the readability of our code, ...
I believe that for writing the predicate static sql code is more readable then dynamic
sql code. Most of the time you need just one line of code compared to 3 - 5 lines.

...requirement: develop a query whose parameters are not known until runtime...
That's not true. We know exactly all the parameters beforehand. What we don't know is
of a given parameter has to be considered or not.
This evaluation has to be postponed until runtime, I completely agree.
But what I'm trying to say is that you are writing code to do the postponing by hand,
while I'm trying to let the database reevaluate the query at runtime.
If some parameters are not to be considered, the database could optimize them away,
and then look for a shared SQL.

...virtually everything 3gl interface to the database uses nothing BUT dynamic SQL...
Correct, but so what?
I don't know for other places, but we are using packages all the time.
So indeed the interface sends a dynamic build piece of code, that is executing some
static written PL/SQL and SQL.
The difference is all this communication is being build by machines (for me that is,
using some purchased software for doing that).
I have not to read it, and maintenance is done by rebuilding the interface.
But we are getting off topic...

...PL/SQL is the exception to this rule, plsql allows you to do 99.99999% of your work
using what PLSQL calls static SQL - known at runtime - and when necessary, gives you
the flexibility to drop down to dynamic SQL...
--> Shouldn't that be ... known at designtime ... ?
I can only speak about my place, but I think that some 10% of my work consists of
writing SQL code where the parameters have to evaluated at runtime.
So, unless I'm doing something terribly wrong, that's a lot more then 0.00001%.

...I am *constantly* changing my mind over the years - things change, the software
changes, stuff happens. And if you think that you should blindly do what you've
been doing for years - well, that isn't good ...
I was not trying to tell that you couldn't change. I was just wondering why you guys
haven't come with a better solution for such a huge problem.
And about me, if I couldn't change, why should I even bother to read your site, or
discuss about it in public?
But I shouldn't started to pray. I'll try to keep it technical ;)...

...because it would entirely and utterly and completely defeat shared sql...
I never said that you have to put the query "as it is" in the shared sql area.
Why couldn't you make a Re-evaluation Area where oracle put in query's that have to be
reevaluated at runtime? (Maybe optionally)
Just my thoughts...

Awaiting your response and enlightment,

Sincerely,

Roeland

Tom Kyte

Followup  

August 03, 2009 - 5:21 pm UTC

sorry, If you want a sql statement whose predicate is not known until runtime, there is precisely and only one way to do it:

dynamic sql. period.


Do I want the optimizer to have to reevaluate the access path every time it executes a query? NO WAY, not a change - zippo, zero, nope, never.


Your requirement is in fact "develop a query whose parameters are not known until runtime..."

You know the possible SET of parameters, you just don't know until runtime if zero, one, two, three, ... or thirty of them will be used this time around. You do NOT know what the parameters are, you only have a finite set of them to consider - but you have no clue what ones will be used.

... while I'm trying to let the database reevaluate the query at runtime....

I will fight you every step of the way on that, I do not want that, I will veto that right out - it goes against the very core of what shared sql is - the one thing that gives us the ability to scale is shared sql. You want a feature from version 5.0 of Oracle - before they rewrote it to.... Scale.

... So, unless I'm doing something terribly wrong, that's a lot more then 0.00001%.....

Unless your job is to write search screens, Yes, I think you must be doing something wrong.







We need a real solution please (Final?)

August 05, 2009 - 5:01 am UTC

Reviewer: Van Heddegem Roeland from Brussels

Hi Tom,

Please calm down, relax and listen. You might think otherwise, but we are getting somewhere.

I'll give you what you want, and at the very same time don't forget myself. Too nice?

1) Your solutions are "notting" but transformations.
With your techniques you are transforming a static query into some other form. In fact your technique can transform EVERY query into this kind of form.
The fun part is that you don't need the SELECT or the FROM clause to do the transformation.
The only thing you need is the PREDICATE (= WHERE clause) and the set of parameters.
This means that you don't have to be SMART to do the transformation.
Even better, I dare to say that when you give me ANY static query without the "select"
and the "where" clause, but with the set of parameters, I can transform this query into one
of your kind of forms and BACK.
This can only mean that they are interchangeable.

2) Why is this so?
Think about if for a second. There is no loss or gain of information.

3) Let the compiler do the job!
If what I say in the previous paragraphs is correct, we can have it both.
I could write a perfectly written static SQL.
If necessary, the compiler could change it at COMPILE TIME using one of your techniques.
So the compiled code is using one of your solutions (if necessary) and I have what I want.

Hope I have convinced you now,

Roeland
Tom Kyte

Followup  

August 05, 2009 - 9:30 am UTC

1) ... Your solutions are "notting" but transformations.
...

No, they are entirely different questions. Don't confuse yourself.

select * from emp where ename = :x
is entirely completely different from
select * from emp
is entirely completely different from
select * from emp where hiredate > :y
is entirely completely different from
select * from emp where ename = :x and hiredate > :y

I see at least 4 different access paths I'd like to be used.


2) huh?? no clue what you mean


3) it (the compiler) does, it takes a statement, a program, and optimizes it statically as best as it can.

Think about this please -

q) how many times do you want to compile something?
a) once

q) if we compile it once, how many access paths would we have?
a) one

q) how many do we want?
a) many


You have not moved me even a millimeter. We are talking at opposite ends of the spectrum here.


Without compiling everytime - your dream cannot be realized.

I will not let you compile everytime.




And you know, this isn't a case of being 'smart', this is a technique - no more so than

"to get the most current record by empno from a table with status_date columns you can:

select * from
(select t.*, row_number() over (partition by empno order by status_date desc)rn from t) where rn = 1;

or you could

select t1.*
from t1, (select empno, max(status_date) status_date from t group by empno) t2
where t1.empno = t2.empno and t1.status_date = t2.status_date;

but you have to code either or and the plans will be very different. One will generally be more efficient than the other (former over latter) but you have to be educated enough to choose the right one - meaning you need to understand how things will be processed, how things work"


We nead a real solution please

August 07, 2009 - 4:52 am UTC

Reviewer: Van Heddegem Roeland from Brussels

1&2) Your solutions are "notting" but transformations.

Parameters:
- p_ename
- p_hiredate

State1: Static SQL
------------------
select * from emp
where (p_ename is null or ename = p_ename)
and (p_hiredate is null or hiredate > p_hiredate);

State2: processed by Tom's new technique
----------------------------------------
mSQL := 'select * from emp';

if p_ename is not null then
mSQL := mSQL || ' where ename = :p_ename';
else
mSQL := mSQL || ' where (1=1 or :p_ename is null)';
end if;

if p_hiredate is not null then
mSQL := mSQL || ' and hiredate > :p_hiredate';
else
mSQL := mSQL || ' and (1=1 or :p_hiredate is null)';
end if;

-----------
What I want to say is that these two states are the same. You can transform from one state
to the other without knowing anything about the database. I did it, although I don't know
anything about table emp.
So this is just a lexical conversion. Something a MACHINE can do (at compile time, ONCE).

Au contraire:
... select * from
(select t.*, row_number() over (partition by empno order by status_date desc)rn from t)
where rn = 1;
and
... select t1.*
from t1, (select empno, max(status_date) status_date from t group by empno) t2
where t1.empno = t2.empno and t1.status_date = t2.status_date;

For this conversion you have to be smart. You have to know a lot. This conversion isn't
simple anymore.
This can't be easily automated.


...sorry, If you want a sql statement whose predicate is not known until runtime, there is precisely and only one way to do it: dynamic sql. period. ...

Sorry, if you want a cpu to execute a programme, there is only one way to do it:

Machine code. Period.

Oops, but we are writing programs in 3GL and 4GL!

... if we compile it once, how many access paths would we have? one ...
Are you saying your compiler doesn't know branching? Really? Didn't knew that.
Serious, and I'll ask it again and again:
Let the COMPILER decide (be it a compiler directive or something else) if the code must be
compiled in state1 (look above) or state2 or stateY. Is this something very hard to do? No.
I have not read even one argument that convinces me that this couldn't be done.

... PL/SQL is the exception to this rule, plsql allows you to do 99.99999% of your work
using static SQL ...
How does it come that this thread is being read the most, year after year after year.
Is this really because some 0.00001% of the work that has to be done?


... only one way to do it: dynamic sql. period. ...
... I will fight you every step of the way on that, I do not want that, I will veto
that right out...
... You have not moved me even a millimeter...
... We are talking at opposite ends of the spectrum here...
... your dream cannot be realized...
... I will not let you ...
At Tom the evangelist:
Could I talk to Tom the technologist, please?
The one who still can dream, who moves around, who don't impose veto's or threads me to fight.
The one who comes with strong arguments.
The one who is happy to think freely.
The one who is willing to cooperate for a better Oracle for all of us.

Please, give the technologist in you a chance!
I can stand losing this debate, on good arguments, but can you?

Roeland
Tom Kyte

Followup  

August 07, 2009 - 10:00 am UTC

they are done before compiling, I am not taking a single query and 'at runtime magically transforming it'

You will not see my point
I will not see yours (I do understand fully with what you are saying, I just totally disagree with it)


I do not want the soft parse to have to rewrite the sql everytime or even anytime. In fact - I do not want the soft parse if possible at all - I just want to bind/execute if I can. But in this case, I will likely soft parse and I don't want that to have to rewrite the query every single time. I will generate the small, finite set of possible querys, each with the optimal plan for the DIFFERENT PREDICATE they all have.



... Something a MACHINE can do (at compile
time, ONCE).
....

Please, look at your example again and tell us what the "one thing done at compile time" would look like (and be efficient). Please do that. And please, generalize it to a 5 table join with many other predicates and aggregation and sorting. Make it general purpose for any query out there - beyond the simple single table example which is trivial.



...sorry, If you want a sql statement whose predicate is not known until
runtime, there is precisely and only one way to do it: dynamic sql. period. ...

Sorry, if you want a cpu to execute a programme, there is only one way to do
it:

Machine code. Period.

Oops, but we are writing programs in 3GL and 4GL!


you utterly lost me with that analogy. It makes no sense whatsoever.

How does the fact that if you do not know your predicate until runtime correspond with machine code and 3gl languages?

There is only one way to run the program on a CPU, to get machine code that can be processed by the CPU. One way to get the machine code is to type in machine code, another way to get machine code is to take a 3gl language and compile it down into machine code. At the end, admit it - you have machine code. I care not a whit your method for getting it.


As the same goes for this problem. The only way to do this is to use dynamic sql. I care not a whit how you get this sql to be dynamic - use whatever approach you like, but if you want the optimal plan based on the predicate the end user decided to build THAT TIME - there you go.


For you see, taking your analogy and turning it around - it actually works to my advantage.

In plsql, using a ref cursor, this is the approach I would take - to fix the number of binds to a constant and let the SQL optimizer optimize away the (1=1 or :x is null) bits.

In plsql, using DBMS_SQL, it would be more like:

if ( p_ename is not null )
then
l_sql := l_sql || ' and ename = :p_ename );
end if;
.... other bits for the other binds

dbms_sql.parse(l_sql)

if (p_ename is not null )
then
dbms_sql.bind (l_sql, 'p_ename', p_ename );
end if;
.... other bits for the other binds


And in java it would look like:

(very much like the dbms_sql, but using java)

and in VB it would look like:

(very much like the java but using VB)

and so on - at the end of the day we have dynamic sql (machine code), but that dynamic sql (machine code) was arrived at in many ways (one day I input machine code directly using hex, the next I used an assembler to get it, then I used C, then I used ....)


... ... if we compile it once, how many access paths would we have? one ...
Are you saying your compiler doesn't know branching? Really? Didn't knew that.
......

It can do some pretty amazing things, and yes, it has tricks like NVL() or expansion - that work on the trivial cases. I am not dealing with the trival here - I am dealing with reality.



I see nothing wrong with the developer developing a query that matches the inputs provided by the end user.

I see everything wrong with making a soft parse even more expensive than it already is.


You and I will have to agree to disagree. It is impossible for us to agree 100% on everything everytime (we are not the same person). I do not see an advantage to making the soft parse even more expensive. I have enough trouble convincing people "soft parse bad, STOP parsing so much" - and anything that makes it more expensive (switch or not, people will switch it on and have the soft parse become more expensive) it a "non-starter" for me.


Just like I hate triggers and wish I could remove them.
And "when others" - wish I could get that out of the language.
And pragma autonomous_transactions - delete them; commit;


I get behind things I think should be done - and I do that all of the time. I fight things I think are a bad idea, heavily.

We need a real solution please!

August 11, 2009 - 4:28 pm UTC

Reviewer: Van Heddegem Roeland from Brussels

... they are done before compiling, I am not taking a single query and 'at runtime
magically transforming it'...
Of course not. But what you do before compiling can the compiler do by compiling (once)

...I will not see yours (I do understand fully with what you are saying, I just totally
disagree with it)...
Maybe you do, maybe you don't. I really don't know by reading your answers.

...I do not want the soft parse to have to rewrite the sql everytime or even anytime.
In fact - I do not want the soft parse if possible at all - I just want to
bind/execute if I can.
But in this case, I will likely soft parse and I don't want that to have to rewrite
the query every single time.
I will generate the small, finite set of possible querys, each with the optimal plan
for the DIFFERENT PREDICATE they all have....
I completely fail to see any difference in what you are doing and what I'm proposing.
Except of course that what you are doing by hand BEFORE compiling, I let the compiler do.
So the resulting code paths are the same.
There's no extra or less work to be done at runtime.

...Please, look at your example again and tell us what the "one thing done at compile
time" would look like (and be efficient)...
It will exactly do YOUR new proposed technique. No more and no less.

...And please, generalize it to a 5 table join with many other predicates
and aggregation and sorting.
Make it general purpose for any query out there - beyond the simple single table
example which is trivial...
I must have totally no imagination. It looks very simple to me.
But I could imagine that you come up with an example where the reality is not that simple.
I would VERY MUCH like to see such an example.

... About my analogy...
The only thing I wanted to make clear, is that it is the compiler who can make this happen.
You are absolutly right, that you need dynamic sql.
But that is not a good argument why the programmer should write it.
I can perfectly write static sql and the compiler should transform it to dynamic sql, if necessary. This process are you now doing by hand.
Let the compliler do it.

...I see everything wrong with making a soft parse even more expensive than it already
is...
It will be as hard as you do it by hand. No more, no less.

...I am not dealing with the trival here - I am dealing with reality...
Allright, give me something then that's not trivial.
Something where you give me the set of parameters and a perfectly written static query
that is so complex that the compiler couldn't transform it following YOUR PROPOSED TECHNIQUE.
If you TOTALLY DISAGREE with me, this shouldn't be too hard, right?

Eagerly awaiting,

Roeland

Give us a REAL solution ASAP!

August 19, 2009 - 7:01 pm UTC

Reviewer: Van Heddegem Roeland from Brussels

So, it seems that you need a lot of time to give us an example.

But not to worry, I'll give an explanation why you -in my opinion- couldn't give me such an example.

If you think about it, if Oracle could compile a static query, it must have analyzed the predicate. Right?
Now what is a predicate? A LOGICAL sequence of CONDITIONS.
What your technique is doing is rewriting some special conditions:
- the special condition: Column_X = nvl(parameter_x, column_x) => could be rewritten as (parameter_x is null or column_x = parameter_x)
- the more general condition: (Parameter_X equals some value OR ...some other conditions)

Is it hard to search and find those conditions. Nope. They could be perfectly defined.
How? It are the conditions who depends on parameters having a special value and being OR'ed.
You don't need to know the structure. Just analyzing the predicate is enough! This is what the compiler already does!

PS: Maybe there are some other more special conditions. But not to worry, leave them alone.
If the compiler could compile these 2, it will have covered almost 100% of the cases.

PPS: Why are aggregations or grouping or sorting making it more or less difficult? Are they making it YOU more difficult while using your technique?

Why am I asking this?

...generalize it to a 5 table join with many other predicates and aggregation and sorting...
Have you EVER used your PROPOSED techniques on this situation yourself?
Have you ever thought about the solution you are proposing in terms of:
- readability
- maintainability
- correctness
- ability to scale? (not at runtime, at designtime!)

So, let's make your example a little harder, lets say you have 100 packages, with in each packages 50 complex queries.
This are 5000 complex queries written in dynamic sql.

If you need to change something in this structure, say break some table in 2 pieces because of some wanted added functionality.
It's in no way an easy job, but in case of dynamic sql, it's almost impossible!

Why?
1) No dependencies. Packages and methods compile happily even if the Dynamic SQL is garbage.

2) Dynamic SQL is ONLY checked at RUNTIME. Testing it makes that you MUST TEST ALL the POSSIBLE CODE PATHS!
For trivial queries that may be doable, for complex ones it will cost AWFUL lots of time.
=> This gives HARD to find bugs, that eventually pop up after some time.
Allthough, these are the not so bad ones...

3) Your new technique works on THE ORDER of PARAMETERS: So if someone just swaps 2 conditions, but forget to swaps the
parameters in the USING (or vice versa) you'll have a PERFECT WORKING, PERFORMANT, ALMOST NOT DETECTABLE, ABSOLUTLY WRONG QUERY, SOME OF THE TIMES.
This are the very, very bad ones...
I find it VERY disturbing that you FAILED to mention this in your article!

Conclusion: If you have more then 20 Queries that needs to be changed for performance, we are left for 2 roads:
- The performant one:
that one day will come back at us WITH WRONG VALUES
- The Not so performant one:
that is maintainable, readable, compact, SEMANTICALLY correct and have dependencies to help staying correct.
Of course we could write wrong static queries, but it is way easier to detect, analyze and correct them then dynamic queries.

If you look at the factor DEVELOPMENT TIME (=MONEY) there's only one that can be chosen. Static queries.
Oh yes, you can have some very rare Dynamic Queries, in those cases when you have to change the FROM-clause.
Otherwise? I have no clue.
Doing it for all the search queries out there is suicide!

You guessed it probably, I want the benefits of the static queries AND the performance of the dynamic ones.
And because I'm confident that the compiler can do it, and you have still not given any valid argument why it shouldn't be possible
or even feasible, I'm expecting this As Soon As Possible!

Oh yes, another thing about my analogy...
I write never in Assembly or in machine code.
So, why should Oracle let the users do the compilation by hand?


Still expecting some feedback, but if that's too hard, maybe you could point me and those other inquiring readers to a place where one can discuss these issues and have some real debate?

Roeland
Tom Kyte

Followup  

August 24, 2009 - 5:04 pm UTC

... So, it seems that you need a lot of time to give us an example. ...

No, I have agreed to disagree, that is all.


You are thinking about very specific simple cases, one column indexes on lots of columns.

I am thinking "in general"


please, come up with the plan for

where x = nvl(:x,x) and y = nvl(:y,y)

where you have an index on (x), (y), (x,y)

now, do it for x,y,z

now, do it for 15 variables

and tell us how big you think that would be?



You are not listening to anything I'm saying - in response to your last paragraph. You are thinking of a very very specific single case. I am thinking "in general"


August 24, 2009 - 5:42 pm UTC

Reviewer: A reader


"MUST TEST ALL the POSSIBLE CODE PATHS"

That's called regression testing. Surely everyone does that, right?


We need a real solution, please

August 26, 2009 - 1:09 am UTC

Reviewer: Van Heddegem Roeland from Brussels

Hi Tom,

I'm glad that you are back.

...You are thinking about very specific simple cases, one column indexes on lots of columns...
No, I'm not.

You seem to think that I'm asking that the compiler should make all the definite code paths at compile time.
That's not what I want.

I want that the compiler use one of your techniques (or similar) to do the break up of a static query at compile time and the constructing of it at runtime.

Ok, I'll try to explain here what you are doing, and what I want to do:

Current situation

A) Developer:
1) Writes Static sql
2) Test static SQL
3) Use TOM's technique for performance, making dynamic sql by hand
B) Compiler:
1) Lexical analyzation
2) ...

Results:

0 complete code paths at compile time
N different constructed dynamic query's at runtime

My Proposition

A) Developer:
1) Writes Static sql
2) Test static SQL
B) Compiler:
1) Lexical analyzation
2) Use TOM's technique for performance (or something similar), making dynamic sql by the compiler
3) (Optional) Make 1 complete code path for the most likely situation (default parameters)
4) ...

Results:

0 complete code paths at compile time
N different constructed dynamic query's at runtime

(Optional)
1 complete code paths at compile time (for the default parameters)
N-1 different constructed dynamic query's at runtime


I hope I made it clear now.

Roeland
Tom Kyte

Followup  

August 26, 2009 - 7:06 pm UTC

I hope I have made clear that we will have to agree to disagree. I do not believe you are correct, you do not believe I am. You are not convincing me, I am not convincing you.

And that is OK, because I would not be writing to the code to do this in the server. I would not be tasking someone to do it. I work with the server as it is, I'll file an enhancement request or whatnot, but I don't change the code inside of it.

I try to show people how to do things, that is all i'm doing here.

And I do not believe your view on this to be viable. That is all.


Your approach would not be feasible in the general case. That is my belief and it would take quite a bit to prove to me I am not accurate in that. Go beyond a single table, small set of columns - using all of the possible combinations and the reordering of joins that would have to take place in the different branches - think about what it would mean to the plan.

sometimes - just sometimes - we have to program ourselves the optimal solution.

1=1

August 27, 2009 - 11:08 am UTC

Reviewer: Developer from PA, USA

Tom,
I read your article. And I think the solution is great to solve the dynamic nature of input parameter values.

I have 2 questions.

1) I understand that 'where (1=1 or :p_ename is NULL);' is better for optimizer than just 'where (:p_ename is NULL);'. Although, if we designed the procedure so that the client cannot pass NULL values for all parameters resulting in a full table scan. We would raise a warning in that case and not process the query. In that case, does it matter to put '1=1' in there?

2) Assuming you can pass 3 input parameters, there would be at most 8 hard parses. Correct?

1 when p1,p2,p3
1 when p1,null,null
1 when p1,p2,null
1 when p1,null,p3
1 when null,p2,null
1 when null,p2,p3
1 when null,null,p3
1 when null,null,null (this can be avoided if we raise an exception).


Tom Kyte

Followup  

August 28, 2009 - 4:54 pm UTC

1) ... In that case, does it matter to put '1=1' in there?
...

yes, that is what lets the optimizer optimize that branch of the predicate out of the plan entirely.

if you leave it out - we do not know until RUNTIME if (:x is null) is true or false, we have to leave it in and consider it. If you (1=1 or :x is null) we know at compile time "that is true" and we optimize it away.


2) yes, if even that many, the fact is, if you have tons of inputs, the real world set of plans is relatively small - not all combinations would be used.

How to get rowcount for the dynamic cursor

August 27, 2009 - 10:48 pm UTC

Reviewer: Amitabha from Albany, NY USA

Tom,
I am using your strategy of using the dynamic query string with bind variables. It is working great. I am pulling data from some million row tables. So I also have to pass the count of the total number of rows in the search result to the front end. Right now I am executing the same query twice once to get the result set and then doing a "Select count(*) from the first query. I am doing this as cursor%rowcount is not working for this type of dynamic query. Do you have any suggestion.
Regards,
Amit
Tom Kyte

Followup  

August 28, 2009 - 5:10 pm UTC

... am pulling data from some million row tables. So I also
have to pass the count of the total number of rows in the search result to the
front end. ...

not possible, no one knows that until the last row is fetched.

My suggest - use google as the gold standard, read some of these:

http://asktom.oracle.com/pls/ask/search?p_string=%22google%22+%22gold+standard%22


getting the count is such an utter waste of resources, change the requirement...

1=1

August 28, 2009 - 5:17 pm UTC

Reviewer: Developer from PA, USA

"If you (1=1 or :x is null) we know at compile time "that is true" and we optimize it away."

1=1 is inside a variable. How can the optimizer SEE at compile time what's inside that variable? It won't be until run-time when the value will become visible. No? I mean it has to evaluate the IF (p_ename is NOT NULL) at run time. Correct?

Tom Kyte

Followup  

August 28, 2009 - 5:25 pm UTC

.. 1=1 is inside a variable. ...

no - it isn't. It is in the where clause of the sql being parsed.

sam

August 29, 2009 - 12:47 pm UTC

Reviewer: A reader

Tom:

when you have a form that allows user to provide inputs like salary, last name, zip code (C1, C2, C3) and then he submits to do a search

Do you usually create one index on each column (C1, C2, C3) or concatentated indexes?

There are many combinations of the dynamic query - actually i think you are always doing it the same all the time using 3 bind variables.


Tom Kyte

Followup  

August 29, 2009 - 7:20 pm UTC

... Do you usually create one index on each column (C1, C2, C3) or concatentated
indexes?
...


sam/smk - you have asked enough questions to know what the answer to that is going to be.

just say it once for me:

it depends


If I am in a data warehouse/reporting system. I would probably have a single column bitmap index on each - so the database can build a new index combining any, all or none of the indexes with or's and and's as needed.

If I am in a system where the underlying data is modified heavily - I might have a b*tree index on last name - any maybe nothing else (why: because I only have 5 salaries and 3 zip codes in my table - you might be different from me)

and so on.



.... actually i think you are
always doing it the same all the time using 3 bind variables.
...

what? I don't know what you mean

We need a real solution, please

September 08, 2009 - 5:10 pm UTC

Reviewer: Roeland Van Heddegem from Brussels

Hi Tom,

... I'll file an enhancement request or whatnot...
Did you file an enhancement request on this topic?
If yes, where?

Roeland
Tom Kyte

Followup  

September 09, 2009 - 6:09 am UTC

You misread me. I wrote:

...
I hope I have made clear that we will have to agree to disagree. I do not believe you are correct, you do not believe I am. You are not convincing me, I am not convincing you.

And that is OK, because I would not be writing to the code to do this in the server. I would not be tasking someone to do it. I work with the server as it is, I'll file an enhancement request or whatnot, but I don't change the code inside of it.

I try to show people how to do things, that is all i'm doing here.
.....


I was simply saying that if I had an idea that I thought had merit to it, I would be filing an enhancement request like anyone else, I do not write the kernel code myself. So it is ok you did not convince me, because I'm not the one that needs to be convinced to have the code written.

No, I did not file and enhancement request - because I personally do not see the merit of doing so. I cannot come up with a succinct business case for this that I believe in.

Follow-up thought - new language feature?

September 09, 2009 - 5:30 pm UTC

Reviewer: Kevin Kirkpatrick from St. Louis, MO

Okay, okay, I'll back down 100% from the readability of '(1=1 or...)'. I'll also grant that, for reasons stated, your solution is superior to my WITH (...) approach - theta joins can indeed be treacherous waters for the CBO (plus, the need to join "params" into every FROM clause throughout a complex query... yuck).

But, I can't shake the feeling that your technique still has an air of "magic" to it:
1) It is not a solution that makes the end-goal immediately apparent to the next developer
2) It is incredibly unlikely that, upon first encountering the need for it, even an expert Oracle developer/DBA/guru would come up with anything like this solution (or a comparably effective solution) ... assuming there are such people who haven't bought your books ;-).

So, I'm going to plod forward, and perhaps we can find a happy middle ground here (or, as has become the popular phrase on this topic, just agree to disagree).

My question: if Oracle added a new language token "BIND" in Oracle version 12, would you consider using / recommending a form such as this in place of your solution:


declare
p_ename varchar2(30) := 'SCOTT';
p_hiredate date := sysdate;
l_query long := 'BIND (p_ename IS :p_ename, p_hiredate IS :p_hiredate)'
|| 'select * '
|| 'from emp '
|| 'where 1=1';
C sys_refcursor;
begin
if ( p_ename is not null ) then
l_query := l_query ||' and ename = p_ename';
end if;
if ( p_hiredate is not null ) then
l_query := l_query || ' and hiredate > p_hiredate';
end if;
open C for l_query using p_ename, p_hiredate;
end;

Note- BIND here would be a pure SQL addition, and would fit into any queries much like WITH(); no syntactic need for a PL/SQL context. So this would be legal:

BIND(my_value is 'y', your_value is 'x')
SELECT * FROM DUAL
WHERE DUMMY=MY_VALUE
OR
DUMMY IN (BIND (our_value is 'z') SELECT our_value from dual union select your_value from dual);

Whether it might have usefulness beyond this niche, I have not really contemplated - which is why I'm not even asking, "do you think it would be a good idea for Oracle to add this feature to Oracle SQL"; just: if it were there, would you use it for this?


Tom Kyte

Followup  

September 14, 2009 - 10:19 am UTC

1) It is not a solution that makes the end-goal immediately apparent to the
next developer



How about this:

/* 
 * This plsql routine does .....
 * It utilizes this technique....
 * The general approach is thus....
 */


Nothing is immediately apparent to the next developer - even if the next developer is you six months later...


2) Note- BIND here would be a pure SQL addition, and would fit into any queries
much like WITH(); no syntactic need for a PL/SQL context. So this would be
legal:


Actually - we did. In 11gr1. You use dbms_sql (which procedurally binds, it does exactly this "bind()" thing ) and then you can "to_refcursor" it using dbms_sql if you need a ref cursor.



September 15, 2009 - 1:39 pm UTC

Reviewer: KC from China

Hi Tom,

... First, the suggested code modification was to build a predicate for each possible bind input that would look like either

1. WHERE column = :bind_variable or

2. WHERE (1=1 or :bind_variable is null) ... (quote On Popularity and Natural Selection)

Just to confirm my understand: this approach is aiming at PL/SQL development, right? There is no point to construct a java preparedstatement in this pattern?

Thanks
Tom Kyte

Followup  

September 15, 2009 - 1:54 pm UTC

... Just to confirm my understand: this approach is aiming at PL/SQL development,...

this is for 11g release 1 and before when you wanted to return a refcursor to a client from a plsql routine that took a set of inputs and only some of the inputs should be used.

in 11g release 2 and above, you would use dbms_sql and to_refcursor (there was a bug that prevented A CLIENT from fetching the refcursor in 11g release 1)

ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> declare
  2          l_cursor number := dbms_sql.open_cursor;
  3          l_refcursor sys_refcursor;
  4          l_data varchar2(30);
  5  begin
  6          dbms_sql.parse
  7          ( l_cursor,
  8           'select username from all_users where rownum <= 5',
  9            dbms_sql.native );
 10          if ( dbms_sql.execute( l_cursor ) != 0 )
 11          then
 12                  raise program_error;
 13          end if;
 14          :x := dbms_sql.to_refcursor(l_cursor);
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM





so, in 11g Release 2 and above, we'll do it like you do it in java, VB, C and whatever - we'll procedurally bind...


so yes, this is aimed at plsql that wants to return a refcursor to a client in 11g Release 1 and before.

fetch

September 19, 2009 - 11:02 am UTC

Reviewer: sam

Tom:

Can't you just do an implicit cursor instead of DBMS_SQL in 11gr2 the way you wrote it?

I thought that was faster than how C, VB do things (i.e prepare, execute, fetch, etc)



Tom Kyte

Followup  

September 28, 2009 - 12:07 pm UTC

you can do what I did in 11gr2 yes, of course. But I don't know what you mean by "implicit cursor"


for x in (select * from t) <<<<=== implicit cursor

select * into l_rec from t where pk = 42; <<<=== implicit cursor

if you do not see open/fetch/close - it is implicit. If you see open/fetch or close - it is an explicit cursor.

And since my example uses "open C for l_query" it is very much an explicit cursor.


And if we used dbms_sql in 11gr2, instead of

if something not null
then
add this
else
add that
end if


we'd be doing

if something not null
then
add this
end if;
....

if something not null
then
bind this
end if;



Dynamic using clause woukld be nice

October 02, 2009 - 3:50 am UTC

Reviewer: BT52 from UK

As someone pointed out earlier...
"That's great - but if you have an unknown number of values to match against a column then is
doesn't help. That is x = p1 or x = p2 or x = p3 with a unknown number of parameters."

I've always wished oracle would let you dynamically create the "using" bit of a cursor...

Something like:

if p_x is not null then

l_query:= l_query|| and X = :p_x;
l_using:= l_using||','||p_x;

open l_cursor
for l_query
using l_using;
end if;
"

I agree that this would be incredibly useful. The solution detailed, as stated several times,relies on a known possible set of parameters.

But what if you don't? This method means that every time you have a new search parameter you have to change the code.

I was using the old context method taking in a collection of parameter,operator,value chosen by the application from a ref data table.
Then I was just adding the "and <parameter> <operator> <value>" accordingly.

So this new method is no good for me because I would need to build the "using" clause dynamically.

What I don't really understand is why this query
SELECT applicants.id
FROM rep_orders,
APPLICANTS
WHERE APPLICANTS.ID = REP_ORDERS.APPL_ID
AND APPLICANTS.NI_NUMBER LIKE sys_context('REP_ORDERS_CONTEXT','PARAM')

gave this explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 3,875
5 MERGE JOIN Cost: 3,875 Bytes: 2,140,216 Cardinality: 82,316
1 INDEX FULL SCAN INDEX TOGDATA.REP_APPL_FK Cost: 26 Bytes: 21,402,173 Cardinality: 1,646,321
4 SORT JOIN Cost: 3,849 Bytes: 758,901 Cardinality: 58,377
3 TABLE ACCESS BY INDEX ROWID TABLE TOGDATA.APPLICANTS Cost: 3,567 Bytes: 758,901 Cardinality: 58,377
2 INDEX RANGE SCAN INDEX (UNIQUE) TOGDATA.APPL_NI_UK Cost: 23 Cardinality: 5,842

whilst this query
SELECT applicants.id
FROM rep_orders,
APPLICANTS
WHERE APPLICANTS.ID = REP_ORDERS.APPL_ID
AND APPLICANTS.NI_NUMBER LIKE :a
gave this plan:
SELECT STATEMENT ALL_ROWSCost: 5
4 NESTED LOOPS Cost: 5 Bytes: 26 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE TOGDATA.APPLICANTS Cost: 4 Bytes: 13 Cardinality: 1
1 INDEX RANGE SCAN INDEX (UNIQUE) TOGDATA.APPL_NI_UK Cost: 3 Cardinality: 1
3 INDEX RANGE SCAN INDEX TOGDATA.REP_APPL_FK Cost: 1 Bytes: 13 Cardinality: 1

Because of the time taken to handle LIKE with the old way I was looking at this new way, but now I'm stuck with a fixed parameter set, so I can see the previous poster's point.

Performance confirmation

February 01, 2010 - 6:19 am UTC

Reviewer: Chris from UK

Hi Tom:

I just want to clarify the point on performance with using the new technique.

I've seen many times through this thread references to beneficial performance - and indeed your excellent article also mentions a "positive impact on performance" on this as well.

As I understand it currently, when comparing the new technique with the "old" (SYS_CONTEXT) approach (and using your EMP example still):

- BOTH techniques would result in, *at most*, 8 hard parses (although you have acknowledged this already in a previous posting to be fair!)
- BOTH techniques would, therefore, generate optimal plans covering all clause combinations (although again, you have mentioned the real-world number of plans would be smaller!).
- BOTH techniques would logically employ soft parsing thereafter.

Therefore, does the only impact on performance come from, as you have already mentioned earlier in the thread, removal of such latch-heavy functions as SYS_CONTEXT?

Or is there some other performance kick I'm missing??

Whilst I agree this technique looks infinitely "cleaner", I just need to justify the possible improvements performance-wise!

Thanks in advance!
Tom Kyte

Followup  

February 01, 2010 - 10:44 am UTC

... Therefore, does the only impact on performance come from, as you have already
mentioned earlier in the thread, removal of such latch-heavy functions as
SYS_CONTEXT?
...

The impacts are:

o no need to get someone to create the context

o no need to worry about type conversions (you have to carefully encode data using sys_context)

o there is some overhead in sys_context when wrapped in a function - versus just getting called directly.

o you will get bind peeking with this approach - not with sys_context. That can be good or bad depending on your perspective (good I think in 11g with adaptive cursor sharing and usually good with 9i and 10g - not meaningful in 8i at all)

natural selection

February 09, 2010 - 1:09 pm UTC

Reviewer: sam

Tom:

I have similar situation to this example except that EMPNAME is EMPNO (number) and the searh criteria can be a string of IN list like (7000,8900,7000) etc.

http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

Can i still use a bind variable for this or not? Can you show how you would modify the if condition for this.
Tom Kyte

Followup  

February 15, 2010 - 2:47 pm UTC

http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html

see "Varying IN Lists"

February 10, 2010 - 11:31 pm UTC

Reviewer: A reader

Would this new approach work well if each variable could have different operators and each variable could potentially be used more than once. For example,

SELECT ...
FROM ...
WHERE col1 = :b1 AND
col2 LIKE :b2 || '%' AND
col3 IN (:b3, :b4, :b5) AND
col4 LIKE '%' || :b6 || '%';

OR

SELECT ...
FROM ...
WHERE col1 = :b1 AND
col2 <> :b2 AND
col3 = :b3 AND
col4 LIKE '%' || :b4 AND
col4 NOT IN (:b5, :b6);

Basically, each variable (column) could be used with various operators. Would the sys_context approach support this kind of requirements better?
Tom Kyte

Followup  

February 16, 2010 - 9:30 am UTC

this would work as well, if not better, than sys_context, yes.



February 16, 2010 - 10:25 am UTC

Reviewer: A reader

In your response to the "February 10, 2010" question, you said that the new approach would work as well if not better than the sys context approach. However, in the http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html article which you wrote about handling varying in lists, the technique makes use of sys context. I'm a bit confused by the conflicting answers. Can you please clarify? thanks.
Tom Kyte

Followup  

February 17, 2010 - 8:02 am UTC

there is no conflict here.

One question was about varying inlists - where one time the query might have 1 thing in the in list, another time it might have 200.

One question was about using binds with the "where 1=1 or" trick versus using sys_context WHEN THE NUMBER OF INPUTS WAS FIXED (as far as I could tell). Seemed like b1..b6 to me.

If the number of binds VARIES - the 1=1 trick cannot work with native dynamic sql.

Returning the cursor to the client

February 18, 2010 - 10:03 am UTC

Reviewer: Russ from Columbus, OH

Tom,

My client software requires that a ref cursor return a record in order to fetch the results. So I can not call a procedure which opens a dynamic cursor according to the article and get the results without additional code.

TYPE p_search_rec IS RECORD (
value_one VARCHAR2,
value_two DATE,
value_three NUMBER
);

TYPE p_search_cur IS REF CURSOR RETURN p_search_rec ;

PROCEDURE p_search( p_cursor IN OUT p_search_cur );

In this situation developers normally populate a global temporary or working table then open a SELECT * ref cursor from that table as a workaround. I'm curious if this is still the best approach.
Tom Kyte

Followup  

February 18, 2010 - 7:20 pm UTC

pipelined functions can do that easily enough.

Instead of:

cursor = f(x,y,z);
or
p(x,y,z,cursor)

to get a result set, your client would simply:

select * from table(f(x,y,z));



search around for pipelined.


weak & strong

February 19, 2010 - 2:07 am UTC

Reviewer: Mihail Bratu

Hi Russ,
Check this:

DECLARE
type refcrsw IS REF CURSOR;
TYPE refcrss IS REF CURSOR RETURN emp%ROWTYPE;
q_weak refcrsw;
q_strong refcrss;

...


BEGIN
OPEN q_weak FOR SELECT * FROM emp;
q_strong := q_weak;

...

Regards

Pipelined changes everything

February 19, 2010 - 3:04 pm UTC

Reviewer: Russ from Columbus, OH

What a tremendous improvement. By applying some simple structure changes I was able to quickly recode a stored procedure into a pipelined function and view the result set in my client tool.

Just checked the Sybase site and the document explaining how to retrieve a result set from an Oracle procedure was last updated in 1999.

April 16, 2010 - 10:46 am UTC

Reviewer: Joe from NY

Tom,

Regarding the below code for dynamic sql and bind variables:

  if (p_hiredate is not null) then
    l_query := l_query || ‘ and hiredate > :p_hiredate ‘;
  else
    l_query := l_query || ‘ and (1 = 1 or :p_hiredate is null) ‘;
  end if;


A co-worker questioned that when the parameter (p_hiredate)is null, the resulting query would be
 and (1 = 1 or null is null)

What is the purpose of the "1 = 1" when the "null is null" will return true anyway.

Why can't we just use ":p_hiredate is null" and get rid of the 1=1? Is there any benefit of having the extra "1 = 1". Wouldn't just having "null is null" equally optimized?

Thanks,
Joe


Tom Kyte

Followup  

April 16, 2010 - 11:34 am UTC

(1=1 or ? is null)

at compile time, before the binds take place, what does that evaluate to? It evaluates as TRUE and can be optimized away.


(? is null)

at compile time, before the binds take place, what does that evaluate to? Either true or false - but we don't know, therefore it has to be in the plan.


If we use 1=1 we can optimize it away completely.

What's wrong with this Approach?

May 11, 2010 - 6:12 pm UTC

Reviewer: Charles from Seattle Wa.

Tom, I use the 1=1 or :bind is null technique often, and to great success, thank you.

Sorry to rehash an old debate, but looking at this thread I can see viewers asking whats wrong with the following approach...

select * from emp
where ename = NVL(:n,ename)
and deptno = NVL(:d,deptno)
and empno = NVL(:e,empno);

Your answer was that would lead to a full table scan every time.

Wouldn't using the nvl approach give you the wrong answer regardless of a full table scan? What if :n is null and scott.ename is null? I believe the technique you describe would handle this scenario whereas the nvl(:bind, column_name) would not.

Link doesn't work anymore...

July 08, 2010 - 11:26 am UTC

Reviewer: Nicosa from Paris, France

Hi Tom,

I remember I read the article you pointed in the first followup on this page... but the link seems not to work anymore : http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D236902990854
8088969&p_cat=JA09_ask%20tom_to_tom.pdf&p_company=822925097021874

I can't find the article about the "new" technique you explained for the "generic where clause".
(I kinda remember you said it would outpast the old "context" method...)
Tom Kyte

Followup  

July 08, 2010 - 1:18 pm UTC

that link works still - i just clicked it.

http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

is the online print view.

Forget my previous post !

July 08, 2010 - 11:32 am UTC

Reviewer: Nicosa from Paris, France

Ok,

Opening my eyes and setting up my brain to ON, I just find it : http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

All my appologizes for polluting this thread.

What about update

October 18, 2010 - 3:00 pm UTC

Reviewer: Stephen from Utah

Can Native Dynamic Sql be used to update or insert generically like this? In other words, can I have a procedure that could take in an emp_ref and name, sal, hiredate and update only those that are not null?

Example:
BEGIN
...
update_emp(v_emp_no, v_name, null, null);
...
END;

Then in procedure update_emp it would dynamically create an update statement like the following:

update emp
set name = v_name
where emp_no = v_emp_no;

OR if I did
BEGIN
...
update_emp(v_emp_no, v_name, v_sal, null);
...
END;

Then in procedure update_emp it would dynamically create an update statement like the following:

update emp
set name = v_name,
sal = v_sal
where emp_no = v_emp_no

Can Native Dynamic Sql do this?
Tom Kyte

Followup  

October 25, 2010 - 10:03 am UTC

just

update emp 
   set name = nvl(v_name,name),
       sal = nvl(v_sal,sal),
       whatever = nvl(v_whatever,whatever)
 where empno_no = v_empno;



No dynamic sql, just a single static sql statement.

Another approach

October 25, 2010 - 4:54 am UTC

Reviewer: Antonio from Portugal

Hi Tom,

Why not just use *allways*:

(...) and (:col1 is null or col1 = :col1) (...)

and getting only 1 query plan for the all dynamic query instead of testing in PL/SQL for bind variable values and come to 2 different query plans for each bind variable used:
(...) and (col1 = :col1)
(...) and (1=1 or :col1 is null)

The disadvantage is we have to bind each variable 2 times in the "using" clause (doesn't Oracle have a way to match parameters with variables like "col1 => :col1" ??).

Thanks
Tom Kyte

Followup  

October 25, 2010 - 6:02 pm UTC

... and getting only 1 query plan for the all dynamic query ...

that is EXACTLY and PRECISELY why you want to do it the ways described above.

so that you get MORE THAN one plan. The one plan you generate would almost certainly never involve an index - ever - since we might not supply any given column at any given time.


by the way, we do support "col1=>something"

ops$tkyte%ORA10GR2> declare
  2          cursor c( x in varchar2 )
  3          is
  4          select *
  5            from all_users
  6           where username = x
  7              or to_char(user_id) = x
  8                  or to_char(created,'dd-mon-yyyy') = x;
  9  begin
 10          open c( x=> 'SCOTT' );
 11  end;
 12  /

PL/SQL procedure successfully completed.

Natural selection

September 14, 2011 - 9:50 am UTC

Reviewer: sam

Tom:

Great artilce in oracle magazine above for implemeting sql for generic search.

two questions:

1) What is the most effective index or indexes to create for that example

since there are 3 parameters "EAME, HIREDATE, SAL" and many different combination of those, how do you determine the number and structure of indees required here.

2) What is the equivaluent statement in 9i for getting the plan printed at the end of the query.

(select plan_Table_output from table(dbms_xplan.display_cursor) (only works in 10g and above)
Tom Kyte

Followup  

September 15, 2011 - 6:04 am UTC

1) it depends. Are they selective enough to be indexed?

Probably just three indexes, one that starts with ename, one with hiredate, one with sal. They could include some or all of the other columns after that (so as to avoid going to the table when you use two or more columns to evaluate the filter)

2) there isn't one directly. You'd have to query v$sql_plan and v$sql to find your query.

December 06, 2011 - 3:09 pm UTC

Reviewer: A reader

Hi Tom,

you said above in one of the review -

"(1=1 or ? is null)

at compile time, before the binds take place, what does that evaluate to? It evaluates as TRUE and can be optimized away.


(? is null)

at compile time, before the binds take place, what does that evaluate to? Either true or false - but we don't know, therefore it has to be in the plan. "


"OPTIMIZED AWAY" in first case AND "HAS TO BE IN THE PLAN" in second case

Can you please explain about this bit more ?
what do you meant by 'it has to be in the plan'?

Regards

Tom Kyte

Followup  

December 07, 2011 - 1:02 pm UTC

ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from dual where (1=1 or :x is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

ops$tkyte%ORA11GR2> select * from dual where (:x is null);

Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:X IS NULL)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 



notice in the above that when you use (1=1 or ? is null) the optimizer knows that is 'true' and does not have a filter step.

but when you use (? is null), the optimizer DOES NOT know that is 'true' until rntime and hence would have to filter that.


the optimizer removes (1=1 or ? is null) from the query altogether , as if it were not there.

Multiple conditions are combined using logical operators

April 27, 2012 - 4:56 am UTC

Reviewer: amol k from India

Hi Tom,
we are building a some kind of the rule engine wherein user can provide the search criteria on two fields
1. Location 2. Gender
The use of logical operators AND, OR are allowed.
In effect user may provide the condition like

(Location = xyz and gender = Male) or (Location = abc and gender = Female)

How to make use of bind variables in the condition like above as more than one value is appearing for Location column?
With Warm Regards,
Amol K





Tom Kyte

Followup  

April 27, 2012 - 8:16 am UTC

depending on the API you use (you, well, didn't care to share that with us...) you either bind by name or by position.


if you bind by name, you'd use different names:

(location = :loc1 and gender = :gen1) or (location = :loc2 and gender = :gen2)


if you bind by position, you'd just reference the i'th bind position - 1, 2, 3, 4

(location=? and gender=?) or (location=? and gender=?)


That said, you might want to do something like this:


where (location,gender) in (select * from table(:x))


and just bind in a single collection

create type myScalarType as object (loc varchar2(10), gender varchar2(10) );
create type myTableType as table of myScalarType;

and then bind in a single value of myTableType....

Thanks for Help: Multiple conditions are combined using logical operators

April 27, 2012 - 9:32 pm UTC

Reviewer: amol k from India

Hi Tom,
Thanks for the quick reply. I didnt share API cause I am asked to design it. I have a option of doing it in the application or database. I want to do it in the database. I think the approach given by
where (location,gender) in (select * from table(:x))
will work perfect.
User can defined the criteria however complex it is, I can transform it to pairs !!! and then i can apply it as a filter.

Thanks ....

bad plan?

May 31, 2012 - 10:59 am UTC

Reviewer: Will from UK

Hi Tom

From one of the previous posts :

"Why not just use *allways*:

(...) and (:col1 is null or col1 = :col1) (...)"

Please can you explain in more detail why this is not a good method? You mention indexes and the optimiser only being able to use one plan, why is that? please add more detail!

So are you saying the preferred method to this "static" sql is to either use dynamic sql with sys_context or use and (1=1 or :col1 IS NULL) method etc


I am thinking of the case of a stored proc which is something like:

create procedure p1( p_search in varchar2,
cur in out sys_refcursor)
as
begin
open cur as
select *
from tab
where (:p_search is null or col1 = :p_search);
end;
/

Is this not a good method??

Thanks
Tom Kyte

Followup  

May 31, 2012 - 11:23 am UTC

if you say:

where (:col1 is null or col1 = :col1 )
  and (:col2 is null or col2 = :col2 )


and you have an index I1 on (col1) and an index I2 on (col2)

please tell me how we could come up with a plan that would

o use I1 when :col1 was not null and :col2 was null
o use I2 when :col1 was null and :col2 was not null
o use neither I1 nor I2 (full scan) when :col1 and :col2 were null
o decode between using either of I1 or I2 when :col1 and :col2 where not null

Now, take that plan and make it work for 3 binds, 4 binds, etc (you get LOTS of combinations don't you .... imagine....)


begin
    open cur as
    select *
    from tab
    where (:p_search is null or col1 = :p_search);
end;


is this *probably* ok if you can use NVL instead:

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create index t_ix on t(username);

Index created.

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x varchar2(30)
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where (:x is null or username = :x);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 59500 |  2266K| 27124   (1)| 00:05:26 |
|*  1 |  TABLE ACCESS FULL| T    | 59500 |  2266K| 27124   (1)| 00:05:26 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:X IS NULL OR "USERNAME"=:X)

ops$tkyte%ORA11GR2> select * from t where username = nvl(:x,username);

Execution Plan
----------------------------------------------------------
Plan hash value: 3946961226

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      | 60000 |  2285K|     4   (0)| 00:00:01 |
|   1 |  CONCATENATION                |      |       |       |            |          |
|*  2 |   FILTER                      |      |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    | 50000 |  1904K|     2   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | T_IX |  1000K|       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |      |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T    | 10000 |   380K|     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | T_IX |  4000 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X IS NULL)
   4 - filter("USERNAME" IS NOT NULL)
   5 - filter(:X IS NOT NULL)
   7 - access("USERNAME"=:X)

ops$tkyte%ORA11GR2> set autotrace off



that uses a bit of magic called NVL or expansion - but it works for just about ONE column...

small tables

July 19, 2012 - 3:31 am UTC

Reviewer: dxl from UK

I was just reviewing your response above. (Thanks for the info).

I understand that the approach will not perform with large amounts of data as any execution plan could not utilise indexes in all cases.
However are we just talking about performance here?
If we have tables with small amounts of data eg lookup tables then is the original approach a valid one?

So if your goal is ease of use and to write the minimium amount of code to satisfy multiple filters (as required by a website form) then is this approach ok?:

create procedure p1( p_search1 in varchar2,
p_search2 in varchar2,
..
..
cur in out sys_refcursor)
as
begin
open cur as
select *
from tab
where (:p_search1 is null or col1 = :p_search1)
and (:p_search2 is null or col1 = :p_search2)
and (:p_search3 is null or col1 = :p_search3)
and (:p_search4 is null or col1 = :p_search4)
and (:p_search5 is null or col1 = :p_search5)
.. etc
end;
/


For small tables, is that better than:

1. doing it in a dynamic sql way using sys_contexts etc
OR
2. the method of :
where (1=1 or :COLUMN_NAME1 is null) and ( column_name2 = :COLUMN_NAME2 )

This may come down to personal preference but I was wondering if there is any technical reason to pick one method above the others (in the case of small tables, performance not an issue).

Thanks
Tom Kyte

Followup  

July 19, 2012 - 12:14 pm UTC

So if your goal is ease of use and to write the minimium amount of code to
satisfy multiple filters (as required by a website form) then is this approach
ok?:


if your goal is to make it as easy as possible for the programmer, yes, that would be find. Just be aware that you will full scan every single time. If that is OK, then it is OK.


typo

July 19, 2012 - 3:33 am UTC

Reviewer: dxl from UK

Apolgies the select statement in my previous post should've read

select *
from tab
where (:p_search1 is null or col1 = :p_search1)
and (:p_search2 is null or col2 = :p_search2)
and (:p_search3 is null or col3 = :p_search3)
and (:p_search4 is null or col4 = :p_search4)
and (:p_search5 is null or col5 = :p_search5)
.. etc

generic query

May 22, 2013 - 3:23 am UTC

Reviewer: A reader


sys_refcursor

May 22, 2013 - 4:13 am UTC

Reviewer: A reader

Tom:

I have a stored procedure that runs fine in Oracle 9iR2 database (WE8ISO8859P1) and 10g http server(apache 1.3/mod_plsql).
The SP creates a web page using HTP.P package.

It uses this generic query method and SYS_REFCURSOR per your article here.

http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D236902990854
8088969&p_cat=JA09_ask%20tom_to_tom.pdf&p_company=822925097021874


When I migrated this to Oracle 11.2.0.3 (UTF-8/RHEL) and 11g http server (apache 2.2) I get the following internal error


Failed to execute target procedure ORA-00600: internal error code, arguments: [opixrb-3], [1036], [ORA-01036: illegal variable name/number
], [], [], [], [], [], [], [], [], []
ORA-06512: at "INVOICES", line 239
ORA-06512: at line 33

Are there any known bugs using SYS_REFCURSOR in 11g R2? It seems it fails when the bind variables do not have a value if the query is for all set.


declare

l_cursor sys_refcursor;

l_query varchar2(512) default 'SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media,
a.invoice_date, a.total_amount, a.status_code
FROM invoice@db_link a WHERE a.status_code = ''P'' ';


cursor l_template is select a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media,
a.invoice_date, a.total_amount, a.status_code
from invoice@db_link a;

begin

if statements....

open l_cursor for l_query using l_vendcode, l_cntr; /* Line 239 */



/* This is the L_QUERY variable when I print it */

SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media, a.invoice_date, a.total_amount, a.status_code
FROM invoice@db_link a
WHERE a.status_code = 'P' and (1=1 or :l_vendcode is null) and (1=1 or :l_cntr is null)
ORDER BY a.invoice_date, a.vendcode


Tom Kyte

Followup  

May 22, 2013 - 1:39 pm UTC

please utilize support for all ora-600, ora-7445, ora-3113 type of errors. They indicate a bug.

you'd have to supply a fully reproducible test case for me to even look at it - and even then, all I'd be able to do is open a bug (which works better if you open it and tie it to an active SR)


Just run this in sqlplus:

variable l_vendcode number 
variable l_cntr number

SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media, 
a.invoice_date, a.total_amount, a.status_code 
FROM invoice@db_link a 
WHERE a.status_code = 'P' and (1=1 or :l_vendcode is null) and (1=1 or :l_cntr 
is null) 
ORDER BY a.invoice_date, a.vendcode 
/




I guessed at the datatype for the variable definition - fix it if not correct. That should fail as well - and then you have a very very very simple test case.

short of that, use:

variable x refcursor

declare 
   l_vendcode number;
   l_cntr number;
begin
  open :x for q'|SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, 
                        a.invoice_media, a.invoice_date, a.total_amount, a.status_code 
                   FROM invoice@db_link a 
                  WHERE a.status_code = 'P' 
                    and (1=1 or :l_vendcode is null) 
                    and (1=1 or :l_cntr is null) 
                  ORDER BY a.invoice_date, a.vendcode|' 
    using l_vendcode, l_cntr;
end;
/
print x



just to simplify the example down to it's bare minimum for working with support.


In my 11.2.0.3 I cannot reproduce:

ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace
  2  procedure my_new_procedure
  3  ( p_ename    in varchar2 default NULL,
  4    p_hiredate in date     default NULL,
  5    p_sal      in number   default NULL,
  6    p_cur      in out sys_refcursor )
  7  as
  8    l_query varchar2(512)
  9    default 'select * from emp';
 10  begin
 11   if (p_ename is NOT NULL)
 12   then
 13          l_query := l_query || ' where ename like ''%''||:p_ename||''%'' ';
 14   else
 15      l_query := l_query || ' where (1=1 or :p_ename is null) ';
 16   end if;
 17  
 18   if ( p_hiredate is NOT NULL )
 19   then
 20      l_query := l_query || ' and hiredate > :p_hiredate ';
 21   else
 22      l_query := l_query || ' and (1=1 or :p_hiredate is null) ';
 23   end if;
 24  
 25   if ( p_sal is NOT NULL )
 26   then
 27      l_query := l_query || ' and sal > :p_sal ';
 28   else
 29      l_query := l_query || ' and (1=1 or :p_sal is null) ';
 30   end if;
 31  
 32   dbms_output.put_line( l_query );
 33   open p_cur
 34    for l_query
 35   using p_ename,
 36         p_hiredate,
 37         p_sal;
 38  
 39  end;
 40  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable c refcursor
ops$tkyte%ORA11GR2> exec my_new_procedure( null, null, null, :c );
select * from emp where (1=1 or :p_ename is null)  and (1=1 or :p_hiredate is null)  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 09-DEC-82       4500                    20
      7369 SMITH      CLERK           7902 17-DEC-80       1200                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       2400        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1875        500         30
      7566 JONES      MANAGER         7839 02-APR-81     4462.5                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1875       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       4275                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       3675                    10
      7839 KING       PRESIDENT            17-NOV-81       7500                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       2250          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1650                    20
      7900 JAMES      CLERK           7698 03-DEC-81       1425                    30
      7902 FORD       ANALYST         7566 03-DEC-81       4500                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1950                    10

14 rows selected.

ops$tkyte%ORA11GR2> exec my_new_procedure( 'a', null, null, :c );
select * from emp where ename like '%'||:p_ename||'%'  and (1=1 or :p_hiredate is null)  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( null, sysdate, null, :c );
select * from emp where (1=1 or :p_ename is null)  and hiredate > :p_hiredate  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( null, null, 10000, :c );
select * from emp where (1=1 or :p_ename is null)  and (1=1 or :p_hiredate is null)  and sal > :p_sal

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( null, sysdate, 10000, :c );
select * from emp where (1=1 or :p_ename is null)  and hiredate > :p_hiredate  and sal > :p_sal

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( 'a', sysdate, null, :c );
select * from emp where ename like '%'||:p_ename||'%'  and hiredate > :p_hiredate  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( 'a', null, 10000, :c );
select * from emp where ename like '%'||:p_ename||'%'  and (1=1 or :p_hiredate is null)  and sal > :p_sal

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

ops$tkyte%ORA11GR2> exec my_new_procedure( 'a', sysdate, 10000, :c );
select * from emp where ename like '%'||:p_ename||'%'  and hiredate > :p_hiredate  and sal > :p_sal

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

no rows selected

generic query

May 22, 2013 - 5:24 pm UTC

Reviewer: A reader

Tom:

Excellent example and information.

Take a look below. Your example worked perfectly fine.

Then I changed your procedure to select from EMP over a db link and the problem came up.

can you change your "my_new_procedure" to select EMP from another database and see if it works.




SQL> variable l_vendcode  varchar2(4);
SQL> variable l_cntr  varchar2(7);

SQL> SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media,
             a.invoice_date, a.total_amount, a.status_code 
  2   FROM invoice@db_link a 
  3   WHERE a.status_code = 'P' and (1=1 or :l_vendcode is null) and (1=1 or :l_cntr is null) 
  4   ORDER BY a.invoice_date, a.vendcode ;
 ORDER BY a.invoice_date, a.vendcode
                      *
ERROR at line 4:
ORA-00600: internal error code, arguments: [opixrb-4], [1036], [ORA-01036:
illegal variable name/number
], [], [], [], [], [], [], [], [], []


SQL> variable x refcursor;
SQL> 
SQL> declare
 2     l_vendcode  varchar2(4);
 3     l_cntr  varchar2(7);
 4  begin
 5    open :x for q'|SELECT a.invoice_no, a.fy, a.vendcode, a.cntr, a.invoice_type, a.invoice_media, a.invoice_date,
 6                       a.total_amount, a.status_code 
 7                 FROM invoice@db_link a 
 8                 WHERE a.status_code = 'P' and (1=1 or :l_vendcode is null) and (1=1 or :l_cntr i
l) 
 9                 ORDER BY a.invoice_date, a.vendcode|'
10                 using l_vendcode, l_cntr;
11    end;
12  /
declare

ERROR at line 1:
ORA-00600: internal error code, arguments: [opixrb-3], [1036], [ORA-01036:
illegal variable name/number
, [], [], [], [], [], [], [], [], []
RA-06512: at line 5


SQL11g> create or replace
  2   procedure my_new_procedure
  3   ( p_ename    in varchar2 default NULL,
  4     p_hiredate in date     default NULL,
  5     p_sal      in number   default NULL,
  6     p_cur      in out sys_refcursor )
  7   as
  8     l_query varchar2(512)
  9     default 'select * from emp';
 10     begin
 11     if (p_ename is NOT NULL)
 12     then
 13           l_query := l_query || ' where ename like ''%''||:p_ename||''%'' ';
 14     else
 15        l_query := l_query || ' where (1=1 or :p_ename is null) ';
 16     end if;
 17    
 18     if ( p_hiredate is NOT NULL )
 19     then
 20        l_query := l_query || ' and hiredate > :p_hiredate ';
 21     else
 22        l_query := l_query || ' and (1=1 or :p_hiredate is null) ';
 23     end if;
 24    
 25    if ( p_sal is NOT NULL )
 26     then
 27        l_query := l_query || ' and sal > :p_sal ';
 28     else
 29        l_query := l_query || ' and (1=1 or :p_sal is null) ';
 30     end if;
 31    
 32    dbms_output.put_line( l_query );
 33     open p_cur
 34     for l_query
 35     using p_ename,
 36           p_hiredate,
 37           p_sal;
 38    
 39   end;
 40  /

Procedure created.

SQL11g> variable c refcursor
SQL11g> exec my_new_procedure( null, null, null, :c );
select * from emp where (1=1 or :p_ename is null)  and (1=1 or :p_hiredate is
null)  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.


SQL> print c

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.


SQL> exec my_new_procedure( 'a', null, null, :c );
select * from emp where ename like '%'||:p_ename||'%'  and (1=1 or :p_hiredate
is null)  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

SQL> exec my_new_procedure( null, sysdate, null, :c );
select * from emp where (1=1 or :p_ename is null)  and hiredate > :p_hiredate
and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.
SQL> print c

no rows selected

SQL> exec my_new_procedure( null, null, 10000, :c);
select * from emp where (1=1 or :p_ename is null)  and (1=1 or :p_hiredate is
null)  and sal > :p_sal

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

SQL> exec my_new_procedure( null, sysdate, 10000, :c);
select * from emp where (1=1 or :p_ename is null)  and hiredate > :p_hiredate
and sal > :p_sal

PL/SQL procedure successfully completed.

SQL> exec my_new_procedure( 'a', sysdate, null, :c);
select * from emp where ename like '%'||:p_ename||'%'  and hiredate >
:p_hiredate  and (1=1 or :p_sal is null)

PL/SQL procedure successfully completed.

SQL> exec my_new_procedure( 'a', null, 1000, :c);
select * from emp where ename like '%'||:p_ename||'%'  and (1=1 or :p_hiredate
is null)  and sal > :p_sal

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

SQL> exec my_new_procedure( 'a', sysdate, 10000, :c);
select * from emp where ename like '%'||:p_ename||'%'  and hiredate >
:p_hiredate  and sal > :p_sal

PL/SQL procedure successfully completed.

SQL> print c

no rows selected



-- I changed my_new_procedure to select EMP table over a DB link
SQL> variable c refcursor;
SQL> exec my_new_procedure( null, null, null, :c );
select * from emp@db_link where (1=1 or :p_ename is null)  and (1=1 or :p_hiredate is null)  and (1
BEGIN my_new_procedure( null, null, null, :c ); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [opixrb-3], [1036], [ORA-01036: illegal variable name/num
], [], [], [], [], [], [], [], [], []
ORA-06512: at "MY_NEW_PROCEDURE", line 32
ORA-06512: at line 1

Tom Kyte

Followup  

May 22, 2013 - 6:48 pm UTC

confirmed, here is the simplified example to reproduce:



ops$tkyte%ORA11GR2> variable x varchar2(30);
ops$tkyte%ORA11GR2> select * from dual@ora11gr2@loopback where (1=1 or :x is null);
select * from dual@ora11gr2@loopback where (1=1 or :x is null)
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [opixrb-4], [1036], [ORA-01036:
illegal variable name/number
], [], [], [], [], [], [], [], [], []


ops$tkyte%ORA11GR2> 



I've created bug #16849637 "ORA-600 OPIXRB-4 ORA-1036 WHEN USING DBLINK AND OPTIMIZER REMOVES BIND FROM PRED" for this.

Please open a SR and reference this bug (you won't be able to see the bug until you take it over via the SR)


A workaround for you that gets you going this afternoon would be to utilize this code in 11g (it uses new 11g APIs for dbms_sql)

ops$tkyte%ORA11GR2> create or replace
  2  procedure my_new_procedure
  3  ( p_ename    in varchar2 default NULL,
  4    p_hiredate in date     default NULL,
  5    p_sal      in number   default NULL,
  6    p_cur      in out sys_refcursor )
  7  as
  8      l_theCursor integer default dbms_sql.open_cursor;
  9      l_status    number;
 10      l_query     varchar2(512) default 'select * from emp@ora11gr2@loopback where 1=1';
 11  begin
 12      if (p_ename is NOT NULL)
 13      then
 14         l_query := l_query || ' and ename like ''%''||:p_ename||''%'' ';
 15      end if;
 16      if ( p_hiredate is NOT NULL )
 17      then
 18         l_query := l_query || ' and hiredate > :p_hiredate ';
 19      end if;
 20      if ( p_sal is NOT NULL )
 21      then
 22         l_query := l_query || ' and sal > :p_sal ';
 23      end if;
 24  
 25      dbms_output.put_line( l_query );
 26      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 27  
 28      if (p_ename is NOT NULL)
 29      then
 30          dbms_sql.bind_variable( l_theCursor, ':p_ename', p_ename );
 31      end if;
 32      if (p_hiredate is NOT NULL)
 33      then
 34          dbms_sql.bind_variable( l_theCursor, ':p_hiredate', p_hiredate );
 35      end if;
 36      if (p_sal is NOT NULL)
 37      then
 38          dbms_sql.bind_variable( l_theCursor, ':p_sal', p_sal );
 39      end if;
 40  
 41      l_status := dbms_sql.execute(l_theCursor);
 42      p_cur := dbms_sql.to_refcursor( l_theCursor );
 43  end;
 44  /

Procedure created.



if you want a single procedure for all releases, please see conditional compilation:
http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46plsql-096385.html


generic query

May 22, 2013 - 9:30 pm UTC

Reviewer: A reader

Tom:

thanks for the confirmation.

When I open an SR and reference this bug #16849637
What does oracle do? Does not take them days/weeks/months to release a patch?

I am trying to minimize code rewrites and testing. Is rewriting it using DBMS_SQL the only way? that would change the whole code.

Another workaround i was thinking of, is that it seems to happen when all variables are null. I was thinking of passing a dummy value to one varaible for that case.

I tried creating a local view that uses the link but I got the same error.
Tom Kyte

Followup  

May 23, 2013 - 2:26 pm UTC

there is already a patch for 11.2.0.4 and 12.1 (neither is released just yet, but the fix has been cut)

You will have to contact them to see the availablity .


In reading that article - you know there is another way, sys_context.


It isn't because of the NULL sam. It is because the optimizer sees

(1=1 or :x is null)


and says that is equal to [this space left blank]. the 1=1 makes it disappear.

ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> select * from dual@ora11gr2@loopback where (1=1 or :x is null);
select * from dual@ora11gr2@loopback where (1=1 or :x is null)
       *
ERROR at line 1:
ORA-00600: internal error code, arguments: [opixrb-4], [1036], [ORA-01036:
illegal variable name/number
], [], [], [], [], [], [], [], [], []


ops$tkyte%ORA11GR2> exec :x := 42;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from dual@ora11gr2@loopback where (1=1 or :x is null);
select * from dual@ora11gr2@loopback where (1=1 or :x is null)
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [opixrb-4], [1036], [ORA-01036:
illegal variable name/number
], [], [], [], [], [], [], [], [], []





before you suggest workarounds Sam, you should test them out....


But you know, the long you wait to contact support (something I asked you to do 24 hours ago........) the longer you will wait.


If you want this fixed this afternoon - dbms_sql or sys_context.

SR

May 23, 2013 - 4:09 am UTC

Reviewer: A reader

Tom:

I entered an SR for this and oracle support say this is same as
Bug 14700119 : ORA-600 [OPIXRB-4] [1036] ON SQL EXECUTED WITH INVALID CONDITION

They have a backport for AIX and it seems it will be fixed for 11.2.0.4. see document ID 14700119.8

DO you see this issue as same as Bug 14700119?
Tom Kyte

Followup  

May 23, 2013 - 2:48 pm UTC

yes, that is the same bug.

SR

May 23, 2013 - 2:50 pm UTC

Reviewer: A reader

Tom:

I did contact support. see above.

They tell me this is same as this bug.

Bug 14700119 : ORA-600 [OPIXRB-4] [1036] ON SQL EXECUTED WITH INVALID CONDITION

If that is true why would you enter a new bug number?

One idea I had, is create a local MV based on invoice table.
Since this is a SEARCH page (no updates to invoice table), I can have a LOCAL MATERIALZIED VIEW and refresh every 5 minutes UNTIL THE PATCH is RELEASED.

otherwise i have 10 procedures that use this method and I have to rewrite everything and test it and I am very tight on time.

Do you like the temp workaround of MV until ideploy the patch?


Tom Kyte

Followup  

May 23, 2013 - 3:21 pm UTC

I entered a new bug number because the subject of the other existing bug is stupid and doesn't describe the problem at hand.

there is no invalid condition anywhere.

So I didn't see that it already existed. I read that header and said that couldn't possibly be it. That was a problem with invalidate SQL.


you'd have as much testing and trouble with a materialized view i would think.


how could you be tight on time Sam? You are obviously in your test environment, not production - because you would have tested this functionality before pushing a new database release out wouldn't you?

SR

May 23, 2013 - 4:12 pm UTC

Reviewer: A reader

Tom:

I think oracle support hiring standards have gone down considerably because i dealt with some engineers who do not seem to have a clue or expertise with the product they support.

Rewriting & testing 10 SPs might take me a 4-5 days.

Creating an local MV based on remote table and scheduling a job to refresh takes me 5 minutes. Modifying the SPs to use the MV instead of table/link takes me one HOUR. All code should work as if it is using a remote table for search.

I am checking with support if they have the patch for this in RHEL. I would rather deploy that patch if available.




Tom Kyte

Followup  

May 23, 2013 - 5:21 pm UTC

Sam,

seriously?

You filed a tar and within minutes it seems - they told you "duplicate bug, it has patches" - they identified the problem immediately.


they found the duplicate, they recognized it - i didn't. And darn quick.


so what the heck are you saying Sam???????????


do whatever you want Sam, I have never, I will never just rubber stamp what you want to do. I say what I would be inclined to do in your situation. Period.


SR

May 23, 2013 - 5:42 pm UTC

Reviewer: A reader

You are right but that was my 2nd attempt..
I filed an SR before I ask you and the first guy had no clue and i gave up on the disucssion.

Nothing personal, but I think oracle has only one THOMAS KYTE.


Tom Kyte

Followup  

May 23, 2013 - 6:20 pm UTC

And did you see what I did Sam?

I said "I cannot reproduce, go away".

If you gave them the same level of bits and pieces details you usually give me - I don't think it was that they didn't have a clue, but rather, they had no information to go on. Getting the relevant facts from some people is like pulling healthy teeth out.

I made you reproduce the issue, plainly, simply, without a lot of your code - without ANY of your code in fact. distilling the problem down to its essence. That is our job as developers, that is what developers do. It is called debugging. If you want help, assistance - you recreate the issue using the tiniest possible bit of code.

I got it down to a single select against DUAL. You should have done that. You should have come here and said:

look
select * from dual@database_line where (1=1 or :x is null);
fails but
select * from dual where (1=1 or :x is null);
does not.


Nothing personal, but *everyone* can do this.

Support cannot take your code and run it could they? I couldn't.... I ran my code and said "looks OK to me". It took you reproducing it (no plsql, not necessary, no ref cursors, not necessary) removing EVERYTHING that didn't affect the outcome.

You led with:

Are there any known bugs using SYS_REFCURSOR in 11g R2? It seems it fails when the bind variables
do not have a value if the query is for all set.


ref cursors had nothing to do with it - you should have removed them as you were stripping the code down - to figure out what was wrong at the core. Getting rid of everything you can. turning hundreds of lines of code into nothing (if people wrote more modular code - this would be so much easier too...)

That was what you should have done from the very beginning. 99% of the time when I do that - I find my mistake, the one I made in the code. This is the 1% of the time when it isn't in your code - and you got a one line test case to show it in action.

Once you gave them the bare bones - everything went really smooth and fast.


In closing Sam, there is nothing special or magic about me. I just know how to debug things, how to code things that are debuggable, how to create test cases. Period. That is 99.9999% of what I do here on asktom. It is not a gift, it is a skill - a learned skill - developed over time.

generic query

June 03, 2013 - 7:14 pm UTC

Reviewer: A reader

Tom:

I got the patch and deployed it in source DB only and it took care of the ORA-00600 error as shown below.



SQL> variable x varchar2(30);
SQL> select * from dual@pcs_link where (1=1 or :x is null);

D
-
X

1 row selected.



However, the cursor search is not accurate. How do you explain this in this example?







PROCEDURE MY_NEW_PROCEDURE2
 ( p_vendcd    in varchar2      default NULL,
   p_cntr     in varchar2     default NULL,
   p_cur      in out sys_refcursor )
 as
 
  l_query varchar2(512)
   default 'SELECT a.invoice_no, a.fy, a.vendcd, a.cntr, a.invoice_type, a.invoice_date,
  a.total_amount, a.status_code FROM invoice@db_link a where a.status_code= ''P''
  ';

   begin
   if (p_vendcd is NOT NULL)
   then
         --l_query := l_query || ' and vendcd like ''%''||:p_vendcd||''%'' ';
         l_query := l_query || ' and a.vendcd = :p_vendcd ';
   else
        l_query := l_query || ' and (1=1 or :p_vendcd is null) ';
   end if;

  if (p_cntr is NOT NULL)
   then
         l_query := l_query || ' and a.cntr like ''%''||:p_cntr||''%'' ';
   else
        l_query := l_query || ' and (1=1 or :p_cntr is null) ';
   end if;

  dbms_output.put_line( l_query );

  open p_cur
   for l_query
   using p_vendcd,
         p_cntr;

 end;


1) When I run the program and I print the query using EQUAL operator I get no ROWS found
even though I should get 3 ROWS found.

SQL>  exec my_new_procedure2('ABC',NULL,:c);

SELECT a.invoice_no, a.fy, a.vendcd, a.cntr, a.invoice_type,  a.invoice_date,
      a.total_amount, a.status_code 
      FROM invoice@db_link a 
      where status_code= 'P'
      and vendcd = :p_vendcd  and (1=1 or :p_cntr is null)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected


2)  When I run the program and I print the query using LIKE operator I get a 8 rows instead of 3 rows

SQL>  exec my_new_procedure2('NTB',null,:c);
SELECT a.invoice_no, a.fy, a.vendcd, a.cntr, a.invoice_type,  a.invoice_date,
      a.total_amount, a.status_code 
      FROM invoice@db_link a 
      where status_code= 'P'
and prdr like '%'||:p_vendcd||'%'  and (1=1 or :p_cntr is null)

PL/SQL procedure successfully completed.

SQL> print c


.. 8 ROWS found




3) When i run the query manually using SQL*PLUS without a bind variable I get 3 ROWS which is the correct answer.

SQL> variable p_cntr varchar2
SQL> SELECT a.invno, a.fy, a.prdr, a.cntr, a.invoice_type,  a.invoice_date
,  a.total_amount, a.status_code 
   FROM invoice@db_link a 
  where status_code= 'P'
  and prdr = 'ABC' and (1=1 or :p_cntr is null);



3 rows selected.



Do you know why?

Tom Kyte

Followup  

June 03, 2013 - 8:01 pm UTC

SAM,

is your remote table using a CHAR type by any chance.

as I've asked you before - please present a 100% reproducible testcase that uses the least amount of code possible (as in - get rid of the procedure, just reproduce using SQLPLUS and nothing else. we discussed this at length above - tinest test case with least amount of code possible please)


query

June 03, 2013 - 9:29 pm UTC

Reviewer: A reader

Tom:

No, I never use CHAR type. it is all VARCHAR2 or DATE column types.

I can put a small test case for you. I thought you will catch it just by looking at the code
Tom Kyte

Followup  

June 04, 2013 - 1:55 pm UTC

I thought you will catch it just by
looking at the code


Sam,

I do not believe you read what I wrote above - strongly wrote above. You seem to have missed the most basic point I've been trying to make.



I made you reproduce the issue, plainly, simply, without a lot of your code - without ANY of your code in fact. distilling the problem down to its essence. That is our job as developers, that is what developers do. It is called debugging. If you want help, assistance - you recreate the issue using the tiniest possible bit of code.



generic query

June 03, 2013 - 10:29 pm UTC

Reviewer: A reader

Tom:

here is a test case for 11.2.0.3 on RHEL.

--Create this table in REMOTE DB

create table invoice (
  invoice_no  varchar2(15),
  vendcd  varchar2(8),
  cntr     varchar2(7),
  status_code  varchar2(1) )
/

INSERT into invoice VALUES ('1A','NBA','12-ABC','P')
/
INSERT into invoice VALUES ('1B','NBA','13-ABC','P')
/
INSERT into invoice VALUES ('1C','NBA','12-ABC','P')
/
INSERT into invoice VALUES ('1D','NBA','14-ABC','P')
/
COMMIT
/



-- Create this procedue in LOCAL DB
-- Make sure you have a link name DB_LINK to remote DB

CREATE OR REPLACE PROCEDURE MY_NEW_PROCEDURE4
 ( p_vendcd    in varchar2      default NULL,
   p_cntr     in varchar2     default NULL,
   p_cur      in out sys_refcursor )
 as
 
  l_query varchar2(512)
   default 'SELECT *  FROM invoice@db_link a where a.status_code= ''P''   ';

   begin
   if (p_vendcd is NOT NULL)
   then
         --l_query := l_query || ' and vendcd like ''%''||:p_vendcd||''%'' ';
         l_query := l_query || ' and a.vendcd = :p_vendcd ';
   else
        l_query := l_query || ' and (1=1 or :p_vendcd is null) ';
   end if;

  if (p_cntr is NOT NULL)
   then
         l_query := l_query || ' and a.cntr like ''%''||:p_cntr||''%'' ';
   else
        l_query := l_query || ' and (1=1 or :p_cntr is null) ';
   end if;

  dbms_output.put_line( l_query );

  open p_cur
   for l_query
   using p_vendcd,
         p_cntr;

 end;
/
 
_- Run this in local DB

SQL> variable c refcursor;

SQL> exec my_new_procedure4('NBA',null,:c);


SQL>  exec my_new_procedure4('NBA',null,:c);
SELECT *  FROM invoice@db_link a where a.status_code= 'P'  and a.vendcd =:p_vendcd  and (1=1 or :p_cntr is null)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

Tom Kyte

Followup  

June 04, 2013 - 1:56 pm UTC

re-read what I wrote above Sam.

then you'll have your teeny tiny, standalone, no plsql involved, very simple to reproduce test case

for support to work from...

generic query

June 04, 2013 - 3:47 pm UTC

Reviewer: A reader

Tom:

I was trying to show you that the issue seems to be with PL/SQL But with more testing it seems it is in SQL too.

Does this tell you this patch is still buggy and support needs to check out this strange behaviour?

1) CASE 1: Vendor code hardcoded in SQL Query - Correct results

SQL> variable p_cntr  varchar2(7);
SQL> select * from invoice@db_link a where a.status_code='P' and a.vendcd = 'NBA' and (1=1 or :p_cntr is null);

INVOICE_NO      VENDCD   CNTR    S
--------------- -------- ------- -
1A              NBA      12-ABC  P
1B              NBA      13-ABC  P
1C              NBA      12-ABC  P
1D              NBA      14-ABC  P

4 rows selected.


2) CASE 2: Vendor Code passed using PL/SQL procedure - NO RESULTS RETURNED from remote table.

SQL> variable c refcursor;
SQL> exec my_new_procedure4('NBA',null,:c);
SELECT *  FROM invoice@db_link a where a.status_code= 'P'    and a.vendcd =
:p_vendcd  and (1=1 or :p_cntr is null)

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> print c

no rows selected

3) CASE 3: Vendor code passed as variable to SQL Query

SQL> variable p_vendcd varchar2(8)
SQL> variable p_cntr varchar2(7)
SQL> exec :p_vendcd := 'NBA'

PL/SQL procedure successfully completed.

SQL> print p_vendcd

P_VENDCD
--------------------------------
NBA

SQL> print p_cntr

P_CNTR
--------------------------------


SQL> select * from invoice@db_link where vendcd = :p_vendcd and (1=1 or :p_cntr is null);
select * from invoice@db_link where vendcd = :p_vendcd and (1=1 or :p_cntr is null)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 16080
Session ID: 209 Serial number: 13957



Tom Kyte

Followup  

June 04, 2013 - 4:27 pm UTC

... I was trying to show you that the issue seems to be with PL/SQL But with more
testing it seems it is in SQL too. ...


Sam -
since plsql just runs sql, it would make sense that it does - JUST LIKE LAST TIME.

I'm trying really hard to teach you a methodology that you would employ every time, any time you see something suspicious. REMOVE EVERYTHING YOU CAN TO GET THE SMALLEST possible test case - with everything, everything, stripped out that is not relevant to the problem. That way you won't jump to conclusions like you did here (again).

please contact support with your small test case that doesn't involve any plsql.

generic query

June 04, 2013 - 4:48 pm UTC

Reviewer: A reader

OK thanks for teaching me that. you will always be my best mentor.

I was hoping you say "Yes I do see a problem that support needs to check".

I hope this patch did not screw up other queries i have and it only involves using binds over remote link. It does not seem they did much testing for this patch.

ref cursor

June 19, 2013 - 9:35 pm UTC

Reviewer: A reader

Tom:

I got a message from oracle support that I can not use Ref cursor over DB Link with 11g.

This did not make any sense to me as it works fine with 9i and 10g. I have noticed others reporting similar problems with 11g though.

http://stackoverflow.com/questions/4598725/different-behavior-for-ref-cursor-between-oracle-10g-and-11g-when-unique-index-p

It sounds like the developer is clueless. Does it make any sense to you?

In case i want to rewrite it, since it has been weeks without any resolution yet what would you use?

ref cursor

June 20, 2013 - 12:49 pm UTC

Reviewer: A reader

Tom:

Here is the link support sends to mention the restriction in 11g. However, I am not getting an error - just no data results.

Can this be true where it works in 9i and they do not support it in 11g. It is hard to believe?


This is a restriction from 11g onwards :
note in the PL/SQL Users Guide [Restrictions on Cursor Variables

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/static.htm#i40146

Using a REF CURSOR variable in a server-to-server RPC results in an error.


Tom Kyte

Followup  

June 20, 2013 - 2:55 pm UTC

Sam,

what does a ref cursor have to do with anything here.

did you not see me write


Sam - 
since plsql just runs sql, it would make sense that it does - JUST LIKE LAST TIME. 

I'm trying really hard to teach you a methodology that you would employ every time, any time you see something suspicious. REMOVE EVERYTHING YOU CAN TO GET THE SMALLEST possible test case - with everything, everything, stripped out that is not relevant to the problem. That way you won't jump to conclusions like you did here (again). 

please contact support with your small test case that doesn't involve any plsql. 



doesn't involve ANY plsql.


if it doesn't involve any plsql, it doesn't involve a ref cursor. It is just a query.


the referenced quote from the documentation is talking about the fact that you've NEVER been able to return a ref cursor from a remote database. A ref cursor opened on a given database however can definitely use dblinks.


You cannot RETURN a ref cursor from a remote database. You never have been, that has always been documented.

A ref cursor however can definitely reference a remote database.


however, it is not relevant at all to the issue here. you have a test case that DOES NOT INVOLVE A REF CURSOR AT ALL. I don't know why you are even bringing it up.



genric query

June 20, 2013 - 4:40 pm UTC

Reviewer: A reader

Tom:

How can you show the issue with pure SQL (no ref cursors)?

I think there are two different problems here.
The 1st one is a communication error ORA-03113 which they said there is a bug for in development. I am not even sure they are the same though?

Bug 12763598 : ORA-7445 [KPNXBICBK()+521] [SIGSEGV]


If i take that SAME SQL and put in PL/SQL and execute it using REF CURSOR then no records are found (incorrect result set).

so my question, is how can you show that NO RESULTS show up without using REF CURSOR.

---------------------------------------------------------------------------------
Case 1: ORA-03113 Error

SQL>  select a.invoice_no from invoice@db_link a
  2   where a.status_code='P' and vendcd = 'NBA' and (1=1 or :p_cntr is null);

INVOICE_NO
---------------
1A
1B
1C
1D

4 rows selected.


SQL> variable p_vendcd varchar2(8);
SQL> variable p_cntr varchar2(8);
SQL> exec :p_vendcd := 'NBA';

PL/SQL procedure successfully completed.



  1   select a.invoice_no from invoice@db_link a
  2*  where a.status_code='P' and vendcd = :p_vendcd and (1=1 or :p_cntr is null)
SQL> /
 select a.invoice_no from invoice@db_link a
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

-----------------------------------------------------------------------------
Case 2 

 SQL> variable c refcursor;
SQL> exec my_new_procedure4('NBA',null,:c);
SELECT *  FROM invoice@db_link a where a.status_code= 'P'    and a.vendcd =
:p_vendcd  and (1=1 or :p_cntr is null)

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> print c

no rows selected

Tom Kyte

Followup  

June 20, 2013 - 8:11 pm UTC

your 3113 seems to do it, doesn't it now.

also, you should be able to code:

declare
   p_vendcd varchar2(8);
   p_cntr varchar2(8) := 'NBA';
begin
   for x in (select a.invoice_no from invoice@db_link a
    where a.status_code='P' and vendcd = p_vendcd and (1=1 or p_cntr is null) )
   loop
     dbms_output.put_line( a.invoice_no );
   end loop;
end;
/


to demonstrate it without a ref cursor (but you DO NOT NEED to, you have the 3113 to get fixed!!)


remember, REMOVE ALL NON-RELEVANT BITS OF CODE.

if you want to go the ref cursor course, feel free to have the support analyst email me and I'll set them straight on what the paragraph in the docs are talking about. they are talking about RETURNING a ref cursor over a dblink. That is NOT what you are trying to do. You are using a ref cursor that uses a dblink, that is perfectly valid, has always been supported, is still supported. They have misinterpreted the documentation.

or just refer the support analyst to this link...

generic query

June 20, 2013 - 10:54 pm UTC

Reviewer: A reader

Tom:

Thanks for the help. I will provide support with this and ask them to email you if they still do not understand it.

You already entered a BUG for this and they fixed the ORA-0600 issue. However the results are not correct so is it still same bug or different bug?

Bug 14700119 : ORA-600 [OPIXRB-4] [1036] ON SQL EXECUTED WITH INVALID CONDITION


Do you see this as one issue related to using Bind variables over a DB link or there are multiple issues here?


ref cursor

June 21, 2013 - 1:55 pm UTC

Reviewer: A reader

Tom:

I am sort of giving up on oracle support to provide a patch for this.

Check out their SAME response even after I show them this issue is not related to REF CURSOR. They do not admit there is a BUG and it needs a PATCH. Can you escalate tickets to upper management or file a complaint or would you open another ticket and hope someone sees the issue?

can you try the solution:

---------------------------------------------------------
Use PL-SQL Data table. OR Provide select grant and use select command over DBLink from initiator site instead of opening the Cursor.or The simplest solution would be to put the SELECT statement on TN2,
referencing tables on TN1.

If that is not an option, and you absolutely have to call the stored procedure in the remote database, you can write a Java stored procedure that calls the remote procedure and operates on the results. If you're not
comfortable with Java and Java stored procedures, though, that may add quite a bit of complexity to the system.
-----------------------------------------------------------

Tom Kyte

Followup  

July 01, 2013 - 5:02 pm UTC

Sam, you need to escalate this up through the duty manager, I cannot do that for you. Please follow the standard support procedures for escalation.

this should be a p1 bug - wrong answer from a query. please work it via support. use my name and this blog entry if you want to. tell the analyst to get in touch with me if they need clarification.

support

July 05, 2013 - 2:13 pm UTC

Reviewer: A reader

Tom:

<<<standard support procedures for escalation. >>>

This is what i am trying to figure out. Do you do that by calling in or somehow on metalink?

I had a call from someone in that country (you know where your dev team is) and he talked for 10 minutes and I could not understand 80% of his accent. I understood it was escalated to a *senior* team but nothing has been figured out yet.

very inefficient center if you ask me. I reported this like 2 months ago and no solution yet!





Search String

September 16, 2013 - 4:55 pm UTC

Reviewer: Ankit Bhatt from India

hi tom,

Hope you hale and hearty.
My query is that: As you can see in the following query , there is column named as "NAME", all i want is that if i just type "MS 16" (for example), then the query should return those records which contains "MS 16" in them, the query should be like searching tool, whatever i input in to search, i should get the fetched records containing those words. I have written the below query for your convenience.
Hope to see your reply soon and just carry on your incredible "HELPING" services.


create table t_text
as
SELECT 'MS 16 MM ARKING HORN ' NAME FROM DUAL
UNION ALL
SELECT 'MS 16 MM ARKING HORN ' NAME FROM DUAL
UNION ALL
SELECT 'EN-8 HEX LOCK NUT M20' NAME FROM DUAL
UNION ALL
SELECT 'ROUND ROLL SPRING' NAME FROM DUAL
UNION ALL
SELECT '12MM ROUND ITEM' NAME FROM DUAL
UNION ALL
SELECT 'IRON ROUND 12MM' NAME FROM DUAL
UNION ALL
SELECT 'SPRING HORN 19MM' NAME FROM DUAL
select * from T;



Thanking You.
Ankit Bhatt.


Tom Kyte

Followup  

September 24, 2013 - 4:59 pm UTC

select * from table where name like :x;


set :x to '%MS 16%'


Generic Search

September 27, 2013 - 4:12 pm UTC

Reviewer: ankit from india

Tom,Thanks for your reply but my requirement is different.
I want a generic function via which i can search the data just like google-search, for example the following query will return a set of records, the query is written as follows:

with t as
(
select 'internal production order' a from dual
union all
select 'internal sale order' a from dual
union all
select 'sale order' a from dual
union all
select 'production order' a from dual
union all
select 'internal jobwork' a from dual
union all
select 'internal order' a from dual
)
select * from t

case 1 :- i/p search string->'internal order' or 'order internal'
then
o/p like --
internal production order
internal order
internal sale order

don't come sale order,production order,internal jobwork

case 2 :- i/p search string->'internal order' or 'order internal'
then
o/p like --
internal production order
internal order
internal sale order



I have given you the two cases as is written above, in simple language all i want is to search like google, suppose i type in any order ( for example i type "ankit", then
it should return those records from consisting of "ankit" BUT suppose i want to search "ankit bhatt", then the query should be like that even if i type "bhatt ankit", it should return those records which consists of "ankit" and "bhatt" IN ANY ORDER.
please run the above query and provide me that query in which i should be capable of searching the records ( irrespective of the order of input i make in ).
It would be highly appreciative for your assistance.
i would be highly obliged and keep on doing the good job TOM. Great Work !!

for Ankit

September 30, 2013 - 6:44 pm UTC

Reviewer: Barbara Boehmer from City of Jurupa Valley, California, U.S.A.

Ankit,

If you are looking for google-like searches, then you are looking for Oracle Text.  The example that you have provided is very simple and can be done with just Oracle Text.  If you are looking for more google-like searches, then you can additionally use Oracle Text product manager Roger Ford's parser package that you can find and download here:

https://blogs.oracle.com/searchtech/entry/oracle_text_query_parser

I have provided a simple example below, first without the parser, then using the parser, for a more google-like search.

Barbara

SCOTT@orcl12c> -- table and data:
SCOTT@orcl12c> create table t as
  2  select 'internal production order' a from dual
  3  union all
  4  select 'internal sale order' a from dual
  5  union all
  6  select 'sale order' a from dual
  7  union all
  8  select 'production order' a from dual
  9  union all
 10  select 'internal jobwork' a from dual
 11  union all
 12  select 'internal order' a from dual
 13  /

Table created.

SCOTT@orcl12c> -- Oracle Text context index:
SCOTT@orcl12c> create index t_idx on t (a) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl12c> -- search using just Oracle Text:
SCOTT@orcl12c> variable search_string varchar2(100)
SCOTT@orcl12c> exec :search_string := 'order internal'

PL/SQL procedure successfully completed.

SCOTT@orcl12c> select score(1), a from t
  2  where  contains (a, replace (:search_string, ' ', ' and '), 1) > 0
  3  order  by score(1) desc
  4  /

  SCORE(1) A
---------- -------------------------
         3 internal production order
         3 internal order
         3 internal sale order

3 rows selected.

SCOTT@orcl12c> -- searches using the parser package:
SCOTT@orcl12c> select score(1), a from t
  2  where  contains (a, parser.andsearch (:search_string), 1) > 0
  3  order  by score(1) desc
  4  /

  SCORE(1) A
---------- -------------------------
         3 internal production order
         3 internal order
         3 internal sale order

3 rows selected.

SCOTT@orcl12c> select score(1), a from t
  2  where  contains (a, parser.simplesearch (:search_string), 1) > 0
  3  order  by score(1) desc
  4  /

  SCORE(1) A
---------- -------------------------
        52 internal sale order
        52 internal production order
        52 internal order
         2 internal jobwork
         2 production order
         2 sale order

6 rows selected.

SCOTT@orcl12c> select ceil (score(1)) score, a from t
  2  where  contains (a, parser.progrelax (:search_string), 1) > 0
  3  order  by score(1) desc
  4  /

     SCORE A
---------- -------------------------
        74 internal order
        74 internal sale order
        74 internal production order
         1 internal jobwork
         1 sale order
         1 production order

6 rows selected.



Generate number with random interval

October 04, 2013 - 4:31 am UTC

Reviewer: Ankit from India

OK thanks Tom for teaching me that.
you will always be my best MENTOR.........

I think I can ask it here, if not - sorry :-(

February 09, 2014 - 12:47 am UTC

Reviewer: A reader

Tom, what if we need to search in scenario like this: Consider these two tables:
create table TDATA
(
c1 VARCHAR2(8),
c2 VARCHAR2(8),
c3 VARCHAR2(8)
);
create table TSEARCH
(
c1 VARCHAR2(8),
c2 VARCHAR2(8),
c3 VARCHAR2(8)
);
Table tData has millions of rows. Table tSearch is MUCH smaller - let's say 10-20 records. We need to search tData by "templates", provided in tSearch - if any column in tSearch is not null and has data - we require exact match in tData in the same column. If column in tSearch is null - we completely ignore this column from comparison. So, we need to get ALL records from tData, that match any "template" in tSearch. To make things even more complicated - we also need to be able to search by "not match" scenario - in other words, if column in tSearch has value - we require correspondent column in tData to NOT match tSearch value. If this column in tData is null - we don't care about this mismatch.
There is no problem to write query with conditions for the first scenario like "where ((tData.c1=tSearch.c1) or (tSearch.c1 is null)) and (same for other columns), but the question is how to do it efficiently. I mean - what would be your suggestion from indexing and querying perspective?
If it is better not to have null values in either tData or tSearch - it is perfectly fine to replace them with some value like "NONE" or anything like this. Also - columns are always fixed characters long, so we can easily switch to char if it helps.
And one more - Oracle SE, not EE, so no bitmap indexes (if it matters).
I hope this questions can be asked here, though I expect solution to be quite different from the case, when you have just one set of fields and values, provided for the query...
Thanks!!!!!!!!!

Very interested in your opinion :-)

April 01, 2014 - 7:39 pm UTC

Reviewer: A reader

Tom,
if you have any thought on what is the best way to solve this problem - please advice. Looks like not exactly the original question, but very close from the perspective of building generic search...
Thanks!!!!!
Tom Kyte

Followup  

April 02, 2014 - 5:36 am UTC

... I mean - what would be your suggestion from indexing and querying
perspective? ...

there would probably be no indexes. but it depends, how many rows would be returned from such a query. if the number of rows is "10's of thousands and more", indexes won't make sense. If the number if "about 5", a series of indexes *MIGHT* make sense.

this tdata table, how big is it? number of rows = meaningless information. GB's = meaningful information. If it had 10,000,000 rows, I'd guess "about 1/3 of a GB". If you used compression on it (alter table t compress, alter table t move), It might become 150mb or less - meaning a full scan wouldn't be very painful at all.


Links not working

May 18, 2017 - 1:51 pm UTC

Reviewer: Sejal

Hello,

I tried to open the links mentioned in the answers but they dont seem to be working. Where can I find these articles now?
Connor McDonald

Followup  

May 18, 2017 - 6:47 pm UTC

let us know *which* link you're after

broken link

July 12, 2018 - 8:02 pm UTC

Reviewer: Vidya from NY

Connor McDonald

Followup  

July 24, 2018 - 3:05 am UTC

The article is here

https://blogs.oracle.com/oraclemagazine/on-popularity-and-natural-selection


Is This Okay?

July 09, 2019 - 11:02 pm UTC

Reviewer: Laurence Prescott from Brisbane Qld Australia

Hi Tom,

This is an example of how I solve the problem.
Is this a reasonable approach?

declare dsql     varchar2(4000);
        cnt      pls_integer;
        p_empno  pls_integer := 1;
        p_salary pls_integer := null; -- null to simulate no salary entered
begin
    dsql := 'select count(*) from emp e, (select :empNo empNo, :salary salary from dual) t where e.empNo = nvl(t.empNo, e.empNo) and e.salary = nvl(t.salary, e.salary)';
    execute immediate dsql into cnt using p_empno, p_salary;
    dbms_output.put_line('cnt:' ||cnt);
end;


Cheers
Laurence
Chris Saxon

Followup  

July 10, 2019 - 10:24 am UTC

There's no reason to use dynamic SQL here. The select from dual seems redundant to me too.

You could have just:

declare 
  cnt      pls_integer;
  p_empno  pls_integer := 1;
  p_salary pls_integer := null; -- null to simulate no salary entered
begin

  select count(*) 
  into   cnt
  from   emp e
  where  e.empNo = nvl(:empNo, e.empNo) 
  and    e.salary = nvl(:salary, e.salary);
  
  dbms_output.put_line('cnt:' ||cnt);
  
end;
/

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.