[teiid-issues] [JBoss JIRA] (TEIID-2858) Database use of char[>1] cause using Convert(x, x) and negates using an index

[Tom Johnston (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2858?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12946603#comment-12946603 ] 

Tom Johnston commented on TEIID-2858:
-------------------------------------

As a general rule any function will negate the use of any index on the field. Though there are "function-based-indexes" these are not options with COTS products.  Some consideration must be made when a potential fix has ramifications that would diminish the performance.  "Middleware" already has something to prove when it comes to NOT heavily impacting performance so let's not purposely do something that can impact performance.
                
> Database use of char[>1] cause using Convert(x,x) and negates using an index
> ----------------------------------------------------------------------------
>
>                 Key: TEIID-2858
>                 URL: https://issues.jboss.org/browse/TEIID-2858
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.4.1
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>
> When a database defines a char of length greater than 1, a convert(x,x) is being used in the criteria, and thereby, negating the use of an index and causing table scans.  
> Note:  There is no option to change the database schemas, because they are from a COTS product.
> I would think there needs to be a solution in the translator such that if it can be detected that its for a char column and that it does not do the convert. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira