SQL Oracle 完整提示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6168581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Full Hint
提问by Ricko M
If i understand the documentation correctly; the full hint should force a full table scan. In the below scenario it is not performing the same;
如果我正确理解文档；完整提示应该强制进行全表扫描。在下面的场景中，它的表现不同；
Num as in index created on it.
在其上创建的索引中的 Num。
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM NOT NULL NUMBER
NUM2 NUMBER(10)
NUM3 NUMBER
Query:
询问：
select num from test;
Result:
结果：
NUM
----------
1
2
Execution Plan
执行计划
Plan hash value: 410557223
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | ID | 2 | 4 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Query:
询问：
select /* +full(test) */ num from test;
Result:
结果：
NUM
----------
1
2
Execution Plan
执行计划
Plan hash value: 410557223
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | ID | 2 | 4 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
I understand i am selecting a value which is stored in the index. Addition of any other column makes the scan as full. Hence i have to ask the obvious. Is hint a request or is it a command to the optimizer ?
我知道我正在选择一个存储在索引中的值。任何其他列的添加都会使扫描已满。因此，我不得不问显而易见的问题。提示是请求还是优化器的命令？
On a side note , what does computation of statistics have to do with optimization. Are the statistics of an index updated automatically or is this an explicit operation?
附带说明一下，统计计算与优化有什么关系。索引的统计信息是自动更新还是显式操作？
回答by eaolson
I haven't tested this, but the correct syntax for using a hint is:
我还没有测试过这个，但使用提示的正确语法是：
select /*+ full(test) */ num from test;
It's slash-star-plus-space.
这是斜线星加空格。
回答by APC
"On a side note , what does computation of statistics have to do with optimization. Are the statistics of an index updated automatically or is this an explicit operation?"
“附带说明一下，统计数据的计算与优化有什么关系。索引的统计数据是自动更新还是显式操作？”
Probably merits a question in its own right (it's certainly more pertinent than your main question :-D ).
可能本身就值得提出一个问题（它肯定比您的主要问题更相关 :-D ）。
Oracle does not automatically maintain 100% statistics for all our objects. Prior to 10g we had to explicitly schedule background jobs to do this, using DBMS_STATS.GATHER_%_STATISTICS.
Oracle 不会自动维护我们所有对象的 100% 统计信息。在 10g 之前，我们必须使用 DBMS_STATS.GATHER_%_STATISTICS 明确安排后台作业来执行此操作。
Since 11g Oracle has changed the default behaviour. It monitors DML issued against a schema and runs a job to gather statistics for objects when the existing statistics become stale. Even then it only computes stats for a percentage of rows. This is usually Good Enough, especially for large tables when it would be expensive to examine all the rows.
自 11g Oracle 更改了默认行为。它监视针对模式发出的 DML，并在现有统计数据变得陈旧时运行作业以收集对象的统计数据。即便如此，它也只计算一定百分比的行的统计信息。这通常已经足够了，特别是对于检查所有行的成本很高的大型表。
This default behaviour itself is usually Good Enough. If you have problems with bad query plans it might be worthwhile gather fresh statistics, perhaps against a larger sample of rows. but don't feel compelled to routinely gather full stats against your whole schema, as many places still do. Most of the time you're probably just wasting CPU cycles, and you run the risk of destabilising some of you existing plans.
这种默认行为本身通常足够好。如果您对糟糕的查询计划有疑问，可能值得收集新的统计信息，也许是针对更大的行样本。但不要觉得有必要像许多地方一样定期收集整个架构的完整统计数据。大多数情况下，您可能只是在浪费 CPU 周期，并且冒着破坏某些现有计划的风险。
Database statistics is a big topic. The Performance Tuning Guide has a whole chapter on it. Find out more.Also, read the PL/SQL Packages guide for more on DBMS_STATSitself.
数据库统计是一个很大的话题。性能调优指南有一整章关于它。 了解更多。另外，请阅读PL/SQL 包指南以了解有关 DBMS_STATS本身的更多信息。
