Oracle object statistics

I. Overview

Object statistics describe how data is stored in the database. For example, how many rows of data exist in a table and the maximum value of a column. This information helps the query optimizer find the correct and efficient execution plan. For example, in a specific scenario, which traffic mode is the fastest? Cars, trains, or planes? If you don't know where I am and where I am, you won't get a reasonable answer. Similarly, if no object statistics are available, the query optimizer cannot find a correct and efficient execution plan.

2. Available object statistics

There are three types of object statistics available: Table statistics, column statistics, and index statistics. The following example shows the types of statistics provided by these three types.

1. Prepare table data

SET SERVEROUTPUT ON

Drop table t;

Execute dbms_random.seed (0)

Create table t
AS
SELECT rownum AS id,
Round (dbms_randm.normal * 1000) AS val1,
100 + round (ln (rownum/3.25 + 2) AS val2,
100 + round (ln (rownum/3.25 + 2) AS val3,
Dbms_random.string ('P', 250) AS pad
FROM dual
Connect by level <= 1000
Order by dbms_random.value;

UPDATE t SET val1 = null where val1 <0;

Alter table t add constraint t_pk primary key (id );

Create index t_valinii ON t (val1 );
Create index t_val2_ I ON t (val2 );

BEGIN
Dbms_stats.gather_table_stats (ownname => user,
Tabname => 'T ',
Estimate_percent = & gt; 100,
Method_opt => 'for all columns size skewonly ',
Cascade => TRUE );
END;
/

The stored procedure is used to collect the statistics of the current user table T. The meaning of each parameter is not described.

1 2 3 4 Next Page