This discussion is archived
8 Replies Latest reply: Nov 14, 2012 8:39 PM by user12288492 RSS

sinlge select query in diff schemas for same table(Indentical Structure)

973776 Newbie
Currently Being Moderated
Scenario :

Table XYZ is created in Schema A
After an year, the old data from the previous year would be moved to different schema. However in the other schema the same table name would be used.

For eg

Schema A contains table XYZ with data of 2012 yr
Schema B contains table XYZ with data of 2011 yr
Table XYZ in both the schemas have identical structure.

So can we fire a single select query to read the data from both the tables in effective way.
Eg select * from XYZ where date range between 15-Oct-2011 to 15-Mar-2012.
However the data resides in 2 different schema altogether.

Creating an view is an option.
But my problem, there is ORM layer(either Hibernate or Eclipse Top Link) between the application and the database.
So the queries would be formed by the ORM layer and are not hand generated.
So i cannot use view.
There are 10000 of entities , so creating view for them would not be a nice option
is there a work around?

But is a pre built application.So creating views would be a difficult job.

Let me brief you my problem.
Actually the data created for my application is in TB per year.
So whenever the year is completed, the data is moved from main schema to another schema.
This is done to make the query faster.
But whenever the old data is required, it might require the data from both the schemas.
So i wanted a select query to be fired ob both schemas using select query.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points