Force PDI to use DATE instead of TIMESTAMP in Parameterized SQL Queries
If your query optimizer is incorrectly using the predicate TIMESTAMP, it is likely because the JDBC driver/database normally converts the data type from a TIMESTAMP to a DATE. In special circumstances this casting prevents the query optimizer of the database not to use the correct index.
Use a Select Values
step and set the Precision
to 1 and Value
to DATE. This forces the parameter to set as a DATE instead of a TIMESTAMP.
For example, if Oracle says it cannot use the index, and generates an error message that states:
The predicate DATE used at line ID 1 of the execution plan contains an implicit data type conversion on indexed column DATE. This implicit data type conversion prevents the optimizer from selecting indices on table A.
After changing the Precision
to 1, setting the Value as a DATE, the index can be used correctly.