扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
在本页阅读全文(共2页)
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
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。
现场直击|2021世界人工智能大会
直击5G创新地带,就在2021MWC上海
5G已至 转型当时——服务提供商如何把握转型的绝佳时机
寻找自己的Flag
华为开发者大会2020(Cloud)- 科技行者