| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Analyze - For All option
> -----Original Message----- > quriyat > > Can anyone enlighten me on the diff between > ANALYZE TABLE COMPUTE STATISTICS > --AND-- > analyze table my_emp compute statistics for table for all indexes > for all indexed columns;
Time to dust off the old "check analyze options" SQL!
These test were done in Oracle 9.2, and show that
analyze table compute statistics
is the same as
analyze table compute statistics for table for all indexes for all columns size 1
and
analyze table my_emp compute statistics for table for all indexes for all indexed columns ;
uses the default size (75) for the histograms for the indexed columns.
If your question is "what is the use of statistics?" then you should read
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
Part Number A96533-02
Chapter 3
Gathering Optimizer Statistics
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm
Finally, to gather statistics, Oracle recommends that you use the DBMS_STATS procedure instead of the ANALYZE command.
SQL> @analyze_options
SQL> create table X
2 as select object_id, owner, object_name, object_type, last_ddl_time
3 from all_objects ;
SQL> create unique index XI1 on X (object_id) ; SQL> create index XI2 on X (owner, object_name) ; SQL> -- +++++++++++++++++++++++++++++++++++ SQL> -- default options SQL> @@show_analyze SQL> set echo off OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> analyze table X compute statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:15 IDX:XI1 YES 2003/10/16 17:42:15 IDX:XI2 YES 2003/10/16 17:42:15 COL:OBJECT_ID YES 2003/10/16 17:42:15 2 COL:OWNER YES 2003/10/16 17:42:15 2 COL:OBJECT_NAME YES 2003/10/16 17:42:15 2 COL:OBJECT_TYPE YES 2003/10/16 17:42:15 2 COL:LAST_DDL_TIME YES 2003/10/16 17:42:15 2 SQL> analyze table X delete statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> -- +++++++++++++++++++++++++++++++++++SQL> -- table, index, all columns size 1 SQL> analyze table X compute statistics
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:16 IDX:XI1 YES 2003/10/16 17:42:16 IDX:XI2 YES 2003/10/16 17:42:16 COL:OBJECT_ID YES 2003/10/16 17:42:16 2 COL:OWNER YES 2003/10/16 17:42:16 2 COL:OBJECT_NAME YES 2003/10/16 17:42:16 2 COL:OBJECT_TYPE YES 2003/10/16 17:42:16 2 COL:LAST_DDL_TIME YES 2003/10/16 17:42:16 2 SQL> analyze table X delete statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> -- +++++++++++++++++++++++++++++++++++ SQL> -- table only SQL> analyze table X compute statistics for table ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:16 IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> analyze table X delete statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> -- +++++++++++++++++++++++++++++++++++SQL> -- table, index, indexed columns
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:17 IDX:XI1 YES 2003/10/16 17:42:17 IDX:XI2 YES 2003/10/16 17:42:17 COL:OBJECT_ID YES 2003/10/16 17:42:17 76 COL:OWNER YES 2003/10/16 17:42:17 6 COL:OBJECT_NAME YES 2003/10/16 17:42:17 76 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> analyze table X delete statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> -- +++++++++++++++++++++++++++++++++++SQL> -- table, index, indexed columns size 75 (default) SQL> analyze table X
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:17 IDX:XI1 YES 2003/10/16 17:42:17 IDX:XI2 YES 2003/10/16 17:42:17 COL:OBJECT_ID YES 2003/10/16 17:42:17 76 COL:OWNER YES 2003/10/16 17:42:17 6 COL:OBJECT_NAME YES 2003/10/16 17:42:17 76 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> analyze table X delete statistics ;SQL> @@show_analyze
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X NO IDX:XI1 NO IDX:XI2 NO COL:OBJECT_ID NO 0 COL:OWNER NO 0 COL:OBJECT_NAME NO 0 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0 SQL> -- +++++++++++++++++++++++++++++++++++SQL> -- table, index, indexed columns size 80 SQL> analyze table X
OBJECT ANALYZED LAST_ANALYZED HISTOGRAMS ---------------------------------- -------- -------------------- ---------- TBL:X YES 2003/10/16 17:42:18 IDX:XI1 YES 2003/10/16 17:42:18 IDX:XI2 YES 2003/10/16 17:42:18 COL:OBJECT_ID YES 2003/10/16 17:42:18 80 COL:OWNER YES 2003/10/16 17:42:18 6 COL:OBJECT_NAME YES 2003/10/16 17:42:18 80 COL:OBJECT_TYPE NO 0 COL:LAST_DDL_TIME NO 0SQL> set echo off
### ANALYZE_OPTIONS.SQL ###
set verify off
set feedback off
drop table X ;
define table_name = "X"
set echo on
create table X
as select object_id, owner, object_name, object_type, last_ddl_time
from all_objects ;
create unique index XI1 on X (object_id) ;
create index XI2 on X (owner, object_name) ;
-- +++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++
for table for all indexes for all columns size 1;
@@show_analyze
analyze table X delete statistics ;
@@show_analyze
-- +++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++
### SHOW_ANALYZE.SQL ###
set echo off
column sort_id noprint
column analyzed format a8
select
'1' as sort_id,
'TBL:' || table_name as object,
case when last_analyzed is null then 'NO' else 'YES' end as analyzed,
last_analyzed,
null as histograms
from user_tables
where table_name = '&&table_name'
union
select
'2' || index_name as sort_id,
'IDX:' || index_name as object,
case when last_analyzed is null then 'NO' else 'YES' end as analyzed,
last_analyzed,
null as histograms
from user_indexes
where table_owner = user and table_name = '&&table_name'
union
select
'3' || to_char (a.column_id) as sort_id,
'COL:' || a.column_name as object,
case when a.last_analyzed is null then 'NO' else 'YES' end as analyzed,
a.last_analyzed,
count (b.endpoint_number) as histograms
from user_tab_columns a, user_tab_histograms b
where
a.table_name = '&&table_name'
and a.table_name = b.table_name (+)
and a.column_name = b.column_name (+)
group by a.column_id, a.column_name, a.last_analyzed
order by 1 ;
clear columns
set echo on
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Oct 16 2003 - 20:49:24 CDT
 |
 |