4 Replies Latest reply on Apr 23, 2018 10:44 AM by tomesc

Avoid full table scans

tomesc Apr 23, 2018 7:05 AM

I would like to avoid (undesired) full table scans by teiid.

My translator does not support joins. Therefore teiid generates separate queries for the left and right table automatically.

Depending on the where-clause teiid makes a full table scan or read the records using a search statement. In my case the right table is very large, thus it is not reasonable to read the whole table.

The following example would select 3 records in the left table.

select le.ISN, ri.ISN

FROM "adabasc"."FILE_0009" le JOIN "adabasc"."FILE_0009$F0" ri on ri.ISN=le.ISN

where le.AA>='50005800' and le.AA<='50005900';

translator:

SELECT FILE_0009.ISN FROM FILE_0009 WHERE FILE_0009.AA >= '50005800' AND FILE_0009.AA <='50005900'

SELECT FILE_0009.ISN FROM FILE_0009

In the case above Teiid reads the right table completely.

If I use the following where-clause the translator does not make a full table scan on the right table:

...

where le.AA='50005800' or le.AA='50005801' or le.AA='50005900';

translator:

SELECT FILE_0009.ISN FROM FILE_0009 WHERE FILE_0009.AA = '50005800' OR FILE_0009.AA = '50005801' OR FILE_0009.AA = '50005900'

SELECT FILE_0009.ISN FROM FILE_0009 WHERE FILE_0009.ISN = 1251 OR FILE_0009.ISN = 200 OR FILE_0009.ISN = 1

Since the end-user does not know how teiid operates I would like avoid the full table scan in the first example. How can I do that?

Thank's in advance.

Thomas

(WILDFLY / TEIID 9.2.3)

1. Re: Avoid full table scans

rareddy Apr 23, 2018 8:44 AM (in response to tomesc)

Looks like you have given your translator has the equality (=) check but not given the capability for < , <=, >, >= operation capabilities. Take look at "CompareCriteriaOrdered" and "CompareCriteriaOrderedExclusive" capabilities. For full translator capabilities see Translator Capabilities · Teiid Documentation

Even after this is done the JOINS will separate queries. However, if you provide the cardinality/cost information on the tables that your source model is generating/defining then optimizer is smarter to query left first and then use the results to fetch the right rather than the full scan. See the Schema Object DDL · Teiid Documentation for how to define this on a foreign table. If you are using Designer, then on the properties panel of the table you will see entry for this property.

HTH.

Ramesh..
Actions
2. Re: Avoid full table scans

tomesc Apr 23, 2018 10:07 AM (in response to rareddy)

Thanks Ramesh, since my translator already had have the mentioned capabilites I think the "cardinality" was the reason. After I was setting the value to 10 million (for test), the right table was read with a where-clause (large combination of OR conditions).

FYI, below are my translator capabilites.
Thanks a lot.
Thomas

    @Override
    public boolean supportsCompareCriteriaEquals() {
        return true;
    }

    @Override
    public boolean supportsCompareCriteriaOrdered() {
        return true;
    }

    @Override
    public boolean supportsCompareCriteriaOrderedExclusive() {
        return true;
    }

    @Override
    public boolean supportsOrCriteria() {
        return true;
    }

    @Override
    public boolean supportsOnlyLiteralComparison() {
        return true;
    }
Actions
3. Re: Avoid full table scans

rareddy Apr 23, 2018 10:29 AM (in response to tomesc)

Great, if this source you are writing the translator to is not an internal source, please consider donating the code to the community
Actions
4. Re: Avoid full table scans

tomesc Apr 23, 2018 10:44 AM (in response to rareddy)

rareddy schrieb:

Great, if this source you are writing the translator to is not an internal source, please consider donating the code to the community
I plan so, as soon as it is more stable. The translator is for Adabas C (Software AG). I know there is already one on the teiid-marketplace on github which is using the CONNEX JDBC driver. Our connector use open source only
Actions

Go to original post