Poor DV Performance with UNION and Columns of different datatypes

Solution Verified - Updated -

Issue

  • 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

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. How can I workaround this?

Environment

  • Red Hat JBoss Data Virtualization (DV) 6.0

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.

Current Customers and Partners

Log in for full access

Log In

New to Red Hat?

Learn more about Red Hat subscriptions

Using a Red Hat product through a public cloud?

How to access this content