Hi Noel and Thomas.
Here is a simplified use case:
CREATE TABLE REF (POSITION VARCHAR(10), REF_1 VARCHAR(1), REF_2 VARCHAR(8),
REF_3 VARCHAR(8),REF_4 VARCHAR(1),REF_5 VARCHAR(1),REF_6 VARCHAR(3))
CREATE INDEX IDX_REF ON REF (REF_3,REF_6)
This request is fast :
SELECT r.ref_1, r.ref_3,
(SELECT COUNT(*)
FROM ref rc
WHERE rc.ref_3 = r.ref_3
AND rc.position <= r.position
) NO_OCCURRENCE
FROM ref r
The plan is :
SELECT
R.REF_1,
R.REF_3,
(SELECT
COUNT(*)
FROM PUBLIC.REF RC
/* PUBLIC.IDXR: REF_3 = R.REF_3 */
WHERE (RC.REF_3 = R.REF_3)
AND (RC.POSITION <= R.POSITION)) AS NO_OCCURRENCE
FROM PUBLIC.REF R
/* PUBLIC.REF.tableScan */
This request take a huge time :
SELECT * FROM (
SELECT r.ref_1, r.ref_3,
(SELECT COUNT(*)
FROM ref rc
WHERE rc.ref_3 = r.ref_3
AND rc.position <= r.position
) NO_OCCURRENCE
FROM ref r
)
The plan of this long request:
SELECT
_7.REF_1,
_7.REF_3,
_7.NO_OCCURRENCE
FROM (
SELECT
R.REF_1,
R.REF_3,
(SELECT
COUNT(*)
FROM PUBLIC.REF RC
/* PUBLIC.IDXR: REF_3 = R.REF_3 */
WHERE (RC.REF_3 = R.REF_3)
AND (RC.POSITION <= R.POSITION)) AS NO_OCCURRENCE
FROM PUBLIC.REF R
/* PUBLIC.REF.tableScan */
) _7
/* SELECT
R.REF_1,
R.REF_3,
(SELECT
COUNT(*)
FROM PUBLIC.REF RC
/++ PUBLIC.IDXR: REF_3 = R.REF_3 ++/
WHERE (RC.REF_3 = R.REF_3)
AND (RC.POSITION <= R.POSITION)) AS NO_OCCURRENCE
FROM PUBLIC.REF R
/++ PUBLIC.REF.tableScan ++/
*/
I don't understand why enclosing the first request in a SELECT * FROM ()
degrades the performance.
Regards
--
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.
