Can you please let me know how you get the explain plan. I am trying to see the plan but can only see part of it because of the length issue.
On Saturday, October 11, 2014 2:25:05 PM UTC-4, Rami Ojares wrote: > 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.
