SQL & PL/SQL

Hi

I am facing a performance issue with a query that has a union all view. I have two partitioned tables on two different databases(regular DB and archived DB ), both of which have the same structure , same partition key. When I write a query on the individual tables with a where clause using the partition key , I get a proper partition scan with the partition being determined by key values.

For eg : When I issue this query to each individual tables in their respective databases

select * from "table" where "part_key" in (SELECT "some_value" FROM "another_table")

I get something like this in the plan, and the output comes out fairly quickly

"PARTITION RANGE ITERATOR Cost: xxxx Bytes: xxxxx Cardinality: xxxxx Partition #: 4 Partitions determined by Key Values"

Now I have a view which is a union all of the two tables without any predicates i.e

create or replace view combo_view
as
Select * from table A
union all
select * from table A@remote_db

If I issue a query to this view,similar to the first query with exact same value being passed, I see that it no more goes for the partition range scan, but goes for a full table scan of both the tables and then attempts to do a Hash. Basically it doesn't push the predicate to the view at all. If I look at the session browser I can see the query doing scans of all the partitions in each of the databases.I have tried using the push_pred hint, but that also does not change the plan

select * from "combo_view" where "part_key" in (SELECT "some_value" FROM "another_table")

PARTITION RANGE ALL Cost: XXXX Bytes: XXXX Cardinality: XXXX Partition #: 6 Partitions accessed #1 - #390
TABLE ACCESS FULL TABLE "TABLE A" Cost: XXXX Bytes: XXXX Cardinality: XXXX Partition #: 6 Partitions accessed #1 - #390

Any ideas why this might happen and if there are any work around for this.

Edited by: Orko on Dec 3, 2012 12:58 PM

Edited by: Orko on Dec 3, 2012 1:14 PM