5 Replies Latest reply on Jun 17, 2014 11:05 AM by Rodion Myronov

    Columns of different type in UNION ALL. Query performance question.

    Rodion Myronov Newbie

      Hi All,


      In my Teiid project, I have two data sources, A and B. Both are Oracle databases.


      Datasource A contains table T of the following structure:

      • ID number
      • VALUE varchar2

      Datasource B contains very similar table T. The only difference is "ID" field data type:

      • ID  varchar2
      • VALUE varchar2


      T.ID field is indexed in both databases.


      Now, I use Teiid to virtualize A and B data sources. I have separate model for each data source and a common model used by end users. Common model contains virtual table T defined as:


      select convert(id, string) as ID, VALUE, 'A' as DATA_SOURCE from A_MODEL.T

      union all

      select ID, VALUE, 'B' as DATA_SOURCE from  B_MODEL.T


      As you can see, I have to convert IDs from data source A to represent two data sources as one.


      Now, the issue comes when user executes query like this:

      select * from COMMON_MODEL.T where DATA_SOURCE='A' and ID in ('1', '2')

      which is translated by Teiid into the following source query:

      select * from A_MODEL.T where convert(ID,string) in ('1','2')

      which is translated into the following Oracle query:

      select * from T where to_char(id) in ('1','2')


      "to_char(id) in ('1','2')" predicate does not allow Oracle to use index built on ID field, so query performance is poor.

      I do understand that function-based index on to_char(id) would help. However, source databases are not under my control and I'm looking for any other, Teiid-side way to improve such queries' performance. 


      So, could you please advice any Teiid trick to make it use predicates like "ID in ('1','2')" instead of "to_char(ID) in ('1','2')" for data source A? I have already tried additional predicates like "ID = convert(ID, string)", but it does not help.


      Thanks in advance,