partitioning [message #323412] |
Wed, 28 May 2008 07:43  |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Hi
I have decided to partition "transactions" table on "year" column with list partioning, so that data could be distributed yearly in a seprate partition.
I wanted to ask
1. "year" column is part of primary key. (year,branch_id,book,srno) columns are primary key. is it fine creating partition on column which is part of primary key?
2. will it effect performance? if 1st parition has 1 million and the 2nd has 1.5 million records.
Thanks
|
|
|
|
Re: partitioning [message #323456 is a reply to message #323412] |
Wed, 28 May 2008 09:05   |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
initialy (branch_id,book,srno) columns were primary key. In our company we start new books every year. so i created year column and made it part of primary key so every year each book could be started from 1.
what will you advice me? should i create list partition on "year" column or not?
|
|
|
|
|
Re: partitioning [message #323466 is a reply to message #323412] |
Wed, 28 May 2008 09:16   |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
i dont archive,(2 Ghz processor,1 Gb ram) is cpu configration.
what should i do? should i create another column which is not part of parimary key for creating partition
and which partioning method will you suggest please?
[Updated on: Wed, 28 May 2008 09:21] Report message to a moderator
|
|
|
Re: partitioning [message #323468 is a reply to message #323466] |
Wed, 28 May 2008 09:23   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What Michel is trying to find out is WHY you want to partition the table in the first place.
|
|
|
Re: partitioning [message #323779 is a reply to message #323412] |
Thu, 29 May 2008 07:37   |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
i want to parition the table because it will have million of records in the years to come so the queries would get slow.
Most of my queries are base on returning data of one year like
select * from transactions partition (&pyear)/ here "pyear" is parameter
that is why i want to partition the table
[Updated on: Thu, 29 May 2008 07:38] Report message to a moderator
|
|
|
Re: partitioning [message #323869 is a reply to message #323779] |
Thu, 29 May 2008 22:10  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If you do:
SELECT *
FROM transactions
WHERE year = &pyear it will work the same way - it will even partition prune.
Such queries would be improved by partitioning.
Queries that return a SMALL number of rows WITHOUT scanning on the year may suffer. To mitigate this, you may need a Global index.
Ross Leishman
|
|
|