CODEWITHSUNDEEP
oracle-databaseoracle11g
Victor
Victor

Reputation: 17087

Oracle Analyze Table command

Is the command Analyze table tbl compute statistics a DDL or DML? Intuitively, it seems to be neither. When i have this command in a .sql file do i need to do :

execute immediate 'Analyze table tbl compute statistics'

I have a smiliar question about the command: GRANT DELETE, INSERT, SELECT, UPDATE ON tbl to user

Upvotes: 4

Views: 18077

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

UPDATE Oracle says that both grant and analyze are Data Definition Language (DDL) statements. They apparently do not make a distinction between DDL and Data Control Language (DCL).

If executing from within PL/SQL, then either execute immediate or DBMS_SQL would be needed.

Also, "Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. " (10gR2) "For the collection of most statistics, use the DBMS_STATS package. ... Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer." (11g R2) Analyze table is deprecated for gathering optimizer statistics, though still usefull for other things. Use DBMS_STATS instead. (I linked to the online Oracle documentation for 10g R2. However I've been having trouble with Oracle's documenation site the last few days, with the 10g R2 documents disappearing and then reappearing.)

Upvotes: 6

Related Questions