Oracle数据库11g：SQL计划管理（三）(5)

作者: Jim Czuprynski/黄永兵 译 出处:51CTO.com　　

 

 

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
;