Re: ANALYZE %

jglickma_at_lynx.dac.neu.edu (jon glickman) writes:

>Does anybody understand why I am seeing no change in the ALL_TABLES,
>DBA_TABLES... for the analyze sample 70 percent option of
>when I query these tables for small databases.

-------(Parts of script cut by Ari Kaplan)------

>select 'analyze table '||owner||'.'||table_name||' estimate statistics
>sample 80 percent;'
>from sys.dba_tables
>where table_name like upper('&vname') and owner like upper ('&vowner');
>Any suggestions?
> jglickma_at_lynx.neu.edu

Jon,

The statistics in the ALL_TABLES/DBA_TABLES (and other) views will NOT change if the data in the table does not change. If you do not UPDATE, DELETE, INSERT, etc., then no matter what percent you sample, the evaluated statistics will not change.

However, if you do change the contents of the table (say by deleting 50% of all records), then many statistics will change (NUM_ROWS for starters).

Hope this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->