| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: losing global statistics after exchange partition (8.1.7.4)
Hi Pawel
"prom" <promanek_at_hektor.umcs.lublin.pl> wrote in message
news:cgv9r5$4er$1_at_opal.futuro.pl...
> Hello all!
> I'm having problem with rollup. I'm using following scheme for every
> summary table T [ T is partitioned table ]:
> 1)export stats of T
> 2)fill temporary table TMP (not partitioned)
> 3)exchange partition (T <-> TMP)
> 4)import stats.
> But after all, global statistics of every table T are lost (database
> 8.1.7.4).
In my opinion the problem is that you have no global statistics at table level (global according to the meaning of USER_STATS.GLOBAL_STATS). This is because the statistics are gathered by ANALYZE and not DBMS_STATS. Notice that in 8i when table, indexes and histograms are gathered simultaneously, ANALYZE is called by DBMS_STATS, i.e. you can have no global stats also if you use, or you think to use, DBMS_STATS. Since in 9i this "restriction" has been removed, you are not able to reproduce the same behaviour in 9i.
In my opinion you also don't need to exp/imp the statistics. Here an example...
>>>>>>>>>>>>>>>> create tables
SQL> CREATE TABLE t (n NUMBER, d DATE)
2 PARTITION BY RANGE (d) (
3 PARTITION t_2002 VALUES LESS THAN
(to_date('01.01.2003','dd.mm.yyyy'))
4 ,PARTITION t_2003 VALUES LESS THAN
(to_date('01.01.2004','dd.mm.yyyy'))
5 ,PARTITION t_2004 VALUES LESS THAN
(to_date('01.01.2005','dd.mm.yyyy'))
6 );
SQL> INSERT INTO t SELECT object_id n, created d FROM all_objects;
SQL> CREATE TABLE t_part AS SELECT rownum n,
to_date('01.02.2005','dd.mm.yyyy') d FROM all_objects;
>>>>>>>>>>>>>>>> gather global statistics
SQL> exec dbms_stats.gather_table_stats(user, 't', cascade=>true,
method_opt=>'for all columns size 1')
SQL> exec dbms_stats.gather_table_stats(user, 't_part', method_opt=>'for all
columns size 1')
>>>>>>>>>>>>>>>> check if the statistics are really global
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name = 'T';
NUM_ROWS GLO
---------- ---
22046 YES SQL> SELECT partition_name, num_rows, global_stats FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position;
PARTITION_NAME NUM_ROWS GLO ------------------------------ ---------- --- T_2002 0 YES T_2003 21970 YES T_2004 76 YES
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name =
'T_PART';
NUM_ROWS GLO
---------- ---
22047 YES
>>>>>>>>>>>>>>>> add a new partition and exchange it with thenon-partitioned table
SQL> ALTER TABLE t ADD PARTITION t_2005 VALUES LESS THAN
(to_date('01.01.2006','dd.mm.yyyy'));
SQL> ALTER TABLE t EXCHANGE PARTITION t_2005 WITH TABLE t_part;
>>>>>>>>>>>>>>>> check that the statistics are ok (of course wrong, but theyare available)
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name = 'T';
NUM_ROWS GLO
---------- ---
22046 YES SQL> SELECT partition_name, num_rows, global_stats FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position;
PARTITION_NAME NUM_ROWS GLO ------------------------------ ---------- --- T_2002 0 YES T_2003 21970 YES T_2004 76 YES T_2005 22047 NO ****************************************EXAMPLE 2: non-global statistics (same statements, only "size 1" is replaced by "size 100" --> notice that GLOBAL_STATS=NO, therefore stats are missing after ADD/EXCHANGE)
SQL> CREATE TABLE t (n NUMBER, d DATE)
2 PARTITION BY RANGE (d) (
3 PARTITION t_2002 VALUES LESS THAN
(to_date('01.01.2003','dd.mm.yyyy'))
4 ,PARTITION t_2003 VALUES LESS THAN
(to_date('01.01.2004','dd.mm.yyyy'))
5 ,PARTITION t_2004 VALUES LESS THAN
(to_date('01.01.2005','dd.mm.yyyy'))
6 );
SQL> INSERT INTO t SELECT object_id n, created d FROM all_objects;
SQL> CREATE TABLE t_part AS SELECT rownum n,
to_date('01.02.2005','dd.mm.yyyy') d FROM all_objects;
SQL> exec dbms_stats.gather_table_stats(user, 't', cascade=>true,
method_opt=>'for all columns size 100')
SQL> exec dbms_stats.gather_table_stats(user, 't_part', method_opt=>'for all
columns size 100')
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name = 'T';
NUM_ROWS GLO
---------- ---
22046 NO
SQL> SELECT partition_name, num_rows, global_stats FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position;
PARTITION_NAME NUM_ROWS GLO ------------------------------ ---------- --- T_2002 0 NO T_2003 21970 NO T_2004 76 NO
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name =
'T_PART';
NUM_ROWS GLO
---------- ---
22047 NO
SQL> ALTER TABLE t ADD PARTITION t_2005 VALUES LESS THAN
(to_date('01.01.2006','dd.mm.yyyy'));
SQL> ALTER TABLE t EXCHANGE PARTITION t_2005 WITH TABLE t_part;
SQL> SELECT num_rows, global_stats FROM user_tables WHERE table_name = 'T';
NUM_ROWS GLO
---------- ---
NO
SQL> SELECT partition_name, num_rows, global_stats FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position;
PARTITION_NAME NUM_ROWS GLO ------------------------------ ---------- --- T_2002 0 NO T_2003 21970 NO T_2004 76 NO T_2005 22047 NO
> Procedure dbms_stats.import_table_stats has additional parameter -
no_invalidate
> on 9i. Is there any way to do exchange partition on 8i without marking
> global stats as invalidated ??
NO_INVALIDATE is used to invalidate (or not) the cursors in the SQL area.
It is not the EXCHANGE statement that remove the stats, but the ADD statement. Therefore I suggest you to create some partitions in advance...
Chris
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |