CODEWITHSUNDEEP
oracle-databaseplsql
Vargan
Vargan

Reputation: 1317

Oracle 10 - query short circuit evaluation

Does Oracle 10 support a short circuit evaluation query? if yes, there are some special key for use it?

Upvotes: 2

Views: 337

Answers (2)

Andrew
Andrew

Reputation: 27294

The only short circuit I have seen Oracle do, relates to NVL vs COALESCE.

SELECT NVL(1,1/0) FROM DUAL

SELECT COALESCE(1,1/0) FROM DUAL

NVL evaluates both sides and throws an error, the coalesce doesn't.

It also looks like decode is doing the same thing:

SELECT decode(1,1,9,2,1/0) FROM DUAL

It isn't evaluating the second part so avoids throwing an error.

On the SQL Server optimizer, I know that the query engine can rewrite these kind of statements and cause problems with short circuit examples like this so as a general rule - you should never rely on a short circuit within your logic - I do not know if the same applies to Oracle - I suspect it will.

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58471

You are probably talking about short circuit evaluation.

A DBMS has cost-based optimizer. There is no guarantee wich condition will get evaluated first and there's no special key to activate this.

Note that PL/SQL does use short circuit evaluation

Upvotes: 5

Related Questions