ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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 test
PCTFREE 0
AS SELECT object_name, object_type
FROM all_objects;
Table created.
CREATE INDEX ix_test
ON test (object_name, object_type)
PCTFREE 0;
Index created.
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
ANALYZE TABLE <table_name> LIST CHAINED ROWS
INTO <table_name>;
conn sys@pdbdev as sysdba
SELECT owner, table_name
FROM dba_tables
WHERE chain_cnt > 0;
conn pm/pm@pdbdev
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