This discussion is archived
3 Replies Latest reply: Nov 30, 2010 8:27 AM by 661228 RSS

"Distinct" not pushed down in outer join

661228 Newbie
Currently Being Moderated
Hello,

OK, going for the trifecta here...

In the same logical service I have been working on, I have a 'group by' that does not get pushed down as 'select distinct'. The result is unacceptable performance, so I need to get past the problem. The service is below. If I make a service using just the ST_TERRITORY_GEO portion of it, using a fixed value for the TERRITORY_ID, it works correctly and pushes the 'select distinct' to the database.

Any idea what's going on here, or how I can fix it?

Thanks!

Jeff



declare function tns:getAllTerritoryHierarchies() as element(ter:TerritoryHierarchy)*{
     for $hier in v_s:V_ST_TERRITORY_HIERARCHY()
     return
     <ter:TerritoryHierarchy>
<ter:EffectiveDate>{fn:data($hier/EFF_DATE)}</ter:EffectiveDate>
<ter:EndDate>{fn:data($hier/END_DATE)}</ter:EndDate>
<ter:StructureId>{fn:data($hier/STRUCTURE_ID)}</ter:StructureId>
<ter:StructureName>{fn:data($hier/STRUCTURE_NAME)}</ter:StructureName>
<ter:AlignmentId>{fn:data($hier/ALIGNMENT_ID)}</ter:AlignmentId>
<ter:AlignmentName>{fn:data($hier/ALIGNMENT_NAME)}</ter:AlignmentName>
<ter:AlignmentTypeCodeId>{fn:data($hier/ALIGNMENT_TYPE_CODE_ID)}</ter:AlignmentTypeCodeId>
<ter:AreaId>{fn:data($hier/AREA_ID)}</ter:AreaId>
<ter:AreaName>{fn:data($hier/AREA_NAME)}</ter:AreaName>
<ter:RegionId>{fn:data($hier/REGION_ID)}</ter:RegionId>
<ter:RegionName>{fn:data($hier/REGION_NAME)}</ter:RegionName>
<ter:TerritoryId>{fn:data($hier/TERRITORY_ID)}</ter:TerritoryId>
<ter:TerritoryName>{fn:data($hier/TERRITORY_NAME)}</ter:TerritoryName>
{
for $ST_TERRITORY_GEO in st_:ST_TERRITORY_GEO()
where $ST_TERRITORY_GEO/TERRITORY_ID eq $hier/TERRITORY_ID
where $ST_TERRITORY_GEO/EFF_DATE le fn:current-dateTime() and $ST_TERRITORY_GEO/END_DATE ge fn:current-dateTime()
where $ST_TERRITORY_GEO/ACTIVE_FLAG eq 'Y'
group by $ST_TERRITORY_GEO/COUNTRY_CODE as $Country
return
<ter:Country>{fn:data($Country)}</ter:Country>
}
</ter:TerritoryHierarchy>

};
  • 1. Re: "Distinct" not pushed down in outer join
    mikereiche Pro
    Currently Being Moderated
    This is an Oracle DB, correct?

    1)

    Try replacing the "inner loop" with the following. You'll end up with two sql statements

    {fn-bea:fence(
    for $ST_TERRITORY_GEO in st_:ST_TERRITORY_GEO()
    where $ST_TERRITORY_GEO/TERRITORY_ID eq $hier/TERRITORY_ID
    where $ST_TERRITORY_GEO/EFF_DATE le fn:current-dateTime() and $ST_TERRITORY_GEO/END_DATE ge fn:current-dateTime()
    where $ST_TERRITORY_GEO/ACTIVE_FLAG eq 'Y'
    group by $ST_TERRITORY_GEO/COUNTRY_CODE as $Country
    return
    <ter:Country>{fn:data($Country)}</ter:Country>
    )}

    2) If the above is not satisfactory - what sql were you hoping to get?

    export and email the dataspace to me so I can experiment. Indicate which data service/function this is.
  • 2. Re: "Distinct" not pushed down in outer join
    661228 Newbie
    Currently Being Moderated
    The bea:fence call forces the inner loop into its own query that gets run once for each item in the outer loop. That means it runs hundreds of subqueries, so the performance is not good. It does generate the "distinct".

    Here's the overall query I get without the fence:

    SELECT t1."ALIGNMENT_ID" AS c3, t1."ALIGNMENT_NAME" AS c4, t1."ALIGNMENT_TYPE_CODE_ID" AS c5,
    t1."AREA_ID" AS c6, t1."AREA_NAME" AS c7, t1."EFF_DATE" AS c8, t1."END_DATE" AS c9,
    t1."REGION_ID" AS c10, t1."REGION_NAME" AS c11, t1."STRUCTURE_ID" AS c12,
    t1."STRUCTURE_NAME" AS c13, t1."TERRITORY_ID" AS c14, t1."TERRITORY_NAME" AS c15, t3.c1, t3.c2
    FROM "CCSUSERDV"."V_ST_TERRITORY_HIERARCHY" t1
    LEFT OUTER JOIN (
    SELECT t2."COUNTRY_CODE" AS c1, t2."TERRITORY_ID" AS c2
    FROM "CCSUSERDV"."ST_TERRITORY_GEO" t2
    WHERE ((t2."ACTIVE_FLAG" = 'Y') AND (t2."EFF_DATE" <= ?) AND (t2."END_DATE" >= ?))
    ) t3
    ON (t3.c2 = t1."TERRITORY_ID")
    ORDER BY t1."TERRITORY_ID" ASC, t3.c1 ASC NULLS FIRST

    What I need is

    SELECT t1."ALIGNMENT_ID" AS c3, t1."ALIGNMENT_NAME" AS c4, t1."ALIGNMENT_TYPE_CODE_ID" AS c5,
    t1."AREA_ID" AS c6, t1."AREA_NAME" AS c7, t1."EFF_DATE" AS c8, t1."END_DATE" AS c9,
    t1."REGION_ID" AS c10, t1."REGION_NAME" AS c11, t1."STRUCTURE_ID" AS c12,
    t1."STRUCTURE_NAME" AS c13, t1."TERRITORY_ID" AS c14, t1."TERRITORY_NAME" AS c15, t3.c1, t3.c2
    FROM "CCSUSERDV"."V_ST_TERRITORY_HIERARCHY" t1
    LEFT OUTER JOIN (
    SELECT DISTINCT t2."COUNTRY_CODE" AS c1, t2."TERRITORY_ID" AS c2
    FROM "CCSUSERDV"."ST_TERRITORY_GEO" t2
    WHERE ((t2."ACTIVE_FLAG" = 'Y') AND (t2."EFF_DATE" <= ?) AND (t2."END_DATE" >= ?))
    ) t3
    ON (t3.c2 = t1."TERRITORY_ID")
    ORDER BY t1."TERRITORY_ID" ASC, t3.c1 ASC NULLS FIRST
  • 3. Re: "Distinct" not pushed down in outer join
    661228 Newbie
    Currently Being Moderated
    BTW, yes it is Oracle db.

    Also, is there a way to make it use an inner join for this situation? Is there an (efficient) way to do a single query with inner joins, or calling a view, and then break it down into hierarchical XML using just XQuery?

    Thanks,

    Jeff

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points