在Oracle中理解解释计划

问题描述

我试图理解oracle中的解释计划，想知道oracle在制定解释计划时会考虑哪些条件

I was trying to understand the explain plan in oracle and wanted to know what conditions oracle considers while forming the explain plan

我正在用oracle 11g中的HR模式测试一个简单查询

I was testing a simple query in HR schema present in oracle 11g

select * from countries
where region_id in (select region_id from regions where region_name = 'Europe');

当我运行以下查询时:

explain plan for
select * from countries
where region_id in (select region_id from regions where region_name = 'Europe');

SELECT * FROM table(dbms_xplan.display(null,null,'basic'));

我在解释表中得到了以下输出:

I got the following output in the explain table:

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  NESTED LOOPS                |                 |
|   2 |   INDEX FULL SCAN            | COUNTRY_C_ID_PK |
|   3 |   TABLE ACCESS BY INDEX ROWID| REGIONS         |
|   4 |    INDEX UNIQUE SCAN         | REG_ID_PK       |
--------------------------------------------------------

在这里，我观察到外部查询首先执行，即按照表3所示，首先执行国家(地区)表.

Here I observed that the outer query was executed first, i.e countries table was executed first as indicated by Row 3.

现在，我在regions表的region_name上添加了一个索引，并再次运行了说明计划并得到以下输出

Now I added an index on the region_name of the regions table and ran the explain plan againand got the following output

--------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|   1 |  NESTED LOOPS                |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| REGIONS               |
|   3 |    INDEX RANGE SCAN          | REGIONNAME_REGIONS_IX |
|   4 |   INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK       |
|   5 |    INDEX RANGE SCAN          | COUNTRIES_REGIONID_IX |
--------------------------------------------------------------

现在我的问题是:

1. 无论索引是否存在，都不应先执行内部查询
2. 如果添加索引改变了执行计划，还有哪些其他功能可以改变它?
3. 通常情况下，执行过程是顺序的(首先执行首先发生的联接，然后执行查询中的下一个联接)?

预先感谢您的帮助.

-Varun

推荐答案

解释计划严重依赖基于成本的优化器(CBO).您可以通过收集要查询的表的统计信息来帮助完成此过程.现在，就索引为什么更改计划而言，这是因为您已经向CBO提供了以前没有的关键信息.相当于我问你这个问题:

The explain plan relies heavily on the Cost Based Optimizer (CBO). You can help this process out by gathering statistics on the table(s) you are querying against. Now in terms of why would the index change the plan, that is because you have supplied critical information to the CBO that it did not have before. It is the equivalent of me asking you this question:

没有索引:
这条街在哪里?"

No index:
"Where is the street?"

带索引:
这条街上有蓝色的房子在哪儿?"

With index:
"Where is the street that has a blue house on it?"

第二个问题提供了更大的上下文，因此，您可以更快地推断出问题，而不必列举所有类似街道的事物.

The second question gives greater context and is thus faster for you to deduce and you don't have to enumerate all such things that are streets.

您可以为查询提供提示，即:

You can supply hints to a query i.e.:

select /*+ parallel */ * from table
提供提示来并行运行此查询.

select /*+ parallel */ * from table
to give a hint to run this query in parallel.

对于第三个问题，我想这只是Oracle流程的一部分，没有记录下来供全世界使用.

For the third question, that I imagine is a bit of the Oracle process and is not documented for the world to consume.

在第一个问题中，不一定，这全都是基于成本的.

In the first question, no not necessarily, it is all cost based.

这篇关于在Oracle中理解解释计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！