First is the plan for the fast query and then for the slow one.

- Rami

SELECT
    T.TRANSLATION_ID,
    (T.TRANSLATION_ID IS NOT NULL) AS ASSIGNED,
    S.SUBTITLE_ID,
    PF.PNID,
    C.CID,
    S.LANG_ID,
    T.TRANSLATOR_ID,
    TOR.NAME AS TRANSLATOR_NAME,
    PF.SERIES_NAME,
    PF.EPISODE_NUMBER,
    PF.NAME AS PROGRAM_NAME,
    T.SENT_TO_TRANSLATOR,
    T.SENT_TO_TRANSLATOR_DATE,
CASE WHEN (T.ARRIVED_USE_RULE = FALSE) THEN T.ARRIVED_DUE_DATE WHEN ((T.ARRIVED_USE_RULE = TRUE) AND (PF.FIRST_BROADCAST IS NOT NULL)) THEN F.DATEX_ADD_MILLIS(PF.FIRST_BROADCAST_DATE, (-86400000 * CAST(T.ARRIVED_DAYS_BEFORE_FIRST_BROADCAST AS BIGINT))) ELSE NULL END AS ARRIVED_DEADLINE_DATE,
    (SELECT
        MIN(BA.START)
    FROM EXODUS_VIEW.BROADCAST_ALL BA
        /* EXODUS_VIEW.EXODUS_VIEW_BROADCAST_ALL_I_2: PNID = PF.PNID */
    WHERE (BA.START > F.TIMEPOINT_NOW())
        AND ((BA.CID = C.CID)
        AND (BA.PNID = PF.PNID))) AS NEXT_BROADCAST,
    T.ARRIVED,
    T.ARRIVED_DATE,
    T.VERIFIED,
    T.VERIFIED_DATE,
    S.ARCHIVED AS SUB_ARCHIVED,
    S.ARCHIVED_DATE AS SUB_ARCHIVED_DATE,
    PF.REC_ARRIVED,
    PF.REC_ARRIVED_DATE,
    PF.REC_ARCHIVED,
    PF.REC_ARCHIVED_DATE,
    S.NOTES AS SUB_NOTES,
    PF.NOTES AS PROGRAM_NOTES
FROM LINGUISTIC.SUBTITLE S
    /* LINGUISTIC.SUBTITLE.tableScan */
INNER JOIN EXODUS_VIEW.PROGRAM_FIXED PF
    /* EXODUS_VIEW.PRIMARY_KEY_20: PNID = S.PNID
        AND PNID = S.PNID
     */
    ON 1=1
    /* WHERE S.PNID = PF.PNID
    */
INNER JOIN METADATA.CHANNEL C
    /* METADATA.CHANNEL.tableScan */
    ON 1=1
LEFT OUTER JOIN LINGUISTIC.TRANSLATION T
    /* LINGUISTIC.CONSTRAINT_INDEX_129: SUBTITLE_ID = S.SUBTITLE_ID */
    ON T.SUBTITLE_ID = S.SUBTITLE_ID
LEFT OUTER JOIN LINGUISTIC.TRANSLATOR TOR
    /* LINGUISTIC.PRIMARY_KEY_B: TRANSLATOR_ID = T.TRANSLATOR_ID */
    ON T.TRANSLATOR_ID = TOR.TRANSLATOR_ID
WHERE S.PNID = PF.PNID

-------------------------------------------------------- SEPARATOR ------------------------------------------------------------------------

SELECT
    _6.TRANSLATION_ID,
    _6.ASSIGNED,
    _6.SUBTITLE_ID,
    _6.PNID,
    _6.CID,
    _6.LANG_ID,
    _6.TRANSLATOR_ID,
    _6.TRANSLATOR_NAME,
    _6.SERIES_NAME,
    _6.EPISODE_NUMBER,
    _6.PROGRAM_NAME,
    _6.SENT_TO_TRANSLATOR,
    _6.SENT_TO_TRANSLATOR_DATE,
    _6.ARRIVED_DEADLINE_DATE,
    _6.NEXT_BROADCAST,
    _6.ARRIVED,
    _6.ARRIVED_DATE,
    _6.VERIFIED,
    _6.VERIFIED_DATE,
    _6.SUB_ARCHIVED,
    _6.SUB_ARCHIVED_DATE,
    _6.REC_ARRIVED,
    _6.REC_ARRIVED_DATE,
    _6.REC_ARCHIVED,
    _6.REC_ARCHIVED_DATE,
    _6.SUB_NOTES,
    _6.PROGRAM_NOTES
FROM (
    SELECT
        T.TRANSLATION_ID,
        (T.TRANSLATION_ID IS NOT NULL) AS ASSIGNED,
        S.SUBTITLE_ID,
        PF.PNID,
        C.CID,
        S.LANG_ID,
        T.TRANSLATOR_ID,
        TOR.NAME AS TRANSLATOR_NAME,
        PF.SERIES_NAME,
        PF.EPISODE_NUMBER,
        PF.NAME AS PROGRAM_NAME,
        T.SENT_TO_TRANSLATOR,
        T.SENT_TO_TRANSLATOR_DATE,
CASE WHEN (T.ARRIVED_USE_RULE = FALSE) THEN T.ARRIVED_DUE_DATE WHEN ((T.ARRIVED_USE_RULE = TRUE) AND (PF.FIRST_BROADCAST IS NOT NULL)) THEN F.DATEX_ADD_MILLIS(PF.FIRST_BROADCAST_DATE, (-86400000 * CAST(T.ARRIVED_DAYS_BEFORE_FIRST_BROADCAST AS BIGINT))) ELSE NULL END AS ARRIVED_DEADLINE_DATE,
        (SELECT
            MIN(BA.START)
        FROM EXODUS_VIEW.BROADCAST_ALL BA
            /* EXODUS_VIEW.EXODUS_VIEW_BROADCAST_ALL_I_2: PNID = PF.PNID */
        WHERE (BA.START > F.TIMEPOINT_NOW())
            AND ((BA.CID = C.CID)
            AND (BA.PNID = PF.PNID))) AS NEXT_BROADCAST,
        T.ARRIVED,
        T.ARRIVED_DATE,
        T.VERIFIED,
        T.VERIFIED_DATE,
        S.ARCHIVED AS SUB_ARCHIVED,
        S.ARCHIVED_DATE AS SUB_ARCHIVED_DATE,
        PF.REC_ARRIVED,
        PF.REC_ARRIVED_DATE,
        PF.REC_ARCHIVED,
        PF.REC_ARCHIVED_DATE,
        S.NOTES AS SUB_NOTES,
        PF.NOTES AS PROGRAM_NOTES
    FROM LINGUISTIC.SUBTITLE S
        /* LINGUISTIC.SUBTITLE.tableScan */
    INNER JOIN METADATA.CHANNEL C
        /* METADATA.CHANNEL.tableScan */
        ON 1=1
    LEFT OUTER JOIN LINGUISTIC.TRANSLATION T
        /* LINGUISTIC.CONSTRAINT_INDEX_129: SUBTITLE_ID = S.SUBTITLE_ID */
        ON T.SUBTITLE_ID = S.SUBTITLE_ID
    LEFT OUTER JOIN LINGUISTIC.TRANSLATOR TOR
        /* LINGUISTIC.PRIMARY_KEY_B: TRANSLATOR_ID = T.TRANSLATOR_ID */
        ON T.TRANSLATOR_ID = TOR.TRANSLATOR_ID
    INNER JOIN EXODUS_VIEW.PROGRAM_FIXED PF
        /* EXODUS_VIEW.PRIMARY_KEY_20: PNID = S.PNID */
        ON 1=1
    WHERE S.PNID = PF.PNID
) _6
    /* SELECT
        T.TRANSLATION_ID,
        (T.TRANSLATION_ID IS NOT NULL) AS ASSIGNED,
        S.SUBTITLE_ID,
        PF.PNID,
        C.CID,
        S.LANG_ID,
        T.TRANSLATOR_ID,
        TOR.NAME AS TRANSLATOR_NAME,
        PF.SERIES_NAME,
        PF.EPISODE_NUMBER,
        PF.NAME AS PROGRAM_NAME,
        T.SENT_TO_TRANSLATOR,
        T.SENT_TO_TRANSLATOR_DATE,
CASE WHEN (T.ARRIVED_USE_RULE = FALSE) THEN T.ARRIVED_DUE_DATE WHEN ((T.ARRIVED_USE_RULE = TRUE) AND (PF.FIRST_BROADCAST IS NOT NULL)) THEN F.DATEX_ADD_MILLIS(PF.FIRST_BROADCAST_DATE, (-86400000 * CAST(T.ARRIVED_DAYS_BEFORE_FIRST_BROADCAST AS BIGINT))) ELSE NULL END AS ARRIVED_DEADLINE_DATE,
        (SELECT
            MIN(BA.START)
        FROM EXODUS_VIEW.BROADCAST_ALL BA
/++ EXODUS_VIEW.EXODUS_VIEW_BROADCAST_ALL_I_2: PNID = PF.PNID ++/
        WHERE (BA.START > F.TIMEPOINT_NOW())
            AND ((BA.CID = C.CID)
            AND (BA.PNID = PF.PNID))) AS NEXT_BROADCAST,
        T.ARRIVED,
        T.ARRIVED_DATE,
        T.VERIFIED,
        T.VERIFIED_DATE,
        S.ARCHIVED AS SUB_ARCHIVED,
        S.ARCHIVED_DATE AS SUB_ARCHIVED_DATE,
        PF.REC_ARRIVED,
        PF.REC_ARRIVED_DATE,
        PF.REC_ARCHIVED,
        PF.REC_ARCHIVED_DATE,
        S.NOTES AS SUB_NOTES,
        PF.NOTES AS PROGRAM_NOTES
    FROM LINGUISTIC.SUBTITLE S
        /++ LINGUISTIC.SUBTITLE.tableScan ++/
    INNER JOIN METADATA.CHANNEL C
        /++ METADATA.CHANNEL.tableScan ++/
        ON 1=1
    LEFT OUTER JOIN LINGUISTIC.TRANSLATION T
/++ LINGUISTIC.CONSTRAINT_INDEX_129: SUBTITLE_ID = S.SUBTITLE_ID ++/
        ON T.SUBTITLE_ID = S.SUBTITLE_ID
    LEFT OUTER JOIN LINGUISTIC.TRANSLATOR TOR
        /++ LINGUISTIC.PRIMARY_KEY_B: TRANSLATOR_ID = T.TRANSLATOR_ID ++/
        ON T.TRANSLATOR_ID = TOR.TRANSLATOR_ID
    INNER JOIN EXODUS_VIEW.PROGRAM_FIXED PF
        /++ EXODUS_VIEW.PRIMARY_KEY_20: PNID = S.PNID ++/
        ON 1=1
    WHERE S.PNID = PF.PNID
     */

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

Reply via email to