RE: analyze partitioned indexes

From: <Cherie_Machler_at_gelco.com>
Date: Wed, 24 Oct 2001 12:50:24 -0700
Message-ID: <F001.003B394C.20011024125517@fatcity.com>

If you analyze all of the partitions in an index (one partition at a time) is the performance of the the end result the same as it would be if you just analyzed the entire index at one time (not partition by partition).

Thanks,

Cherie

                                                                                       
                              
                    Jacques Kilchoer                                                   
                              
                    <Jacques.Kilchoer@       To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>     
                    quest.com>               cc:                                       
                              
                    Sent by:                 Subject:     RE: analyze partitioned 
indexes                            
                    root_at_fatcity.com                                                   
                              
                                                                                       
                              
                                                                                       
                              
                    10/24/01 03:15 PM                                                  
                              
                    Please respond to                                                  
                              
                    ORACLE-L

>-----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 '\' ;

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Wed Oct 24 2001 - 14:50:24 CDT