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';

我试图创建以下索引： '''create index wine_vid_idx on wine(vid); create index wine_cid_idx on wine(cid); create index wine_grade_idx on wine(grade);```

原始查询的执行计划是：

----------------------------------------------------------------------------------------------
| 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 |

- Polly

---

数据库性能取决于许多不同的因素，仅仅查看一个查询并进行“调整”是不可能的。当然，乐于助人的人们会尝试猜测（已经有两个人这样做了），但他们只是在猜测。你需要尝试一些东西，看看哪些适合你。我敦促你阅读优化Oracle查询的这篇回答。它将解释我们在提供明智建议之前需要的详细信息范围。它也可能让你有信心自己调整查询。 - APC

如果可能的话，您应该考虑将CHAR列类型更改为VARCHAR或VARCHAR2，以避免在针对固定宽度列进行查询时可能遇到的各种意外情况。此外，根据表中的数据量，全表扫描有时比索引访问更快。添加索引也会使DML稍微变慢，因为需要保持索引最新状态。您是否遇到了正在尝试解决的性能问题？ - Mick Mnemonic

Answer 1

索引是优化查询的一个常见有效方法，但你需要采取进一步措施。基于文本的搜索通常较慢，因此强烈建议修改你的 class 表，使其具有数字 primary key ，避免在你的 wine 表中存储 SHIRAZ 等文本，而是使用数字 foreign key 引用来自 class 表的 SHIRAZ 记录，并通过数字值从 wine 表中引用。同样，你还应该对 grade 进行类似操作。如果你还没有 grade 表，请创建一个，其中包含数字 primary key 和用于存储值（如 A）的字段。

最后，你的查询正在计算笛卡尔乘积，在集合论中我们知道这可以为问题拓扑中的每个坐标进行匹配。并且，根据关系代数，你的where子句将在三维（葡萄园、班级、葡萄酒）问题空间中的所有点上运行。据我所知，如果你重构查询以使用连接，它应该会变得更快，因为连接有一些优化来避免计算拓扑中的所有点。

让我们重构你当前的查询：

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

让我们重构此查询以适应您的架构结构更改后，我建议采纳：

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from wine
join class
on wine.class_id = class.id and class.cid = 'SHIRAZ'
join grade
on wine.grade_id = grade.id and grade.value = 'A'
join vineyard
on wine.vid = vineyard.vid;

此外，可以安全地假设只有少数年份和葡萄园，因此我们可以更改引入查询表的顺序：

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from grade
join wine
on wine.grade_id = grade.id and grade.value = 'A'
join class
on wine.class_id = class.id and class.cid = 'SHIRAZ'
join vineyard
on wine.vid = vineyard.vid;

如果这还不够，还需要进一步操作，请告诉我是否需要进一步操作。

- Lajos Arpad

---

这个答案里有很多“两美元的词语”。 - APC

@ThorstenKettner 优化是将某物改变为另一种性质相同但性能更好的行为，因此我不认为旧逻辑和新逻辑的等效性是问题。此外，您可以将这些比较放入on中，重点是在维数较小时强制引擎执行它们，而不是在最后执行。请在此处阅读有关笛卡尔乘积/积的内容：http://www.jonathancrabtree.com/mathematics/what-is-descartesian-multiplication/ - Lajos Arpad

@ThorstenKettner，另外，针对您关于查询计划不会改变的批评，我想回应一下：如果按照建议更改模式，查询计划肯定会显著改变。如果使用某些列和表的查询与为这些列和表不存在的模式编写的查询具有相同的查询计划，那么我会非常非常惊讶。 - Lajos Arpad

我认为你在这里做了一些错误的估计。**(1)** 你认为Oracle会在应用WHERE子句之前构建所有表的所有行的笛卡尔积吗？那是错误的。它将使用WHERE子句连接表。显式连接更易于阅读，并在其ON子句中显示连接条件，但对于Oracle来说，无论您是在ON还是WHERE中指定内部连接的条件都没有关系。 - Thorsten Kettner

(2) 你认为数字键比文本键快很多，是什么让你这样想的呢？Oracle将数字存储在变量的多个字节中。在那里不应该有太大的区别。特别是在二叉树索引中。(3) 你认为通过创建一个包含我们必须查找的属性的表，可以加速查询吗？怎么可能呢？以前，葡萄酒表直接包含类属性，因此可以在复合索引中使用它立即获取所需的葡萄酒行。 - Thorsten Kettner

Answer 2

首先，您正在使用旧的连接语法（实际上是20世纪80年代的语法）。以下是我们今天编写查询的方式：

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子句，就能看出你正在寻找与类别和等级匹配的葡萄酒。因此，在这两列上建立索引。顺序可能很重要，所以提供两个索引。将葡萄园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甚至两者都不使用），然后删除这些索引。

- Thorsten Kettner

Answer 3

这是您的查询：

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';

所有的连接都是内连接，所有的过滤都在一个表上进行，并且条件都是等值条件。因此，该表应该是首先访问的表。然后，您需要在其他表的过滤条件和相应的连接键上进行连接：

• wine(cid, grade, vid)（前两个键可以任意顺序）。

其他表的连接键vineyard(vid)和class(cid)已经建立索引，因为它们被声明为主键。因此，以上是您唯一需要的额外索引。

- Gordon Linoff