Table Partition

Question

Table Partition

karthik M

One Orange Chip

Points: 29139
More actions
September 10, 2007 at 9:14 am

#153810

Hi Experts,
i have read an article about 'Table Partition' couple of days back. will it realy helpful in Performance tuning ?

Regards
Karthik

karthik

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I would not consider partitioning a tuning technique. What did the article say about partitioning that you have questions on.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Loner SSC-Insane Points: 21279 More actions · Answer 2

Usually only table partition is used in data warehouse. It can be vertical or horizontal partition. Most people use vertical partition. Basically it divided the table using one of the key, for example each year has its own table. So datatable1 has the data of Year 2000, datatable2 has the data of Year 2001 and so on. So each table size is small instead of creating a huge table of all years. Then create a view name datatable, it uses UNION ALL to combine all the tables together. The users will use the view and they do not need to know the table is partitioned.

The reason why it relates to tuning is when the users query the data warehouse and if most of the queries use current year, the view supposes will look at the current year table. This way the query will run faster.

Jeff Moden SSC Guru Points: 1004370 More actions · Answer 3

That helps, but better to have the partitions on different physical disks for real performance gains...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David McFarland SSChampion Points: 11815 More actions · Answer 4

It's sometimes used to make backups more manageable in a warehouse as well, as the partitions can reside in separate databases (or even separate servers, in which case it's a distributed partitioned view).

Be advised, without the proper contraints in place, it's just a view, not a partitioned view.

Note: I'm assuming you are speaking of partitioned views, not tables, since this is SQL Server 2000.