关于Oracle解释计划中的成本的问题 [英] Question about Cost in Oracle Explain Plan

问题描述

Oracle估算某些查询的成本"时，它实际上是在查看表中的数据量(行)吗?

When Oracle is estimating the 'Cost' for certain queries, does it actually look at the amount of data (rows) in a table?

例如:

如果我要对员工进行全表扫描以查找name ='Bob'，它是通过计算现有行数来估算成本吗?还是总是固定成本?

If I'm doing a full table scan of employees for name='Bob', does it estimate the cost by counting the amount of existing rows, or is it always a set cost?

推荐答案

在默认配置中，Oracle将检查表统计信息(您可以通过查询ALL_TABLES视图来查看该统计信息-参见NUM_ROWS列).通常，Oracle作业会定期运行以通过查询部分或全部表来重新收集这些统计信息.

In the default configuration, Oracle will check the table statistics (which you can look at by querying the ALL_TABLES view - see the column NUM_ROWS). Normally an Oracle job is run periodically to re-gather these statistics by querying part or all of the table.

如果尚未收集统计信息，则优化器将(取决于optimizer_dynamic_sampling参数)在表上运行快速样本查询，以计算该表中行数的估计值.

If the statistics haven't been gathered (yet), the optimizer will (depending on the optimizer_dynamic_sampling parameter) run a quick sample query on the table in order to calculate an estimate for the number of rows in that table.

(更准确地说，扫描表的成本不是根据行数来计算的，而是根据表中的块数来计算的(您可以在ALL_TABLES的BLOCKS列中看到).取这个数字，然后将其除以与多块读取计数相关的因子，以计算该计划那部分的成本.)

(To be more accurate, the cost of scanning a table is calculated not from the number of rows, but the number of blocks in the table (which you can see in the BLOCKS column in ALL_TABLES). It takes this number and divides it by a factor related to the multi-block read count to calculate the cost of that part of the plan.)

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