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.
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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.