RE: analyze partitioned indexes

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 24 Oct 2001 11:59:58 -0700
Message-ID: <F001.003B389E.20011024121529@fatcity.com>

>-----Original Message-----
>From: Daiminger, Helmut [mailto:Helmut.Daiminger_at_KirchGruppe.de]
>
>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 '\' ;

Received on Wed Oct 24 2001 - 13:59:58 CDT