Updating index on oracle table
Is there any SQL hint that could improve performance? EDIT: This query will be run once and then never again.First of all is it a one-time query or is it a recurrent query ?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.Instead you will use the dbms_stats.gather_table_stats command to generate statistics for a single table, and optionally for related table indexes.Oracle requires statistics on both tables and any associated indexes (we will talk about indexes next), and most of the time you will generate both with just one command. Also, in Oracle database 10g and beyond you have the ability to gather system statistics and fixed view statistics.Let's look at each of these operations in a bit more detail next.These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics.
Later you will want to investigate some of the Oracle Database 10g statistics gathering options such as histograms, and granularity.This is the number of rows that the query will process. In this case, Oracle knew (or in most cases it's a good guess) we would process 100 rows because we generated statistics on the EMP table after we created the table.How did Oracle know that it was going to process 100 rows. The optimizer uses these statistics to generate execution plans.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!