Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

History Data

Prabhu_7
Frequent Advisor

History Data

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
3 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: History Data

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.
If it ain't broke, I can fix that.
Prabhu_7
Frequent Advisor

Re: History Data

Thanks. Let me look into Partition.
Yogeeraj_1
Honored Contributor

Re: History Data

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)