8 Replies Latest reply on Nov 15, 2012 4:39 AM by Anju Garg

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

      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.