I can't find an explanation for the sql requests.

[juvenalb]

There are some tables in oracle.

So the question is, why sometimes designs like this.

select * from TABLE t where to_char(t.field) = 'somevalue'

works better than it does.

select * from TABLE t where t.field = 'somevalue'

This field is indexed. It's the same with to_date. I feel like an index for unknown reasons is not covered by straight requests. This is particularly evident in complex requests where several tables are combined.

Version oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

No change in the number of data in the choice.

Plan to be used to_date for TABLE2

select *
  from TABLE1, TABLE2, TABLE3
 where TABLE1.id = TABLE2.TABLEID
   and TABLE1.id = TABLE3.TABLEID
   and TABLE3.DATAFIELD + 1 > trunc(sysdate)
   and TABLE1.DATAFIELD + 1 > trunc(sysdate)
   and ((TABLE2.DATAFIELD = trunc(sysdate)) or (TABLE1.ID = nvl(0, 0)))

Plan hash value: 2464832983
| Id  | Operation                       | Name              |
|   0 | SELECT STATEMENT                |                   |

|   1 |  CONCATENATION                  |                   |

|   2 |   NESTED LOOPS                  |                   |

|   3 |    NESTED LOOPS                 |                   |

|   4 |     NESTED LOOPS                |                   |

|   5 |      TABLE ACCESS BY INDEX ROWID| TABLE1$           |

|   6 |       INDEX UNIQUE SCAN         | PK_TABLE1$        |

|   7 |      TABLE ACCESS BY INDEX ROWID| TABLE2$           |

|   8 |       INDEX RANGE SCAN          | TABLE2$TABLEID    |

|   9 |     INDEX RANGE SCAN            | TABLE3$TABLEID    |

|  10 |    TABLE ACCESS BY INDEX ROWID  | TABLE3$           |

|  11 |   HASH JOIN                     |                   |

|  12 |    TABLE ACCESS FULL            | TABLE2$           |

|  13 |    NESTED LOOPS                 |                   |

|  14 |     NESTED LOOPS                |                   |

PLAN_TABLE_OUTPUT
|  15 |      TABLE ACCESS FULL          | TABLE3$           |

|  16 |      INDEX UNIQUE SCAN          | PK_TABLE1$        |

|  17 |     TABLE ACCESS BY INDEX ROWID | TABLE1$           |

PICLEASE TO_date TABLE2

select *

from TABLE1, TABLE2, TABLE3

where TABLE1.id = TABLE2.TABLEID

and TABLE1.id = TABLE3.TABLEID

and TABLE3.DATAFIELD + 1 > trunc(sysdate)

and TABLE1.DATAFIELD + 1 > trunc(sysdate)

and ((to_date(TABLE2.DATAFIELD) = trunc(sysdate)) or (TABLE1.ID = nvl(0, 0)))


Plan hash value: 1081346625
| Id  | Operation                       | Name              |
|   0 | SELECT STATEMENT                |                   |

|   1 |  CONCATENATION                  |                   |

|   2 |   NESTED LOOPS                  |                   |

|   3 |    NESTED LOOPS                 |                   |

|   4 |     NESTED LOOPS                |                   |

|   5 |      TABLE ACCESS BY INDEX ROWID| TABLE1$           |

|   6 |       INDEX UNIQUE SCAN         | PK_TABLE1$        |

|   7 |      TABLE ACCESS BY INDEX ROWID| TABLE2$           |

|   8 |       INDEX RANGE SCAN          | TABLE2$TABLEID    |

|   9 |     INDEX RANGE SCAN            | TABLE3$TABLEID    |

|  10 |    TABLE ACCESS BY INDEX ROWID  | TABLE3$           |

|  11 |   NESTED LOOPS                  |                   |

|  12 |    NESTED LOOPS                 |                   |

|  13 |     NESTED LOOPS                |                   |

|  14 |      TABLE ACCESS FULL          | TABLE2$           |

PLAN_TABLE_OUTPUT
|  15 |      TABLE ACCESS BY INDEX ROWID| TABLE1$           |

|  16 |       INDEX UNIQUE SCAN         | PK_TABLE1$        |

|  17 |     INDEX RANGE SCAN            | TABLE3$TABLEID    |

|  18 |    TABLE ACCESS BY INDEX ROWID  | TABLE3$           |

[authera]

In such cases, it is important to consider the entire request as a whole rather than its individual elements. Because a change in the optimist ' s preferences in one table may result in a fundamental change in its approach to other tables in the same request.

In this particular case, the date field index was not at all. Oracle never tried to use it. Functioning to_date the field had led to the optimist ' s most likely failure to use the statistics on the field and to a large number of entries in the table on the given condition. Which, in turn, forced the optimist to choose a more reasonable plan to label the other two tables and to follow the other index tables.

And in the option, no. to_date The optimizer used the statistic of the field of the date, thought it would select little data on a specific date. For the small number of data selected in the first step, he considered that full scanning would be better achieved, without using the second table indices, which had led to an increase in the time of implementation, as the data were more than expected by the optimist.

P.S. From OR in full request, the optimist followed two different requests for part one OR and for part two (which was not optimal). The situation was corrected by substitution OR so that only the fields of one table are included.