Home » RDBMS Server » Performance Tuning » Adding +0 and -0 to a number predicate produces differents plans. (10.2.0.4)
Adding +0 and -0 to a number predicate produces differents plans. [message #505073] Fri, 29 April 2011 02:44 Go to next message
nikko
Messages: 9
Registered: September 2010
Junior Member
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.
Re: Adding +0 and -0 to a number predicate produces differents plans. [message #505178 is a reply to message #505073] Fri, 29 April 2011 16:03 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sound like an optimizer quirk. Submit a TAR with Oracle and see what they say. Is this actually a problem or are you just curious? You should note same to oracle when you submit the tar.

Kevin
Previous Topic: What is causing the lock despite such an decent update state
Next Topic: Procedure code taking longer time to execute
Goto Forum:
  


Current Time: Fri Mar 29 02:41:45 CDT 2024