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.

Reply via email to