第14章 优化器不是完美的 练习 14.1 重写SQL 14.8中的游标,使得新游标的访问路径满足:
-- SQL 14.8
DECLARE CURSOR141 CURSOR FOR
SELECT LNAME, FNAME, CNO
FROM CUST
WHERE (LNAME = :LNAMEPREV AND CNO > :CNOPREV)
OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)
ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE
要求:不能去除ORDER BY。 提示:WHERE语句可以包含操作符NOT,不过NOT将使该谓词对于优化器而言太过困难(无匹配列)。 分析:
查询谓词只有两个字段LNAME、CNO,并且这两个字段都有范围条件。要满足“MC=1”,候选索引为(LNAME)、(CNO)、(LNAME,CNO)、(CNO,LNAME)。要满足“仅需访问索引”,则前两个单列候选索引被排除。后两个复合索引还要加入FNAME字段,使之成为宽索引。候选索引变为(LNAME,CNO,FNAME)或(CNO,LNAME,FNAME)。最后要满足“无排序”,则索引只能是(LNAME,CNO,FNAME)。索引确定后需要改写SQL 14.8,使得查询能够走(LNAME,CNO,FNAME)索引。 原查询语句里的OR前后括号里的谓词条件都是是非BT谓词,所以要把外层的OR去掉。根据逻辑运算的等价性做如下转换(前提是:LNAMEPREV <= :LNAMEMAX): (LNAME = :LNAMEPREV AND CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX) => ((LNAME = :LNAMEPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)) AND ((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)) => (LNAME = :LNAMEPREV OR LNAME > :LNAMEPREV) AND (LNAME = :LNAMEPREV OR LNAME <= :LNAMEMAX) AND ((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)) => (LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX) AND ((CNO > :CNOPREV) OR (LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND LNAME <> :LNAMEPREV)) 出于简化,设 LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX 为 a CNO > :CNOPREV 为 b LNAME <> :LNAMEPREV 为 c 则原条件谓词转化为: a and (b or (a and c)) => a and (a or b) and (b or c) => a and (b or c) 再把a、b、c还原回去,则条件原来的条件谓词可以转化为: LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND (CNO > :CNOPREV OR LNAME <> :LNAMEPREV) 这个等价的谓词条件去掉了外层的OR,可以以LNAME作为匹配列,CNO和LNAME作为过滤列访问(LNAME,CNO,FNAME),满足题目要求。最终的SQL改写为:
-- SQL 14.8
DECLARE CURSOR141 CURSOR FOR
SELECT LNAME, FNAME, CNO
FROM CUST
WHERE LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX
AND (CNO > :CNOPREV OR LNAME <> :LNAMEPREV)
ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE
14.2 列出你正在使用的优化器具有的最常见的缺点。