>-----Original Message-----
>From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
>
>I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes.
>...
- with the command "analyze table compute statistics for table for all indexes" all table and index partitions are analyzed
- with the command "analyze index compute statistics" all index partitions are analyzed
- with the command "analyze index partition () compute statistics" only the single index partition is analyzed
Proof:
I created a partitioned table (my_table) with a global index, a locally partitioned index, and a globally partitioned index.
I also created a view (my_view) that shows the analyze date and num_rows for the table and its indexes and partitions.
(see end of e-mail for table and view creation)
SQL> analyze table my_table delete statistics ;
Table analys�e.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL> analyze table my_table compute statistics for table for all indexes ;
Table analys�e.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1 2001/10/24 12:08:28 0
MY_INDEX2 2001/10/24 12:08:28 0
MY_INDEX2 (MY_INDEX2_P1) 2001/10/24 12:08:28 0
MY_INDEX2 (MY_INDEX2_P2) 2001/10/24 12:08:28 0
MY_INDEX3 2001/10/24 12:08:28 0
MY_INDEX3 (MY_INDEX3_P1) 2001/10/24 12:08:28 0
MY_INDEX3 (MY_INDEX3_P2) 2001/10/24 12:08:28 0
MY_TABLE 2001/10/24 12:08:28 0
MY_TABLE (MY_TABLE_P1) 2001/10/24 12:08:28 0
MY_TABLE (MY_TABLE_P2) 2001/10/24 12:08:28 0
SQL> analyze table my_table delete statistics ;
Table analys�e.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL> analyze index my_index3 compute statistics ;
Index analys�.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3 2001/10/24 12:08:57 0
MY_INDEX3 (MY_INDEX3_P1) 2001/10/24 12:08:57 0
MY_INDEX3 (MY_INDEX3_P2) 2001/10/24 12:08:57 0
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL> analyze index my_index3 delete statistics ;
Index analys�.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2)
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
SQL> analyze index my_index2 partition (my_index2_p2) compute statistics ;
Index analys�.
SQL> select * from my_view ;
OBJECT_NAME ANALYZED NUM_ROWS
------------------------------ -------------------- ----------
MY_INDEX1
MY_INDEX2
MY_INDEX2 (MY_INDEX2_P1)
MY_INDEX2 (MY_INDEX2_P2) 2001/10/24 12:09:42 0
MY_INDEX3
MY_INDEX3 (MY_INDEX3_P1)
MY_INDEX3 (MY_INDEX3_P2)
MY_TABLE
MY_TABLE (MY_TABLE_P1)
MY_TABLE (MY_TABLE_P2)
10 ligne(s) s�lectionn�e(s).
-- partitioned table
create table my_table
(my_column1 char (1),
my_column2 date,
my_column3 varchar2 (4),
my_column4 raw (4)
)
partition by range (my_column1)
(partition my_table_p1 values less than ('M'),
partition my_table_p2 values less than (maxvalue)
) ;
-- index
create index my_index1 on my_table (my_column2) ;
-- partitioned index (local)
create bitmap index my_index2 on my_table (my_column3)
local (partition my_index2_p1, partition my_index2_p2) ;
-- partitioned index (global)
create index my_index3 on my_table (my_column4)
global partition by range (my_column4)
(partition my_index3_p1 values less than ('AB'),
partition my_index3_p2 values less than (maxvalue)
) ;
create view my_view as
select
table_name as object_name,
to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_tables
where
table_name = 'MY_TABLE'
union
select
table_name || ' (' || partition_name || ')' as object_name,
to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_tab_partitions
where
table_name = 'MY_TABLE'
union
select
index_name as object_name,
to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_indexes
where
index_name like 'MY\_INDEX%' escape '\'
union
select
index_name || ' (' || partition_name || ')' as object_name,
to_char (last_analyzed, 'SYYYY/MM/DD HH24:MI:SS') as analyzed,
num_rows as num_rows
from
user_ind_partitions
where
index_name like 'MY\_INDEX%' escape '\'
;
