INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

The query is a 3 table join involving 2 local table and 1 remote
table.

When distributions exist in 12.10.xC7 the set explain output
looks like this (and the query take > 30 minutes to complete)

select ...
FROM
        driver d,
        driver_tpay dt,
        person pn

WHERE pn.person_id = dt.driver_id
AND dt.tpay_type_code = 40
AND pn.person_id = d.driver_id
AND d.service_co_code='WM'
AND d.vendor_nbr!=694000
AND pn.win_nbr IS NOT NULL
AND dt.paid_date =
        (select MAX(dt2.paid_date)  FROM driver_tpay dt2
          WHERE dt2.tpay_type_code = 40)
AND dt.last_change_ts =
        (select MAX(dt3.last_change_ts)
          FROM driver_tpay dt3 WHERE dt3.driver_id =
dt.driver_id
          AND dt3.tpay_type_code = dt.tpay_type_code
          AND dt3.paid_date = dt.paid_date)

Estimated Cost: 2679008
Estimated # of Rows Returned: 203

  1) jrenaut.d: SEQUENTIAL SCAN

        Filters: (jrenaut.d.service_co_code = 'WM' AND
jrenaut.d.vendor_nbr != 694000 )

  2) jrenaut.pn: INDEX PATH

        Filters: jrenaut.pn.win_nbr IS NOT NULL

    (1) Index Name: jrenaut.person_pk
        Index Keys: person_id   (Serial, fragments: ALL)
        Lower Index Filter: jrenaut.pn.person_id =
jrenaut.d.driver_id
NESTED LOOP JOIN

  3) jrenaut.dt: REMOTE PATH
    REMOTE SESSION ID FOR 'walrem' is 35

    Remote SQL Request:
    select x2.payment_amt ,x2.driver_id ,x2.tpay_type_code
,x2.paid_date ,x2.last_change_ts from
realdb:"jrenaut".driver_tpay x2 where ((((x2.tpay_type_code = 40
) AND (x2.paid_date = (select max(x0.paid_date ) from
realdb:"jrenaut".driver_tpay x0 where (x0.tp
    ay_type_code = 40 ) ) ) ) AND (x2.last_change_ts = (select
max(x1.last_change_ts ) from realdb:"jrenaut".driver_tpay x1
where (((x1.driver_id = x2.driver_id ) AND (x1.paid_date =
x2.paid_date ) ) AND (x1.tpay_type_code = 40 ) ) ) ) ) AND
(x2.driver_id = ?
    ) )
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost: 2544447
    Estimated # of Rows Returned: 1

      1) jrenaut.dt2: REMOTE PATH
        REMOTE SESSION ID FOR 'walrem' is 35


    Subquery:
    ---------
    Estimated Cost: 239
    Estimated # of Rows Returned: 1

      1) jrenaut.dt3: REMOTE PATH
        REMOTE SESSION ID FOR 'walrem' is 35


When the distributions are dropped on the remote table the set
explain looks like this, and the query completes in ~1 minute

select
...
FROM
        driver d,
        driver_tpay dt,
        person pn

WHERE pn.person_id = dt.driver_id
AND dt.tpay_type_code = 40
AND pn.person_id = d.driver_id
AND d.service_co_code='WM'
AND d.vendor_nbr!=694000
AND pn.win_nbr IS NOT NULL
AND dt.paid_date =
        (select MAX(dt2.paid_date)  FROM driver_tpay dt2
          WHERE dt2.tpay_type_code = 40)
AND dt.last_change_ts =
        (select MAX(dt3.last_change_ts)
          FROM driver_tpay dt3 WHERE dt3.driver_id =
dt.driver_id
          AND dt3.tpay_type_code = dt.tpay_type_code
          AND dt3.paid_date = dt.paid_date)

Estimated Cost: 97546
Estimated # of Rows Returned: 1

  1) jrenaut.d: SEQUENTIAL SCAN

        Filters: (jrenaut.d.service_co_code = 'WM' AND
jrenaut.d.vendor_nbr != 694000 )

  2) jrenaut.pn: INDEX PATH
        Filters: jrenaut.pn.win_nbr IS NOT NULL

    (1) Index Name: jrenaut. 101_4
        Index Keys: person_id   (Serial, fragments: ALL)
        Lower Index Filter: jrenaut.pn.person_id =
jrenaut.d.driver_id
NESTED LOOP JOIN

  3) jrenaut.dt: REMOTE PATH
    REMOTE SESSION ID FOR 'walrem' is 44

    Remote SQL Request:
    select x2.payment_amt ,x2.driver_id ,x2.tpay_type_code
,x2.paid_date ,x2.last_change_ts from
waldata:"jrenaut".driver_tpay x2 where ((x2.tpay_type_code = 40
) AND (x2.paid_date = (select max(x0.paid_date ) from
waldata:"jrenaut".driver_tpay x0 where (x0.tp
    ay_type_code = 40 ) ) ) )
        Filters: jrenaut.dt.last_change_ts = 

    (1) Index Name: driver_tpay_idx2
        Index Keys: driver_id tpay_type_code
        Lower Index Filter: (jrenaut.dt.driver_id =
jrenaut.d.driver_id AND jrenaut.dt.tpay_type_code = 40 )
NESTED LOOP JOIN

    Subquery:
    ---------
    Estimated Cost: 81142
    Estimated # of Rows Returned: 1

      1) jrenaut.dt2: REMOTE PATH
        REMOTE SESSION ID FOR 'walrem' is 44


    Subquery:
    ---------
    Estimated Cost: 6
    Estimated # of Rows Returned: 1
      1) jrenaut.dt3: REMOTE PATH
        REMOTE SESSION ID FOR 'walrem' is 44

        Remote SQL Request:
        select max(x1.last_change_ts ) from
waldata:"jrenaut".driver_tpay x1 where (((x1.driver_id = ? ) AND
(x1.tpay_type_code = 40 ) ) AND (x1.paid_date = ? ) )

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS prior to 12.10.xC13.                            *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Query with remote distributed table taking worse plan when   *
* distributions exist on the remote table than when the        *
* distributions are dropped.                                   *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.12.2018
03.10.2019
03.10.2019

12.10.xC13