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

[rodion.myronov]

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,

Rodion

[shawkins]

We do detect this situation, but are not performing the necessary rewrite here due to a premature check on the predicate that makes us think it's in a final form.  Can you log an issue?

[rodion.myronov]

Steven, thanks for your answer.

Case 01123522 has been created for this issue.

Meanwhile, are you aware of any workarounds (except function-based index creation)?

Thanks in advance,

Rodion Myronov

[shawkins]

Unfortunately there is nothing that wouldn't involve coding that I can see.

[shawkins]

This will be addressed by https://issues.jboss.org/browse/TEIID-3011

[rodion.myronov]

Steven, many thank. Such a fast and constructive response is really fantastic.