SPM_3_1：

—–
— Query: SPM_3_1.sql
—–
VARIABLE cust_last_name VARchar2(30);
VARIABLE beg_prod_id NUMBER;
VARIABLE end_prod_id NUMBER;
BEGIN
:cust_last_name := ’Sand%’;
:beg_prod_id := 100;
:end_prod_id := 500;
END;
/
PRINT :cust_last_name :beg_prod_id :end_prod_id
select /*SPM_3.1*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.products P
where S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN :beg_prod_id AND :end_prod_id
AND C.cust_last_name like :cust_last_name
GROUP BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
ORDER BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
;

SPM_3_2：

—–
— Query: SPM_3_2.1
— Optimizer will use  “star transformation” method to retrieve a
— smaller subset more efficiently
—–
select /*SPM_3_2.1*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,S.amount_sold
,S.quantity_sold
FROM
sh.sales S
,sh.customers C
,sh.products P
where S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN 100 AND 500
AND C.cust_last_name like ’Sand%’
ORDER BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
;

—–
— Query: SPM_3_2.2
— Optimizer will choose a full table scan of SH.CUSTOMERS because of the larger
— subset of data that’s retrieved
—–
select /*SPM_3_2.2*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,S.amount_sold
,S.quantity_sold
FROM
sh.sales S
,sh.customers C
,sh.products P
where S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN 0 AND 99999
AND C.cust_last_name like ’Zil%’
ORDER BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
;

—–
— Query: SPM_3_2.3
— Optimizer will still choose a full table scan of SH.CUSTOMERS but
— return an even larger result set
—–
select /*SPM_3_2.3*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,S.amount_sold
,S.quantity_sold
FROM
sh.sales S
,sh.customers C
,sh.products P
where S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN 0 AND 99999
AND C.cust_last_name like ’S%’
ORDER BY
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
;