electromaio.blogg.se

You query sales2 for the number of rows for product 42, and then gather table statistics using the GATHER AUTO option: SQL> SELECT COUNT(*) FROM sales2 WHERE prod_id = 42 Because sales2 has not yet been queried, the database has not yet created histograms: SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM You query the data dictionary to determine whether histograms exist for the sales2 columns. SQL> CREATE INDEX sh_12c_idx2 ON sales2(cust_id,time_id)

SQL> CREATE INDEX sh_12c_idx1 ON sales2(prod_id) You also create indexes as follows: SQL> CREATE TABLE sales2 AS SELECT * FROM sh_ext You create new table sales2 and perform a bulk load using sh_ext as a source, which automatically creates statistics for sales2 (see " Online Statistics Gathering for Bulk Loads"). For the database to create the histograms automatically, you must run one or more queries to populate the column usage information in SYS.COL_USAGE$.Įxample 11-1 Automatic Histogram CreationĪssume that sh.sh_ext is an external table that contains the same rows as the sh.sales table. If you gather statistics for a table and do not query the table, then the database does not create histograms for columns in this table. For example, even if the data in a table does not change, queries and DBMS_STATS operations can cause the plans for queries that reference these tables to change. You run DBMS_STATS again, causing DBMS_STATS to query SYS.COL_USAGE$ to determine which columns require histograms based on the previous query workload.Ĭonsequences of the AUTO feature include the following:Īs queries change over time, DBMS_STATS may change which statistics it gathers.

The database notes the predicates in the preceding query and updates the data dictionary table SYS.COL_USAGE$. You run DBMS_STATS for a table with the METHOD_OPT parameter set to the default SIZE AUTO. If DBMS_STATS gathers statistics for a table, and if queries have referenced the columns in this table, then Oracle Database creates histograms automatically as needed according to the previous query workload.