Oracle数据库11g：SQL计划管理（三）(1)(5)
-----
-- 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
;
【51CTO.COM 专家翻译，转载请注明出处及作者！】
【相关文章】
