Native query on Oracle use TO_TIMESTAMP function for date fields

[VishalSukhija]

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-jingzhang]

Hi @VishalSukhija 

Is the Native query from View Native Query in Power Query Editor? You apply filter on datetime column with GUI?

Regards,
Community Support Team _ Jing

[VishalSukhija]

Hi,

Yes the native query is from View Native Query in Power BI, and filter is applied on datetime column.

Thanks,

Vishal