Join Factorization transformation

The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries.

In many scenarios the branches in a UNION All query share a common processing, i.e, refer to the same tables. In the current Oracle execution strategy, each branch of a UNION ALL query is evaluated independently, which leads to repetitive processing, including data access and join. The join factorization transformation offers an opportunity to share the common computations across the UNION ALL branches

rajesh@ORA11G> create table t1 as select * from all_objects;

Table created.

rajesh@ORA11G> create table t2 as select * from t1;

Table created.

rajesh@ORA11G> create table t3 as select * from t1;

Table created.

rajesh@ORA11G> create table t4 as select * from t1;

Table created.

rajesh@ORA11G> begin

  2     dbms_stats.gather_table_stats(user,'T1');

  3     dbms_stats.gather_table_stats(user,'T2');

  4     dbms_stats.gather_table_stats(user,'T3');

  5     dbms_stats.gather_table_stats(user,'T4');

  6  end;

  7  /

PL/SQL procedure successfully completed.

rajesh@ORA11G>

So we have four tables loaded with some decent volume of data.

So when a query like this goes for execution, optimizer applies join transformation. And this is visible in explain plan

rajesh@ORA11G> set autotrace traceonly explain statistics

rajesh@ORA11G> select t1.owner, t2.object_name, t3.object_type

  2  from t1, t2, t3

  3  where t1.object_id = t2.object_id

  4  and t2.object_id = t3.object_id

  5  and t1.object_id > 0

  6  union all

  7  select t1.owner, t2.object_name, t4.object_type

  8  from t1, t2, t4

  9  where t1.object_id = t2.object_id

 10  and t2.object_id = t4.object_id

 11  and t1.object_id > 0 ;

169378 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3798759592

---------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                    |   169K|  8601K|  2756   (1)|

|*  1 |  HASH JOIN            |                    |   169K|  8601K|  2756   (1)|

|*  2 |   TABLE ACCESS FULL   | T1                 | 84689 |   909K|   338   (1)|

|   3 |   VIEW                | VW_JF_SET$37540893 |   169K|  6781K|  1900   (1)|

|   4 |    UNION-ALL          |                    |       |       |            |

|*  5 |     HASH JOIN         |                    | 84689 |  3638K|   950   (1)|

|*  6 |      TABLE ACCESS FULL| T3                 | 84689 |  1157K|   338   (1)|

|*  7 |      TABLE ACCESS FULL| T2                 | 84689 |  2481K|   338   (1)|

|*  8 |     HASH JOIN         |                    | 84689 |  3638K|   950   (1)|

|*  9 |      TABLE ACCESS FULL| T4                 | 84689 |  1157K|   338   (1)|

|* 10 |      TABLE ACCESS FULL| T2                 | 84689 |  2481K|   338   (1)|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="ITEM_1")

   2 - filter("T1"."OBJECT_ID">0)

   5 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

   6 - filter("T3"."OBJECT_ID">0)

   7 - filter("T2"."OBJECT_ID">0)

   8 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")

   9 - filter("T4"."OBJECT_ID">0)

  10 - filter("T2"."OBJECT_ID">0)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       7178  consistent gets

       2419  physical reads

          0  redo size

    5559340  bytes sent via SQL*Net to client

      12918  bytes received via SQL*Net from client

       1131  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     169378  rows processed

rajesh@ORA11G>

rajesh@ORA11G> set autotrace off 

So what happened is optimizer scanned table T1 only once, not twice as provided in union all queries. So given this query

select t1.owner, t2.object_name, t3.object_type

from t1, t2, t3

where t1.object_id = t2.object_id

and t2.object_id = t3.object_id

and t1.object_id > 0

union all

select t1.owner, t2.object_name, t4.object_type

from t1, t2, t4

where t1.object_id = t2.object_id

and t2.object_id = t4.object_id

and t1.object_id > 0 ;

Appliying join factorization, it’s transformed into this. So that table T1 is scanned once not twice.

select t1.owner, t.object_name, t.object_type

from t1, (

select t2.object_name, t3.object_type , t2.object_id

from t2, t3

where t2.object_id = t3.object_id

union all

select t2.object_name, t4.object_type , t2.object_id

from t2, t4

where t2.object_id = t4.object_id ) t

where t1.object_id = t.object_id

and t1.object_id > 0

Now comparing this explain plan with explain plan produced without join factorization

·         Table “T1” got scanned twice.

·         Overall logical IO got increases by 16%

·         Overall query cost increased from 2756 to 3187.

rajesh@ORA11G> alter session set "_optimizer_join_factorization"=false;

Session altered.

rajesh@ORA11G> set autotrace traceonly explain statistics

rajesh@ORA11G> select t1.owner, t2.object_name, t3.object_type

  2  from t1, t2, t3

  3  where t1.object_id = t2.object_id

  4  and t2.object_id = t3.object_id

  5  and t1.object_id > 0

  6  union all

  7  select t1.owner, t2.object_name, t4.object_type

  8  from t1, t2, t4

  9  where t1.object_id = t2.object_id

 10  and t2.object_id = t4.object_id

 11  and t1.object_id > 0 ;

169378 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4252849160

-------------------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |   169K|  9097K|       |  3187   (1)| 00:00:39 |

|   1 |  UNION-ALL           |      |       |       |       |            |          |

|*  2 |   HASH JOIN          |      | 84689 |  4548K|  2152K|  1594   (1)| 00:00:20 |

|*  3 |    TABLE ACCESS FULL | T3   | 84689 |  1157K|       |   338   (1)| 00:00:05 |

|*  4 |    HASH JOIN         |      | 84689 |  3390K|  1904K|   938   (1)| 00:00:12 |

|*  5 |     TABLE ACCESS FULL| T1   | 84689 |   909K|       |   338   (1)| 00:00:05 |

|*  6 |     TABLE ACCESS FULL| T2   | 84689 |  2481K|       |   338   (1)| 00:00:05 |

|*  7 |   HASH JOIN          |      | 84689 |  4548K|  2152K|  1594   (1)| 00:00:20 |

|*  8 |    TABLE ACCESS FULL | T4   | 84689 |  1157K|       |   338   (1)| 00:00:05 |

|*  9 |    HASH JOIN         |      | 84689 |  3390K|  1904K|   938   (1)| 00:00:12 |

|* 10 |     TABLE ACCESS FULL| T1   | 84689 |   909K|       |   338   (1)| 00:00:05 |

|* 11 |     TABLE ACCESS FULL| T2   | 84689 |  2481K|       |   338   (1)| 00:00:05 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

   3 - filter("T3"."OBJECT_ID">0)

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   5 - filter("T1"."OBJECT_ID">0)

   6 - filter("T2"."OBJECT_ID">0)

   7 - access("T2"."OBJECT_ID"="T4"."OBJECT_ID")

   8 - filter("T4"."OBJECT_ID">0)

   9 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

  10 - filter("T1"."OBJECT_ID">0)

  11 - filter("T2"."OBJECT_ID">0)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       8391  consistent gets

       2418  physical reads

          0  redo size

    5559340  bytes sent via SQL*Net to client

      12918  bytes received via SQL*Net from client

       1131  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     169378  rows processed

rajesh@ORA11G>

rajesh@ORA11G> set autotrace off

Join factorization is performed under cost-based transformation framework; this means that we cost the plans with and without join factorization and choose the cheapest plan.

In this case the cost of Join factorization query is 2756, but without join factorization cost is 3187, where join factorization seems to be the cheapest so the optimizer adopts to join factorization transformation

Incremental stats gathering changed up in 11.2.0.4 - Part II

 

This will be the prolongation of previous posting

Let’s start with a demo.

 

rajesh@ORA11G> create table t

  2  partition by list(x)

  3  (

  4    partition p1 values (1),

  5    partition p2 values (2)

  6  ) as

  7  select a.*, mod(rownum,2)+1 x ,

  8  (mod(rownum,2)+1)*5 as order_status

  9  from all_objects a;

 

Table created.

 

rajesh@ORA11G>

 

Next set the table level preferences for Incremental stats gathering.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','GRANULARITY','auto');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','TRUE');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> exec dbms_stats.set_table_prefs(user,'T','ESTIMATE_PERCENT','dbms_stats.auto_sample_size');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select dbms_stats.get_prefs('INCREMENTAL',user,'T') incremental,

  2         dbms_stats.get_prefs('GRANULARITY',user,'T') granularity,

  3         dbms_stats.get_prefs('PUBLISH',user,'T') publish,

  4         dbms_stats.get_prefs('ESTIMATE_PERCENT',user,'T') estimate_percent

  5  from dual ;

 

INCREMENTA GRANULARIT PUBLISH    ESTIMATE_PERCENT

---------- ---------- ---------- ---------------------------

TRUE       AUTO       TRUE       DBMS_STATS.AUTO_SAMPLE_SIZE

 

1 row selected.

 

rajesh@ORA11G>

rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2     from user_tab_partitions

  3     where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

----- ----- -----------------------

T     P1    02-SEP-2014 01:40:33 PM

T     P2    02-SEP-2014 01:40:32 PM

 

2 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- --------------- ------------ ----------------------- ---------------

T     CREATED                 1318 02-SEP-2014 01:40:33 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:33 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:33 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:33 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:33 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_ID              84677 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:33 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:33 PM NONE

T     ORDER_STATUS               2 02-SEP-2014 01:40:33 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:33 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:33 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:33 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:33 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:33 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:33 PM NONE

T     X                          2 02-SEP-2014 01:40:33 PM NONE

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by  partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- ----- --------------- ------------ ----------------------- ---------------

T     P1    CREATED                 1206 02-SEP-2014 01:40:32 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:32 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:32 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:32 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:32 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:32 PM NONE

T     P1    ORDER_STATUS               1 02-SEP-2014 01:40:32 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:32 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:32 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:32 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:32 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:32 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:32 PM NONE

T     P2    CREATED                 1207 02-SEP-2014 01:40:31 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:31 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:31 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:31 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:31 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:31 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:31 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:31 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:31 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:31 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:31 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:31 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:31 PM NONE

 

34 rows selected.

 

rajesh@ORA11G>

 

Now let’s add a new partition P3 and change a couple of records in partition P1.

 

rajesh@ORA11G> update t set order_status = 77

  2      where x = 1 and rownum = 1;

 

1 row updated.

 

rajesh@ORA11G>

rajesh@ORA11G> commit;

 

Commit complete.

 

rajesh@ORA11G>

rajesh@ORA11G> alter table t add partition p3 values(3);

 

Table altered.

 

rajesh@ORA11G>

rajesh@ORA11G> insert into t

  2  select a.*, 3 as id,

  3  (mod(rownum,3)+1)*5 as order_status

  4  from all_objects a

  5  where rownum <=1000;

 

1000 rows created.

 

rajesh@ORA11G>

 

When we do stats gathering, we expect only partition P1 and P3 to be re-gathered.

 

rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

----- ----- -----------------------

T     P1    02-SEP-2014 01:40:37 PM

T     P2    02-SEP-2014 01:40:36 PM

T     P3    02-SEP-2014 01:40:35 PM

 

3 rows selected.

 

rajesh@ORA11G>

 

But what happened was we re-gathered the stats on all the available partitions.

 

However, this regather is actually expected behavior---it is necessary to produce correct global table stats. The reason is that a partition being updated is not the only reason that stats may need to be regathered on it. If DBMS_STATS decides that it needs to consider a histogram on a column where it previously did not, then it needs to regather stats on the column in all partitions. That is what has happened here.

 

In our test case, METHOD_OPT is set to the default of FOR ALL COLUMNS SIZE AUTO, so the columns that are considered for histograms will depend on column usage. Create a completely new table, so DBMS_STATS starts by assuming no histograms are included. After you update the table "where x=1", DBMS_STATS decides it needs to consider a histogram on column X to support equijoins on it. This new histogram that needed to be considered forced the regather on all partitions

 

If you check *_part_col_statistics before and after your test case you will see that the histogram for X changes from NONE to FREQUENCY in all of the partitions.

 

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- --------------- ------------ ----------------------- ---------------

T     CREATED                 1318 02-SEP-2014 01:40:37 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:37 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:37 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:37 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:37 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_ID              84876 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:37 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:37 PM NONE

T     ORDER_STATUS               4 02-SEP-2014 01:40:37 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:37 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:37 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:37 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:37 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:37 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:37 PM NONE

T     X                          3 02-SEP-2014 01:40:37 PM FREQUENCY

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- ----- --------------- ------------ ----------------------- ---------------

T     P1    CREATED                 1206 02-SEP-2014 01:40:36 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:36 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:36 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:36 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:36 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:36 PM NONE

T     P1    ORDER_STATUS               2 02-SEP-2014 01:40:36 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:36 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:36 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:36 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:36 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:36 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:36 PM FREQUENCY

T     P2    CREATED                 1207 02-SEP-2014 01:40:35 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:35 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:35 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:35 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:35 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:35 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:35 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:35 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:35 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:35 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:35 PM FREQUENCY

T     P3    CREATED                   10 02-SEP-2014 01:40:34 PM NONE

T     P3    DATA_OBJECT_ID           907 02-SEP-2014 01:40:34 PM NONE

T     P3    EDITION_NAME               0 02-SEP-2014 01:40:34 PM NONE

T     P3    GENERATED                  2 02-SEP-2014 01:40:34 PM NONE

T     P3    LAST_DDL_TIME             30 02-SEP-2014 01:40:34 PM NONE

T     P3    NAMESPACE                  4 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_ID               1000 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_NAME              995 02-SEP-2014 01:40:34 PM NONE

T     P3    OBJECT_TYPE                6 02-SEP-2014 01:40:34 PM NONE

T     P3    ORDER_STATUS               3 02-SEP-2014 01:40:34 PM NONE

T     P3    OWNER                      4 02-SEP-2014 01:40:34 PM NONE

T     P3    SECONDARY                  1 02-SEP-2014 01:40:34 PM NONE

T     P3    STATUS                     1 02-SEP-2014 01:40:34 PM NONE

T     P3    SUBOBJECT_NAME             0 02-SEP-2014 01:40:34 PM NONE

T     P3    TEMPORARY                  2 02-SEP-2014 01:40:34 PM NONE

T     P3    TIMESTAMP                 11 02-SEP-2014 01:40:34 PM NONE

T     P3    X                          1 02-SEP-2014 01:40:34 PM FREQUENCY

 

51 rows selected.

 

rajesh@ORA11G>

 

If you drop and recreate partition P3, update partition P1 again, and regather stats, you will see that partition P2 is not gathered again.

 

rajesh@ORA11G> alter table t truncate partition p3;

 

Table truncated.

 

rajesh@ORA11G> update t set order_status = 54

  2      where x = 1 and rownum = 1;

 

1 row updated.

 

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,last_analyzed

  2  from user_tab_partitions

  3  where table_name ='T' ;

 

TABLE PARTI LAST_ANALYZED

----- ----- -----------------------

T     P1    02-SEP-2014 01:40:40 PM

T     P2    02-SEP-2014 01:40:36 PM

T     P3    02-SEP-2014 01:40:38 PM

 

3 rows selected.

 

rajesh@ORA11G>

 

This confirms P1 and P3 got re-gathered without disturbing Partition P2.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_TAB_col_statistics

  4  where table_name ='T'

  5  order by column_name;

 

TABLE COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- --------------- ------------ ----------------------- ---------------

T     CREATED                 1318 02-SEP-2014 01:40:40 PM NONE

T     DATA_OBJECT_ID          6978 02-SEP-2014 01:40:40 PM NONE

T     EDITION_NAME               1 02-SEP-2014 01:40:40 PM NONE

T     GENERATED                  2 02-SEP-2014 01:40:40 PM NONE

T     LAST_DDL_TIME           1383 02-SEP-2014 01:40:40 PM NONE

T     NAMESPACE                 17 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_ID              84677 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_NAME            49892 02-SEP-2014 01:40:40 PM NONE

T     OBJECT_TYPE               39 02-SEP-2014 01:40:40 PM NONE

T     ORDER_STATUS               3 02-SEP-2014 01:40:40 PM NONE

T     OWNER                     31 02-SEP-2014 01:40:40 PM NONE

T     SECONDARY                  2 02-SEP-2014 01:40:40 PM NONE

T     STATUS                     2 02-SEP-2014 01:40:40 PM NONE

T     SUBOBJECT_NAME           183 02-SEP-2014 01:40:40 PM NONE

T     TEMPORARY                  2 02-SEP-2014 01:40:40 PM NONE

T     TIMESTAMP               1473 02-SEP-2014 01:40:40 PM NONE

T     X                          2 02-SEP-2014 01:40:40 PM FREQUENCY

 

17 rows selected.

 

rajesh@ORA11G>

rajesh@ORA11G> select table_name,partition_name,column_name,num_distinct,

  2    last_analyzed,histogram

  3  from user_part_col_statistics

  4  where table_name ='T'

  5  order by partition_name,column_name;

 

TABLE PARTI COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED           HISTOGRAM

----- ----- --------------- ------------ ----------------------- ---------------

T     P1    CREATED                 1206 02-SEP-2014 01:40:38 PM NONE

T     P1    DATA_OBJECT_ID          3517 02-SEP-2014 01:40:38 PM NONE

T     P1    EDITION_NAME               1 02-SEP-2014 01:40:38 PM NONE

T     P1    GENERATED                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    LAST_DDL_TIME           1221 02-SEP-2014 01:40:38 PM NONE

T     P1    NAMESPACE                 16 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_ID              42338 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_NAME            40264 02-SEP-2014 01:40:38 PM NONE

T     P1    OBJECT_TYPE               38 02-SEP-2014 01:40:38 PM NONE

T     P1    ORDER_STATUS               2 02-SEP-2014 01:40:38 PM NONE

T     P1    OWNER                     31 02-SEP-2014 01:40:38 PM NONE

T     P1    SECONDARY                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    STATUS                     2 02-SEP-2014 01:40:38 PM NONE

T     P1    SUBOBJECT_NAME           139 02-SEP-2014 01:40:38 PM NONE

T     P1    TEMPORARY                  2 02-SEP-2014 01:40:38 PM NONE

T     P1    TIMESTAMP               1292 02-SEP-2014 01:40:38 PM NONE

T     P1    X                          1 02-SEP-2014 01:40:38 PM FREQUENCY

T     P2    CREATED                 1207 02-SEP-2014 01:40:35 PM NONE

T     P2    DATA_OBJECT_ID          3494 02-SEP-2014 01:40:35 PM NONE

T     P2    EDITION_NAME               1 02-SEP-2014 01:40:35 PM NONE

T     P2    GENERATED                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    LAST_DDL_TIME           1223 02-SEP-2014 01:40:35 PM NONE

T     P2    NAMESPACE                 16 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_ID              42339 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_NAME            40324 02-SEP-2014 01:40:35 PM NONE

T     P2    OBJECT_TYPE               37 02-SEP-2014 01:40:35 PM NONE

T     P2    ORDER_STATUS               1 02-SEP-2014 01:40:35 PM NONE

T     P2    OWNER                     31 02-SEP-2014 01:40:35 PM NONE

T     P2    SECONDARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    STATUS                     2 02-SEP-2014 01:40:35 PM NONE

T     P2    SUBOBJECT_NAME           141 02-SEP-2014 01:40:35 PM NONE

T     P2    TEMPORARY                  2 02-SEP-2014 01:40:35 PM NONE

T     P2    TIMESTAMP               1282 02-SEP-2014 01:40:35 PM NONE

T     P2    X                          1 02-SEP-2014 01:40:35 PM FREQUENCY

T     P3    CREATED                    0 02-SEP-2014 01:40:38 PM NONE

T     P3    DATA_OBJECT_ID             0 02-SEP-2014 01:40:38 PM NONE

T     P3    EDITION_NAME               0 02-SEP-2014 01:40:38 PM NONE

T     P3    GENERATED                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    LAST_DDL_TIME              0 02-SEP-2014 01:40:38 PM NONE

T     P3    NAMESPACE                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_ID                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_NAME                0 02-SEP-2014 01:40:38 PM NONE

T     P3    OBJECT_TYPE                0 02-SEP-2014 01:40:38 PM NONE

T     P3    ORDER_STATUS               0 02-SEP-2014 01:40:38 PM NONE

T     P3    OWNER                      0 02-SEP-2014 01:40:38 PM NONE

T     P3    SECONDARY                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    STATUS                     0 02-SEP-2014 01:40:38 PM NONE

T     P3    SUBOBJECT_NAME             0 02-SEP-2014 01:40:38 PM NONE

T     P3    TEMPORARY                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    TIMESTAMP                  0 02-SEP-2014 01:40:38 PM NONE

T     P3    X                          0 02-SEP-2014 01:40:38 PM NONE

 

51 rows selected.

 

rajesh@ORA11G>

 

This change in behavior is due to the fix for Bug 13816060 (replaced by Bug 16726844 ). Before this fix, in 11.2.0.2 for example, although partition P2 is not regathered, some stats on the global partition are gathered instead of being aggregated, which can be much more expensive.