March 1, 2006 at 11:39 am
I have a table 21 million records, 20GB.
Option1: I know I can split this table into 3 or 4 tables and use a CHECK constraint with a UNION ALL view to merge them bak together.
But can I do this, that will do the same thing.
Option2:Keep the large table intact. But create 3 views with the 'WITH CHECK OPTION' on where clause. Then UNION ALL theses views together.
I know option 1 allows me to place tables in different file groups ( dont know anything about that tho, how does that effect queries) for memory usage.
What are the pros and cons of either option ?
What does tables in different file groups do in the same database for queries and joins, etc ?
March 1, 2006 at 3:29 pm
There are several reasons you might want to partition a table, most are concerned with the manageability of the physical data and with performance gains. Typically, the partitions are placed in separate file groups on separate disks (perhaps even separate servers!) in order to spread the I/O burden and eliminate contention on the disk. Your first option would be a step in that direction.
As for your second option - why? I don't see where there would be any reason nor benefit to that approach.
March 1, 2006 at 6:33 pm
Wouldn't option two limit my WHERE clause runtime, as the view CHECK OPTION would limit how many records are scanned.
Main Table - Data for 2 years, 2004 and 2005
View ONE - Data Year 2004
View TWO - Data Year 2005
View UNION ALL of View ONE and View TWO.
A query searching data on YEAR 2005, would only search main table where year is 2005, and NOT read data under 2004 as the CHECK OPTION in View ONE wont let you ! Hence a quicker runtime and better numbers on the query execution plan.
Is this a correct understanding ?
March 2, 2006 at 9:22 am
Option 1 would be better than option 2.
Main reason. You will have Index for each table you create(split). And when you query the UNION ALL VIEW only the index & table on which you reterive or update the row is touched and the other index and table will never be scanned. Hope this clarifies.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply