Hi

Here we go,

When you create a table you can see Oracle will NOT gather ANY stats unitl you explicitely tell Oracle to do:

SQL> CREATE TABLE DEMO(K NUMBER);

Table created.

SQL> SELECT
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE
7 FROM
8 USER_TABLES WHERE TABLE_NAME = 'DEMO'
9 /

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------- ---------- ---------- ------------ ----------
DEMO

As you can see all columns except "table_name" contain NULL values.
Now I tell Oracle to gather tablestats for the table, this can be done with DBMS_STATS but also manually with the ANALYZE TABLE clause:

[SYSTEM@HA3D.WORLD:BENDEV06] BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS( 'SYSTEM', 'DEMO');
3 END;
4 /

PL/SQL procedure successfully completed.

I can also do this with the following clause:

SQL> ANALYZE TABLE SYSTEM.DEMO COMPUTE STATISTICS;

Table analyzed.

Now we can see that user_tables contains more values:

SQL> SELECT
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE
7 FROM
8 USER_TABLES WHERE TABLE_NAME = 'DEMO'
9 /

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------- ---------- ---------- ------------ ----------
DEMO 0 0 1 0

Note I only took a few columns of USER_TALBLES, USER_TABLE contains a lot more columns:

SQL> DESC USER_TABLES
Name
-----------------------------------------------
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING
BACKED_UP
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
DEGREE
INSTANCES
CACHE
TABLE_LOCK
SAMPLE_SIZE
LAST_ANALYZED
PARTITIONED
IOT_TYPE
TEMPORARY
SECONDARY
NESTED
BUFFER_POOL
ROW_MOVEMENT
GLOBAL_STATS
USER_STATS
DURATION
SKIP_CORRUPT
MONITORING
CLUSTER_OWNER



Good luck and pay me a visit http://ora-0000.com/

Remi Vissser