Home » RDBMS Server » Performance Tuning » PARTITION HELP
PARTITION HELP [message #242164] Thu, 31 May 2007 16:44 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

For our business requirement, we have data stored in the tables in the following format.

Table Name : Test
idx
month
value


with idx & month being the primary keys.
month is stored as a varchar2(6) in the format 'YYYYMM'.

so the data looks like this...
idx      month         value
1        200606        10.0
1        200605        10.1
1        200604        10.2
1        200603        10.33
1        200602        10.41
1        200601        10.53
1        200512        10.21
1        200511        10.33
1        200510        10.41
1        200509        10.53
1        200508        10.21
1        200507        10.32
1        200506        10.41
1        200505        10.51
2        200606        10.0
2        200605        10.1
2        200604        10.2
2        200603        10.33
2        200602        10.41
2        200601        10.53
2        200512        10.21
2        200511        10.33
2        200510        10.41
2        200509        10.53
2        200508        10.21
2        200507        10.32
2        200506        10.41
2        200505        10.51



processing is done in the months N to N-5 (meaning 200606,200605,200604,200603,200602 ). There are around 20 other tables that have similar strucure for various business needs. Most of the where clauses(9o%) process the rows N to n5 months.


Select * from test
Where month in ('200606','200605','200604'); or 


like 

select * from test
where month = '200606';



N month is stored in another control table.After the processing for the month is over,N month is incremented to the next month. These table would grow each month by 100000 records.

What is the best way to partition this table so that the queries are faster.

Any help on this is greatly appreciated.




Re: PARTITION HELP [message #242165 is a reply to message #242164] Thu, 31 May 2007 16:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>with idx & month being the primary keys.
Please clarify.
There can be only 1 Primary Key on any table.
Stating "keys" confuses me.
Single PK on two columns?
If so, place index on MONTH as single column.
Re: PARTITION HELP [message #242167 is a reply to message #242164] Thu, 31 May 2007 17:03 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

PRIMARY KEY(IDX,MONTH)

Single pk on two columns.

Ananthi
Re: PARTITION HELP [message #242200 is a reply to message #242164] Fri, 01 June 2007 00:51 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Could you post explain plan for

Quote:
Select * from test
Where month in ('200606','200605','200604'); or

like

select * from test
where month = '200606';



Looks like query goes for FTS.

As anacedent said, Why dont you create a index on MONTH and see the performance, if the issue is with performance.

Brayan.
Re: PARTITION HELP [message #242230 is a reply to message #242200] Fri, 01 June 2007 03:12 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Ill give you the same reply as Michel gave me some week ago.

Quote:
Why partitioning this table?

Have a look at article Partition Decisions from Arup Nanda.


Read it before doing anything else. Wink



Previous Topic: automatic statspack
Next Topic: Performance issues with dbms_job
Goto Forum:
  


Current Time: Sun Dec 11 02:32:00 CST 2016

Total time taken to generate the page: 0.09346 seconds