Requirements(要求): Find the smallest collection of indexes that speed up the processing of all SELECT
statements listed below.
Note, that it is not allowed to use the "hints" in order to force the system to use an index for query processing.(1)
SELECT * FROM ORDERS
WHERE O_ORDERDATE = '12-DEC-2004' AND O_TOTALPRICE = 777;(2)
SELECT *
FROM SUPPLIER
WHERE S_NAME = 'JONES' AND S_PHONE = 1234567;(3)
SELECT *
FROM SUPPLIER
WHERE S_PHONE = 9999999;(4)
SELECT *
FROM SUPPLIER
WHERE S_NAME = 'JONES' OR
S_PHONE = 1234567;(5)
SELECT *
FROM SUPLIER
WHERE S_NAME = 'JONES';(6)
SELECT COUNT(O_ORDERDATE) FROM ORDERS;(7)
SELECT O_TOTALPRICE, COUNT(*)
FROM ORDERS
GROUP BY O_TOTAL_PRICE;(8)
SELECT * FROM ORDERS
WHERE O_ORDERDATE + 1 = '14-DEC-2004';Implement SQL script that creates the indexes found in the previous step and lists the query execution plans for all SELECT statements given above. There is NO need to execute the queries and to collect the statistics.
statements listed below.
Note, that it is not allowed to use the "hints" in order to force the system to use an index for query processing.(1)
SELECT * FROM ORDERS
WHERE O_ORDERDATE = '12-DEC-2004' AND O_TOTALPRICE = 777;(2)
SELECT *
FROM SUPPLIER
WHERE S_NAME = 'JONES' AND S_PHONE = 1234567;(3)
SELECT *
FROM SUPPLIER
WHERE S_PHONE = 9999999;(4)
SELECT *
FROM SUPPLIER
WHERE S_NAME = 'JONES' OR
S_PHONE = 1234567;(5)
SELECT *
FROM SUPLIER
WHERE S_NAME = 'JONES';(6)
SELECT COUNT(O_ORDERDATE) FROM ORDERS;(7)
SELECT O_TOTALPRICE, COUNT(*)
FROM ORDERS
GROUP BY O_TOTAL_PRICE;(8)
SELECT * FROM ORDERS
WHERE O_ORDERDATE + 1 = '14-DEC-2004';Implement SQL script that creates the indexes found in the previous step and lists the query execution plans for all SELECT statements given above. There is NO need to execute the queries and to collect the statistics.
create index idx_supplier_name on SUPPLIER(S_NAME);
create index idx_supplier_phone on SUPPLIER(S_PHONE);这样2,3,5都可用索引,4将导致全表扫描,改为
SELECT *
FROM SUPPLIER
WHERE S_NAME = 'JONES'
union
SELECT *
FROM SUPPLIER
WHERE S_PHONE = 1234567; ORDERS上索引
O_ORDERDATE,O_TOTALPRICE 上建复合索引,1会有很好效率
O_TOTAL_PRICE上建索引,7会利用
8用不上索引,除非建立函数索引