Query Optimizer does not ignore constant condition?

[hemal]

I came across a very strange situation at work. There is an order of
magnitude difference in execution time for the following two queries
(10 v/s ~130 msec):

select count(*) from table_name where column_name = 'value' or 1 = 0

select count(*) from table_name where column_name = 'value'

I do not want to go into the reason why the redundent condition
exists, the example is representative of the real query where it
serves a purpose.

Any suggestions what I can do to improve the situation?

Thanks in advance.

[hemal]

Hi Ed,

Didn't I say I don't want to go into why it exists? <g>

Consider that about five sub-conditions, created at different
locations in the program, are OR'd together by the client code. That
is,

whereClause = part1.getCondition() + "OR" + part2.getCondition() +
"OR" ....

While usually there is always something to fill-in in each of these
sub-conditions, in one particular (exception) case the third part does
not produce a condition.

Instead of changing the client code to check whether the condition is
non-null before adding it to the where clause, the programmer decided
to return "(1=0)", assuming that the query optimizer will ignore it.
But it doesn't.

Hope this satisfies your inquiring mind.

I have modified the strucure so that this is no longer an issue. But I
would like to know why this happens.
ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...

h0*@hotmail.com (hemal) wrote in message news:<16**************************@posting.google. com>...

I came across a very strange situation at work. There is an order of
magnitude difference in execution time for the following two queries
(10 v/s ~130 msec):

select count(*) from table_name where column_name = 'value' or 1 = 0

select count(*) from table_name where column_name = 'value'

I do not want to go into the reason why the redundent condition
exists, the example is representative of the real query where it
serves a purpose.

Any suggestions what I can do to improve the situation?

Thanks in advance.

I have no explaination for you, just a question:
what purpose does a redundant condition serve?

Enquiring minds want to know!

ed

[Daniel Roy]

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel

[hemal]

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel

[Ed prochak]

h0*@hotmail.com (hemal) wrote in message news:<16**************************@posting.google. com>...

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

Well, it may SEEM elementary, but how would you code the optimizer to
recognize it? Remember that extra clause might be nearly anything:
OR -1=0
OR 2+2=3
OR -1=1
OR 0=1/0
OR 'JULY 1 1970' = '07-01-70'

In theory
<any expression evaluating to a constant>
= <any expression evaluating to another constant>

But back to your practical problem. It is a long shot, but try putting
the constant expression at the other end of the where clause. IOW if
now you use:

select * from tables
where a = b
and 0=1;

change it to be

select * from tables
where 0=1
and a = b;

I really don't know if that will help, but the optimizer does seem to
check expressions last to first. If it gets a chance to check the real
expressions first, MAYBE it will help.

Caveat THIS IS ONLY A GUESS, LONGSHOT, PIE-IN-THE-SKY, crack-pot idea.

Yes you may call me a crackpot if I'm wrong.
And I fully expect to be wrong. 8^)

[Daniel Roy]

Why can't the DBA help you on getting this info?

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel

[Daniel Roy]

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel

[hemal]

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel

[Ed prochak]

h0*@hotmail.com (hemal) wrote in message news:<16**************************@posting.google. com>...

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

Well, it may SEEM elementary, but how would you code the optimizer to
recognize it? Remember that extra clause might be nearly anything:
OR -1=0
OR 2+2=3
OR -1=1
OR 0=1/0
OR 'JULY 1 1970' = '07-01-70'

In theory
<any expression evaluating to a constant>
= <any expression evaluating to another constant>

But back to your practical problem. It is a long shot, but try putting
the constant expression at the other end of the where clause. IOW if
now you use:

select * from tables
where a = b
and 0=1;

change it to be

select * from tables
where 0=1
and a = b;

I really don't know if that will help, but the optimizer does seem to
check expressions last to first. If it gets a chance to check the real
expressions first, MAYBE it will help.

Caveat THIS IS ONLY A GUESS, LONGSHOT, PIE-IN-THE-SKY, crack-pot idea.

Yes you may call me a crackpot if I'm wrong.
And I fully expect to be wrong. 8^)

[Daniel Roy]

Why can't the DBA help you on getting this info?

Oracle version is 9i.

I don't know how to retrieve the execution plan, optimizer_mode and
the information about when the tables were analyzed. Can you please
tell me how?

I spoke the DBA and he did say something about the optimizer used.
According to him, the rule bases optimizer works better in such cases.
"Queries involving OR conditions can be difficult for the cost-based
optimizer to resolve efficiently. Sometimes (but not always) a
histogram can help. Otherwise, FULL or USE_CONCAT and INDEX hints can
be used to select the best execution plan if OR condition is
mandatory"

But, but....I would consider this particular problem to be, er,
elementary. All these optimizer_mode and table statistics are required
for deciding that ((1=0) or x) is equivalent to (x)? I am surprised.

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

Post the execution plans for both cases, and then we'll be able to see
what's going on. Important data include Oracle version,
optimizer_mode, tables analyzed recently or not, ...

Daniel