Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Formatted on 1/27/2015 12:44:47 PM (QP5 v5.256.13226.35510) */
- SELECT a.STS_PERF_PERIOD,
- a.PART_ACCT_NO,
- a.PWN_PART_AKA_NME,
- a.PWN_P_W_IND,
- a.AFL_SOCIETY_NME,
- b.sts_CHANNEL_TYPE,
- c.stp_s03_program_cd--TOTAL Music Minutes CALCULATION:
- ,
- SUM (
- CASE
- WHEN STG_STATUS = '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.ASCAP_BG
- + a.ASCAP_LOGO
- + a.ASCAP_THEME
- + a.ASCAP_BV
- + a.FOREIGN_FEATURE
- + a.FOREIGN_BG
- + a.FOREIGN_LOGO
- + a.FOREIGN_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
- + 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)
- / 60
- AS "TOT_MUSIC_MINS"--BMI Music Minutes CALCULATION:
- ,
- SUM (
- CASE
- WHEN STG_STATUS = 'DP'
- THEN
- 0
- ELSE
- a.perf_count
- * ( ( a.BMI_FEATURE
- + a.BMI_BG
- + a.BMI_LOGO
- + a.BMI_THEME
- + a.BMI_BV))
- END)
- / 60
- AS "BMI_MUSIC_MINS"
- FROM PRDM.COWS_PURPLE_PART a,
- prdm.cows_TCUE_STS_SCHEDULE b,
- prdm.cows_tcue_stp_program c
- WHERE A.STS_PERF_PERIOD = b.sts_perf_period
- AND A.STS_DPS_TYPE = b.sts_dps_type
- AND A.STG_GROUP_NO = b.sts_group_no
- AND a.stg_group_seq_no = b.sts_group_seq_no
- AND b.sts_provider = c.stp_provider
- AND b.sts_program_no = c.stp_program_no
- AND a.STS_PERF_PERIOD ='20133'
- AND b.sts_perf_source = 'CB'
- AND a.PWN_P_W_IND = 'W'
- AND c.stp_s03_program_cd <> '36'
- GROUP BY a.STS_PERF_PERIOD,
- a.PART_ACCT_NO,
- a.PWN_PART_AKA_NME,
- a.PWN_P_W_IND,
- a.AFL_SOCIETY_NME,
- b.sts_CHANNEL_TYPE,
- c.stp_s03_program_cd
- Plan hash value: 3089352397
- ----------------------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
- ----------------------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 651M| 90G| | 14M (1)| 16:21:07 | | |
- | 1 | HASH GROUP BY | | 651M| 90G| 104G| 14M (1)| 16:21:07 | | |
- |* 2 | HASH JOIN | | 651M| 90G| | 1274K (2)| 01:24:59 | | |
- | 3 | VIEW | VW_GBC_9 | 7106 | 652K| | 262K (2)| 00:17:30 | | |
- | 4 | HASH GROUP BY | | 7106 | 1145K| 3110M| 262K (2)| 00:17:30 | | |
- | 5 | PARTITION RANGE SINGLE | | 12M| 2004M| | 122K (2)| 00:08:09 | 19 | 19 |
- |* 6 | TABLE ACCESS FULL | COWS_PURPLE_PART | 12M| 2004M| | 122K (2)| 00:08:09 | 19 | 19 |
- | 7 | VIEW | VW_GBF_10 | 52M| 2775M| | 1007K (2)| 01:07:12 | | |
- | 8 | HASH GROUP BY | | 52M| 2421M| 6607M| 1007K (2)| 01:07:12 | | |
- |* 9 | HASH JOIN | | 115M| 5265M| 535M| 580K (1)| 00:38:44 | | |
- |* 10 | INDEX FAST FULL SCAN | COWS_TCUE_STP_PROG_IDX01 | 24M| 256M| | 12602 (3)| 00:00:51 | | |
- | 11 | PARTITION RANGE ALL | | 13M| 477M| | 517K (1)| 00:34:30 | 1 | 22 |
- | 12 | TABLE ACCESS BY LOCAL INDEX ROWID| COWS_TCUE_STS_SCHEDULE | 13M| 477M| | 517K (1)| 00:34:30 | 1 | 22 |
- |* 13 | INDEX SKIP SCAN | COWS_TCUE_STS_SCHED_IDX01 | 13M| | | 24441 (1)| 00:01:38 | 1 | 22 |
- ----------------------------------------------------------------------------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$B410752D
- 3 - SEL$C19FF6E1 / VW_GBC_9@SEL$377C5901
- 4 - SEL$C19FF6E1
- 6 - SEL$C19FF6E1 / A@SEL$1
- 7 - SEL$DD65AEC8 / VW_GBF_10@SEL$E327F127
- 8 - SEL$DD65AEC8
- 10 - SEL$DD65AEC8 / C@SEL$1
- 12 - SEL$DD65AEC8 / B@SEL$1
- 13 - SEL$DD65AEC8 / B@SEL$1
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- USE_HASH_AGGREGATION(@"SEL$C19FF6E1")
- FULL(@"SEL$C19FF6E1" "A"@"SEL$1")
- USE_HASH_AGGREGATION(@"SEL$DD65AEC8")
- SWAP_JOIN_INPUTS(@"SEL$DD65AEC8" "C"@"SEL$1")
- USE_HASH(@"SEL$DD65AEC8" "C"@"SEL$1")
- LEADING(@"SEL$DD65AEC8" "B"@"SEL$1" "C"@"SEL$1")
- INDEX_FFS(@"SEL$DD65AEC8" "C"@"SEL$1" ("COWS_TCUE_STP_PROGRAM"."STP_S03_PROGRAM_CD" "COWS_TCUE_STP_PROGRAM"."STP_PROVIDER"
- "COWS_TCUE_STP_PROGRAM"."STP_PROGRAM_NO"))
- INDEX_SS(@"SEL$DD65AEC8" "B"@"SEL$1" ("COWS_TCUE_STS_SCHEDULE"."COWS_DIST_PERIOD" "COWS_TCUE_STS_SCHEDULE"."STS_PERF_PERIOD"
- "COWS_TCUE_STS_SCHEDULE"."STS_PERF_SOURCE"))
- USE_HASH_AGGREGATION(@"SEL$B410752D")
- USE_HASH(@"SEL$B410752D" "VW_GBF_10"@"SEL$E327F127")
- LEADING(@"SEL$B410752D" "VW_GBC_9"@"SEL$377C5901" "VW_GBF_10"@"SEL$E327F127")
- NO_ACCESS(@"SEL$B410752D" "VW_GBF_10"@"SEL$E327F127")
- NO_ACCESS(@"SEL$B410752D" "VW_GBC_9"@"SEL$377C5901")
- OUTLINE(@"SEL$1")
- OUTLINE(@"SEL$377C5901")
- OUTLINE(@"SEL$E327F127")
- PLACE_GROUP_BY(@"SEL$1" ( "B"@"SEL$1" "C"@"SEL$1" ) ( "A"@"SEL$1" ) 9)
- OUTLINE_LEAF(@"SEL$B410752D")
- OUTLINE_LEAF(@"SEL$C19FF6E1")
- OUTLINE_LEAF(@"SEL$DD65AEC8")
- NO_PARALLEL
- DB_VERSION('11.2.0.2')
- OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
- IGNORE_OPTIM_EMBEDDED_HINTS
- END_OUTLINE_DATA
- */
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("ITEM_4"="ITEM_4" AND "ITEM_3"="ITEM_3" AND "ITEM_2"="ITEM_2" AND "ITEM_1"="ITEM_1")
- 6 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20133')
- 9 - access("B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO" AND "B"."STS_PROVIDER"="C"."STP_PROVIDER")
- 10 - filter("C"."STP_S03_PROGRAM_CD"<>'36')
- 13 - access("B"."STS_PERF_SOURCE"='CB')
- filter("B"."STS_PERF_SOURCE"='CB')
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - (#keys=7) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
- "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2], SUM("ITEM_5"*"ITEM_5")[22], SUM("ITEM_6"*"ITEM_6")[22]
- 2 - (#keys=4) "ITEM_4"[VARCHAR2,5], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8],
- "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2]
- 3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,10], "ITEM_4"[VARCHAR2,5], "ITEM_5"[NUMBER,22],
- "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22], "ITEM_8"[VARCHAR2,30], "ITEM_9"[VARCHAR2,1], "ITEM_10"[VARCHAR2,8]
- 4 - (#keys=8) "A"."STG_GROUP_SEQ_NO"[NUMBER,22], "A"."STG_GROUP_NO"[NUMBER,22], "A"."STS_DPS_TYPE"[VARCHAR2,10],
- "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],
- "A"."AFL_SOCIETY_NME"[VARCHAR2,8], SUM(CASE "STG_STATUS" WHEN 'DP' THEN 0 ELSE
- "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
- '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".
- "ASCAP_BG"+"A"."ASCAP_LOGO"+"A"."ASCAP_THEME"+"A"."ASCAP_BV"+"A"."FOREIGN_FEATURE"+"A"."FOREIGN_BG"+"A"."FOREIGN_LOGO"+"A"."FOREIGN_TH
- 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
- 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]
- 5 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
- "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],
- "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22], "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], "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], "A"."FOREIGN_BV"[NUMBER,22],
- "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22],
- "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22],
- "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22],
- "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22],
- "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
- 6 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2],
- "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],
- "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], "A"."ASCAP_BG"[NUMBER,22], "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], "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], "A"."FOREIGN_BV"[NUMBER,22],
- "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22],
- "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22],
- "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22],
- "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22],
- "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22]
- 7 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,10], "ITEM_4"[VARCHAR2,5], "ITEM_5"[NUMBER,22],
- "ITEM_6"[NUMBER,22], "ITEM_7"[VARCHAR2,7], "ITEM_8"[VARCHAR2,2]
- 8 - (#keys=6) "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_GROUP_NO"[NUMBER,22], "B"."STS_DPS_TYPE"[VARCHAR2,10],
- "B"."STS_PERF_PERIOD"[VARCHAR2,5], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], COUNT(*)[22]
- 9 - (#keys=2) "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], "B"."STS_PERF_PERIOD"[VARCHAR2,5], "B"."STS_GROUP_NO"[NUMBER,22],
- "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10]
- 10 - "C"."STP_PROGRAM_NO"[NUMBER,22], "C"."STP_PROVIDER"[VARCHAR2,1], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2]
- 11 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
- "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
- "B"."STS_PERF_PERIOD"[VARCHAR2,5]
- 12 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], "B"."STS_GROUP_NO"[NUMBER,22],
- "B"."STS_GROUP_SEQ_NO"[NUMBER,22], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10],
- "B"."STS_PERF_PERIOD"[VARCHAR2,5]
- 13 - "B".ROWID[ROWID,10], "B"."STS_PERF_PERIOD"[VARCHAR2,5]
- Note
- -----
- - automatic DOP: skipped because of IO calibrate statistics are missing
- - Warning: basic plan statistics not available. These are only collected when:
- * hint 'gather_plan_statistics' is used for the statement or
- * parameter 'statistics_level' is set to 'ALL', at session or system level
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
