History Data

[Prabhu_7]

I have a history table which has data for 25 months. Each month no of customers will
keep on increasing, like ,

1st month - 1000
2nd month - 1250
3rd month - 1550 ..so on..
..
..
24th month(Jul) - 15300
25th month(Aug) - 15500

Note : Above Numbers are for example and in actual data I have millions of records.

Say, 2 most recent month's data are of July and August.
I'm interested only in new customers in august, which i can get by

Aug customers
MINUS
Jul customers

When i say aug minus july , due to huge volume of data it takes long to return.
I'm planning to create a view which will have only one month (July) data. Will this
improve performance ? I have a doubt, since its going to access same huge
data when i query the view also.

We cant delete the historical data, as we use them for other processes.
Please advise , how this situation can be handled.

Thanks

[A. Clay Stephenson]

A view may or may not improve your retrieval times; it all depends upon the efficiency of your query/indexing scheme. Ideally, you would like the same index which selects your rows to also be the index that orders the output --- in those cases the first chunk of rows which satify the where clause are returned almost immediately.

Plan B - a kludge: If this is a very common query the pre-select the monthly queries into tables and then do your monthly queries against these tables.

Plan C - Consider the use of partitioned tables.

[Prabhu_7]

Thanks. Let me look into Partition.

[Yogeeraj_1]

hi,

to add to the above replies, Partitioning wil be the way to go.

In general, range partitioning should come to your rescue, especially that it is useful when you have data that is logically segregated by some value(s). Time-based data immediately comes to the forefront as a classif example. Partition by "Sales Quarter". Partition by "Fiscal year". Partition by "Month". Range partitioning is able to take advantage of partition elimination in many cases, including use of exact equality and ranges - less than, greater than, between, and so on.

e.g.
CREATE TABLE partitioned (
timestamp date,
id int)
PARTITION BY RANGE(timestamp)
(
PARTITION jan_2003 VALUES LESS THAN (to_date('01/02/2003','dd/mm/yyyy')),
PARTITION feb_2003 VALUES LESS THAN (to_date('01/03/2003','dd/mm/yyyy')),
PARTITION mar_2003 VALUES LESS THAN (to_date('01/04/2003','dd/mm/yyyy')),
PARTITION apr_2003 VALUES LESS THAN (to_date('01/05/2003','dd/mm/yyyy')),
PARTITION may_2003 VALUES LESS THAN (to_date('01/06/2003','dd/mm/yyyy')),
PARTITION jun_2003 VALUES LESS THAN (to_date('01/07/2003','dd/mm/yyyy')),
PARTITION jul_2003 VALUES LESS THAN (to_date('01/08/2003','dd/mm/yyyy')),
PARTITION aug_2003 VALUES LESS THAN (to_date('01/09/2003','dd/mm/yyyy')),
PARTITION sep_2003 VALUES LESS THAN (to_date('01/10/2003','dd/mm/yyyy')),
PARTITION oct_2003 VALUES LESS THAN (to_date('01/11/2003','dd/mm/yyyy')),
PARTITION nov_2003 VALUES LESS THAN (to_date('01/12/2003','dd/mm/yyyy')),
PARTITION dec_2003 VALUES LESS THAN (to_date('01/01/2004','dd/mm/yyyy')),
PARTITION jan_2004 VALUES LESS THAN (to_date('01/02/2004','dd/mm/yyyy')),
PARTITION feb_2004 VALUES LESS THAN (to_date('01/03/2004','dd/mm/yyyy')),
PARTITION the_rest VALUES LESS THAN (maxvalue));

You may also wish to keep HISTORY tables and age the data out and archive it.
If you need any further assistance, please let us know.

hope this helps!

regards
Yogeeraj