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