Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Oracle Index Scan When Converting Dates TO_TIMESTAMP

  • 9 January 2017
  • 8 replies
  • 196 views
D

Hi,



I have an index in Oracle of type ‘date’ (with no timestamp).



When filtering on this field in the explore, I am unable to avoid a TO_TIMESTAMP() cast being added by Looker.



This results in an index scan rather than an index seek.



I have tried various solutions such as changing the dimension LookML with no luck.



Any ideas on how Looker can use an index in Oracle of type ‘date’?



Thanks,


Dean.

8 replies

Morgan
Rank
Userlevel 3
Badge

Hi Dean,



Could you please visit help.looker.com and provide some more info? Please note what timezone settings you are using, the dimension definition, and an example of the generated SQL.



Thanks!



Morgan

D

Thanks Morgan.



I have sent an email to Support.



Regards,


Dean.

K

Hello,


I have the exact same issue. Did you find a fix?



Thanks!


Kapil

rachel.johnson
Rank
Userlevel 2

Hey @kpowar,



Could you please visit help.looker.com so we can investigate this in depth? Thanks!

K


Email sent.

D

Hi Kapil7,



A fix was released for this, although a colleague recently came back to this issue and is currently in discussion with the Looker Support team in testing it out.



Thanks,


Dean.

K

Thanks Dean! We got the same response from support. Apparently this issue was fixed in version 4.8.


We are planning to update Looker next week to see if this resolves the issue…

K

RESOLVED!


As suggested by the Looker support person, we upgraded to the latest stable version (4.20) and also added ‘datatype: date’ to the LookML as shown below…



dimension_group: date_column {


type: time


datatype: date


timeframes: [day_of_week, day_of_week_index, date, month, year]


convert_tz: no


sql: ${TABLE}.DATE_COLUMN ;;


}



This changed the casting


from


TO_TIMESTAMP(TRUNC(CURRENT_TIMESTAMP))…


to


CAST(TRUNC((TO_TIMESTAMP(TRUNC(CURRENT_TIMESTAMP))…



And changed the execution path from index full scan 😖 to index range scan 🙂 and as expected the queries are faster.


Hope this helps!

Reply


Terms of Service