[펌] oralce analyze 관련 구문

출처 : http://www.psoug.org/reference/analyze.html

다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.

Note: Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. Those ANALYZE capabilities not recommended by Oracle are not documented here.
Create Table To Hold Validation Output	utlvalid.sql
	SQL> @?/rdbms/admin/utlvalid.sql desc invalid_rows
Create Table To Hold Chained Row Output	utlvalid.sql
	SQL> @?/rdbms/admin/utlchn1.sql desc chained_rows
CLUSTER
Create Demo Cluster	CREATE CLUSTER uw_cluster ( table_name VARCHAR2(30)) SIZE 512; CREATE INDEX ix_tabnames ON CLUSTER uw_cluster; CREATE TABLE uwtables CLUSTER uw_cluster (table_name) AS SELECT table_name, tablespace_name FROM all_tables; CREATE TABLE uwindexes CLUSTER uw_cluster (table_name) AS SELECT table_name, index_name FROM all_indexes; SELECT COUNT(*) FROM uwtables; SELECT COUNT(*) FROM uwindexes;
List Chained Rows	ANALYZE CLUSTER <cluster_name> LIST CHAINED ROWS INTO <table_name>;
	ANALYZE CLUSTER uw_cluster LIST CHAINED ROWS INTO chained_rows; SELECT * FROM chained_rows;
Validate Structure	ANALYZE CLUSTER <cluster_name> VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE>
	ANALYZE CLUSTER uw_cluster VALIDATE STRUCTURE CASCADE;
INDEX
Create Demo Table & Index	CREATE TABLE test PCTFREE 0 AS SELECT object_name, object_type FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; CREATE INDEX ix_test ON test (object_name, object_type) PCTFREE 0;
Validate Structure	ANALYZE INDEX <index_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE>
	desc index_stats set linesize 121 SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; ANALYZE INDEX ix_test VALIDATE STRUCTURE; SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_stats; DROP INDEX ix_test; CREATE INDEX ix_test ON test (object_name, object_type) PCTFREE 0 COMPRESS 1; ANALYZE INDEX ix_test VALIDATE STRUCTURE; SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space FROM index_stats WHERE name = 'IX_TEST'; SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_stats;
TABLE
List Chained Rows Note: While pm.online_media has chained rows in previous versions of Oracle it does not in the 11gR2 beta	ANALYZE TABLE <table_name> LIST CHAINED ROWS INTO <table_name>;
	conn / as sysdba SELECT owner, table_name FROM dba_tables WHERE chain_cnt > 0; conn pm/pm SQL> @?/rdbms/admin/utlchn1.sql desc chained_rows; ANALYZE TABLE online_media LIST CHAINED ROWS INTO chained_rows; set linesize 121 col owner_name format a10 col table_name format a15 col cluster_name format a7 col partition_name format a9 col subpartition_name format a12 col head_rowid format a20 SELECT * FROM chained_rows;
Compute Statistics Deprecated: Use DBMS_STATS	ANALYZE TABLE <table_name> <COMPUTE | DELETE | ESTIMATE> STATISTICS
	conn uwclass/uwclass SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test COMPUTE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test DELETE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST'; ANALYZE TABLE test ESTIMATE STATISTICS; SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM user_tables WHERE table_name ='TEST';
Validate Structure	ANALYZE TABLE <table_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE [INTO <table_name>] <OFFLINE | ONLINE>
	conn uwclass/uwclass ANALYZE TABLE test VALIDATE STRUCTURE CASCADE ONLINE; SELECT * FROM invalid_rows;