When do we Analyze the tables? How do we do it? - ETL

When do we Analyze the tables? How do we do it?

- The statement ANALYZE allows to validate and computes statistics for an index,table or cluster

- Cost-based optimizer uses these statistics while calculating the most efficient plan for data retrieval.

- ANALYZE statement supports object validation structures and managing space in the system

- The operations include COMPUTER,ESTIMATE and DELETE.

- An example for Oracle 7

select OWNER,
sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
count(TABLE_NAME) total
from dba_tables
where OWNER not in ('SYS', 'SYSTEM')
group by OWNER