Performance Recommendations for Large Volume of Data

indexoraclepartitioningperformance

Oracle Table:

Fields: 55
Roughly 10 are Used for Querying
Single Record Length: 512 bytes
Records: 3-5 million records per day
Period of Time: 13 months to be available, > 13 months to be purged daily or monthly.

Questions:

What is the best mechanism for us to maintain this large volume of data and be able to query with good performance ( partitions ? Monthly tables ? )
Tests on indexing the data have not yielded good results, any suggestions on creating one or more indexes ?
Date format for these records is YYYYMMDDHHMMSSCSS
example: 20190710093054579
Current coding s forcing a SUBSTR or Like to be used in the query
example: SELECT * FROM TABLE WHERE SUBSTR(SYSDATE,1,8)='20190710'

How can we optimize the performance of querying perhaps for a whole day with other fields in the criteria ?

Is it possible to index this properly ? Or should the date and time be separated into 2 fields ?

We have observed positive results by using between 20190710000000000 and 2019071999999999 but performance is still problematic.

Best Answer

It's important to know what sort of queries you are running. Are they mostly based on the time of the record, or something other field? That will inform what sort of approach you should take here. Maybe your indexes should actually be multi-indexes for example.

What's your EXPLAIN statement say for your queries? That's really one of the best ways to see where exactly things are slowing down and attack it.

Edit: Sorry, I just saw the SUBSTR query, I believe you can make indexes based on substrings of a column. Have you done that? That IS different from just having an index on the column itself vs. having a index on the substring(1,8) of said column.

Related Solutions

SQL Server – Is Table Partitioning Improving Performance?

Using partitioning is only going to help your query performance if the partitioning scheme is built to serve your specific queries.

You're going to have to review your query patterns and see how they are accessing the table in order to identify the best approach. The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.

There are two factors that affect if partition elimination can occur and how well it will perform:

Partition Key - Partitioning can only occur on a single column and your query must include that column. For example, if your table is partitioned on date and your query uses that date column, then partition elimination should occur. However, if you don’t include the partition key within the query predicate, the engine can not perform elimination.
Granularity - If your partitions are to big, you won’t gain any benefit from elimination because it will still pull back more data than it needs to. However, make it to small and it becomes difficult to manage.

In many ways, partitioning is just like using any other index, with some added benefits. However, you don’t realize those benefits unless you're dealing with incredibly large tables. Personally, I don't even consider partitioning until my table is over 250 GB in size. Most of the time, well defined indexing will cover many of the use cases on tables smaller than that. Based on your description, you're not seeing huge data growth, so it could be that a properly index table will perform just fine for your table.

I would strongly recommend that you review whether partitioning is actually necessary to solve your issues. One would usually partition a very large table for the purpose of:

Distributing data between different types of disk so that more “active” data can be placed on faster, more expensive storage while less active data is placed on cheaper, slower storage. This is mostly a cost savings measure.
Assisting in index maintenance for extremely large tables. Since you can rebuild partitions individually, this can assist in keeping indexes properly maintained with minimal impact.
Leveraging partitioning for improved archival process. See sliding windows.

Postgresql – How to optimize an ordered date range query with several OR filters (for streaming)

Have you tried making 4 separate queries, and then using union all, so that you can get rid of the or statements?

Example:

select * from my_table
where date >= now() - '1 day'::interval
  and type2 = 'T2A' 
  and type1 = 'T1A'

union all
select * from my_table
where date >= now() - '1 day'::interval
  and type2 = 'T2B' 
  and type1 = 'T1B1'
...

Related Question