Updating index on oracle table
One of the things they built in the database is this program called the optimizer.The optimizer's job is to take SQL statements and decide how to get the data that is being asked for in the SQL statement and how to get it in the quickest way possible.An execution plan is like a set of instructions that tells Oracle how to go and get the data. In it, the optimizer tells Oracle to first go get all the rows of the EMP department, and then sort those rows (Reading an execution plan is somewhat of an art, so trust us on this one, the full scan of the EMP table is first).Did you notice in the plan that there is a column called ROWS?This is the easiest way to generate statistics for a large number of objects.Here is an example of using the dbms_stats.gather_schema_stats procedure to gather statistics on the SCOTT schema of a database: This command will generate statistics on all tables in the SCOTT schema.If you create a new table, then it may not be practical or desirable to re-generate statistics on the entire schema if the schema is quite large and the database is very busy.
It's been deprecated in Oracle Database 10g (which means it's really not supported anymore). The dbms_stats.gather_schema_stats procedure allows you to gather statistics for all objects in a give schema.
Since we included the cascade command, the indexes will also have statistics generated on them.
This is important, you need statistics on indexes as well as on tables in Oracle!
The CBO uses object statistics to choose the best execution plan for all SQL statements.
The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.
Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO.