Skip to Main Content
  • Questions
  • Optimization of view using union on 2 tables of different schema

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nilima.

Asked: February 16, 2018 - 11:37 am UTC

Last updated: February 19, 2018 - 9:39 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi,
I need to get data of table from 2 different schema . So i have created a view which will have union of 2 tables of 2 different schema .
E.g. create or replace view acct_details as
select column1,column2..column n from Table1
Union
select column1,column2..column n from Table2@database_link_name

Actually Table1 and Table2 both tables have huge data like all account details of bank etc.
Because of this vie wis taking so much time to get load and select on newly created view is taking more time to get execute.

Please help how can i design view so that performance will get improve.

and Connor said...

Two schema or two database ?

For two schema, you only need:

select column1,column2..column n from schema1.Table1
Union
select column1,column2..column n from schema2.Table2

If it is for two *databases*, then you need the database link.

*If* the rows in each table are mutually exclusive (ie, no rows overlap between the two), then you should change the "UNION" to "UNION ALL"

That should yield a benefit.

If it is still slow, then you might want to consider a materialized view for the remote data refresh onto your local database, so that both tables are now local.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One possibility

lh, February 19, 2018 - 11:21 am UTC

Hi

I have seen few cases where partition pruning is not pushed into base tables of union. This can happen when partitioning column is not directly referenced in where clause.
If there does not exists union (only one table is referenced)
some magic, like bloom filters etc is happening and partitioning is used.


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.