Hello!
I have a query that takes a bind variable, like (simplified example here - my real SQLs involve 10+ tables with 100K+ rows, hence the impact is much higher):
create table test_table(id integer NOT NULL, text varchar2(2000));
insert into test_Table values(1, 'FOO');
insert into test_Table values(2, 'BAR');
insert into test_Table values(3, 'GAZ');
create index test_table_idx on test_table(id);
Now, I have the query:
select * from test_table
where id = :the_id
..which will performance OK.. (ALL_ROWSCost: 2 Bytes: 1.015 Cardinality: 1 )
However, the :the_id bind variable is "optional" (can be NULL), which means it should be ignored and return all rows, like:
select * from test_table
where id = NVL(:the_id,id)
When :the_id is set to NULL, this query returns the right rows, but it performs less (ALL_ROWSCost: 3 Bytes: 5.075 Cardinality: 5)
The query can also be rewritten to:
select * from test_table
where (:the_id IS NULL or :the_id = id)
But this is not optimal either: (ALL_ROWSCost: 3 Bytes: 1.015 Cardinality: 1 )
Dynamic SQL is not an option here...
Is there a good way to handle such "nullable" bind variables?
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production