April 30, 2013 at 1:05 pm
Hi Experts,
Migrating warehouse from Oracle to SQL Server :
Status: In process
In Oracle : 6 Schemas
In SQL Server: Created a database with 6 Schemas.
-Every Schema has 3 Paritioned and 3 non partitioned tables.
-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.
-Only parent table has date column and non other table has date column for partitioning.
-all the 5 tables in each schema has primary key.
My Question is can I partition parent table with date column and child tables with Primary key column ?
If so please can someone advise the partitioning method for a parent table and child tables.
sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..
Thanks for all your time.
April 30, 2013 at 2:11 pm
Sqlism (4/30/2013)
Migrating warehouse from Oracle to SQL Server :Status: In process
In Oracle : 6 Schemas
In SQL Server: Created a database with 6 Schemas.
-Every Schema has 3 Paritioned and 3 non partitioned tables.
-Out of 3 partitioned tables one is parent table and all other 5 tables are child tables.
-Only parent table has date column and non other table has date column for partitioning.
-all the 5 tables in each schema has primary key.
Are those tables partitioned on the Oracle implementation?
If the answer is yes... how are they partitioned?
If the answer is no... why are you thinking on partitioning?
Sqlism (4/30/2013)
My Question is can I partition parent table with date column and child tables with Primary key column ?
Short answer is: Yes... followed by a question: Why? What are you trying to accomplish?
Sqlism (4/30/2013)
sometimes the partitioning column in parent table has abnormal dates like 2099,2088. How can i seperate those values. I listened that we can do by MAX and MIN dates while partitioning but not sure how to implement that..
How is the distribution of such 'abnormal" values? Either way, usually those are catched in a maxval partition.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 30, 2013 at 2:28 pm
Are those tables partitioned on the Oracle implementation?
If the answer is yes... how are they partitioned?
If the answer is no... why are you thinking on partitioning?
---Yes these table are partitioned in oracle. In Oracle all tables have date column and they are partitioned on that column. But in SQL Server we removed those columns.
--------------------------------------------------------
My Question is can I partition parent table with date column and child tables with Primary key column ?
Short answer is: Yes... followed by a question: Why? What are you trying to accomplish?
I am worried because it might cause performance issues.
-----------------------------------------------------
If i have data from 2010 to present. How should i plan partitioning with maxval and minval partition please advise.
Thanks for your time and reply.
April 30, 2013 at 2:35 pm
Sqlism (4/30/2013)
I am worried because it might cause performance issues.
Table partitioning is not about performance but administration.
In most cases a partitioning strategy is based on thinking how to make your life easier when archiving, purging and other processes like these are in the horizon.
No performance gain is achieved just by partitioning a table - a sound indexing strategy and well designed queries are the key for good performance.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 30, 2013 at 2:46 pm
Thanks Paul for your time.
If i have data for Eg from
2009
2010
2011
2012
2013
How should i plan the partitioning for unexpected data where the date is like 1988 or 2050
Please advise
April 30, 2013 at 2:59 pm
Sqlism (4/30/2013)
Thanks Paul for your time.If i have data for Eg from
2009
2010
2011
2012
2013
How should i plan the partitioning for unexpected data where the date is like 1988 or 2050
Create a partition that catches data with dates to the left of 2009/01/01 for th 1988 alike data and a partition that catches data to the right of the top year on your schema
http://www.databasejournal.com/features/mssql/partitioning-in-sql-server-part-3.html
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply