有这样的SQL语句,
Select D.CODE, D.DESC, D.ED, Count(*) CT
From A, B,C,D
Where A.SID = B.SID and A.CID = C.CID and C.CODE ='code' and D.RID = B.RID
Group By D.CODE, D.DESC, D.ED
Order By CT 执行计划如下:
index scan index_name cost CPU cost
index unique scan CCID_index 1 14443
index range scan ASID_CID_index 13 697907
index fast full scan BS_SID_RID_index 376 68398823
index unique scan DRID_index 0 1250
(C、D表运用了唯一索引 CCID_index、DRID_index;
A、B表运用了复合索引ASID_CID_index 、BS_SID_RID_index 。)
如果要优化上面的sql,使其降低查询 B表所消耗的 CPU cost,该如何优化?
Select D.CODE, D.DESC, D.ED, Count(*) CT
From A, B,C,D
Where A.SID = B.SID and A.CID = C.CID and C.CODE ='code' and D.RID = B.RID
Group By D.CODE, D.DESC, D.ED
Order By CT 执行计划如下:
index scan index_name cost CPU cost
index unique scan CCID_index 1 14443
index range scan ASID_CID_index 13 697907
index fast full scan BS_SID_RID_index 376 68398823
index unique scan DRID_index 0 1250
(C、D表运用了唯一索引 CCID_index、DRID_index;
A、B表运用了复合索引ASID_CID_index 、BS_SID_RID_index 。)
如果要优化上面的sql,使其降低查询 B表所消耗的 CPU cost,该如何优化?
如果用的是CBO 的话,一般来说, 数据量少的表放在from 的最后,能过滤掉大部分数据的条件放在 where 最后。
复合索引的话,查询时按照建立的字段顺序来查询。
2、在D表上,添加CODE、DESC、ED三个字段的复合索引