oracle compute statistics,Analyze table compute statistics

Hi,

in order to perform the query efficiently, you need to know how many rows would be returned along every step of the query (at least approximately). And for that, you need to know how many rows you have in your tables, what are min/max values, etc. The optimizer is using this information to pick the right method of accessing the data, and if this information is not up-to-date, then if can make very expensive mistakes, because some access methods are efficient when retrieving small amounts of data, and others are efficient for fetching large bulk of data.

You can use the following analogy. Imagine that you are running a laundry business, and you need to pick up laundry from different locations. You own several vehicles, from heavy trucks to bicycles. And in order to minimize your costs, you have to always choose the right vehicle to pick up your order. If you send a big truck to pick up a few pounds of laundry, you would waste a lot of money on gas and other transportation expenses (and plus, you won't be able to use the trunk somewhere else, where it may be more needed). If you send a bicyclist, and he finds that he has to pick up tons of laundry, you're in trouble, too, because he'll have to do many round trips to pick up the load.

It's the same story with the database. And since your data changes every day, sometimes you have to update stats every day, too. Imagine what would happen if you are about to select rows from a table where 99% of rows were deleted the day before, but the optimizer doesn't know about it since the stat haven't been updated. Etc.

Best regards,

Nikolay