How to improve the performance of custom queries against tables containing hundreds of high power columns and millions of records?
In my case, I have a table with one indexed DATE SDATE column, one VARCHAR2 NE column and 750 numeric columns, most of which contain high power columns whose values ββare in the range 0 to 100 . The table is updated with almost 20000 new entries every hour. The queries to this table look like this:
SELECT * FROM TAB WHERE SDATE BETWEEN :SDATE AND :EDATE AND V1 > :V1 AND V3 < :V3
or
SELECT * FROM TAB WHERE SDATE BETWEEN :SDATE AND :EDATE AND NE = :NE AND V4 > :V4
and etc.
Until now, I have always advised users not to enter large interval dates in order to limit the number of records retrieved using the date index access path; however, from time to time it becomes necessary to indicate large intervals.
If V1, V2, ..., V750 were low power columns, I could use raster indexes. Unfortunately, this is not the case.
What advice on this? How do I solve this problem?
Thanks.
source share