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 <[EMAIL PROTECTED]>
quest.com> cc:
Sent by: Subject: RE: analyze partitioned
indexes
[EMAIL PROTECTED]
10/24/01 03:15 PM
Please respond to
ORACLE-L
>-----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 '\'
;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
