Optimizer partition oddities, part 1: Severe bug in 10.2.0.4 patch set

Forward to part 2

It seems to be a bit like in the movie the "Groundhog Day": Although I've highlighted already twice an issue when using range-list subpartition pruning, it seems to be reoccurring and there are still a lot of oddities to tell about.

A recent case of a client showed that the subpartition pruning fix that has been incorporated into the 10.2.0.4 patch set is unfortunately quite buggy.

The test case used in my previous posts therefore represents only half of the truth.

Although when using explicit subpartition pruning by means of the "subpartition" clause 10.2.0.4 correctly applies the subpartition statistics (in contrast to 10.2.0.3 which uses the partition level statistics), it fails miserably when using "normal" partition pruning by specifying the partition keys.

Using the same test setup as in my previous post running this simple query in the different versions reveals some interesting results:

select data1 from partition_test where x_pkey = 1 and x_slice = '001';

This simply prunes to a single subpartition containing 10 rows which can be seen from the subpartition level statistics. You can refer to the previous posts for the setup and statistics details.

Here are the EXPLAIN PLAN results from the different versions which were taken from the 10053 trace files (explaining the slightly odd formatting).

11.1.0.7:

------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 9 | 90 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)

10.2.0.4:

------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 1 | 38 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)

10.2.0.3:

------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 10 | 100 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)

So according to the EXPLAIN PLAN output 11.1.0.7 and 10.2.0.3 get it right, but 10.2.0.4 reports an incorrect cardinality.

Checking the 10053 optimizer trace file reveals some interesting details:

11.1.0.7:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 10 #Blks: 1 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [3]
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
Access path analysis for PARTITION_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PARTITION_TEST[PARTITION_TEST]
Column (#2):
NewDensity:0.004950, OldDensity:0.000495 BktCnt:1010, PopBktCnt:1010, PopValCnt:2, NDV:2

Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 10.000000 Rounded: 9 Computed: 9.03 Non Adjusted: 9.03
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 8055
Resp_io: 2.00 Resp_cpu: 8055
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 9.03 Bytes: 0

***************************************

11.1.0.7 correctly identifies the subpartition (10 rows, 1 block). What seems to be odd that it reports for the column #2 (X_SLICE) apparently partition level statistics (1010 BktCnt, Number of Distinct Values: 2), but it doesn't seem to influence the cardinality estimate significantly. When removing any histograms on subpartition level the cardinality estimate is correctly shown as 10.

10.2.0.4:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 0 #Blks: 1 AvgRowLen: 0.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [2] (NOT ANALYZED)
#Rows: 0 #Blks: 58593632 AvgRowLen: 1025.00
#Rows: 0 #Blks: 1 AvgRowLen: 0.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 2
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#1): X_PKEY(NUMBER)
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2) Part#: 2
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 0 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7121
Resp_io: 2.00 Resp_cpu: 7121
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0
***************************************

This looks like a mess, and it is indeed. You'll get always an estimated cardinality of 0 (sanity checked to 1) in the 10.2.0.4 patch set if you prune to a single subpartition. This will screw up almost any plan and hence potentially show abysmal performance.

The issue is tracked with bug Bug 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS and Bug 7264383 - WRONG STATISTICS 0 ROWS FOR QUERY PRUNED TO SUBPARTITION ON 10.2.0.4. More information can be found in Metalink Note 728056.1. A patch for bug 7210921 can be requested and is already available for a couple of platforms.

10.2.0.3:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 1010 #Blks: 4 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [1]
#Rows: 1010 #Blks: 4 AvgRowLen: 10.00
#Rows: 1010 #Blks: 1 AvgRowLen: 10.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 1
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#1): X_PKEY(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#2): X_SLICE(VARCHAR2) Part#: 1
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 1010 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.01 Resp: 2.01 Degree: 0
Cost_io: 2.00 Cost_cpu: 63081
Resp_io: 2.00 Resp_cpu: 63081
Best:: AccessPath: TableScan
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 10.00 Bytes: 0
***************************************

The 10.2.0.3 output reveals that it uses the partition level statistics and only gets it right in this particular case due to an frequency histogram on X_SLICE on the partition level. If you remove this histogram the cardinality estimate will be wrong (505).

So if you're using range-list subpartitioning with 10.2.0.4 and use to prune to a single subpartition you should either use 10.2.0.3 optimizer features using the OPTIMIZER_FEATURES_ENABLE parameter/hint to avoid the mess shown above - though 10.2.0.3 can only use partition level statistics - or get the patch mentioned installed.

In the next part of this series I'm going to show some further oddities when using list partitioning. These apply also to 11gR1.

Exchange partition and aggregated statistics - 10g, 11g documentation bugs

Aggregated statistics on partition or global level are by definition of worse quality than actually gathered statistics, mainly because you can not derive each and every statistic by aggregation (e.g. number of distinct column values), nevertheless there might be applications where they are sufficient and maintaining them is far less resource intensive than actually gathering them.

Aggregated statistics are only maintained when calling DBMS_STATS.GATHER_*_STATS if two conditions are met: For all underlying (sub-)partition objects statistics are available (either gathered or aggregated themselves in case of subpartitioning) and there are no actual statistics gathered on the upper level. In case actual statistics have been gathered, these take always precedence over aggregated statistics which means they are never overwritten by aggregation. You can distinguish between aggregated and gathered statistics by checking the column "GLOBAL_STATS" e.g. in the *_STATISTICS dictionary views. If it says "YES", the statistics are gathered and won't be overwritten by aggregation. In case of "NO" the statistics are based on aggregation and can be updated by aggregating the underlying statistics (provided that for all underlying objects statistics are available).

Up to 10gR2 the documentation of ALTER TABLE EXCHANGE PARTITION mentiones that "All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. Oracle Database recalculates the aggregate statistics of the table receiving the new partition."

This is only correct for the first part, the second part regarding the automatic aggregation of statistics is a documentation bug. In the beginning of partitioning support with Oracle8 according to Oracle this was true but due to customer complaints that this automatic re-aggregation took a significant amount of time (probably in case there were numerous partitions or subpartitions) it has been removed from the default code. There is a (more or less) documented (internal) parameter "_MINIMAL_STATS_AGGREGATION" that has been introduced in Oracle 8i to control the aggregation of statistics. According to the documentation available the following applies: "If the parameter is FALSE, the aggregate statistics will be recomputed. If the parameter is TRUE, the statistics will not be recomputed.". See here the documentation. So you have to be a bit cautious when using this parameter as the setting is counter-intuitive. You have to set it to the non-default value of FALSE to activate the aggregation when exchanging partitions.

While coming across this issue a documentation bug has been raised with Oracle that should correct the last part of the first quoted paragraph regarding the default aggregation rule (so that the documentation correctly mentions that no aggregation is performed by default).

Now looking at the latest available documentation of 11gR1, the particular paragraph reads like this:

"Statistics and histograms on the table or partition are not exchanged. Use the DBMS_STATS package to reaggregate statistics or create a histogram for the table receiving the new partition."

This is again quite interesting as now both parts have been modified. It says now not only that no aggregation takes place but the statistics are not exchanged at all. This would mean a significant change in functionality in 11gR1 compared to pre-11 versions.

I think it is a good idea at this point to come up with a simple test case to check what the actual behaviour is in the current releases 10gR2 Patch Set 3 (10.2.0.4) and 11gR1 (11.1.0.6).

Test case applied against 10.2.0.4:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table exchange_test;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- The exchange table now holds twice the data of subpartition pkey_1_101
SQL>
SQL> -- Now we get rid of global stats and use instead aggregated statistics on partition and global level
SQL> -- Otherwise exchanging subpartitions will never affect the partition or global level stats
SQL> -- Gathered statistics on partition or global level take precedence over aggregated statistics, check the column GLOBAL_STATS
SQL> -- If it shows YES, then the statistics have been gathered, if it shows NO then these are aggregated statistics
SQL> -- Aggregated stats are created/updated whenever all partitions/subpartitions of the lower level have statistics
SQL> -- and one of the lower level partitions/subpartitions are analyzed AND the parent level does not have global stats
SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_0', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_1', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_0_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_1_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'exchange_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Now perform an exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have not been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Undo the exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Now enable aggregated stats update
SQL> alter session set "_minimal_stats_aggregation" = false;

Session altered.

SQL>
SQL> -- And do the same again
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Finally revert to the previous state
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- This is were we started from
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Back to default value
SQL> alter session set "_minimal_stats_aggregation" = true;

Session altered.

SQL>
SQL> spool off

Same test case applied against 11.1.0.6:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table exchange_test;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- The exchange table now holds twice the data of subpartition pkey_1_101
SQL>
SQL> -- Now we get rid of global stats and use instead aggregated statistics on partition and global level
SQL> -- Otherwise exchanging subpartitions will never affect the partition or global level stats
SQL> -- Gathered statistics on partition or global level take precedence over aggregated statistics, check the column GLOBAL_STATS
SQL> -- If it shows YES, then the statistics have been gathered, if it shows NO then these are aggregated statistics
SQL> -- Aggregated stats are created/updated whenever all partitions/subpartitions of the lower level have statistics
SQL> -- and one of the lower level partitions/subpartitions are analyzed AND the parent level does not have global stats
SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_0', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_1', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_0_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_1_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'exchange_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Now perform an exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have not been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Undo the exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Now enable aggregated stats update
SQL> alter session set "_minimal_stats_aggregation" = false;

Session altered.

SQL>
SQL> -- And do the same again
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Finally revert to the previous state
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- This is were we started from
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Back to default value
SQL> alter session set "_minimal_stats_aggregation" = true;

Session altered.

SQL>
SQL> spool off


This simple test shows that the documentation is unfortunately still or again wrong since it is obvious that 11g still behaves like 10gR2 including the option to aggregate the statistics when setting the "_minimal_stats_aggregation" parameter.

So in my opinion the mentioned paragraph in the official documentation could be written like the following:
"All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. Oracle Database does not recalculate any aggregated statistics of the table receiving the new partition by default. If you want to have this aggregation to happen automatically each time you exchange a partition, set the parameter '_minimal_stats_aggregation' to FALSE. The default setting of the parameter is TRUE which means that it prevents the aggregation from happening."

Overview of new and changed features in 10gR2 Patch Set 3 (10.2.0.4)

The Patch Set 3 of 10gR2 (Version 10.2.0.4) introduces a couple of interesting new and changed functionality.

10.2.0.4 supports now the "Real Application Testing" functionality (or more precisely the "Workload capture" functionality of "Database Replay") that has been introduced with Oracle 11gR1. This definitely makes sense as the most obvious application of "Real Application Testing" is testing an upgrade from 10gR2 to Oracle 11gR1 and therefore gathering your actual workload in 10gR2 and replaying it in your 11g test environment is a required functionality to be able to perform that. The new package "DBMS_WORKLOAD_CAPTURE" has been introduced in 10.2.0.4 to support the workload capturing. For more details please look here:

Updated 10gR2 documentation "Performance Tuning Guide"

In addition the "Test Case Builder" (TCB) introduced in Oracle 11gR1 has also been backported to 10gR2, so that you now have the package "dbms_sqldiag" available that allows to export and import test cases.

Another feature that has been added to 10.2.0.4 are the new DIFF_TABLE_STATS* functions in the DBMS_STATS package that allow you easily to compare statistics which comes in handy if you are looking for reasons why you got different execution plans in different environments.

You can find details about the latter two features here:

The blog of the Oracle optimizer group

and here:

Whitepaper about upgrading from Oracle 9i to Oracle 10g, published February 2008!

The native hash full outer join introduced in Oracle 11gR1 has obviously been backported to 10.2.0.4, but it needs to be explicitly enabled using the following internal parameter:

_optimizer_native_full_outer_join =force

This is mentioned in the ReadMe of the 10.2.0.4 Patch set documentation. Use the DocID 316900.1 in MetaLink to access the latest version of the ReadMe document.

Here is a the result of the original 11gR1 test case applied against 10.2.04:

SQL>
SQL> drop table native_full_outer_join_test purge;

Table dropped.

Elapsed: 00:00:03.31
SQL> drop table native_full_outer_join_test2 purge;

Table dropped.

Elapsed: 00:00:00.09
SQL>
SQL> create table native_full_outer_join_test as
2 select
3 trunc(sqrt(rownum-1)) as skewed_data,
4 rownum-1 as id,
5 lpad(rownum-1,10) id_char,
6 rpad('x',50, 'x') as filler
7 from
8 all_objects;

Table created.

Elapsed: 00:00:15.49
SQL>
SQL> create table native_full_outer_join_test2 as
2 select
3 trunc(sqrt(rownum+20000-1)) as skewed_data,
4 rownum+20000-1 as id,
5 lpad(rownum+20000-1,10) id_char,
6 rpad('x',50, 'x') as filler
7 from
8 all_objects;

Table created.

Elapsed: 00:00:06.81
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>USER, tabname=>'native_full_outer_join_test');
3 dbms_stats.gather_table_stats(ownname=>USER, tabname=>'native_full_outer_join_test2');
4 end;
5 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.39
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select /*+ opt_param('_optimizer_native_full_outer_join', 'force') */ a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id = b.id
4 order by a.id_char;

70079 rows selected.

Elapsed: 00:00:01.33

Execution Plan
----------------------------------------------------------
Plan hash value: 1136243049

---------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
---------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִִִִ|ִִ2063ִִִ(1)|ִ00:00:25ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִ14M|ִִ2063ִִִ(1)|ִ00:00:25ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִ|ִVW_FOJ_0ִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִִִִ|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|*ִִ3ִ|ִִִִHASHִJOINִFULLִOUTER|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6846K|ִִ4016K|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|ִִִ4ִ|ִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ3ִ-ִaccess("A"."ID"="B"."ID")


Statistics
----------------------------------------------------------
ִִִִִִִִִִ1ִִrecursiveִcalls
ִִִִִִִִִִ0ִִdbִblockִgets
ִִִִִִִ1059ִִconsistentִgets
ִִִִִִִִִִ0ִִphysicalִreads
ִִִִִִִִִִ0ִִredoִsize
ִִִִ2537237ִִbytesִsentִviaִSQL*Netִtoִclient
ִִִִִִ51777ִִbytesִreceivedִviaִSQL*Netִfromִclient
ִִִִִִִ4673ִִSQL*Netִroundtripsִto/fromִclient
ִִִִִִִִִִ1ִִsortsִ(memory)
ִִִִִִִִִִ0ִִsortsִ(disk)
ִִִִִִ70079ִִrowsִprocessed

SQL>
SQL> select a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id = b.id
4 order by a.id_char;

70079 rows selected.

Elapsed: 00:00:01.38

Execution Plan
----------------------------------------------------------
Plan hash value: 4036012045

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִִִִ|ִִ2875ִִִ(2)|ִ00:00:35ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִ20M|ִִ2875ִִִ(2)|ִ00:00:35ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִִִִ|ִִִ873ִִִ(2)|ִ00:00:11ִ|
|ִִִ3ִ|ִִִִUNION_ALLִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִִִִִִ|ִִִִִִִִִִ|
|*ִִ4ִ|ִִִִִHASHִJOINִOUTERִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִ6846K|ִִ4016K|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|ִִִ5ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ6ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִִ7ִ|ִִִִִHASHִJOINִRIGHTִANTI|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ20007ִ|ִִ1465K|ִִִִִִִ|ִִִ241ִִִ(3)|ִ00:00:03ִ|
|ִִִ8ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִִ244K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ9ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ4ִ-ִaccess("A"."ID"="B"."ID"(+))
ִִִ7ִ-ִaccess("A"."ID"="B"."ID")


Statistics
----------------------------------------------------------
ִִִִִִִִִִ1ִִrecursiveִcalls
ִִִִִִִִִִ0ִִdbִblockִgets
ִִִִִִִ2118ִִconsistentִgets
ִִִִִִִִִִ0ִִphysicalִreads
ִִִִִִִִִִ0ִִredoִsize
ִִִִ2537569ִִbytesִsentִviaִSQL*Netִtoִclient
ִִִִִִ51777ִִbytesִreceivedִviaִSQL*Netִfromִclient
ִִִִִִִ4673ִִSQL*Netִroundtripsִto/fromִclient
ִִִִִִִִִִ1ִִsortsִ(memory)
ִִִִִִִִִִ0ִִsortsִ(disk)
ִִִִִִ70079ִִrowsִprocessed

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id < b.id
4 order by a.id_char;
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 4239385412

---------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
---------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִִִִ|ִִ4845Kִִ(1)|ִ16:09:04ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִ36G|ִִ4845Kִִ(1)|ִ16:09:04ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִִִִ|ִִ5294ִִ(42)|ִ00:01:04ִ|
|ִִִ3ִ|ִִִִUNION_ALLִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִִִִִִ|ִִִִִִִִִִ|
|ִִִ4ִ|ִִִִִMERGEִJOINִOUTERִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ16G|ִִִִִִִ|ִִ4059ִִ(54)|ִ00:00:49ִ|
|ִִִ5ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִִ6ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִִ7ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִִ8ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ9ִ|ִִִִִMERGEִJOINִANTIִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ47575ִ|ִִ3484K|ִִִִִִִ|ִִ1234ִִִ(2)|ִ00:00:15ִ|
|ִִ10ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִ11ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִ12ִ|ִִִִִִSORTִUNIQUEִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִִ244K|ִִ1192K|ִִִ282ִִִ(4)|ִ00:00:04ִ|
|ִִ13ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִִ244K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ7ִ-ִaccess("A"."ID"<"B"."ID"(+))
ִִִִִִִfilter("A"."ID"<"B"."ID"(+))
ִִ12ִ-ִaccess(INTERNAL_FUNCTION("A"."ID")<INTERNAL_FUNCTION("B"."ID"))
ִִִִִִִfilter(INTERNAL_FUNCTION("A"."ID")<INTERNAL_FUNCTION("B"."ID"))

SQL>
SQL> spool off

So apart from the fact that you need to explicitly enable the native full outer join - either at statement, session or instance level - 10.2.0.4 seems to behave exactly like 11.1.0.6.

Furthermore as shown in detail in my other post the optimizer functionality regarding the treatment of subpartition statistics has been changed significantly in 10.2.0.4, a change you should be aware of if you are using range-list subpartitioning and your subpartitions differ in size.

You can find the details here.

Another notable change in optimizer behaviour introduced in 10.2.0.4 is the treatment of non-existing values in frequency histograms when applying equality predicates. You can find more details here and in the description of the bugs 5483301 and 6082745 on MetaLink.

According to MetaLink document 555579.1 (10.2.0.4 Patch Set - Availability and Known Issues) there is another notable change regarding the usage of bind variable peeking. In 10.2.0.4 a bug is fixed that used to apply bind variable peeking when it was not supposed to happen (so e.g. even if you had set "_optim_peek_user_binds" to FALSE). This means that now there might be situations where the optimizer does no longer have bind variable value information available when determining the execution plan which could lead to execution plan changes.

Although it looks like that the new density calculation option introduced in 11gR1 has also been made available in 10.2.0.4 (because the corresponding undocumented parameter is now available), setting the new undocumented parameter "_optimizer_enable_density_improvements" to true didn't have any noticeable effects in my test cases. According to the 10053 optimizer trace still the original density saved in the dictionary was used for unpopular values in case a height based histogram existed.

You can find more details about this new 11g feature here.

Revisiting subpartition pruning and the optimizer - Oracle 10gR2 Patch Set 3 (10.2.0.4) and Oracle 11gR1 (11.1.0.6)

UPDATE: If you came here because you seem to have issues with execution plans after upgrading to 10.2.0.4, please read this note about a severe bug in optimizer of the 10.2.0.4 patch set regarding subpartition pruning to a single list subpartition when using range-list composite partitioning.

Some time ago I wrote about the particular issue that the optimizer refused to use subpartition statistics even in cases where a subpartition pruning clearly identified a single subpartition. This may result in suboptimal execution plans if your subpartitions are different in size caused by the usage of the partition level statistics that probably lead to inappropriate estimates for certain subpartitions that differ significantly from the average size.

I've updated that blog entry with the information that Oracle accepted this as a bug and it is going to be fixed in 11gR1 and 10gR2 Patch Set 3 (10.2.0.4). Oracle said that they are not able to provide a back port of the fix as it was too complex and touched too many source files (this is what they said).

Now that both new versions are available I'll provide here the result of the test case used to demonstrate the issue in 10.2.0.3.

It is worth to note that the bug 5996801 is not mentioned explicitly anywhere in the 10.2.0.4 documentation, it only shows up in the section "Undocumented Oracle Server" of the following document: MetaLink DocumentID 401436.1. Although Oracle has this general disclaimer that each patch set needs to be thoroughly tested before brought into production I wonder why this significant change in optimizer behaviour has not been documented at all. If you are currently using range-list subpartitioning and your list subpartitions are not equal then you'll probably encounter significant execution plan changes when upgrading to 10.2.0.4 and I think it would be good to know about this particular change.

For 11gR1 it is a bit different story as it introduces a whole new set of partitioning options so I guess it is acceptable and may be even expected that things regarding partitioning in the optimizer are also very likely to change.

So here is the result of the known test cases against 10.2.0.4 and 11.1.06:

Testcase 1 (cardinality estimates) against 10.2.0.4:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> explain plan for select data1 from partition_test subpartition (pkey_1_001);

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 315551227

--------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִ|ִrowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
--------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ2ִ|ִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TESTִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
--------------------------------------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> select /*+ gather_plan_statistics */ data1 from partition_test subpartition (pkey_1_001);

ִִִִִDATA1
----------
ִִִִִִִִִ1
ִִִִִִִִִ2
ִִִִִִִִִ3
ִִִִִִִִִ4
ִִִִִִִִִ5
ִִִִִִִִִ6
ִִִִִִִִִ7
ִִִִִִִִִ8
ִִִִִִִִִ9
ִִִִִִִִ10

10 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8r0m2ku4f5vaw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ data1 from partition_test subpartition (pkey_1_001)

Plan hash value: 315551227

--------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִ|ִStartsִ|ִE-rowsִ|ִA-rowsִ|ִִִA-Timeִִִ|ִBuffersִ|
--------------------------------------------------------------------------------------------------------
|ִִִ1ִ|ִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִ|ִִִִִִ1ִ|ִִִִִ10ִ|ִִִִִ10ִ|00:00:00.01ִ|ִִִִִִִ4ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TESTִ|ִִִִִִ1ִ|ִִִִִ10ִ|ִִִִִ10ִ|00:00:00.01ִ|ִִִִִִִ4ִ|
--------------------------------------------------------------------------------------------------------


13 rows selected.

SQL>
SQL> spool off

So this clearly shows that the optimizer in 10.2.0.4 actually makes use of the subpartition statistics.

And now the "join" test case:

SQL>
SQL> drop table partition_test2;

Table dropped.

SQL>
SQL> create table partition_test2 (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number not null
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test2 (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test2 (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1_101',numrows=>1000000,numblks=>100000); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1',numrows=>1000010,numblks=>100005); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',numrows=>1000010,numblks=>100005); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 col_stat_rec dbms_stats.StatRec;
3 col_vals dbms_stats.numarray := dbms_stats.numarray(1, 1000000);
4 begin
5 col_stat_rec.epc := 2;
6 col_vals(1) := 1;
7 col_vals(2) := 1000000;
8 dbms_stats.prepare_column_values(col_stat_rec, col_vals);
9 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1_101',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
10 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
11 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
12 end;
13 /

PL/SQL procedure successfully completed.

SQL>
SQL> column table_name format a20
SQL> column index_name format a20
SQL> column partition_name format a10
SQL> column subpartition_name format a15
SQL> column column_name format a15
SQL> column plan_table_output format a300
SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows, blocks from user_tab_statistics where table_name = 'PARTITION_TEST2';

TABLE_NAMEִִִִִִִִִִִPARTITION_ִSUBPARTITION_NAִִִNUM_ROWSִִִִִBLOCKS
--------------------ִ----------ִ---------------ִ----------ִ----------
PARTITION_TEST2ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000010ִִִִִ100005
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִ1000010ִִִִִ100005
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִPKEY_0_001ִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_101ִִִִִִִִִ1000000ִִִִִ100000
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_001ִִִִִִִִִִִִִִ10ִִִִִִִִִִ1
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select column_name, num_distinct, density, num_nulls from user_tab_col_statistics where table_name = 'PARTITION_TEST2';

COLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ------------ִ----------ִ----------
X_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
X_SLICEִִִִִִִִִִִִִִִִִִִִ2ִִִִִִִִִ,5ִִִִִִִִִִ0
DATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

SQL>
SQL> select partition_name, column_name, num_distinct, density, num_nulls from user_part_col_statistics where table_name = 'PARTITION_TEST2';

PARTITION_ִCOLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
----------ִ---------------ִ------------ִ----------ִ----------
PKEY_0ִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0ִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0ִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1ִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1ִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ2ִִִִִִִִִ,5ִִִִִִִִִִ0
PKEY_1ִִִִִDATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

6 rows selected.

SQL>
SQL> select subpartition_name, column_name, num_distinct, density, num_nulls from user_subpart_col_statistics where table_name = 'PARTITION_TEST2';

SUBPARTITION_NAִCOLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ---------------ִ------------ִ----------ִ----------
PKEY_0_XXXִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_XXXִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_XXXִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_001ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_001ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_001ִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִ10ִִִִִִִִִ,1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִDATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

15 rows selected.

SQL>
SQL> drop table join_table;

Table dropped.

SQL>
SQL> create table join_table (
2 data1 number not null,
3 data2 number
4 );

Table created.

SQL>
SQL> insert into join_table (data1, data2)
2 select seq, seq
3 from (
4 select level as seq from dual connect by level <= 1000 5 ); 1000 rows created. SQL>
SQL> create unique index join_table_pk on join_table (data1);

Index created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'JOIN_TABLE', cascade=>true); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'JOIN_TABLE',numrows=>10000000,numblks=>1000000); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_index_stats(ownname=>USER,indname=>'JOIN_TABLE_PK',numrows=>10000000,numlblks=>100000,numdist=>10000000,clstfct=>5000000,indlevel=>3); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 col_stat_rec dbms_stats.StatRec;
3 col_vals dbms_stats.numarray := dbms_stats.numarray(1, 10000000);
4 begin
5 col_stat_rec.epc := 2;
6 col_vals(1) := 1;
7 col_vals(2) := 10000000;
8 dbms_stats.prepare_column_values(col_stat_rec, col_vals);
9 dbms_stats.set_column_stats(ownname=>USER,tabname=>'JOIN_TABLE',colname=>'DATA1',distcnt=>10000000,density=>1/10000000, srec=>col_stat_rec);
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, num_rows, blocks from user_tab_statistics where table_name = 'JOIN_TABLE';

TABLE_NAMEִִִִִִִִִִִִִNUM_ROWSִִִִִBLOCKS
--------------------ִ----------ִ----------
JOIN_TABLEִִִִִִִִִִִִִ10000000ִִִִ1000000

SQL>
SQL> select column_name, num_distinct, density, num_nulls from user_tab_col_statistics where table_name = 'JOIN_TABLE';

COLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ------------ִ----------ִ----------
DATA1ִִִִִִִִִִִִִִִ10000000ִִִ,0000001ִִִִִִִִִִ0
DATA2ִִִִִִִִִִִִִִִִִִִ1000ִִִִִִִ,001ִִִִִִִִִִ0

SQL>
SQL> select table_name, index_name, num_rows, leaf_blocks, clustering_factor, distinct_keys, blevel
2 from user_ind_statistics where table_name = 'JOIN_TABLE';

TABLE_NAMEִִִִִִִִִִִINDEX_NAMEִִִִִִִִִִִִִNUM_ROWSִLEAF_BLOCKSִCLUSTERING_FACTORִDISTINCT_KEYSִִִִִBLEVEL
--------------------ִ--------------------ִ----------ִ-----------ִ-----------------ִ-------------ִ----------
JOIN_TABLEִִִִִִִִִִִJOIN_TABLE_PKִִִִִִִִִִ10000000ִִִִִִ100000ִִִִִִִִִִִ5000000ִִִִִִ10000000ִִִִִִִִִִ3

SQL>
SQL> explain plan for select a.data1, b.data2 from partition_test2 partition (pkey_1) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1688927127

-------------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
-------------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִִִ10M|ִִִִִִִ|ִִִ252Kִִ(1)|ִ00:50:30ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִִִ10M|ִִִִ15M|ִִִ252Kִִ(1)|ִ00:50:30ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִRANGEִSINGLE|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ2ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִִPARTITIONִLISTִALLִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ1ִ|ִִִִִ3ִ|
|ִִִ4ִ|ִִִִִTABLEִACCESSִFULLִִִ|ִPARTITION_TEST2ִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ3ִ|ִִִִִ5ִ|
|ִִִ5ִ|ִִִTABLEִACCESSִFULLִִִִִ|ִJOIN_TABLEִִִִִִ|ִִִִ10M|ִִִִ66M|ִִִִִִִ|ִִִ220Kִִ(1)|ִ00:44:04ִ|ִִִִִִִ|ִִִִִִִ|
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ1ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> explain plan for select a.data1, b.data2 from partition_test2 subpartition (pkey_1_001) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2056129735

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִNESTEDִLOOPSִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TEST2ִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
|ִִִ4ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִJOIN_TABLEִִִִִִ|ִִִִִ1ִ|ִִִִִ7ִ|ִִִִִ3ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ5ִ|ִִִִINDEXִUNIQUEִSCANִִִִִִִִִ|ִJOIN_TABLE_PKִִִ|ִִִִִ1ִ|ִִִִִִִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ5ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> explain plan for select /*+ cardinality(a, 10) */ a.data1, b.data2 from partition_test2 subpartition (pkey_1_001) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2056129735

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִNESTEDִLOOPSִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TEST2ִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
|ִִִ4ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִJOIN_TABLEִִִִִִ|ִִִִִ1ִ|ִִִִִ7ִ|ִִִִִ3ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ5ִ|ִִִִINDEXִUNIQUEִSCANִִִִִִִִִ|ִJOIN_TABLE_PKִִִ|ִִִִִ1ִ|ִִִִִִִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ5ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> spool off

This shows again that the optimizer now makes use of the subpartition statistics and therefore comes to the conclusion that using the index and nested loop access is the cheaper approach rather than doing a full table scan on the large table.

And now the same using 11.1.0.6, first test case 1:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

8 rows selected.

SQL>
SQL> explain plan for select data1 from partition_test subpartition (pkey_1_001);

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 315551227

--------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
--------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ2ִ|ִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TESTִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
--------------------------------------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> select /*+ gather_plan_statistics */ data1 from partition_test subpartition (pkey_1_001);

ִִִִִDATA1
----------
ִִִִִִִִִ1
ִִִִִִִִִ2
ִִִִִִִִִ3
ִִִִִִִִִ4
ִִִִִִִִִ5
ִִִִִִִִִ6
ִִִִִִִִִ7
ִִִִִִִִִ8
ִִִִִִִִִ9
ִִִִִִִִ10

10 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8r0m2ku4f5vaw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ data1 from partition_test
subpartition (pkey_1_001)

Plan hash value: 315551227

--------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִ|ִStartsִ|ִE-Rowsִ|ִA-Rowsִ|ִִִA-Timeִִִ|ִBuffersִ|
--------------------------------------------------------------------------------------------------------
|ִִִ1ִ|ִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִ|ִִִִִִ1ִ|ִִִִִ10ִ|ִִִִִ10ִ|00:00:00.01ִ|ִִִִִִִ4ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TESTִ|ִִִִִִ1ִ|ִִִִִ10ִ|ִִִִִ10ִ|00:00:00.01ִ|ִִִִִִִ4ִ|
--------------------------------------------------------------------------------------------------------


14 rows selected.

SQL>
SQL> spool off

OK, looks good, subpartition statistics are being used. Test case 2, the join:

SQL>
SQL> drop table partition_test2;

Table dropped.

SQL>
SQL> create table partition_test2 (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number not null
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test2 (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test2 (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1_101',numrows=>1000000,numblks=>100000); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1',numrows=>1000010,numblks=>100005); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'PARTITION_TEST2',numrows=>1000010,numblks=>100005); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 col_stat_rec dbms_stats.StatRec;
3 col_vals dbms_stats.numarray := dbms_stats.numarray(1, 1000000);
4 begin
5 col_stat_rec.epc := 2;
6 col_vals(1) := 1;
7 col_vals(2) := 1000000;
8 dbms_stats.prepare_column_values(col_stat_rec, col_vals);
9 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1_101',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
10 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',partname=>'PKEY_1',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
11 dbms_stats.set_column_stats(ownname=>USER,tabname=>'PARTITION_TEST2',colname=>'DATA1',distcnt=>1000000,density=>1/1000000, srec=>col_stat_rec);
12 end;
13 /

PL/SQL procedure successfully completed.

SQL>
SQL> column table_name format a20
SQL> column index_name format a20
SQL> column partition_name format a10
SQL> column subpartition_name format a15
SQL> column column_name format a15
SQL> column plan_table_output format a300
SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows, blocks from user_tab_statistics where table_name = 'PARTITION_TEST2';

TABLE_NAMEִִִִִִִִִִִPARTITION_ִSUBPARTITION_NAִִִNUM_ROWSִִִִִBLOCKS
--------------------ִ----------ִ---------------ִ----------ִ----------
PARTITION_TEST2ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000010ִִִִִ100005
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִ1000010ִִִִִ100005
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִPKEY_0_001ִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_0ִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_101ִִִִִִִִִ1000000ִִִִִ100000
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_001ִִִִִִִִִִִִִִ10ִִִִִִִִִִ1
PARTITION_TEST2ִִִִִִPKEY_1ִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select column_name, num_distinct, density, num_nulls from user_tab_col_statistics where table_name = 'PARTITION_TEST2';

COLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ------------ִ----------ִ----------
X_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
X_SLICEִִִִִִִִִִִִִִִִִִִִ2ִִִִִִִִִ,5ִִִִִִִִִִ0
DATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

SQL>
SQL> select partition_name, column_name, num_distinct, density, num_nulls from user_part_col_statistics where table_name = 'PARTITION_TEST2';

PARTITION_ִCOLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
----------ִ---------------ִ------------ִ----------ִ----------
PKEY_0ִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0ִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0ִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1ִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1ִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ2ִִִִִִִִִ,5ִִִִִִִִִִ0
PKEY_1ִִִִִDATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

6 rows selected.

SQL>
SQL> select subpartition_name, column_name, num_distinct, density, num_nulls from user_subpart_col_statistics where table_name = 'PARTITION_TEST2';

SUBPARTITION_NAִCOLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ---------------ִ------------ִ----------ִ----------
PKEY_0_XXXִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_XXXִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_XXXִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_0_001ִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_XXXִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִִ0ִִִִִִִִִִ0ִִִִִִִִִִ0
PKEY_1_001ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_001ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_001ִִִִִִDATA1ִִִִִִִִִִִִִִִִִִִִִ10ִִִִִִִִִ,1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִX_PKEYִִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִX_SLICEִִִִִִִִִִִִִִִִִִִִ1ִִִִִִִִִִ1ִִִִִִִִִִ0
PKEY_1_101ִִִִִִDATA1ִִִִִִִִִִִִִִִִ1000000ִִִִ,000001ִִִִִִִִִִ0

15 rows selected.

SQL>
SQL> drop table join_table;

Table dropped.

SQL>
SQL> create table join_table (
2 data1 number not null,
3 data2 number
4 );

Table created.

SQL>
SQL> insert into join_table (data1, data2)
2 select seq, seq
3 from (
4 select level as seq from dual connect by level <= 1000 5 ); 1000 rows created. SQL>
SQL> create unique index join_table_pk on join_table (data1);

Index created.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'JOIN_TABLE', cascade=>true); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_table_stats(ownname=>USER,tabname=>'JOIN_TABLE',numrows=>10000000,numblks=>1000000); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> begin dbms_stats.set_index_stats(ownname=>USER,indname=>'JOIN_TABLE_PK',numrows=>10000000,numlblks=>100000,numdist=>10000000,clstfct=>5000000,indlevel=>3); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 col_stat_rec dbms_stats.StatRec;
3 col_vals dbms_stats.numarray := dbms_stats.numarray(1, 10000000);
4 begin
5 col_stat_rec.epc := 2;
6 col_vals(1) := 1;
7 col_vals(2) := 10000000;
8 dbms_stats.prepare_column_values(col_stat_rec, col_vals);
9 dbms_stats.set_column_stats(ownname=>USER,tabname=>'JOIN_TABLE',colname=>'DATA1',distcnt=>10000000,density=>1/10000000, srec=>col_stat_rec);
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, num_rows, blocks from user_tab_statistics where table_name = 'JOIN_TABLE';

TABLE_NAMEִִִִִִִִִִִִִNUM_ROWSִִִִִBLOCKS
--------------------ִ----------ִ----------
JOIN_TABLEִִִִִִִִִִִִִ10000000ִִִִ1000000

SQL>
SQL> select column_name, num_distinct, density, num_nulls from user_tab_col_statistics where table_name = 'JOIN_TABLE';

COLUMN_NAMEִִִִִNUM_DISTINCTִִִִDENSITYִִNUM_NULLS
---------------ִ------------ִ----------ִ----------
DATA1ִִִִִִִִִִִִִִִ10000000ִִִ,0000001ִִִִִִִִִִ0
DATA2ִִִִִִִִִִִִִִִִִִִ1000ִִִִִִִ,001ִִִִִִִִִִ0

SQL>
SQL> select table_name, index_name, num_rows, leaf_blocks, clustering_factor, distinct_keys, blevel
2 from user_ind_statistics where table_name = 'JOIN_TABLE';

TABLE_NAMEִִִִִִִִִִִINDEX_NAMEִִִִִִִִִִִִִNUM_ROWSִLEAF_BLOCKSִCLUSTERING_FACTORִDISTINCT_KEYSִִִִִBLEVEL
--------------------ִ--------------------ִ----------ִ-----------ִ-----------------ִ-------------ִ----------
JOIN_TABLEִִִִִִִִִִִJOIN_TABLE_PKִִִִִִִִִִ10000000ִִִִִִ100000ִִִִִִִִִִִ5000000ִִִִִִ10000000ִִִִִִִִִִ3

SQL>
SQL> explain plan for select a.data1, b.data2 from partition_test2 partition (pkey_1) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1688927127

-------------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
-------------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִִִ10M|ִִִִִִִ|ִִִ252Kִִ(1)|ִ00:50:30ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִִִ10M|ִִִִ15M|ִִִ252Kִִ(1)|ִ00:50:30ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִRANGEִSINGLE|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ2ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִִPARTITIONִLISTִALLִִִ|ִִִִִִִִִִִִִִִִִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ1ִ|ִִִִִ3ִ|
|ִִִ4ִ|ִִִִִTABLEִACCESSִFULLִִִ|ִPARTITION_TEST2ִ|ִִ1000K|ִִ3906K|ִִִִִִִ|ִ22033ִִִ(1)|ִ00:04:25ִ|ִִִִִ3ִ|ִִִִִ5ִ|
|ִִִ5ִ|ִִִTABLEִACCESSִFULLִִִִִ|ִJOIN_TABLEִִִִִִ|ִִִִ10M|ִִִִ66M|ִִִִִִִ|ִִִ220Kִִ(1)|ִ00:44:04ִ|ִִִִִִִ|ִִִִִִִ|
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ1ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> explain plan for select a.data1, b.data2 from partition_test2 subpartition (pkey_1_001) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2056129735

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִNESTEDִLOOPSִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TEST2ִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
|ִִִ4ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִJOIN_TABLEִִִִִִ|ִִִִִ1ִ|ִִִִִ7ִ|ִִִִִ3ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ5ִ|ִִִִINDEXִUNIQUEִSCANִִִִִִִִִ|ִJOIN_TABLE_PKִִִ|ִִִִִ1ִ|ִִִִִִִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ5ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> explain plan for select /*+ cardinality(a, 10) */ a.data1, b.data2 from partition_test2 subpartition (pkey_1_001) a, join_table b where a.data1 = b.data1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2056129735

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִ(%CPU)|ִTimeִִִִִ|ִPstart|ִPstopִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ1ִ|ִִNESTEDִLOOPSִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִ100ִ|ִִִִ32ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|ִִִ2ִ|ִִִPARTITIONִCOMBINEDִITERATOR|ִִִִִִִִִִִִִִִִִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִKEYִ|ִִִKEYִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִFULLִִִִִִִִִ|ִPARTITION_TEST2ִ|ִִִִ10ִ|ִִִִ30ִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִ4ִ|ִִִִִ4ִ|
|ִִִ4ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִJOIN_TABLEִִִִִִ|ִִִִִ1ִ|ִִִִִ7ִ|ִִִִִ3ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
|*ִִ5ִ|ִִִִINDEXִUNIQUEִSCANִִִִִִִִִ|ִJOIN_TABLE_PKִִִ|ִִִִִ1ִ|ִִִִִִִ|ִִִִִ2ִִִ(0)|ִ00:00:01ִ|ִִִִִִִ|ִִִִִִִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ5ִ-ִaccess("A"."DATA1"="B"."DATA1")

17 rows selected.

SQL>
SQL> spool off

Apart from the fact that the 11gR1 optimizer shows a second NESTED LOOP operation for the join when accessing the base table rows, it shows that again the subpartition statistics are used and 11.1.0.6 comes to the same result as 10.2.0.4.

So this means that you definitely need to check database applications that make use of range-list subpartitioning before applying the 10.2.0.4 patch set.