Oracle Database Discussions

Hi All

I have one table named 'MYBIGTABLE' with billions of rows and two BLOB columns. below is sample pattern of data in this table.

01-JAN-2000 1522098
.....
.....
.....
30-Nov-2009 1835573

I have millions of records in this table. Now the problem is that my application is giving timeouts for queries running on this perticular table. I am looking some kind of solution to this problem and i was thinking of creating a kind of cache for current months data in oracle so that if user at least generate reports for current month, he should not get time out. If he tries for some old month or may be with a bigger range, then its fine with the timeout but aleast he should not get this error for current months data. I am not sure how can i keep this thing in kind of buffer or cache. any idea if i can achieve this.

For other things, like is query tuned? are stats upto date? is eveything else fine? I assure you that everything is perfect. query is optimized to upmost but the problem is with the number of records query has to toggle through. Application has timeout value defined for 3 minutes and if result dont comes out in 3 minutes it raises a timeout error. Tuned query results data in 4 Minute and 20 seconds for current month. there fore, i was looking for some tuning mechanism using which i can keep this data in a pool (for current month).

One more thing i found about this is that this can be achieved by creating revese index. Any idea if this is the case? I have not tested this thing in development environment. but i will be doing this soon and will publish results here too.

MYBIGTABLE is range partitioned on the data column which is being refered by query. But still it returns result in around 4 minutes.

Oracle 10.2.0.4.0 (RAC)
Sun Solaris 10.

Thanks
aps

Edited by: aps on Dec 14, 2009 11:29 PM