| View previous topic :: View next topic |
| Author |
Message |
coboladdict
Beginner
Joined: 02 Mar 2010
Posts: 15
Topics: 5
|
 Posted: Sun Mar 07, 2010 7:57 am Post subject: 0 = 1 |
 |
|
| Code: |
select col1, col2, col3
from table1
where cola = 123
and colb = 456
|
we had a query with the code above.
the performance wasn't good enough and the db2 guy in the company suggested the following:
| Code: |
select col1, col2, col3
from table1
where cola = 123
and colb = 456
AND 0 = 1
|
the performance is way better now.
i don't understand the purpose of this "0=1".
to me, it's a condition that can never be satisfied.
can someone explain how it works, please?
thanks. |
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Sun Mar 07, 2010 10:05 pm Post subject: |
|
|
It is as you say - a condition that can never be satisfied. That is how it works. . .
It runs faster because it is automatically NOT= without even looking at the database columns.
Also, i suspect that for whatever reason, you did not post the complete sql but rather only part of it. . .
_________________
All the best,
di |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Mon Mar 08, 2010 6:44 am Post subject: |
|
|
I'm not very sure how the performance is better for 2nd query - it's(0=1) used in an "AND" operation...  . Do you have explain statistics with you?
_________________
Regards,
Anuj |
|
| Back to top |
|
|
coboladdict
Beginner
Joined: 02 Mar 2010
Posts: 15
Topics: 5
|
| Posted: Mon Mar 08, 2010 11:46 am Post subject: |
|
|
| Quote: |
It runs faster because it is automatically NOT= without even looking at the database columns.
|
if this condition is never satisfied, how can the query retrieve any line from the table?
thanks. |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Mon Mar 08, 2010 4:47 pm Post subject: |
|
|
Most likely, the goal is to return no rows. . . The first query would have to process rows which would take resources. The second query need not as the literals will never be equal.
Is this part of some larger query? If so, post the entire query. . .
_________________
All the best,
di |
|
| Back to top |
|
|
coboladdict
Beginner
Joined: 02 Mar 2010
Posts: 15
Topics: 5
|
| Posted: Mon Mar 08, 2010 5:44 pm Post subject: |
|
|
Ok,
Here goes the entire query:
| Code: |
SELECT ...
FROM TABLE
WHERE CD_PRF_DEPE = :WCD-PRF-DEPE
AND CD_INST = :WCD-INST
AND (DD_BAL = :WDD-BAL-INI OR 0 = 1)
AND NR_PT_CTB = :WNR-PT-CTB-INI
AND DT_BAL = :WDT-BAL-INI
AND NR_SEQL_LCTO = :WNR-SEQL-LCTO-INI
AND NR_DOC = :WNR-DOC-INI
ORDER BY CD_PRF_DEPE, DD_BAL, CD_TIP_OBJ, CD_OBJ, ...
|
Thanks. |
|
| Back to top |
|
|
coboladdict
Beginner
Joined: 02 Mar 2010
Posts: 15
Topics: 5
|
| Posted: Mon Mar 08, 2010 6:02 pm Post subject: |
|
|
Actually, it's not AND but OR.
Still doesn't make sense to me.
Thanks. |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Tue Mar 09, 2010 5:02 am Post subject: |
|
|
By combining the predicate '0 = 1' with an OR to another predicate, you force DB2 not to consider index access for this column.
Probably irritated with missing runstats values for this column the DB2 optimizer could use the index on DD_BAL, which actually could lead to longer access paths.
regards
Christian |
|
| Back to top |
|
|
coboladdict
Beginner
Joined: 02 Mar 2010
Posts: 15
Topics: 5
|
| Posted: Tue Mar 09, 2010 7:31 am Post subject: |
|
|
is it considered a good way to solve a db2 performance problem?
how can an avarege guy like me know when to use this trick?
could you provide some guidance?
thanks. |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Tue Mar 09, 2010 4:58 pm Post subject: |
|
|
| Quote: |
is it considered a good way to solve a db2 performance problem?
how can an avarege guy like me know when to use this trick?
|
Typically, tricks should not be used. As with your situation, they often cause confusion. If some "trick" is to be used, it should be well documented and approved by the dba(s).
Also, an upgrade could improve the situation being circumvented by some "trick" and the improvement would not be seen because of the "trick".
| Quote: | | could you provide some guidance? |
Analyze what a query is going to generate "under the covers". Work with the database support people. Use EXPLAIN. Run and measure real-world tests.
_________________
All the best,
di |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
