hello
I have a weird optimizer behaviour on a 10.2.0.4 db and need your help.
When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why.
Here are the statements:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
VFUTFIX.ValTik,
VFUTFIX.MNETIT,
VFUTFIX.CODDEVBDR,
VFUTFIX.MNTNML,
VFUTFIX.DATMATTIT
FROM TFXPPRODUCT,
VFUTFIX
WHERE TFXPPRODUCT.Codfix = VFUTFIX.CodFut
AND TFXPPRODUCT.DESKID + 0 = 13
associated plan :
============
Plan Table
============
--------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4602 | |
| 1 | NESTED LOOPS OUTER | | 2814 | 335K | 4602 | 00:00:56 |
| 2 | HASH JOIN | | 2814 | 300K | 4600 | 00:00:56 |
| 3 | TABLE ACCESS FULL | TDEVBDR | 238 | 2380 | 3 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 2814 | 272K | 4597 | 00:00:56 |
| 5 | NESTED LOOPS OUTER | | 2814 | 239K | 4596 | 00:00:56 |
| 6 | HASH JOIN | | 2814 | 201K | 4595 | 00:00:56 |
| 7 | TABLE ACCESS FULL | TPRODUCT | 2814 | 69K | 58 | 00:00:01 |
| 8 | MAT_VIEW ACCESS FULL | TFUTFIX | 1059K | 50M | 4525 | 00:00:55 |
| 9 | INDEX RANGE SCAN | IDX_NBE21 | 1 | 14 | 0 | |
| 10 | TABLE ACCESS BY INDEX ROWID | TFUTFXP | 1 | 12 | 1 | 00:00:01 |
| 11 | INDEX RANGE SCAN | IDX_NBE22 | 1 | | 0 | |
| 12 | TABLE ACCESS BY INDEX ROWID | TVALSBLFUTBDR | 1 | 13 | 1 | 00:00:01 |
| 13 | INDEX RANGE SCAN | IVALSBLFUTBDR1| 1 | | 0 | |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("D"."CA3ISODEV"="T"."CODDEV")
6 - access("TFXPPRODUCT"."CODFIX"="T"."CODFUT")
7 - filter("TFXPPRODUCT"."DESKID"+0=13)
9 - access("F"."MNETIT"="T"."MNETIT")
11 - access("X"."MNEFUT"="T"."MNETIT")
13 - access("V"."CODFUT"="T"."CODFUT")
B- Second statement with optimal plan where i replace "+" by "-":
SELECT /*OK*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
VFUTFIX.ValTik,
VFUTFIX.MNETIT,
VFUTFIX.CODDEVBDR,
VFUTFIX.MNTNML,
VFUTFIX.DATMATTIT
FROM TFXPPRODUCT,
VFUTFIX
WHERE TFXPPRODUCT.Codfix = VFUTFIX.CodFut
AND TFXPPRODUCT.DESKID - 0 = 13
associated plan :
============
Plan Table
============
------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 331 | |
| 1 | NESTED LOOPS OUTER | | 89 | 11K | 331 | 00:00:04 |
| 2 | NESTED LOOPS OUTER | | 89 | 9701 | 330 | 00:00:04 |
| 3 | NESTED LOOPS OUTER | | 89 | 8633 | 329 | 00:00:04 |
| 4 | HASH JOIN | | 89 | 7387 | 329 | 00:00:04 |
| 5 | MAT_VIEW ACCESS BY INDEX ROWID | TFUTFIX | 1 | 48 | 3 | 00:00:01 |
| 6 | NESTED LOOPS | | 89 | 6497 | 325 | 00:00:04 |
| 7 | TABLE ACCESS FULL | TPRODUCT | 89 | 2225 | 58 | 00:00:01 |
| 8 | INDEX RANGE SCAN | IFUTFIX0 | 1 | | 2 | 00:00:01 |
| 9 | TABLE ACCESS FULL | TDEVBDR | 238 | 2380 | 3 | 00:00:01 |
| 10 | INDEX RANGE SCAN | IDX_NBE21 | 1 | 14 | 0 | |
| 11 | TABLE ACCESS BY INDEX ROWID | TFUTFXP | 1 | 12 | 1 | 00:00:01 |
| 12 | INDEX RANGE SCAN | IDX_NBE22 | 1 | | 0 | |
| 13 | TABLE ACCESS BY INDEX ROWID | TVALSBLFUTBDR | 1 | 13 | 1 | 00:00:01 |
| 14 | INDEX RANGE SCAN | IVALSBLFUTBDR1| 1 | | 0 | |
------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."CA3ISODEV"="T"."CODDEV")
7 - filter("TFXPPRODUCT"."DESKID"-0=13)
8 - access("TFXPPRODUCT"."CODFIX"="T"."CODFUT")
10 - access("F"."MNETIT"="T"."MNETIT")
12 - access("X"."MNEFUT"="T"."MNETIT")
14 - access("V"."CODFUT"="T"."CODFUT")
I m quite surprised by this behaviour.
Could anyone have any idea ?
Regards
nikko.
] 
Blog
Search
Help
Members
Register
Login
Home
