Federated query optimization

[guido_enexis]

Hi, I have a question about the query optimizer.

We have a query combines data from two different databases.

select ... from

stationeansjv ses [DB 1]

left join realenergydagagg [DB 2] red on ses.stationsnummer = red.deviceid

inner join stationnedusjvdagagg sns [DB 1] on ses.stationsnummer = sns.stationsnummer

left join stationpir sp [DB1] on ses.stationsnummer = sp.stationsnummer

left join energiediefstal.stations sl [DB 2] on ses.stationsnummer = sl.stationsnummer ;

I have collected table statistics.

If i look at the queryplan the optimizer chooses to access the tables in the same order as the query. This is not the best plan. I would expect the optimizer to recognize the two source databases and change the join order to be able to push down as much of the query as possible to the underlying databases.

I can influence the query plan by rewriting my query ....

select ... from

stationeansjv ses [DB1]

left join stationpir sp [DB1] on ses.stationsnummer = sp.stationsnummer

inner join stationnedusjvdagagg sns [DB 1] on ses.stationsnummer = sns.stationsnummer

left join realenergydagagg red [DB 2] on ses.stationsnummer = red.deviceid

left join energiediefstal.stations sl [DB 2] on ses.stationsnummer = sl.stationsnummer

;

This is a solution, but i would like the optimizer to do the work for me. Is there some better way for me to achieve this instead of rewriting my own queries.

Regards,

Guido

[van.halbert]

Can I first asked, which translator are you using?

[guido_enexis]

DB1 is Oracle, DB2 is MS SQL Server (on Azure SQL DB) I am testing my querys using JBDS (Database development) connecting to my VDB.

[van.halbert]

Can you post your VDB or a version that replicates the issue?

Thanks.

[van.halbert]

Also, which version of Teiid you are using?

[rareddy]

Guido,

If you want optimizer to take size of the data into account, you must provide the "costing" information on the tables, otherwise Teiid does not have any idea how many rows does each table contains. If you are using the Designer, there is a property called "cardinality" you need to provide the size of the table in that column for all the tables involved. Then rebuild the VDB, and test. Then you should see any optimizations based on cost. If that is not satisfactory, then attach the whole query plan along with at least sample VDB structure to duplicate the error. Then we consider for enhancement.

Ramesh..

[guido_enexis]

Hi Ramesh/Van,

I am using Teiid version 8.12.5 and jboss-eap-6.4.9.CP

I double checked, but there are cardinalities provided for the tables used in my queries.

I also checked the query plans, there are costs (not -1)

I will attach the query, query plans and the vdb (exported as dynamic vdb).

az = azure sql database, or = oracle database

mind the order of the table joins

QUERY 1

select

ora_ses.stationsnummer,

ora_ses.aansluiting_ean,

ora_ses.profielcategorie,

ora_ses.asset_soort,

ora_ses.sjv_laag_tarief,

ora_ses.sjv_normaal_tarief,

az_red.deviceid,

az_red.value,

az_red.description,

az_red.date,

ora_sns.verbruik_totaal,

ora_sp.verbruiksegment as PIR_Verbruik,

ora_sp.type_meter as PIR_TypeMeter,

ora_sp.marktpartij_ean as PIR_Marktpartij,

az_sl.lat as Locatie_H,

az_sl.long as Locatie_B

from stationeansjv ora_ses

left join realenergydagagg az_red on ora_ses.stationsnummer = az_red.deviceid

inner join stationnedusjvdagagg ora_sns on ora_ses.stationsnummer = ora_sns.stationsnummer

left join stationpir ora_sp on ora_ses.stationsnummer = ora_sp.stationsnummer

left join energiediefstal.stations az_sl on ora_ses.stationsnummer = az_sl.stationsnummer

;

QUERY 2

select

ora_ses.stationsnummer,

ora_ses.aansluiting_ean,

ora_ses.profielcategorie,

ora_ses.asset_soort,

ora_ses.sjv_laag_tarief,

ora_ses.sjv_normaal_tarief,

az_red.deviceid,

az_red.value,

az_red.description,

az_red.date,

ora_sns.verbruik_totaal,

ora_sp.verbruiksegment as PIR_Verbruik,

ora_sp.type_meter as PIR_TypeMeter,

ora_sp.marktpartij_ean as PIR_Marktpartij,

az_sl.lat as Locatie_H,

az_sl.long as Locatie_B

from stationeansjv ora_ses

inner join stationnedusjvdagagg ora_sns on ora_ses.stationsnummer = ora_sns.stationsnummer

left join stationpir ora_sp on ora_ses.stationsnummer = ora_sp.stationsnummer

left join realenergydagagg az_red on ora_ses.stationsnummer = az_red.deviceid

left join energiediefstal.stations az_sl on ora_ses.stationsnummer = az_sl.stationsnummer

;

Regards,

Guido

[guido_enexis]

The attachments...

[shawkins]

Hi Guido,

[TEIID-5016] Exploit left outer join associativity prior to join planning - JBoss Issue Tracker  and [TEIID-3652] Teiid could push some outer joins to the source database, but does not - JBoss Issue Tracker are related to this topic.  Unfortunately neither addressed this case.

If you log an issue with these details I can address this in the upstream.  You will need to create a support case for inclusion in product branches.

Steve

[guido_enexis]

Hi Steven, I am quite new to this. Can you tell me how and where to create a support case? Regards, Guido

[rareddy]

You can create a JIRA issue at [TEIID-5016] Exploit left outer join associativity prior to join planning - JBoss Issue Tracker

If you are Redhat customer then you can open support case using Redhat customer portal

[shawkins]

Logged as [TEIID-5212] Additional optimization of left outer joins - JBoss Issue Tracker