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.