[teiid-issues] [JBoss JIRA] Moved: (TEIID-968) Timestamp literals with fractional seconds result in source query execution performance when being compared to less percise time types

[Van Halbert (JIRA)]

     [ https://jira.jboss.org/jira/browse/TEIID-968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Van Halbert moved JBEDSP-1143 to TEIID-968:
-------------------------------------------

              Project: Teiid  (was: JBoss Enterprise Data Services Platform)
                  Key: TEIID-968  (was: JBEDSP-1143)
          Component/s:     (was: Connectors)
                           (was: Query)
        Fix Version/s:     (was: Westport)
                           (was: 5.5.3)
                           (was: 5.5.4)
    Affects Version/s: 7.1
                           (was: 5.5.3)
                           (was: 5.5.4)


> Timestamp literals with fractional seconds result in source query execution performance when being compared to less percise time types
> --------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-968
>                 URL: https://jira.jboss.org/jira/browse/TEIID-968
>             Project: Teiid
>          Issue Type: Feature Request
>    Affects Versions: 7.1
>         Environment: MM 5.5.3
> Oracle 10i
> Oracle ANSI Connector
>            Reporter: Larry O'Leary
>            Assignee: Larry O'Leary
>         Attachments: mylyn-context.zip
>
>   Original Estimate: 0 minutes
>  Remaining Estimate: 0 minutes
>
> Seeing that the only "time" type for MetaMatrix is timestamp and Oracle uses DATE to store time and TIMESTAMP to store time with fractional seconds we need a method of sending a timestamp as a literal that Oracle can use as a DATE instead of requiring the comparison to be normalized to TIMESTAMP as this results in a huge performance hit within Oracle.
> It is possible to change this behaviour by actually setting a date only column to MetaMatrix date in the model but this does not account for situations where a DATE column in Oracle holds date and time information.  In those cases, I would still need to use timestamp for MetaMatrix and this would result in a huge performance impact.
> Oracle will automatically use the widest type possible when doing comparisons so if I store date only data in a DATE column and then attempt to query using criteria on that column such ad {d'2000-01-01'} the date literal gets converted to {ts'2000-01-01 00:00:00.0'} prior to it being sent to Oracle.  This will result in Oracle converting my DATE column into a TIMESTAMP column to handle the comparison.  
> The current DataDirect driver we are using actually accounts for this issue when dealing with timestamp literals.  Basically, if the timestamp literal does not include fractional seconds, the driver will pass it to Oracle as a date instead of a timestamp.  This greatly improves performance in these situations.  
> So, if the current logic is kept, but we simply truncate the timestamp literal to remove the fractional seconds, we get the advantage of the driver performing better type mapping in this situation.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira