database - Oracle - Do you need to calculate statistics after creating index or adding columns? -

We use an Oracle 10.2.0.5 database in production.

Optimizer "Cost-based"

Do we need to calculate the data:

• Creating a new index < Li> Adding a column
• Create a new table

  ?

  Thanks

  There is no small answer, it is completely dependent on your data. And how you use it. Here are some things to consider:

  As told by @NullUserException, statistics are automatically collected, usually it is usually quite good each night; In most (OLTP) environments, if you have added new objects, then there will not be too many data before the data is automatically collected. Plans will not be bad, and if things are new they probably will not be used far away.

  • Creating a new index - Number
  • Adding a column - maybe. If used to join the column and predicts that you want statistics on it. If this is just used to store and display the data then it will not really affect any plan, but if the new column takes up a lot of space, then this is the average line length, number of blocks, row chaining etc. Can change a lot, and the adapter should be aware of it.
  • Create a new table - maybe. Oracle is able to compensate through the missing data, although this is often not quite good. Especially if there is too much data in the new table; Bad statistics almost always assess cardinality, which can lead to the nest loop when you want to have a hash. In addition, even if the table data has not changed, you may need to collect the data to enable the histogram. is. By default, Oracle creates histograms for skewed data, but those histograms will not be able to enable if those columns have not been used as a WordTech. (This is also applied by adding a new column too) If you leave a table and make it again, even with the same name, Oracle will not use any data in that column, and it will not be known That you need a histogram on some columns.

    It is more difficult to collect optimizer data because most people realize it. At my current job, most of our performance problems are ultimately due to poor statistics if you are trying to come up with a plan for your system, then you should read it.

    ---

    Update:

    There is no need to collect empty data; Dynamic Sampling Data will work with data reading only (without a large number of inquiries and parsing without figures based on a quick test). If you disable dynamic sampling then there may be some strange cases where The default values ​​of Oracle make wrong plans, and you better figures an empty table.

    I think the Oracle gathers statistics for self indexed at the time of creation because it does not cost too much. When you make an index, you have to read all the blocks in the table, so Oracle can calculate the levels, blocks, keys etc. at the same time.

    Table figures can be more complex, and many passes of data may be required. Creating an index is arbitrarily relatively simple than SQL, which can be used as part of a selection-table-selection. It is not possible or efficient, those arbitrary SQL statements can take and They can convert them into a query, which also gives the necessary information to collect the data.

    Of course, it will not spend anything extra to collect data for the empty table but this does not get anything to you, and it will only be deceptive to anyone who sees USER_TABLES. Las-nalised - The table is being analyzed, but not with any meaningful data.