| PARTITION HELP [message #242164] |
Thu, 31 May 2007 16:44  |
jinga
Messages: 116
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 #242200 is a reply to message #242164] |
Fri, 01 June 2007 00:51  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
