API tools faq
paste
Advertisement
cdtaylor

Period_ID_20141

cdtaylor
Jan 27th, 2015
249
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.76 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 ='20141'
  75. AND b.sts_perf_source = 'CB'
  76. AND a.PWN_P_W_IND = 'W'
  77. AND c.stp_s03_program_cd <> '36'
  78. GROUP BY a.STS_PERF_PERIOD,
  79. a.PART_ACCT_NO,
  80. a.PWN_PART_AKA_NME,
  81. a.PWN_P_W_IND,
  82. a.AFL_SOCIETY_NME,
  83. b.sts_CHANNEL_TYPE,
  84. c.stp_s03_program_cd
  85.  
  86. Plan hash value: 3919220529
  87.  
  88. --------------------------------------------------------------------------------------------------------------------------------------------
  89. | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
  90. --------------------------------------------------------------------------------------------------------------------------------------------
  91. | 0 | SELECT STATEMENT | | 34391 | 4769K| | 721K (1)| 00:48:07 | | |
  92. | 1 | HASH GROUP BY | | 34391 | 4769K| 1012M| 721K (1)| 00:48:07 | | |
  93. |* 2 | HASH JOIN | | 6931K| 938M| 111M| 655K (1)| 00:43:41 | | |
  94. |* 3 | HASH JOIN | | 815K| 101M| | 614K (1)| 00:40:58 | | |
  95. | 4 | VIEW | VW_GBC_9 | 34 | 3196 | | 96964 (3)| 00:06:28 | | |
  96. | 5 | HASH GROUP BY | | 34 | 5610 | | 96964 (3)| 00:06:28 | | |
  97. | 6 | PARTITION RANGE SINGLE | | 10M| 1582M| | 96246 (2)| 00:06:25 | 21 | 21 |
  98. |* 7 | TABLE ACCESS FULL | COWS_PURPLE_PART | 10M| 1582M| | 96246 (2)| 00:06:25 | 21 | 21 |
  99. | 8 | PARTITION RANGE ALL | | 13M| 477M| | 517K (1)| 00:34:30 | 1 | 22 |
  100. | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| COWS_TCUE_STS_SCHEDULE | 13M| 477M| | 517K (1)| 00:34:30 | 1 | 22 |
  101. |* 10 | INDEX SKIP SCAN | COWS_TCUE_STS_SCHED_IDX01 | 13M| | | 24441 (1)| 00:01:38 | 1 | 22 |
  102. |* 11 | INDEX FAST FULL SCAN | COWS_TCUE_STP_PROG_IDX01 | 24M| 256M| | 12602 (3)| 00:00:51 | | |
  103. --------------------------------------------------------------------------------------------------------------------------------------------
  104.  
  105. Query Block Name / Object Alias (identified by operation id):
  106. -------------------------------------------------------------
  107.  
  108. 1 - SEL$E1DB99B5
  109. 4 - SEL$C19FF6E1 / VW_GBC_9@SEL$377C5901
  110. 5 - SEL$C19FF6E1
  111. 7 - SEL$C19FF6E1 / A@SEL$1
  112. 9 - SEL$E1DB99B5 / B@SEL$1
  113. 10 - SEL$E1DB99B5 / B@SEL$1
  114. 11 - SEL$E1DB99B5 / C@SEL$1
  115.  
  116. Outline Data
  117. -------------
  118.  
  119. /*+
  120. BEGIN_OUTLINE_DATA
  121. USE_HASH_AGGREGATION(@"SEL$C19FF6E1")
  122. FULL(@"SEL$C19FF6E1" "A"@"SEL$1")
  123. USE_HASH_AGGREGATION(@"SEL$E1DB99B5")
  124. USE_HASH(@"SEL$E1DB99B5" "C"@"SEL$1")
  125. USE_HASH(@"SEL$E1DB99B5" "B"@"SEL$1")
  126. LEADING(@"SEL$E1DB99B5" "VW_GBC_9"@"SEL$377C5901" "B"@"SEL$1" "C"@"SEL$1")
  127. INDEX_FFS(@"SEL$E1DB99B5" "C"@"SEL$1" ("COWS_TCUE_STP_PROGRAM"."STP_S03_PROGRAM_CD" "COWS_TCUE_STP_PROGRAM"."STP_PROVIDER"
  128. "COWS_TCUE_STP_PROGRAM"."STP_PROGRAM_NO"))
  129. INDEX_SS(@"SEL$E1DB99B5" "B"@"SEL$1" ("COWS_TCUE_STS_SCHEDULE"."COWS_DIST_PERIOD" "COWS_TCUE_STS_SCHEDULE"."STS_PERF_PERIOD"
  130. "COWS_TCUE_STS_SCHEDULE"."STS_PERF_SOURCE"))
  131. NO_ACCESS(@"SEL$E1DB99B5" "VW_GBC_9"@"SEL$377C5901")
  132. OUTLINE(@"SEL$1")
  133. OUTLINE(@"SEL$377C5901")
  134. PLACE_GROUP_BY(@"SEL$1" ( "A"@"SEL$1" ) 9)
  135. OUTLINE_LEAF(@"SEL$E1DB99B5")
  136. OUTLINE_LEAF(@"SEL$C19FF6E1")
  137. NO_PARALLEL
  138. DB_VERSION('11.2.0.2')
  139. OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
  140. IGNORE_OPTIM_EMBEDDED_HINTS
  141. END_OUTLINE_DATA
  142. */
  143.  
  144. Predicate Information (identified by operation id):
  145. ---------------------------------------------------
  146.  
  147. 2 - access("B"."STS_PROVIDER"="C"."STP_PROVIDER" AND "B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO")
  148. 3 - access("ITEM_4"="B"."STS_PERF_PERIOD" AND "ITEM_3"="B"."STS_DPS_TYPE" AND "ITEM_2"="B"."STS_GROUP_NO" AND
  149. "ITEM_1"="B"."STS_GROUP_SEQ_NO")
  150. 7 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20141')
  151. 10 - access("B"."STS_PERF_SOURCE"='CB')
  152. filter("B"."STS_PERF_SOURCE"='CB')
  153. 11 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
  154.  
  155. Column Projection Information (identified by operation id):
  156. -----------------------------------------------------------
  157.  
  158. 1 - (#keys=7) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
  159. "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], SUM("ITEM_5")[22], SUM("ITEM_6")[22]
  160. 2 - (#keys=2) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
  161. "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2]
  162. 3 - (#keys=4) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
  163. "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1],
  164. "B"."STS_CHANNEL_TYPE"[VARCHAR2,7]
  165. 4 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,10], "ITEM_4"[VARCHAR2,5], "ITEM_5"[NUMBER,22],
  166. "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8]
  167. 5 - (#keys=8) "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30],
  168. "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."STS_DPS_TYPE"[VARCHAR2,10],
  169. "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."STG_GROUP_NO"[NUMBER,22], SUM(CASE "STG_STATUS" WHEN 'DP' THEN 0 ELSE
  170. "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
  171. '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
  172. "."ASCAP_BG"+"A"."ASCAP_LOGO"+"A"."ASCAP_THEME"+"A"."ASCAP_BV"+"A"."FOREIGN_FEATURE"+"A"."FOREIGN_BG"+"A"."FOREIGN_LOGO"+"A"."FOREIG
  173. N_THEME"+"A"."FOREIGN_BV"+"A"."PD_FEATURE"+"A"."PD_BG"+"A"."PD_LOGO"+"A"."PD_THEME"+"A"."PD_BV"+"A"."SESAC_FEATURE"+"A"."SESAC_BG"+"
  174. A"."SESAC_LOGO"+"A"."SESAC_THEME"+"A"."SESAC_BV"+"A"."NA_FEATURE"+"A"."NA_BG"+"A"."NA_LOGO"+"A"."NA_THEME"+"A"."NA_BV") END )[22]
  175. 6 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
  176. "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."PART_ACCT_NO"[NUMBER,22],
  177. "A"."PWN_PART_AKA_NME"[VARCHAR2,30], "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22],
  178. "A"."BMI_BG"[NUMBER,22], "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22],
  179. "A"."ASCAP_BV"[NUMBER,22], "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22],
  180. "A"."FOREIGN_BV"[NUMBER,22], "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22],
  181. "A"."NA_THEME"[NUMBER,22], "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22],
  182. "A"."BMI_FEATURE"[NUMBER,22], "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22],
  183. "A"."FOREIGN_FEATURE"[NUMBER,22], "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22],
  184. "A"."NA_LOGO"[NUMBER,22], "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
  185. 7 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
  186. "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."PART_ACCT_NO"[NUMBER,22],
  187. "A"."PWN_PART_AKA_NME"[VARCHAR2,30], "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22],
  188. "A"."BMI_BG"[NUMBER,22], "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22],
  189. "A"."ASCAP_BV"[NUMBER,22], "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22],
  190. "A"."FOREIGN_BV"[NUMBER,22], "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22],
  191. "A"."NA_THEME"[NUMBER,22], "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22],
  192. "A"."BMI_FEATURE"[NUMBER,22], "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22],
  193. "A"."FOREIGN_FEATURE"[NUMBER,22], "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22],
  194. "A"."NA_LOGO"[NUMBER,22], "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
  195. 8 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
  196. "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
  197. "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  198. 9 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
  199. "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
  200. "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  201. 10 - "B".ROWID[ROWID,10], "B"."STS_PERF_PERIOD"[VARCHAR2,5]
  202. 11 - "C"."STP_PROGRAM_NO"[NUMBER,22], "C"."STP_PROVIDER"[VARCHAR2,1], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2]
  203.  
  204. Note
  205. -----
  206. - automatic DOP: skipped because of IO calibrate statistics are missing
  207. - Warning: basic plan statistics not available. These are only collected when:
  208. * hint 'gather_plan_statistics' is used for the statement or
  209. * 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!