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].
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.
