High Power Custom Column Queries

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.

+4
source share
2 answers

I assume you are stuck in design, so a few thoughts that I will probably look at are

1) use sections - if you have a markup option

2) use some triggers to denormalize (or normalize in this case) the query table, which is more optimized to use the query

3) take some pictures

4) see if there is a current table or a set of tables with day entries (or some suitable subset) and collapse them into a large table to store hsitory.

It depends on usage patterns and all the other limitations that the system has - it can help you get started if you have more detailed information, probably the best solution.

+1
source

I think inserts will be a big problem. You have an index on sdate that slows down inserts and speeds up selections. But coming back to your problems:

If users specify an interval that is large (say> 5%), it should have a table broken by sdate on a daily or weekly or monthly basis. Oracle Document Separation

(If you partition the table, be sure to also partition the index. And if you want to do this live, use exchange partition ).

Also, as a workaround, if you have a powerful machine, you can use parallel queries. Oracle Parallel docs

+1
source

Source: https://habr.com/ru/post/1436027/


All Articles