Here's the plan for a select. In the next to last step the scan count
matches the number of matching rows (494). The last step is a left join on
a primary key. The number of rows doesn't change, but the scan count
doubles, which I find curious. Why is that?
SELECT
GENE,
I,
EXPSCORES
FROM (
SELECT
P.GENE,
P.ID,
JOINS.PID,
JOINS.I,
JOINS.SID
FROM (
SELECT
PROBES.NAME AS GENE,
PROBES.ID
FROM TABLE(NAME VARCHAR=?1) T
/* function */
INNER JOIN PUBLIC.PROBES
/* PUBLIC.CONSTRAINT_INDEX_8D2: EID = ?2
AND NAME = T.NAME
*/
ON 1=1
WHERE (PROBES.EID = ?2)
AND (T.NAME = PROBES.NAME)
) P
/* SELECT
PROBES.NAME AS GENE,
PROBES.ID
FROM TABLE(NAME VARCHAR=?1) T
/++ function ++/
INNER JOIN PUBLIC.PROBES
/++ PUBLIC.CONSTRAINT_INDEX_8D2: EID = ?2
AND NAME = T.NAME
++/
ON 1=1
WHERE (PROBES.EID = ?2)
AND (T.NAME = PROBES.NAME)
*/
LEFT OUTER JOIN PUBLIC.JOINS
/* PUBLIC.CONSTRAINT_INDEX_4: PID = P.ID */
ON P.ID = JOINS.PID
WHERE JOINS.I = ?3
) _29
/* SELECT
P.GENE,
P.ID,
JOINS.PID,
JOINS.I,
JOINS.SID
FROM (
SELECT
PROBES.NAME AS GENE,
PROBES.ID
FROM TABLE(NAME VARCHAR=?1) T
/++ function ++/
INNER JOIN PUBLIC.PROBES
/++ PUBLIC.CONSTRAINT_INDEX_8D2: EID = ?2
AND NAME = T.NAME
++/
ON 1=1
WHERE (PROBES.EID = ?2)
AND (T.NAME = PROBES.NAME)
) P
/++ SELECT
PROBES.NAME AS GENE,
PROBES.ID
FROM TABLE(NAME VARCHAR=?1) T
/++ function ++/
INNER JOIN PUBLIC.PROBES
/++ PUBLIC.CONSTRAINT_INDEX_8D2: EID = ?2
AND NAME = T.NAME
++/
ON 1=1
WHERE (PROBES.EID = ?2)
AND (T.NAME = PROBES.NAME)
++/
LEFT OUTER JOIN PUBLIC.JOINS
/++ PUBLIC.CONSTRAINT_INDEX_4: PID = P.ID ++/
ON P.ID = JOINS.PID
WHERE JOINS.I = ?3
*/
/* scanCount: 494 */
LEFT OUTER JOIN PUBLIC.SCORES
/* PUBLIC.PRIMARY_KEY_91: ID = SID */
ON SID = SCORES.ID
/* scanCount: 986 */
--
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/groups/opt_out.
