Home > Oracle, Performance > Index on last 6 months data!

Index on last 6 months data!


I saw a question in a  forum – Can I create an index on 6 months or 1 year data out of a large table containing 10 years of data.  Application uses only last 6 months data, so no point keeping a whole  data in the index.  I am not sure how much relevant is the question – but this is possible.

I have created my test table as below.

 SQL> create table mytest as
 SELECT LEVEL id,'Testinggggggggggggggggggg' v1,(sysdate - level) v2SQL>
 FROM dual
 CONNECT BY LEVEL <= 25000;
Table created.

SQL> insert into mytest select * from mytest;
25000 rows created.

SQL> /
50000 rows created.

SQL> commit ;
Commit complete.

SQL> select count(*) from MYTEST;
 COUNT(*)
 ----------
 100000

SQL> select min(v2) from mytest;
 MIN(V2)
 -----------
 25-DEC-1943

Table contains data from  1943 to till date.  Out of this 50+ years of data, suppose business want to access only last 6 months data.

First of all, I have to create a DETERMINISTIC function to refer the SYSDATE in the function based indexes.

SQL> create or replace function get_sysdate(my_date date default SYSDATE)
return date
DETERMINISTIC
 2 is
 3 begin 4
 5 return trim(my_date); 6
 7 end;
 8 /
Function created.

Using the DETERMINISTIC function, create the index for the last 6 months data from SYSDATE.

SQL> create index i_v2 on 
mytest(decode(floor((get_sysdate - v2)/180),0,trim(v2),NULL));
Index created.

Now, the index with last 6 months data is in place.  Now use your index as below.  Any data older than six months will be marked as NULL in the index, virtually will not exist.

SQL> select * from mytest where 
decode(floor((get_sysdate - v2)/180),0,trim(v2),NULL)= trim(sysdate -1) ;

Execution Plan
----------------------------------------------------------
Plan hash value: 4141705261
--------------------------------------------------------------------------------------
| Id | Operation                     | Name    | Rows  | Bytes  | Cost (%CPU)    | Time |
-----------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT              |         | 904   | 49720  | 4 (0)          | 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID   | MYTEST  | 904   | 49720  | 4 (0)          | 00:00:01 |
|* 2 | INDEX RANGE SCAN              | I_V2    | 361   |        | 1 (0           | 00:00:01  |
--------------------------------------------------------------------------------------------------------

That’s it, your last 6 months indexed data is ready.

Advertisements
  1. June 6, 2012 at 1:50 am

    I suppose you would have to re-create the index from time to time to keep it up-to-date?

  2. June 6, 2012 at 6:05 am

    Yes correct, you need to “REBUILD ONLINE” (I want to say, recreate is unnecessary) to keep it up to date.

    Since I have created the index yesterday, I will have 181 days of data can be accessed through the index.

    SQL> select * from mytest where
    decode(floor((get_sysdate – v2)/180),0,trim(v2),NULL)= trim(sysdate -181) ;

    ID V1 V2
    ———- ————————- ———
    180 Testinggggggggggggggggggg 08-DEC-11
    180 Testinggggggggggggggggggg 08-DEC-11
    180 Testinggggggggggggggggggg 08-DEC-11
    180 Testinggggggggggggggggggg 08-DEC-11

    Execution Plan
    ———————————————————-
    Plan hash value: 4141705261

    ————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————–
    | 0 | SELECT STATEMENT | | 904 | 49720 | 4 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 904 | 49720 | 4 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | I_V2 | 361 | | 1 (0)| 00:00:01 |
    ————————————————————————————–

    Once the rebuild is done, I will be back to 180 days of data.

    SQL> alter index I_V2 rebuild online;

    Index altered.

    SQL> select * from mytest where decode(floor((get_sysdate – v2)/180),0,trim(v2),NULL)= trim(sysdate -181) ;

    no rows selected

    Execution Plan
    ———————————————————-
    Plan hash value: 4141705261

    ————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————–
    | 0 | SELECT STATEMENT | | 904 | 49720 | 4 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 904 | 49720 | 4 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | I_V2 | 361 | | 1 (0)| 00:00:01 |
    ————————————————————————————–

    Thanks
    Thomas

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: