Poor DV Performance with UNION and Columns of different datatypes
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.