The scripts are mostly similar to your case with slight modifications for partitioning. I am sharing the same.
The version of our database is 11.2.0.2.0
--table 1
create table t1_sdw_part
partition by list(object_type)
(
partition t1_1 values('TABLE','TABLE PARTITION','TABLE SUBPARTITION'),
partition t1_2 values('INDEX','INDEX PARTITION','INDEX SUBPARTITION'),
partition t1_other values (DEFAULT)
)
as select OBJECT_ID,OWNER, OBJECT_NAME, SUBOBJECT_NAME,DATA_OBJECT_ID, OBJECT_TYPE
from all_objects where 1=2;
--table 2
create table t2_sdw_part
PARTITION BY LIST (object_type)
(
partition t2_1 values('TABLE','TABLE PARTITION','TABLE SUBPARTITION'),
partition t2_2 values('INDEX','INDEX PARTITION','INDEX SUBPARTITION'),
partition t2_other values (DEFAULT)
)
as select OBJECT_ID,OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP,
STATUS, TEMPORARY, GENERATED, SECONDARY from all_objects where 1=2;
--stats
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 300000000, numblks => 3000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_1', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_2', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_OTHER', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 300000000, numblks => 3000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_1', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_2', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_OTHER', numblks => 1000000 );
--constraints
alter table t1_sdw_part add constraint t1_sdw_part_pk primary key(object_id) rely disable novalidate;
alter table t2_sdw_part add constraint t2_sdw_part_pk primary key(object_id) rely disable novalidate;
alter table t2_sdw_part add constraint t2_fk_t1_sdw_part foreign key(object_id) references t1_sdw_part(object_id) rely disable novalidate;
alter table t1_sdw_part add constraint t1_fk_t2_sdw_part foreign key(object_id) references t2_sdw_part(object_id) rely disable novalidate;
--view
create or replace view vw_sdw_part
as select
t1.OBJECT_ID,
t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
from t1_sdw_part t1, t2_sdw_part t2
where t1.object_id = t2.object_id and t1.object_type=t2.object_type;
explain plan for select * from vw_sdw_part where object_type = 'TABLE';
select * from table(dbms_xplan.display(NULL,NULL,'BASIC PARTITION ROWS BYTES PREDICATE'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 144M| | | |
|* 1 | HASH JOIN | | 1000K| 144M| 72M| | |
| 2 | PART JOIN FILTER CREATE| :BF0000 | 1000K| 61M| | | |
| 3 | PARTITION LIST SINGLE | | 1000K| 61M| | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T2_SDW_PART | 1000K| 61M| | 1 | 1 |
| 5 | PARTITION LIST SINGLE | | 1000K| 83M| |KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | T1_SDW_PART | 1000K| 83M| | 1 | 1 |
----------------------------------------------------------------------------------------
explain plan for select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
DATA_OBJECT_ID from vw_sdw_part where object_type = 'TABLE';
select * from table(dbms_xplan.display(NULL,NULL,'BASIC PARTITION ROWS BYTES PREDICATE'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 106M| | | |
|* 1 | HASH JOIN | | 1000K| 106M| 34M| | |
| 2 | PART JOIN FILTER CREATE| :BF0000 | 1000K| 22M| | | |
| 3 | PARTITION LIST SINGLE | | 1000K| 22M| | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T2_SDW_PART | 1000K| 22M| | 1 | 1 |
| 5 | PARTITION LIST SINGLE | | 1000K| 83M| |KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | T1_SDW_PART | 1000K| 83M| | 1 | 1 |
----------------------------------------------------------------------------------------
In the above case all the columns are selected from first table but still I get a join with second table. I have tried without partitioning (exactly similar to your example) and it works fine.
Out of frustration I played around with various options but still couldn't get it to work
a) Added PKEY to constraint
b) Removed object type join from view definition and kept the constraint only on object id.
c) Changed hidden parameters on predicate push down and transitive closure etc
Please guide.
Thanks
Prashant