Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Partitioning Scenario question. Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 1:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:27 PM
Points: 230, Visits: 954
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.


Post #1448205
Posted Tuesday, April 30, 2013 2:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1448231
Posted Tuesday, April 30, 2013 2:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:27 PM
Points: 230, Visits: 954
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.
Post #1448235
Posted Tuesday, April 30, 2013 2:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1448237
Posted Tuesday, April 30, 2013 2:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:27 PM
Points: 230, Visits: 954
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
Post #1448242
Posted Tuesday, April 30, 2013 2:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1448248
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse