Outerjoining a Bind-Variable in SQL-Statement (Oracle 11g)

Question

we have a lot of Select Statements which use Bind-Variables that can be Null. Null means that the Bind-Value should not restrict the statement.

Here is a simple example what we are doing:

CREATE TABLE PERSON AS
SELECT LEVEL AS ID, 'Person_'||LEVEL AS NAME
FROM DUAL 
CONNECT BY LEVEL <= 5000;

create index IPERSON1 on PERSON(NAME, ID);

begin
dbms_stats.gather_table_stats(user, 'PERSON');
end;

select * from PERSON
where NAME = nvl(:b1, NAME);

select * from PERSON
where (NAME =:b1 or :b1 is null);

The 2 Statements have the following execution plans:

select * from PERSON where NAME =nvl(:b1, NAME): 
------------------------------------------------------------------------------------
| Id  | Operation              | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |       |     4 (100)|          |
|   1 |  CONCATENATION         |          |        |       |            |          |
|*  2 |   FILTER               |          |        |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IPERSON1 |    500 |  7500 |     2   (0)| 00:00:01 |
|*  4 |   FILTER               |          |        |       |            |          |
|*  5 |    INDEX RANGE SCAN    | IPERSON1 |      1 |    15 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

select * from PERSON where (NAME =:b1 or :b1 is null): 
-----------------------------------------------------------------------------
| Id  | Operation         | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| PERSON |     26 |   390 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

So, what do you think is the best solution for such outer-joins?

I can't see any join in your query, let alone an outer join? So I don't really understand the question. — Codo
– Codo, Commented Dec 3, 2012 at 14:39
Sorry, i used "outer join" by the meaning that the Bind-Value must "exist" or "not exist" — bernhard.weingartner
– bernhard.weingartner, Commented Dec 3, 2012 at 14:44

Codo · Accepted Answer · 2012-12-03 15:12:58Z

I don't think that the question can be answered generally as it depends on the specific table schemas and on the availabe indexes.

What can be said generally is:

Both proposed WHERE conditions are unable to directly find the relevant rows via an index lookup. Instead, the have to fully scan either the table or an index.
It is considered an anti-pattern to merge different variations of the same query into one because Oracle will have to use the same execution plan for all variations, which is most likely suboptimal for all of them.

So it would probably be better to either:

Build the query dynamically, i.e. add the WHERE condition only if the parameter is not null. If the WHERE condition is present, then you should still use bound parameter, which is essential for optimal performance.
Or, implement separate queries depending on whether the parameter is null or not.

Option 1 is the preferred option. It's probably easier to implement if you run the queries for C#, Java or a similar language.

Option 2 is the one you would probably use from PL/SQL. However, it could result in many different queries if you have several parameters that can be null.

Collectives™ on Stack Overflow

Outerjoining a Bind-Variable in SQL-Statement (Oracle 11g)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related