Home » SQL & PL/SQL » SQL & PL/SQL » partitioning (oracle 9i)
partitioning [message #323412] Wed, 28 May 2008 07:43 Go to next message
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 #323423 is a reply to message #323412] Wed, 28 May 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. Yes
2. Maybe yes, maybe no, it depends

Why did you decide this?

Regards
Michel
Re: partitioning [message #323456 is a reply to message #323412] Wed, 28 May 2008 09:05 Go to previous messageGo to next message
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 #323458 is a reply to message #323456] Wed, 28 May 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It depends.
Do you purge? Do you archive? What are the queries? What are your disk? What are your cpu?...

Regards
Michel

[Updated on: Wed, 28 May 2008 09:07]

Report message to a moderator

Re: partitioning [message #323462 is a reply to message #323412] Wed, 28 May 2008 09:08 Go to previous messageGo to next message
nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi
Senior Member
depends on what?
Re: partitioning [message #323466 is a reply to message #323412] Wed, 28 May 2008 09:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: simple select from sys.user_tab_columns going all wrong in pl/sql
Next Topic: Execute any sql statement in package and return cursor
Goto Forum:
  


Current Time: Fri Feb 07 18:34:32 CST 2025