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