Native query on Oracle use TO_TIMESTAMP function for date fields

[VishalSukhija]

community.powerbi.com/t5/Power-Query/Native-query-on-Oracle-use-TO-TIMESTAMP-function-for-date-fields/m-p/1938663

In Power Query for Oracle DB queries involving fields with the DATE type all filtering through query-folding is using the TO_TIMESTAMP function. As incremental refresh is implemented, we cannot use SQL statement option.

This causes issue as Oracle invokes internal function on date field used in filter to match timestamp. This is problematic as for large tables the quesry goes for full table scan instead of using index created on date field. This results in performance issues and the refresh failures invloving fact tables with huge data.

Index is consider if TO_TIMESTAMP is replaced with TO_DATE function

Is there a way to avoid TO_TIMESTAMP function being called here?

Thanks,

Vishal

[v-robertq-msft]

Hi,

Have you tried the TO_DATE function and use the index to improve the speed?

You can go to the Oracle community to ask for the suggestions of experts in Oracle.

Best Regards,

Community Support Team _Robert Qin

[VishalSukhija]

Hi,

It's not about using TO_DATE in Oracle. It's Power BI using TO_TIMESTAMP function while firing sql in Oracle which I am unable to change. 

This happens when Incremental Refresh is implemented and we have to define two parameters RangeStart and RangeEnd, and use this to filter table on cdc date field. The native query from this step uses to_timestamp on the parameters.

I tried using custom functions on parameters to change the format to datetime, still to_timestamp is used in native query.

Found a post with same issue explained:

https://social.technet.microsoft.com/Forums/en-US/08146ac8-d691-4a95-b58f-a9ccc518eeae/oracle-query-...

Thanks,

Vishal