Question

Oracle Index Scan When Converting Dates TO_TIMESTAMP

  • 9 January 2017
  • 8 replies
  • 168 views

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

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!

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…

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.


Email sent.

Userlevel 2

Hey @kpowar,


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

Hello,

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


Thanks!

Kapil

Thanks Morgan.


I have sent an email to Support.


Regards,

Dean.

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

Reply