This content has been marked as final. Show 8 replies
Let me brief you my problem.Partitioning (works with partitions, not schemas)
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.
requires Enterprise Edition and Partitioning option
Range partition on that table will solve your problem.
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.
queries will do partition pruning if the queries has predicates on date.
We have enterprise edition and we can implement partition.
But the data that our application generates is huge.
Sumwhere around 3-4TB of data per year.
So gradually with the time the data will increase.
Even if we create partition it wont help as all the partitions are located in same physical database.
To increase the size of database as the year progresses, we would need different schema or altogether a different database where we can shift the data.
Thats why i m stressing upon the select query in multiple schemas.
Can the size of enterprise edition increased as the data size increases?
Hope i am clear enough to mention my problem.
Edited by: stephen.b.fernandes on Nov 13, 2012 7:05 AM
Edited by: stephen.b.fernandes on Nov 13, 2012 7:07 AM
How far the business users will go back to run reports ? first findout the requirement from the business.
Partition by month (not by year) and keep only required months of data in the table and move old partitions to different database or schema as you wish.
You can use
-- Union in the query if schemas are different
- Interval partiotioning if schema can be same
as u said "keep only required months of data in the table and move old partitions to different database or schema as you wish."
if i move the partitions to different schema or database and if i want the data from the different schema. How do i achieve it?
Is their and easier way to get the data from the partitions which have been to different schemas?
as u said "Union in the query if schemas are different"
If i have 2 schemas out of which one contain new data and other contain old data.
But 90 % of the times the data will be fetched from the new data schema and only 10% of the case the old data or combination of both the data would be required.
But if we use union in it will always query both the schemas and that would be expensive.
Is there any work around?
U can use a combination of union and interval partitioning. In that case, it will not access the partitions which do not contain relevant data.