SPM情景#1：

/*
|| Script:  SPM_2_1.sql
|| Purpose: Generate several SQL statements that perform Data
||          Warehouse query loads on an Oracle 10gR2 database
||          for capture into a SQL Tuning Set (STS) that will be
||          transferred to an Oracle 11g database to demonstrate
||          SQL Plan Baseline seeding.
*/

—–
— Query:   SPM_2_1.1
— Purpose: Summarize sales activity within country and Promotion
—–   
select /*SPM_2_1.1*/
CTY.country_total_id
,PR.promo_total_id
,count(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.promotions PR
where S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,PR.promo_total_id
;

—–
— Query:   SPM_2_1.2
— Purpose: Summarize sales activity within country and Promotion
—–   
select /*SPM_2_1.2*/
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,PR.promo_total_id
,count(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.promotions PR
where S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,PR.promo_total_id
;

—–
— Query:   SPM_2_1.3
— Purpose: Summarize sales activity within country, Product,
—          Channel and Promotion
—–   
select /*SPM_2_1.3*/
CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id   
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,count(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
where S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
;

—–
— Query:   SPM_2_1.4
— Purpose: Summarize sales activity within country, Product,
—          Channel and Promotion
—–   
select /*SPM_2_1.4*/
CTY.country_total_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,count(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
where S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
;

—–
— Query:   SPM_2_1.5
— Purpose: Summarize sales activity within country, Product,
—          Channel and Promotion
—–   
select /*SPM_2_1.5*/
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,count(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
where S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
;

SPM情景#2：

/*
|| SFA_Queries.sql
||
|| Various queries to produce workload against simulated new
||  Sales Force Administration application
||
*/

—–
— Set up and initialize bind variables
—–
VARIABLE rgn_abbr  VARchar2(4);
VARIABLE rgn_desc  VARchar2(40);
VARIABLE cust_id    NUMBER;
BEGIN
:rgn_abbr  := ’NE00’;
:rgn_desc  := ’South%’;
:cust_id    := 9090;
END;
/

—–
— Query:   SPM_2.2.1
— Purpose: Summarize quantity sold and revenue within U.S. States
—–
select /*SPM_2.2.1*/
C.cust_state_province   
,SUM(sh.quantity_sold)
,SUM(sh.amount_sold)
FROM
  sh.sales SH
,sh.customers C
,sh.countries T
where SH.cust_id = C.cust_id
AND C.country_id = T.country_id
AND T.country_iso_code IN (’GB’,’PL’)
GROUP BY C.cust_state_province
;

—–
— Query:   SPM_2.2.2
— Purpose: Show distribution of geographic areas within
—          Sales Region and District
—–
select /*SPM_2.2.2*/
SR.abbr,
SD.abbr,
SZ.geo_id,
count(C.cust_id) “count”
FROM
sfa.sales_regions SR,
sfa.sales_districts SD,
sfa.sales_zones SZ,
sh.customers C
where SD.region_id = SR.region_id
AND SZ.district_id = SD.district_id
AND C.cust_state_province = SZ.geo_id
AND SR.abbr = ’SE00’
GROUP BY
SR.abbr
,SD.abbr
,SZ.geo_id
;

—–
— Query:   SPM_2.2.3
— Purpose: Accumulate quantities and revenue within Sales Region, District,
—   and Territory. Note that this query +should+ take advantage of
—          materialized view SFA.MV_SALES_SUMMARY for most effective retrieval
—–
select /*SPM_2_2.3*/
SR.abbr
,SD.abbr
,SZ.geo_id
,C.cust_id
,SUM(SH.quantity_sold)
,AVG(SH.quantity_sold)
,count(SH.quantity_sold)
,SUM(SH.amount_sold)
,AVG(SH.amount_sold)
,count(SH.amount_sold)
FROM
sfa.sales_regions SR
,sfa.sales_districts SD
,sfa.sales_zones SZ
,sh.customers C
,sh.sales SH
where SD.region_id = SR.region_id
AND SZ.district_id = SD.district_id
AND C.cust_state_province = SZ.geo_id
AND C.cust_ID = SH.cust_id
GROUP BY
SR.abbr
,SD.abbr
,SZ.geo_id
,C.cust_id   
ORDER BY
SR.abbr
,SD.abbr
,SZ.geo_id
,C.cust_id   
;

—–
— Query:   SPM_2.2.4
— Purpose: Accumulate quantities and revenue within Sales Region, District,
—   and Territory by querying directly against SFA.MV_SALES_SUMMARY
—–
select /*SPM_2_2.4*/
rgn_abbr
,dst_abbr
,ter_abbr       
,cust_id
,tot_qty_sold
,avg_qty_sold
,cnt_qty_sold
,tot_amt_sold
,avg_amt_sold
,cnt_amt_sold
FROM
sfa.mv_sales_summary
where dst_abbr = ’NE20’
ORDER BY
rgn_abbr
,dst_abbr
,ter_abbr       
,cust_id
; 

—–
— Query:   SPM_2.2.5
— Purpose: Accumulate quantities and revenue within Sales Region and District.
—   for a selected Region. Note that this query +should+ take advantage
—          of materialized view SFA.MV_SALES_SUMMARY for most effective retrieval
—–
select /*SPM_2_2.5*/
SR.abbr
,SD.abbr
,SUM(SH.quantity_sold)
,AVG(SH.quantity_sold)
,count(SH.quantity_sold)
,SUM(SH.amount_sold)
,AVG(SH.amount_sold)
,count(SH.amount_sold)
FROM
sfa.sales_regions SR
,sfa.sales_districts SD
,sfa.sales_zones SZ
,sh.customers C
,sh.sales SH
where SD.region_id = SR.region_id
AND SZ.district_id = SD.district_id
AND C.cust_state_province = SZ.geo_id
AND C.cust_ID = SH.cust_id
AND SR.abbr = :rgn_abbr
GROUP BY
SR.abbr
,SD.abbr
ORDER BY
SR.abbr
,SD.abbr
;

—–
— Query:   SPM_2.2.6
— Purpose: Accumulate quantities and revenue within Sales Region. Note that
—          this query +cannot+ take advantage of SFA.MV_SALES_SUMMARY for
—          effective retrieval because of the selection criteria against
—          Region Description
—–
select /*SPM_2_2.6*/
SR.abbr
,SUM(SH.quantity_sold)
,AVG(SH.quantity_sold)
,count(SH.quantity_sold)
,SUM(SH.amount_sold)
,AVG(SH.amount_sold)
,count(SH.amount_sold)
FROM
sfa.sales_regions SR
,sfa.sales_districts SD
,sfa.sales_zones SZ
,sh.customers C
,sh.sales SH
where SD.region_id = SR.region_id
AND SZ.district_id = SD.district_id
AND C.cust_state_province = SZ.geo_id
AND C.cust_ID = SH.cust_id
AND C.cust_id = :cust_id
AND SR.description like :rgn_desc
GROUP BY
SR.abbr
ORDER BY
SR.abbr
;