API tools faq
paste
Advertisement
cdtaylor

Period_ID_20133_WithAdditionalFilter

cdtaylor
Jan 27th, 2015
242
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.83 KB | None | 0 0
raw download clone embed print report
  1. SELECT a.STS_PERF_PERIOD,
  2. a.PART_ACCT_NO,
  3. a.PWN_PART_AKA_NME,
  4. a.PWN_P_W_IND,
  5. a.AFL_SOCIETY_NME,
  6. b.sts_CHANNEL_TYPE,
  7. c.stp_s03_program_cd--TOTAL Music Minutes CALCULATION:
  8. ,
  9. SUM (
  10. CASE
  11. WHEN STG_STATUS = 'DP'
  12. THEN
  13. 0
  14. ELSE
  15. a.perf_count
  16. * ( ( a.BMI_FEATURE
  17. + a.BMI_BG
  18. + a.BMI_LOGO
  19. + a.BMI_THEME
  20. + a.BMI_BV
  21. + a.ASCAP_FEATURE
  22. + a.ASCAP_BG
  23. + a.ASCAP_LOGO
  24. + a.ASCAP_THEME
  25. + a.ASCAP_BV
  26. + a.FOREIGN_FEATURE
  27. + a.FOREIGN_BG
  28. + a.FOREIGN_LOGO
  29. + a.FOREIGN_THEME
  30. + a.FOREIGN_BV
  31. + a.PD_FEATURE
  32. + a.PD_BG
  33. + a.PD_LOGO
  34. + a.PD_THEME
  35. + a.PD_BV
  36. + a.SESAC_FEATURE
  37. + a.SESAC_BG
  38. + a.SESAC_LOGO
  39. + a.SESAC_THEME
  40. + a.SESAC_BV
  41. + a.NA_FEATURE
  42. + a.NA_BG
  43. + a.NA_LOGO
  44. + a.NA_THEME
  45. + a.NA_BV))
  46. END)
  47. / 60
  48. AS "TOT_MUSIC_MINS"--BMI Music Minutes CALCULATION:
  49. ,
  50. SUM (
  51. CASE
  52. WHEN STG_STATUS = 'DP'
  53. THEN
  54. 0
  55. ELSE
  56. a.perf_count
  57. * ( ( a.BMI_FEATURE
  58. + a.BMI_BG
  59. + a.BMI_LOGO
  60. + a.BMI_THEME
  61. + a.BMI_BV))
  62. END)
  63. / 60
  64. AS "BMI_MUSIC_MINS"
  65. FROM PRDM.COWS_PURPLE_PART a,
  66. prdm.cows_TCUE_STS_SCHEDULE b,
  67. prdm.cows_tcue_stp_program c
  68. WHERE A.STS_PERF_PERIOD = b.sts_perf_period
  69. AND A.STS_DPS_TYPE = b.sts_dps_type
  70. AND A.STG_GROUP_NO = b.sts_group_no
  71. AND a.stg_group_seq_no = b.sts_group_seq_no
  72. AND b.sts_provider = c.stp_provider
  73. AND b.sts_program_no = c.stp_program_no
  74. AND a.STS_PERF_PERIOD ='20133'
  75. AND b.STS_PERF_PERIOD ='20133'
  76. AND b.sts_perf_source = 'CB'
  77. AND a.PWN_P_W_IND = 'W'
  78. AND c.stp_s03_program_cd <> '36'
  79. GROUP BY a.STS_PERF_PERIOD,
  80. a.PART_ACCT_NO,
  81. a.PWN_PART_AKA_NME,
  82. a.PWN_P_W_IND,
  83. a.AFL_SOCIETY_NME,
  84. b.sts_CHANNEL_TYPE,
  85. c.stp_s03_program_cd
  86.  
  87. Plan hash value: 1037146296
  88.  
  89. ----------------------------------------------------------------------------------------------------------------------------------------------
  90. | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
  91. ----------------------------------------------------------------------------------------------------------------------------------------------
  92. | 0 | SELECT STATEMENT | | 8060K| 1145M| | 504K (1)| 00:33:40 | | |
  93. | 1 | HASH GROUP BY | | 8060K| 1145M| 1325M| 504K (1)| 00:33:40 | | |
  94. |* 2 | HASH JOIN | | 8060K| 1145M| | 338K (2)| 00:22:35 | | |
  95. | 3 | VIEW | VW_GBC_9 | 7106 | 652K| | 262K (2)| 00:17:30 | | |
  96. | 4 | HASH GROUP BY | | 7106 | 1145K| 3110M| 262K (2)| 00:17:30 | | |
  97. | 5 | PARTITION RANGE SINGLE | | 12M| 2004M| | 122K (2)| 00:08:09 | 19 | 19 |
  98. |* 6 | TABLE ACCESS FULL | COWS_PURPLE_PART | 12M| 2004M| | 122K (2)| 00:08:09 | 19 | 19 |
  99. | 7 | VIEW | VW_GBF_10 | 654K| 34M| | 76212 (2)| 00:05:05 | | |
  100. | 8 | HASH GROUP BY | | 654K| 29M| 248M| 76212 (2)| 00:05:05 | | |
  101. |* 9 | HASH JOIN | | 4321K| 197M| 23M| 60367 (2)| 00:04:02 | | |
  102. | 10 | PARTITION RANGE ALL | | 508K| 17M| | 23550 (1)| 00:01:35 | 1 | 22 |
  103. | 11 | TABLE ACCESS BY LOCAL INDEX ROWID| COWS_TCUE_STS_SCHEDULE | 508K| 17M| | 23550 (1)| 00:01:35 | 1 | 22 |
  104. |* 12 | INDEX SKIP SCAN | COWS_TCUE_STS_SCHED_IDX01 | 508K| | | 5028 (1)| 00:00:21 | 1 | 22 |
  105. |* 13 | INDEX FAST FULL SCAN | COWS_TCUE_STP_PROG_IDX01 | 24M| 256M| | 12602 (3)| 00:00:51 | | |
  106. ----------------------------------------------------------------------------------------------------------------------------------------------
  107.  
  108. Query Block Name / Object Alias (identified by operation id):
  109. -------------------------------------------------------------
  110.  
  111. 1 - SEL$B410752D
  112. 3 - SEL$C19FF6E1 / VW_GBC_9@SEL$377C5901
  113. 4 - SEL$C19FF6E1
  114. 6 - SEL$C19FF6E1 / A@SEL$1
  115. 7 - SEL$DD65AEC8 / VW_GBF_10@SEL$E327F127
  116. 8 - SEL$DD65AEC8
  117. 11 - SEL$DD65AEC8 / B@SEL$1
  118. 12 - SEL$DD65AEC8 / B@SEL$1
  119. 13 - SEL$DD65AEC8 / C@SEL$1
  120.  
  121. Outline Data
  122. -------------
  123.  
  124. /*+
  125. BEGIN_OUTLINE_DATA
  126. USE_HASH_AGGREGATION(@"SEL$C19FF6E1")
  127. FULL(@"SEL$C19FF6E1" "A"@"SEL$1")
  128. USE_HASH_AGGREGATION(@"SEL$DD65AEC8")
  129. USE_HASH(@"SEL$DD65AEC8" "C"@"SEL$1")
  130. LEADING(@"SEL$DD65AEC8" "B"@"SEL$1" "C"@"SEL$1")
  131. INDEX_FFS(@"SEL$DD65AEC8" "C"@"SEL$1" ("COWS_TCUE_STP_PROGRAM"."STP_S03_PROGRAM_CD" "COWS_TCUE_STP_PROGRAM"."STP_PROVIDER"
  132. "COWS_TCUE_STP_PROGRAM"."STP_PROGRAM_NO"))
  133. INDEX_SS(@"SEL$DD65AEC8" "B"@"SEL$1" ("COWS_TCUE_STS_SCHEDULE"."COWS_DIST_PERIOD" "COWS_TCUE_STS_SCHEDULE"."STS_PERF_PERIOD"
  134. "COWS_TCUE_STS_SCHEDULE"."STS_PERF_SOURCE"))
  135. USE_HASH_AGGREGATION(@"SEL$B410752D")
  136. USE_HASH(@"SEL$B410752D" "VW_GBF_10"@"SEL$E327F127")
  137. LEADING(@"SEL$B410752D" "VW_GBC_9"@"SEL$377C5901" "VW_GBF_10"@"SEL$E327F127")
  138. NO_ACCESS(@"SEL$B410752D" "VW_GBF_10"@"SEL$E327F127")
  139. NO_ACCESS(@"SEL$B410752D" "VW_GBC_9"@"SEL$377C5901")
  140. OUTLINE(@"SEL$1")
  141. OUTLINE(@"SEL$377C5901")
  142. OUTLINE(@"SEL$E327F127")
  143. PLACE_GROUP_BY(@"SEL$1" ( "B"@"SEL$1" "C"@"SEL$1" ) ( "A"@"SEL$1" ) 9)
  144. OUTLINE_LEAF(@"SEL$B410752D")
  145. OUTLINE_LEAF(@"SEL$C19FF6E1")
  146. OUTLINE_LEAF(@"SEL$DD65AEC8")
  147. NO_PARALLEL
  148. DB_VERSION('11.2.0.2')
  149. OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
  150. IGNORE_OPTIM_EMBEDDED_HINTS
  151. END_OUTLINE_DATA
  152. */
  153.  
  154. Predicate Information (identified by operation id):
  155. ---------------------------------------------------
  156.  
  157. 2 - access("ITEM_4"="ITEM_4" AND "ITEM_3"="ITEM_3" AND "ITEM_2"="ITEM_2" AND "ITEM_1"="ITEM_1")
  158. 6 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20133')
  159. 9 - access("B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO" AND "B"."STS_PROVIDER"="C"."STP_PROVIDER")
  160. 12 - access("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
  161. filter("B"."STS_PERF_PERIOD"='20133' AND "B"."STS_PERF_SOURCE"='CB')
  162. 13 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
  163.  
  164. Column Projection Information (identified by operation id):
  165. -----------------------------------------------------------
  166.  
  167. 1 - (#keys=7) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
  168. "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2], SUM("ITEM_5"*"ITEM_5")[22], SUM("ITEM_6"*"ITEM_6")[22]
  169. 2 - (#keys=4) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
  170. "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2]
  171. 3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,10], "ITEM_4"[VARCHAR2,5], "ITEM_5"[NUMBER,22],
  172. "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8]
  173. 4 - (#keys=8) "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."STG_GROUP_NO"[NUMBER,22], "A"."STS_DPS_TYPE"[VARCHAR2,10],
  174. "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30], "A"."PWN_P_W_IND"[VARCHAR2,1],
  175. "A"."AFL_SOCIETY_NME"[VARCHAR2,8], SUM(CASE "STG_STATUS" WHEN 'DP' THEN 0 ELSE
  176. "A"."PERF_COUNT"*("A"."BMI_FEATURE"+"A"."BMI_BG"+"A"."BMI_LOGO"+"A"."BMI_THEME"+"A"."BMI_BV") END )[22], SUM(CASE "STG_STATUS" WHEN
  177. 'DP' THEN 0 ELSE "A"."PERF_COUNT"*("A"."BMI_FEATURE"+"A"."BMI_BG"+"A"."BMI_LOGO"+"A"."BMI_THEME"+"A"."BMI_BV"+"A"."ASCAP_FEATURE"+"A".
  178. "ASCAP_BG"+"A"."ASCAP_LOGO"+"A"."ASCAP_THEME"+"A"."ASCAP_BV"+"A"."FOREIGN_FEATURE"+"A"."FOREIGN_BG"+"A"."FOREIGN_LOGO"+"A"."FOREIGN_TH
  179. EME"+"A"."FOREIGN_BV"+"A"."PD_FEATURE"+"A"."PD_BG"+"A"."PD_LOGO"+"A"."PD_THEME"+"A"."PD_BV"+"A"."SESAC_FEATURE"+"A"."SESAC_BG"+"A"."SE
  180. SAC_LOGO"+"A"."SESAC_THEME"+"A"."SESAC_BV"+"A"."NA_FEATURE"+"A"."NA_BG"+"A"."NA_LOGO"+"A"."NA_THEME"+"A"."NA_BV") END )[22]
  181. 5 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
  182. "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30],
  183. "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22], "A"."BMI_BG"[NUMBER,22],
  184. "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22], "A"."ASCAP_BV"[NUMBER,22],
  185. "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22], "A"."FOREIGN_BV"[NUMBER,22],
  186. "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22],
  187. "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22],
  188. "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22],
  189. "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22],
  190. "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
  191. 6 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
  192. "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30],
  193. "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22], "A"."BMI_BG"[NUMBER,22],
  194. "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22], "A"."ASCAP_BV"[NUMBER,22],
  195. "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22], "A"."FOREIGN_BV"[NUMBER,22],
  196. "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22],
  197. "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22],
  198. "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22],
  199. "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22],
  200. "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
  201. 7 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,10], "ITEM_4"[VARCHAR2,5], "ITEM_5"[NUMBER,22],
  202. "ITEM_6"[NUMBER,22], "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2]
  203. 8 - (#keys=6) "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_GROUP_NO"[NUMBER,22], "B"."STS_DPS_TYPE"[VARCHAR2,10],
  204. "B"."STS_PERF_PERIOD"[VARCHAR2,5], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], COUNT(*)[22]
  205. 9 - (#keys=2) "B"."STS_PERF_PERIOD"[VARCHAR2,5], "B"."STS_GROUP_NO"[NUMBER,22], "B"."STS_GROUP_SEQ_NO"[NUMBER,22],
  206. "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2]
  207. 10 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
  208. "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
  209. "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  210. 11 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
  211. "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
  212. "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  213. 12 - "B".ROWID[ROWID,10], "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  214. 13 - "C"."STP_PROGRAM_NO"[NUMBER,22], "C"."STP_PROVIDER"[VARCHAR2,1], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2]
  215.  
  216. Note
  217. -----
  218. - automatic DOP: skipped because of IO calibrate statistics are missing
  219. - Warning: basic plan statistics not available. These are only collected when:
  220. * hint 'gather_plan_statistics' is used for the statement or
  221. * parameter 'statistics_level' is set to 'ALL', at session or system level
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!