views:

68

answers:

2

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

For example:

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?

+3  A: 

The cost optimizer uses segment (table and index) statistics as well as system (cpu + i/o performance) statistics for the estimates. Although it depends on how your database is configured, from 10g onwards the segment statistics are usually computed once per day by a process that is calling the DBMS_STATS package.

dpbradley
+2  A: 

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.

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.

(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.)

Jeffrey Kemp
+1 for the mention of dynamic sampling estimation
dpbradley