11 Replies Latest reply on Jan 12, 2018 2:54 PM by Steven Hawkins

    Federated query optimization

    Guido Puts Newbie

      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