Hi, This looks very complicated.
Could you please simplify the problem as much as possible, so the problem still shows up? And of course it would need to be a reproducible test case (including "create table", "create index", and data). Regards, Thomas On Tuesday, June 16, 2015, Laurent Tourreau <[email protected]> wrote: > Hi > I post the full 1st request: > > SELECT REF_COUNT.ref_1,CMP_COUNT.cmp_1 > FROM > (SELECT > r.ref_1,r.ref_3,r.ref_6,r.ref_7,r.ref_8,r.ref_10,r.ref_12,r.ref_14,r.ref_20,r.ref_25, > (SELECT COUNT(*) > FROM ref rc > WHERE rc.ref_3 =r.ref_3 > AND rc.ref_6 =r.ref_6 > AND rc.ref_7 =r.ref_7 > AND rc.ref_8 =r.ref_8 > AND rc.ref_10 =r.ref_10 > AND rc.ref_12 =r.ref_12 > AND rc.ref_14 =r.ref_14 > AND rc.ref_20 =r.ref_20 > AND rc.ref_25 =r.ref_25 > AND rc.position<= r.position > ) NO_OCCURRENCE > FROM ref r) REF_COUNT > LEFT JOIN > (SELECT > c.cmp_1,c.cmp_3,c.cmp_6,c.cmp_7,c.cmp_8,c.cmp_10,c.cmp_12,c.cmp_14,c.cmp_20,c.cmp_25, > (SELECT COUNT(*) > FROM cmp cc > WHERE cc.cmp_3 =c.cmp_3 > AND cc.cmp_6 =c.cmp_6 > AND cc.cmp_7 =c.cmp_7 > AND cc.cmp_8 =c.cmp_8 > AND cc.cmp_10 =c.cmp_10 > AND cc.cmp_12 =c.cmp_12 > AND cc.cmp_14 =c.cmp_14 > AND cc.cmp_20 =c.cmp_20 > AND cc.cmp_25 =c.cmp_25 > AND cc.position<= c.position > ) NO_OCCURRENCE > FROM cmp c) CMP_COUNT > ON REF_COUNT.ref_3 = CMP_COUNT.cmp_3 > AND REF_COUNT.ref_6 = CMP_COUNT.cmp_6 > AND REF_COUNT.ref_7 = CMP_COUNT.cmp_7 > AND REF_COUNT.ref_8 = CMP_COUNT.cmp_8 > AND REF_COUNT.ref_10 = CMP_COUNT.cmp_10 > AND REF_COUNT.ref_12 = CMP_COUNT.cmp_12 > AND REF_COUNT.ref_14 = CMP_COUNT.cmp_14 > AND REF_COUNT.ref_20 = CMP_COUNT.cmp_20 > AND REF_COUNT.ref_25 = CMP_COUNT.cmp_25 > AND REF_COUNT.NO_OCCURRENCE = CMP_COUNT.NO_OCCURRENCE > > here is the plan for the 1st request : > > SELECT > REF_COUNT.REF_1, > CMP_COUNT.CMP_1 > FROM ( > SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /* PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > */ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /* PUBLIC.REF.tableScan */ > ) REF_COUNT > /* SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /++ PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > ++/ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /++ PUBLIC.REF.tableScan ++/ > */ > LEFT OUTER JOIN ( > SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /* PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > */ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /* PUBLIC.CMP.tableScan */ > ) CMP_COUNT > /* SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /++ PUBLIC.IDXC: CMP_25 IS ?9 > AND CMP_20 IS ?8 > AND CMP_14 IS ?7 > AND CMP_12 IS ?6 > AND CMP_10 IS ?5 > AND CMP_8 IS ?4 > AND CMP_7 IS ?3 > AND CMP_3 IS ?1 > AND CMP_6 IS ?2 > ++/ > WHERE ((C.CMP_25 IS ?9) > AND ((C.CMP_20 IS ?8) > AND ((C.CMP_14 IS ?7) > AND ((C.CMP_12 IS ?6) > AND ((C.CMP_10 IS ?5) > AND ((C.CMP_8 IS ?4) > AND ((C.CMP_7 IS ?3) > AND ((C.CMP_3 IS ?1) > AND (C.CMP_6 IS ?2))))))))) > AND ((SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) IS ?10): CMP_3 = REF_COUNT.REF_3 > AND CMP_6 = REF_COUNT.REF_6 > AND CMP_7 = REF_COUNT.REF_7 > AND CMP_8 = REF_COUNT.REF_8 > AND CMP_10 = REF_COUNT.REF_10 > AND CMP_12 = REF_COUNT.REF_12 > AND CMP_14 = REF_COUNT.REF_14 > AND CMP_20 = REF_COUNT.REF_20 > AND CMP_25 = REF_COUNT.REF_25 > AND NO_OCCURRENCE = REF_COUNT.NO_OCCURRENCE > */ > ON (REF_COUNT.NO_OCCURRENCE = CMP_COUNT.NO_OCCURRENCE) > AND ((REF_COUNT.REF_25 = CMP_COUNT.CMP_25) > AND ((REF_COUNT.REF_20 = CMP_COUNT.CMP_20) > AND ((REF_COUNT.REF_14 = CMP_COUNT.CMP_14) > AND ((REF_COUNT.REF_12 = CMP_COUNT.CMP_12) > AND ((REF_COUNT.REF_10 = CMP_COUNT.CMP_10) > AND ((REF_COUNT.REF_8 = CMP_COUNT.CMP_8) > AND ((REF_COUNT.REF_7 = CMP_COUNT.CMP_7) > AND ((REF_COUNT.REF_3 = CMP_COUNT.CMP_3) > AND (REF_COUNT.REF_6 = CMP_COUNT.CMP_6))))))))) > > Here the second request where I underline the difference : > > *SELECT * FROM (* > SELECT REF_COUNT.ref_1,CMP_COUNT.cmp_1 > FROM > (SELECT > r.ref_1,r.ref_3,r.ref_6,r.ref_7,r.ref_8,r.ref_10,r.ref_12,r.ref_14,r.ref_20,r.ref_25, > (SELECT COUNT(*) > FROM ref rc > WHERE rc.ref_3 =r.ref_3 > AND rc.ref_6 =r.ref_6 > AND rc.ref_7 =r.ref_7 > AND rc.ref_8 =r.ref_8 > AND rc.ref_10 =r.ref_10 > AND rc.ref_12 =r.ref_12 > AND rc.ref_14 =r.ref_14 > AND rc.ref_20 =r.ref_20 > AND rc.ref_25 =r.ref_25 > AND rc.position<= r.position > ) NO_OCCURRENCE > FROM ref r) REF_COUNT > LEFT JOIN > (SELECT > c.cmp_1,c.cmp_3,c.cmp_6,c.cmp_7,c.cmp_8,c.cmp_10,c.cmp_12,c.cmp_14,c.cmp_20,c.cmp_25, > (SELECT COUNT(*) > FROM cmp cc > WHERE cc.cmp_3 =c.cmp_3 > AND cc.cmp_6 =c.cmp_6 > AND cc.cmp_7 =c.cmp_7 > AND cc.cmp_8 =c.cmp_8 > AND cc.cmp_10 =c.cmp_10 > AND cc.cmp_12 =c.cmp_12 > AND cc.cmp_14 =c.cmp_14 > AND cc.cmp_20 =c.cmp_20 > AND cc.cmp_25 =c.cmp_25 > AND cc.position<= c.position > ) NO_OCCURRENCE > FROM cmp c) CMP_COUNT > ON REF_COUNT.ref_3 = CMP_COUNT.cmp_3 > AND REF_COUNT.ref_6 = CMP_COUNT.cmp_6 > AND REF_COUNT.ref_7 = CMP_COUNT.cmp_7 > AND REF_COUNT.ref_8 = CMP_COUNT.cmp_8 > AND REF_COUNT.ref_10 = CMP_COUNT.cmp_10 > AND REF_COUNT.ref_12 = CMP_COUNT.cmp_12 > AND REF_COUNT.ref_14 = CMP_COUNT.cmp_14 > AND REF_COUNT.ref_20 = CMP_COUNT.cmp_20 > AND REF_COUNT.ref_25 = CMP_COUNT.cmp_25 > AND REF_COUNT.NO_OCCURRENCE = CMP_COUNT.NO_OCCURRENCE > *)* > > And here is the plan for the 2nd request : > > SELECT > _15.REF_1, > _15.CMP_1 > FROM ( > SELECT > REF_COUNT.REF_1, > CMP_COUNT.CMP_1 > FROM ( > SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /* PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > */ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /* PUBLIC.REF.tableScan */ > ) REF_COUNT > /* SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /++ PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > ++/ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /++ PUBLIC.REF.tableScan ++/ > */ > LEFT OUTER JOIN ( > SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /* PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > */ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /* PUBLIC.CMP.tableScan */ > ) CMP_COUNT > /* SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /++ PUBLIC.IDXC: CMP_25 IS ?9 > AND CMP_20 IS ?8 > AND CMP_14 IS ?7 > AND CMP_12 IS ?6 > AND CMP_10 IS ?5 > AND CMP_8 IS ?4 > AND CMP_7 IS ?3 > AND CMP_3 IS ?1 > AND CMP_6 IS ?2 > ++/ > WHERE ((C.CMP_25 IS ?9) > AND ((C.CMP_20 IS ?8) > AND ((C.CMP_14 IS ?7) > AND ((C.CMP_12 IS ?6) > AND ((C.CMP_10 IS ?5) > AND ((C.CMP_8 IS ?4) > AND ((C.CMP_7 IS ?3) > AND ((C.CMP_3 IS ?1) > AND (C.CMP_6 IS ?2))))))))) > AND ((SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) IS ?10): CMP_3 = REF_COUNT.REF_3 > AND CMP_6 = REF_COUNT.REF_6 > AND CMP_7 = REF_COUNT.REF_7 > AND CMP_8 = REF_COUNT.REF_8 > AND CMP_10 = REF_COUNT.REF_10 > AND CMP_12 = REF_COUNT.REF_12 > AND CMP_14 = REF_COUNT.REF_14 > AND CMP_20 = REF_COUNT.REF_20 > AND CMP_25 = REF_COUNT.REF_25 > AND NO_OCCURRENCE = REF_COUNT.NO_OCCURRENCE > */ > ON (REF_COUNT.NO_OCCURRENCE = CMP_COUNT.NO_OCCURRENCE) > AND ((REF_COUNT.REF_25 = CMP_COUNT.CMP_25) > AND ((REF_COUNT.REF_20 = CMP_COUNT.CMP_20) > AND ((REF_COUNT.REF_14 = CMP_COUNT.CMP_14) > AND ((REF_COUNT.REF_12 = CMP_COUNT.CMP_12) > AND ((REF_COUNT.REF_10 = CMP_COUNT.CMP_10) > AND ((REF_COUNT.REF_8 = CMP_COUNT.CMP_8) > AND ((REF_COUNT.REF_7 = CMP_COUNT.CMP_7) > AND ((REF_COUNT.REF_3 = CMP_COUNT.CMP_3) > AND (REF_COUNT.REF_6 = CMP_COUNT.CMP_6))))))))) > ) _15 > /* SELECT > REF_COUNT.REF_1, > CMP_COUNT.CMP_1 > FROM ( > SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /++ PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > ++/ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /++ PUBLIC.REF.tableScan ++/ > ) REF_COUNT > /++ SELECT > R.REF_1, > R.REF_3, > R.REF_6, > R.REF_7, > R.REF_8, > R.REF_10, > R.REF_12, > R.REF_14, > R.REF_20, > R.REF_25, > (SELECT > COUNT(*) > FROM PUBLIC.REF RC > /++ PUBLIC.IDXR: REF_25 = R.REF_25 > AND REF_20 = R.REF_20 > AND REF_14 = R.REF_14 > AND REF_12 = R.REF_12 > AND REF_10 = R.REF_10 > AND REF_8 = R.REF_8 > AND REF_7 = R.REF_7 > AND REF_3 = R.REF_3 > AND REF_6 = R.REF_6 > ++/ > WHERE (RC.POSITION <= R.POSITION) > AND ((RC.REF_25 = R.REF_25) > AND ((RC.REF_20 = R.REF_20) > AND ((RC.REF_14 = R.REF_14) > AND ((RC.REF_12 = R.REF_12) > AND ((RC.REF_10 = R.REF_10) > AND ((RC.REF_8 = R.REF_8) > AND ((RC.REF_7 = R.REF_7) > AND ((RC.REF_3 = R.REF_3) > AND (RC.REF_6 = R.REF_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.REF R > /++ PUBLIC.REF.tableScan ++/ > ++/ > LEFT OUTER JOIN ( > SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /++ PUBLIC.CMP.tableScan ++/ > ) CMP_COUNT > /++ SELECT > C.CMP_1, > C.CMP_3, > C.CMP_6, > C.CMP_7, > C.CMP_8, > C.CMP_10, > C.CMP_12, > C.CMP_14, > C.CMP_20, > C.CMP_25, > (SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) AS NO_OCCURRENCE > FROM PUBLIC.CMP C > /++ PUBLIC.IDXC: CMP_25 IS ?9 > AND CMP_20 IS ?8 > AND CMP_14 IS ?7 > AND CMP_12 IS ?6 > AND CMP_10 IS ?5 > AND CMP_8 IS ?4 > AND CMP_7 IS ?3 > AND CMP_3 IS ?1 > AND CMP_6 IS ?2 > ++/ > WHERE ((C.CMP_25 IS ?9) > AND ((C.CMP_20 IS ?8) > AND ((C.CMP_14 IS ?7) > AND ((C.CMP_12 IS ?6) > AND ((C.CMP_10 IS ?5) > AND ((C.CMP_8 IS ?4) > AND ((C.CMP_7 IS ?3) > AND ((C.CMP_3 IS ?1) > AND (C.CMP_6 IS ?2))))))))) > AND ((SELECT > COUNT(*) > FROM PUBLIC.CMP CC > /++ PUBLIC.IDXC: CMP_25 = C.CMP_25 > AND CMP_20 = C.CMP_20 > AND CMP_14 = C.CMP_14 > AND CMP_12 = C.CMP_12 > AND CMP_10 = C.CMP_10 > AND CMP_8 = C.CMP_8 > AND CMP_7 = C.CMP_7 > AND CMP_3 = C.CMP_3 > AND CMP_6 = C.CMP_6 > ++/ > WHERE (CC.POSITION <= C.POSITION) > AND ((CC.CMP_25 = C.CMP_25) > AND ((CC.CMP_20 = C.CMP_20) > AND ((CC.CMP_14 = C.CMP_14) > AND ((CC.CMP_12 = C.CMP_12) > AND ((CC.CMP_10 = C.CMP_10) > AND ((CC.CMP_8 = C.CMP_8) > AND ((CC.CMP_7 = C.CMP_7) > AND ((CC.CMP_3 = C.CMP_3) > AND (CC.CMP_6 = C.CMP_6)))))))))) IS ?10): CMP_3 = REF_COUNT.REF_3 > AND CMP_6 = REF_COUNT.REF_6 > AND CMP_7 = REF_COUNT.REF_7 > AND CMP_8 = REF_COUNT.REF_8 > AND CMP_10 = REF_COUNT.REF_10 > AND CMP_12 = REF_COUNT.REF_12 > AND CMP_14 = REF_COUNT.REF_14 > AND CMP_20 = REF_COUNT.REF_20 > AND CMP_25 = REF_COUNT.REF_25 > AND NO_OCCURRENCE = REF_COUNT.NO_OCCURRENCE > ++/ > ON (REF_COUNT.NO_OCCURRENCE = CMP_COUNT.NO_OCCURRENCE) > AND ((REF_COUNT.REF_25 = CMP_COUNT.CMP_25) > AND ((REF_COUNT.REF_20 = CMP_COUNT.CMP_20) > AND ((REF_COUNT.REF_14 = CMP_COUNT.CMP_14) > AND ((REF_COUNT.REF_12 = CMP_COUNT.CMP_12) > AND ((REF_COUNT.REF_10 = CMP_COUNT.CMP_10) > AND ((REF_COUNT.REF_8 = CMP_COUNT.CMP_8) > AND ((REF_COUNT.REF_7 = CMP_COUNT.CMP_7) > AND ((REF_COUNT.REF_3 = CMP_COUNT.CMP_3) > AND (REF_COUNT.REF_6 = CMP_COUNT.CMP_6))))))))) > */ > > Regards > Laurent > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
