sql - Oracle 选择索引优化

Question

我需要使用索引优化此查询。我试图索引一些列，但它没有帮助。有没有人有想法？

我需要优化的查询:

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from vineyard, class, wine
where wine.vid = vineyard.vid
and wine.cid = class.cid
and wine.cid = 'SHIRAZ' and grade = 'A';

我尝试创建以下索引:'''在 wine(vid) 上创建索引 wine_vid_idx；在 wine(cid) 上创建索引 wine_cid_idx；在 wine(grade) 上创建索引 wine_grade_idx；```

我对原始查询的执行计划是:

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    42 |  9114 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |              |    42 |  9114 |    10   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |    42 |  6930 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CLASS    |     1 |    50 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C0027457 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS CLUSTER       | WINE    |    42 |  4830 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
---------------------------------------------------------------------------------------------
|   6 |   TABLE ACCESS FULL           | VINEYARD |   160 |  8320 |     8   (0)| 00:00:01 |

Answer 1

首先:您使用的是旧的连接语法(实际上是 1980 年代的)。这就是我们今天编写查询的方式:

Select
   w.vintage, w.wine_no, w.wname, w.pctalc, w.grade, w.price, w.vid, v.vname, w.cid, c.cname
from wine w
join vineyard v on v.vid = w.vid
join class c on c.cid = w.cid
where w.cid = 'SHIRAZ' 
and w.grade = 'A';

只要看一下 WHERE 子句就足以看出您正在寻找与类别和等级相匹配的 Wine 。所以，在两列上有一个索引。顺序可能很重要，因此提供两个索引。用葡萄园 ID 扩展它，以便快速找到葡萄园表。

至于等级和葡萄园，你应该已经有了他们ID的索引。您可能希望为您选择的每个表添加一列，以便 DBMS 可以直接从索引中获取值。

create index idx01 on wine ( cid, grade, vid );
create index idx02 on wine ( grade, cid, vid );
create index idx03 on class ( cid, cname );
create index idx04 on vineyard ( vid, vname );

使用执行计划来检测未使用的索引(查询将只使用 idx01 或 idx02 或什至不使用这些索引，而不是两者)并删除它们。