Statistics

Contrary to what some people may have heard, a balanced search tree is, as the name suggests, always — read that again, please — always balanced. It is a myth that you have to rebuild the index whenever the performance of your queries is below par. There are extremely rare cases when Oracle recommends that you rebuild the indexes but in almost all cases you do not have to rebuild your indexes. [1]

Oracle nowadays automatically collects statistics, so once you create an index, Oracle takes care of the rest. You can see the schedule and some basic information about the statistics collection with the following statement:

1 SELECT
2   *
3 FROM
4   dba_autotask_client
5 WHERE
6   client_name = 'auto optimizer stats collection'
7 ;

For most databases the automatic statistics collection is sufficient. If, however, your database has tables that are being deleted and truncated between collection runs, then it can make sense to go gather optimizer statistics manually.

When you create an index, Oracle automatically gathers optimizer statistics because it needs to do a full scan anyway. As of Oracle Database 12c, the same piggybacking is done for the statistics collection of create-table-as-select (CTAS) and insert-as-select (IAS) operations, which is quite nifty; histograms require additional data scans, so these are not automatically gathered. The execution plans of CTAS and IAS statements show whether statistics are being collected at runtime: OPTIMIZER STATISTICS GATHERING, right below the LOAD AS SELECT operation.

If you change the definition of an index, you may want to update the statistics. Please coordinate with the DBA to avoid unwanted side effects, such as degrading the performance of all but your own queries because invalidation of execution plans; gathering statistics does not lock the table, it’s like running multiple queries against it. [2]

Notes